kedro.extras.datasets.pandas.sql_dataset.SQLTableDataSet

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

SQLTableDataSet modifies the save parameters and stores the data with no index. This is designed to make load and save methods symmetric.

Example usage for the YAML API:

shuttles_table_dataset:
  type: pandas.SQLTableDataSet
  credentials: db_credentials
  table_name: shuttles
  load_args:
    schema: dwschema
  save_args:
    schema: dwschema
    if_exists: replace

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 SQLTableDataSet
>>> import pandas as pd
>>>
>>> data = pd.DataFrame({"col1": [1, 2], "col2": [4, 5],
>>>                      "col3": [5, 6]})
>>> table_name = "table_a"
>>> credentials = {
>>>     "con": "postgresql://scott:tiger@localhost/test"
>>> }
>>> data_set = SQLTableDataSet(table_name=table_name,
>>>                            credentials=credentials)
>>>
>>> data_set.save(data)
>>> reloaded = data_set.load()
>>>
>>> assert data.equals(reloaded)