Help with use of SQLQueryDataSet

[meaningfromdata]

I have a sql table I am trying to load into a pandas dataframe as the first step of my Kedro data_processing pipeline. All I am trying to do at this point is read the table from the database and save it as a csv just to be sure I can read data in from the database in kedro and understand how to use the catalog and credentials. I can do this in pandas using pd.read_sql without issue but I can't seem to get it right using SQLQueryDataSet in kedro. I have added my connection string for the database to my credentials.yml and then created an entry for the SQL table/query in the catalog.yml as follows:

cohort_sql_query:
type: pandas.SQLQueryDataSet
credentials: my_credentials_for_db
sql: SELECT * FROM ..<table_with_cohort>

I have added a node to my data_processing pipeline that takes cohort_sql_query as the input and attempts to output a csv of the cohort, which is registered in the catalog.yml.

The catalog entry is:

cohort_csv:
type: pandas.CSVDataSet
filepath: data/02_intermediate/patient_cohort.csv

The node is simply:

def get_cohort_from_db(query_dataset):
cohort_df = query_dataset
return cohort_df

The entry in the pipeline is as follows:

node(
func=get_cohort_from_db
inputs="cohort_sql_query"
outputs= "cohort_csv"
name= "get_cohort_from_db_node"

However, when I try to run the pipeline I am getting a DataSetError that, in brief, states:
"Object 'SQLTableDataSet' cannot be loaded from 'kedro.extras.datasets.pandas' Please see the documentation on how to install relevant dependencies for kedro.extras.datasets.pandas.SQLTableDataSet"

I took a look at the documentation but it wasn't clear to me what more I needed to install. I am using a conda environment with kedro 0.17.6 and kedro-telemetry 0.1.3 installed.

Any help getting this very basic step working would be appreciated.

[datajoely]

Kedro itself doesn't include the SqlAlchemy library, you need to add it to your requirements definition/install explicitly:

pip install 'kedro[pandas.SQLQueryDataSet]'

See our documentation on installing optional dependencies here.

[meaningfromdata]

Yes, I copy and pasted your line and still got the error. I'm using pip 21.3.1 with Python 3.8.12 inside a conda environment on a Windows machine. I was able to pip install another package, so I don't think pip is broken. Is it possible to install these using conda install?

[datajoely]

I'm not sure what's going on exactly, it will be something with quotes - to unblock this particular issue you can declare the dependencies explicitly:

• pandas>=0.24
• SQLAlchemy~=1.2
• xlrd~=1.0
• xlsxwriter~=1.0

(this is how it works behind the scenes)

[meaningfromdata]

Ok, finally got the installs working and I think the issue was my Python version. The following fixed the problem:
i.) create new conda env with Python==3.7
ii.) activate new conda env
iii.) pip install kedro into new env
iv.) kedro new to create new project
v.) pip install -r requirements.txt
vi.) pip install "kedro[pandas]"==0.17.6

No problems with install but will see how the environment runs (kedro run) tomorrow.
Thanks for your help. I'll likely be a regular around here as I get the hang of kedro.

[datajoely]

Awesome - glad we got to the bottom of that!

[meaningfromdata]

Ok, confirming I was able to load sql table to pandas df after testing pipeline with "kedro run". However, just making a note here that it was also necessary to add both sqlalchemy and pyodbc to requirements.txt file.