kedro.io.SQLQueryDataSet¶

class kedro.io.SQLQueryDataSet(sql, credentials, load_args=None)[source]¶

Bases: kedro.io.core.AbstractDataSet

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:

from kedro.io 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()

Methods

`SQLQueryDataSet.__init__`(sql, credentials[, …])	Creates a new `SQLQueryDataSet`.
`SQLQueryDataSet.exists`()	Checks whether a data set’s output already exists by calling the provided _exists() method.
`SQLQueryDataSet.from_config`(name, config[, …])	Create a data set instance using the configuration provided.
`SQLQueryDataSet.get_last_load_version`()	Versioned datasets should override this property to return last loaded version
`SQLQueryDataSet.get_last_save_version`()	Versioned datasets should override this property to return last saved version.
`SQLQueryDataSet.load`()	Loads data by delegation to the provided load method.
`SQLQueryDataSet.release`()	Release any cached data.
`SQLQueryDataSet.save`(data)	Saves data by delegation to the provided save method.

__init__(sql, credentials, load_args=None)[source]¶

Creates a new SQLQueryDataSet.

Parameters:	sql (`str`) – The sql query statement. credentials (`Dict`[`str`, `Any`]) – A dictionary with a `SQLAlchemy` connection string. Users are supposed to provide the connection string ‘con’ through credentials. It overwrites con parameter in `load_args` and `save_args` in case it is provided. To find all supported connection string formats, see here: https://docs.sqlalchemy.org/en/13/core/engines.html#database-urls load_args (`Optional`[`Dict`[`str`, `Any`]]) – Provided to underlying pandas `read_sql_query` function along with the connection string. To find all supported arguments, see here: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html To find all supported connection string formats, see here: https://docs.sqlalchemy.org/en/13/core/engines.html#database-urls
Raises:	`DataSetError` – When either `sql` or `con` parameters is emtpy.
Return type:	`None`

exists()¶

Checks whether a data set’s output already exists by calling the provided _exists() method.

Return type:	`bool`
Returns:	Flag indicating whether the output already exists.
Raises:	`DataSetError` – when underlying exists method raises error.

classmethod from_config(name, config, load_version=None, save_version=None)¶

Create a data set instance using the configuration provided.

Parameters:	name (`str`) – Data set name. config (`Dict`[`str`, `Any`]) – Data set config dictionary. load_version (`Optional`[`str`]) – Version string to be used for `load` operation if the data set is versioned. Has no effect on the data set if versioning was not enabled. save_version (`Optional`[`str`]) – Version string to be used for `save` operation if the data set is versioned. Has no effect on the data set if versioning was not enabled.
Return type:	`AbstractDataSet`
Returns:	An instance of an `AbstractDataSet` subclass.
Raises:	`DataSetError` – When the function fails to create the data set from its config.

get_last_load_version()¶

Versioned datasets should override this property to return last loaded version

Return type:	`Optional`[`str`]

get_last_save_version()¶

Versioned datasets should override this property to return last saved version.

Return type:	`Optional`[`str`]

load()¶

Loads data by delegation to the provided load method.

Return type:	`Any`
Returns:	Data returned by the provided load method.
Raises:	`DataSetError` – When underlying load method raises error.

release()¶

Release any cached data.

Raises:	`DataSetError` – when underlying exists method raises error.
Return type:	`None`

save(data)¶

Saves data by delegation to the provided save method.

Parameters:	data (`Any`) – the value to be saved by provided save method.
Raises:	`DataSetError` – when underlying save method raises error.
Return type:	`None`