Skip to content

sql module

Module with SQLData.

SQLData class

SQLData(
    wrapper,
    data,
    single_key=True,
    classes=None,
    level_name=None,
    fetch_kwargs=None,
    returned_kwargs=None,
    last_index=None,
    delisted=None,
    tz_localize=None,
    tz_convert=None,
    missing_index=None,
    missing_columns=None,
    **kwargs
)

Data class for fetching data from a database using SQLAlchemy.

See https://www.sqlalchemy.org/ for the SQLAlchemy's API.

See https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html for the read method.

See SQLData.pull() and SQLData.fetch_key() for arguments.

Usage

  • Set up the engine settings globally (optional):
>>> from vectorbtpro import *

>>> vbt.SQLData.set_engine_settings(
...     engine_name="postgresql",
...     populate_=True,
...     engine="postgresql+psycopg2://...",
...     engine_config=dict(),
...     schema="public"
... )
  • Pull tables:
>>> data = vbt.SQLData.pull(
...     ["TABLE1", "TABLE2"],
...     engine="postgresql",
...     start="2020-01-01",
...     end="2021-01-01"
... )
  • Pull queries:
>>> data = vbt.SQLData.pull(
...     ["SYMBOL1", "SYMBOL2"],
...     query=vbt.key_dict({
...         "SYMBOL1": "SELECT * FROM TABLE1",
...         "SYMBOL2": "SELECT * FROM TABLE2"
...     }),
...     engine="postgresql"
... )

Superclasses

Inherited members

create_schema class method

SQLData.create_schema(
    schema,
    engine=None,
    engine_name=None,
    engine_config=None
)

Create a schema if it doesn't exist yet.

fetch_feature class method

SQLData.fetch_feature(
    feature,
    **kwargs
)

Fetch the table of a feature.

Uses SQLData.fetch_key().

fetch_key class method

SQLData.fetch_key(
    key,
    table=None,
    schema=None,
    query=None,
    engine=None,
    engine_name=None,
    engine_config=None,
    dispose_engine=None,
    start=None,
    end=None,
    align_dates=None,
    parse_dates=None,
    to_utc=None,
    tz=None,
    start_row=None,
    end_row=None,
    keep_row_number=None,
    row_number_column=None,
    index_col=None,
    columns=None,
    dtype=None,
    chunksize=None,
    chunk_func=None,
    squeeze=None,
    **read_sql_kwargs
)

Fetch a feature or symbol from a SQL database.

Can use a table name (which defaults to the key) or a custom query.

Args

key : str

Feature or symbol.

If table and query are both None, becomes the table name.

Key can be in the SCHEMA:TABLE format, in this case schema argument will be ignored.

table : str or Table

Table name or actual object.

Cannot be used together with query.

schema : str

Schema.

Cannot be used together with query.

query : str or Selectable

Custom query.

Cannot be used together with table and schema.

engine : str or object
See SQLData.resolve_engine().
engine_name : str
See SQLData.resolve_engine().
engine_config : dict
See SQLData.resolve_engine().
dispose_engine : bool
See SQLData.resolve_engine().
start : any

Start datetime (if datetime index) or any other start value.

Will parse with to_timestamp() if align_dates is True and the index is a datetime index. Otherwise, you must ensure the correct type is provided.

If the index is a multi-index, start value must be a tuple.

Cannot be used together with query. Include the condition into the query.

end : any

End datetime (if datetime index) or any other end value.

Will parse with to_timestamp() if align_dates is True and the index is a datetime index. Otherwise, you must ensure the correct type is provided.

If the index is a multi-index, end value must be a tuple.

Cannot be used together with query. Include the condition into the query.

align_dates : bool

Whether to align start and end to the timezone of the index.

Will pull one row (using LIMIT 1) and use Data.prepare_dt() to get the index.

parse_dates : bool, list, or dict

Whether to parse dates and how to do it.

If query is not used, will get mapped into column names. Otherwise, usage of integers is not allowed and column names directly must be used. If enabled, will also try to parse the datetime columns that couldn't be parsed by Pandas after the object has been fetched.

For dict format, see pd.read_sql_query.

to_utc : bool, str, or sequence of str
See Data.prepare_dt().
tz : any

Timezone.

See to_timezone().

start_row : int

Start row.

Table must contain the column defined in row_number_column.

Cannot be used together with query. Include the condition into the query.

end_row : int

End row.

Table must contain the column defined in row_number_column.

Cannot be used together with query. Include the condition into the query.

keep_row_number : bool
Whether to return the column defined in row_number_column.
row_number_column : str
Name of the column with row numbers.
index_col : int, str, or list

One or more columns that should become the index.

If query is not used, will get mapped into column names. Otherwise, usage of integers is not allowed and column names directly must be used.

columns : int, str, or list

One or more columns to select.

Will get mapped into column names. Cannot be used together with query.

dtype : dtype_like or dict

Data type of each column.

If query is not used, will get mapped into column names. Otherwise, usage of integers is not allowed and column names directly must be used.

For dict format, see pd.read_sql_query.

