kedro.extras.datasets.pandas.SQLQueryDataSet¶
-
class
kedro.extras.datasets.pandas.
SQLQueryDataSet
(sql, credentials, load_args=None)[source]¶ Bases:
kedro.io.core.AbstractDataSet
SQLQueryDataSet
loads data from a provided SQL query. It usespandas.DataFrame
internally, so it supports all allowed pandas options onread_sql_query
. Since Pandas uses SQLAlchemy behind the scenes, when instantiatingSQLQueryDataSet
one needs to pass a compatible connection string either incredentials
(see the example code snippet below) or inload_args
. Connection string formats supported by SQLAlchemy can be found here: https://docs.sqlalchemy.org/en/13/core/engines.html#database-urlsIt 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.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()
Attributes
Methods
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.
Loads data by delegation to the provided load method.
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 aSQLAlchemy
connection string. Users are supposed to provide the connection string ‘con’ through credentials. It overwrites con parameter inload_args
andsave_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-urlsload_args (
Optional
[Dict
[str
,Any
]]) – Provided to underlying pandasread_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
orcon
parameters is emtpy.
-
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 forload
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 forsave
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.
-
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 release 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
-