kedro.extras.datasets.pandas.sql_dataset.SQLQueryDataSet

SQLQueryDataSet loads data from a provided SQL query. It uses pandas.DataFrame internally, so it supports all allowed pandas options on read_sql_query. Since Pandas uses SQLAlchemy behind the scenes, when instantiating SQLQueryDataSet one needs to pass a compatible connection string either in credentials (see the example code snippet below) or in load_args. Connection string formats supported by SQLAlchemy can be found here: https://docs.sqlalchemy.org/en/13/core/engines.html#database-urls

It does not support save method so it is a read only data set. To save data to a SQL server use SQLTableDataSet.

Example usage for the YAML API:

shuttle_id_dataset:
  type: pandas.SQLQueryDataSet
  sql: "select shuttle, shuttle_id from spaceflights.shuttles;"
  credentials: db_credentials

Advanced example using the stream_results and chunksize options to reduce memory usage:

shuttle_id_dataset:
  type: pandas.SQLQueryDataSet
  sql: "select shuttle, shuttle_id from spaceflights.shuttles;"
  credentials: db_credentials
  execution_options:
    stream_results: true
  load_args:
    chunksize: 1000

Sample database credentials entry in credentials.yml:

db_credentials:
  con: postgresql://scott:tiger@localhost/test

Example usage for the Python API:

>>> from kedro.extras.datasets.pandas import SQLQueryDataSet
>>> import pandas as pd
>>>
>>> data = pd.DataFrame({"col1": [1, 2], "col2": [4, 5],
>>>                      "col3": [5, 6]})
>>> sql = "SELECT * FROM table_a"
>>> credentials = {
>>>     "con": "postgresql://scott:tiger@localhost/test"
>>> }
>>> data_set = SQLQueryDataSet(sql=sql,
>>>                            credentials=credentials)
>>>
>>> sql_data = data_set.load()