chunksize : int
See pd.read_sql_query.
chunk_func : callable

Function to select and concatenate chunks from Iterator.

Gets called only if chunksize is set.

squeeze : int
Whether to squeeze a DataFrame with one column into a Series.
**read_sql_kwargs
Other keyword arguments passed to pd.read_sql_query.

See https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html for other arguments.

For defaults, see custom.sql in data. Global settings can be provided per engine name using the engines dictionary.

fetch_symbol class method

SQLData.fetch_symbol(
    symbol,
    **kwargs
)

Fetch the table for a symbol.

Uses SQLData.fetch_key().

get_engine_setting class method

SQLData.get_engine_setting(
    *args,
    engine_name=None,
    **kwargs
)

CustomData.get_custom_setting() with sub_path=engine_name.

get_engine_settings class method

SQLData.get_engine_settings(
    *args,
    engine_name=None,
    **kwargs
)

CustomData.get_custom_settings() with sub_path=engine_name.

get_last_row_number class method

SQLData.get_last_row_number(
    table,
    schema=None,
    row_number_column=None,
    engine=None,
    engine_name=None,
    engine_config=None
)

Get last row number.

get_table_relation class method

SQLData.get_table_relation(
    table,
    schema=None,
    engine=None,
    engine_name=None,
    engine_config=None
)

Get table relation.

has_engine_setting class method

SQLData.has_engine_setting(
    *args,
    engine_name=None,
    **kwargs
)

CustomData.has_custom_setting() with sub_path=engine_name.

has_engine_settings class method

SQLData.has_engine_settings(
    *args,
    engine_name=None,
    **kwargs
)

CustomData.has_custom_settings() with sub_path=engine_name.

has_schema class method

SQLData.has_schema(
    schema,
    engine=None,
    engine_name=None,
    engine_config=None
)

Check whether the database has a schema.

has_table class method

SQLData.has_table(
    table,
    schema=None,
    engine=None,
    engine_name=None,
    engine_config=None
)

Check whether the database has a table.

list_schemas class method

SQLData.list_schemas(
    pattern=None,
    use_regex=False,
    sort=True,
    engine=None,
    engine_name=None,
    engine_config=None,
    dispose_engine=None,
    **kwargs
)

List all schemas.

Uses CustomData.key_match() to check each symbol against pattern.

Keyword arguments **kwargs are passed to inspector.get_schema_names.

If dispose_engine is None, disposes the engine if it wasn't provided.

list_tables class method

SQLData.list_tables(
    *,
    schema_pattern=None,
    table_pattern=None,
    use_regex=False,
    sort=True,
    schema=None,
    incl_views=True,
    engine=None,
    engine_name=None,
    engine_config=None,
    dispose_engine=None,
    **kwargs
)

List all tables and views.

If schema is None, searches for all schema names in the database and prefixes each table with the respective schema name (unless there's only one schema "main"). If schema is False, sets the schema to None. If schema is provided, returns the tables corresponding to this schema without a prefix.

Uses CustomData.key_match() to check each schema against schema_pattern and each table against table_pattern.

Keyword arguments **kwargs are passed to inspector.get_table_names.

If dispose_engine is None, disposes the engine if it wasn't provided.

pull class method

SQLData.pull(
    keys=None,
    *,
    keys_are_features=None,
    features=None,
    symbols=None,
    schema=None,
    list_tables_kwargs=None,
    engine=None,
    engine_name=None,
    engine_config=None,
    dispose_engine=None,
    share_engine=None,
    **kwargs
)

Override Data.pull() to resolve and share the engine among the keys and use the table names available in the database in case no keys were provided.

resolve_engine class method

SQLData.resolve_engine(
    engine=None,
    engine_name=None,
    return_meta=False,
    **engine_config
)

Resolve the engine.

Argument engine can be

1) an object of the type sqlalchemy.engine.base.Engine, 2) a URL of the engine as a string, which will be used to create an engine with sqlalchemy.engine.create.create_engine and engine_config passed as keyword arguments (you should not include url in the engine_config), or 3) an engine name, which is the name of a sub-config with engine settings under custom.sql.engines in data. Such a sub-config can then contain the actual engine as an object or a URL.

Argument engine_name can be provided instead of engine, or also together with engine to pull other settings from a sub-config. URLs can also be used as engine names, but not the other way around.

resolve_engine_setting class method

SQLData.resolve_engine_setting(
    *args,
    engine_name=None,
    **kwargs
)

CustomData.resolve_custom_setting() with sub_path=engine_name.

set_engine_settings class method

SQLData.set_engine_settings(
    *args,
    engine_name=None,
    **kwargs
)

CustomData.set_custom_settings() with sub_path=engine_name.

update_feature method

SQLData.update_feature(
    feature,
    **kwargs
)

Update data of a feature.

Uses SQLData.update_key().

update_key method

SQLData.update_key(
    key,
    from_last_row=None,
    from_last_index=None,
    **kwargs
)

Update data of a feature or symbol.

update_symbol method

SQLData.update_symbol(
    symbol,
    **kwargs
)

Update data for a symbol.

Uses SQLData.update_key().