Skip to content
New: Star-import, signal unraveling, Python 3.12 support, and more

#
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

- Analyzable
- AttrResolverMixin
- BaseDataMixin
- Cacheable
- Chainable
- Comparable
- Configured
- CustomData
- DBData
- Data
- DataWithFeatures
- ExtPandasIndexer
- HasSettings
- IndexingBase
- LocalData
- OHLCDataMixin
- PandasIndexer
- Pickleable
- PlotsBuilderMixin
- Prettified
- StatsBuilderMixin
- Wrapping

Inherited members

- AttrResolverMixin.deep_getattr()
- AttrResolverMixin.post_resolve_attr()
- AttrResolverMixin.pre_resolve_attr()
- AttrResolverMixin.resolve_attr()
- AttrResolverMixin.resolve_shortcut_attr()
- BaseDataMixin.assert_has_feature()
- BaseDataMixin.assert_has_symbol()
- BaseDataMixin.get_feature()
- BaseDataMixin.get_feature_idx()
- BaseDataMixin.get_symbol()
- BaseDataMixin.get_symbol_idx()
- BaseDataMixin.has_feature()
- BaseDataMixin.has_multiple_keys()
- BaseDataMixin.has_symbol()
- BaseDataMixin.prepare_key()
- BaseDataMixin.select_features()
- BaseDataMixin.select_symbols()
- Cacheable.get_ca_setup()
- Chainable.pipe()
- Configured.copy()
- Configured.equals()
- Configured.get_writeable_attrs()
- Configured.prettify()
- Configured.resolve_merge_kwargs()
- Configured.update_config()
- CustomData.get_custom_setting()
- CustomData.get_custom_settings()
- CustomData.has_custom_setting()
- CustomData.has_custom_settings()
- CustomData.key_match()
- CustomData.resolve_custom_setting()
- CustomData.set_custom_settings()
- DBData.classes
- DBData.close
- DBData.cls_dir
- DBData.column_only_select
- DBData.column_type
- DBData.columns
- DBData.config
- DBData.daily_log_returns
- DBData.daily_returns
- DBData.data
- DBData.delisted
- DBData.dict_type
- DBData.drawdowns
- DBData.feature_classes
- DBData.feature_config
- DBData.feature_oriented
- DBData.feature_wrapper
- DBData.features
- DBData.fetch_kwargs
- DBData.freq
- DBData.group_select
- DBData.has_ohlc
- DBData.has_ohlcv
- DBData.high
- DBData.hlc3
- DBData.iloc
- DBData.index
- DBData.indexing_kwargs
- DBData.key_wrapper
- DBData.keys
- DBData.last_index
- DBData.level_name
- DBData.loc
- DBData.log_returns
- DBData.low
- DBData.missing_columns
- DBData.missing_index
- DBData.ndim
- DBData.ohlc
- DBData.ohlc4
- DBData.ohlcv
- DBData.open
- DBData.plots_defaults
- DBData.range_only_select
- DBData.rec_state
- DBData.returned_kwargs
- DBData.returns
- DBData.returns_acc
- DBData.self_aliases
- DBData.shape
- DBData.shape_2d
- DBData.single_feature
- DBData.single_key
- DBData.single_symbol
- DBData.stats_defaults
- DBData.symbol_classes
- DBData.symbol_oriented
- DBData.symbol_wrapper
- DBData.symbols
- DBData.trade_count
- DBData.tz_convert
- DBData.tz_localize
- DBData.volume
- DBData.vwap
- DBData.wrapper
- DBData.xloc
- Data.add()
- Data.add_column()
- Data.add_feature()
- Data.add_key()
- Data.add_symbol()
- Data.align_columns()
- Data.align_data()
- Data.align_index()
- Data.build_feature_config_doc()
- Data.check_dict_type()
- Data.column_stack()
- Data.concat()
- Data.dropna()
- Data.fetch()
- Data.fix_data_dict_type()
- Data.fix_dict_types_in_kwargs()
- Data.from_csv()
- Data.from_data()
- Data.from_data_str()
- Data.from_duckdb()
- Data.from_feather()
- Data.from_hdf()
- Data.from_parquet()
- Data.from_sql()
- Data.get()
- Data.get_base_setting()
- Data.get_base_settings()
- Data.get_feature_wrapper()
- Data.get_key_wrapper()
- Data.get_keys()
- Data.get_symbol_wrapper()
- Data.has_base_setting()
- Data.has_base_settings()
- Data.has_key_dict()
- Data.indexing_func()
- Data.invert()
- Data.invert_data()
- Data.merge()
- Data.modify_state()
- Data.override_feature_config_doc()
- Data.plot()
- Data.prepare_dt()
- Data.prepare_dt_column()
- Data.prepare_dt_index()
- Data.prepare_tzaware_index()
- Data.realign()
- Data.remove()
- Data.remove_columns()
- Data.remove_features()
- Data.remove_keys()
- Data.remove_symbols()
- Data.rename()
- Data.rename_columns()
- Data.rename_features()
- Data.rename_in_dict()
- Data.rename_keys()
- Data.rename_symbols()
- Data.replace()
- Data.resample()
- Data.resolve_base_setting()
- Data.resolve_columns()
- Data.resolve_features()
- Data.resolve_key_arg()
- Data.resolve_keys()
- Data.resolve_keys_meta()
- Data.resolve_symbols()
- Data.row_stack()
- Data.run()
- Data.select()
- Data.select_classes()
- Data.select_columns()
- Data.select_delisted()
- Data.select_feature_from_dict()
- Data.select_feature_idxs()
- Data.select_feature_kwargs()
- Data.select_fetch_kwargs()
- Data.select_from_dict()
- Data.select_key_from_dict()
- Data.select_key_kwargs()
- Data.select_keys()
- Data.select_last_index()
- Data.select_returned_kwargs()
- Data.select_run_func_args()
- Data.select_run_func_kwargs()
- Data.select_symbol_from_dict()
- Data.select_symbol_idxs()
- Data.select_symbol_kwargs()
- Data.set_base_settings()
- Data.sql()
- Data.switch_class()
- Data.to_csv()
- Data.to_duckdb()
- Data.to_feather()
- Data.to_feature_oriented()
- Data.to_hdf()
- Data.to_parquet()
- Data.to_sql()
- Data.to_symbol_oriented()
- Data.transform()
- Data.try_fetch_feature()
- Data.try_fetch_symbol()
- Data.try_run()
- Data.try_update_feature()
- Data.try_update_symbol()
- Data.update()
- Data.update_classes()
- Data.update_fetch_kwargs()
- Data.update_returned_kwargs()
- Data.use_feature_config_of()
- HasSettings.get_path_setting()
- HasSettings.get_path_settings()
- HasSettings.get_setting()
- HasSettings.get_settings()
- HasSettings.has_path_setting()
- HasSettings.has_path_settings()
- HasSettings.has_setting()
- HasSettings.has_settings()
- HasSettings.reset_settings()
- HasSettings.resolve_setting()
- HasSettings.resolve_settings_paths()
- HasSettings.set_settings()
- IndexingBase.indexing_setter_func()
- OHLCDataMixin.get_daily_log_returns()
- OHLCDataMixin.get_daily_returns()
- OHLCDataMixin.get_drawdowns()
- OHLCDataMixin.get_log_returns()
- OHLCDataMixin.get_returns()
- OHLCDataMixin.get_returns_acc()
- PandasIndexer.xs()
- Pickleable.decode_config()
- Pickleable.decode_config_node()
- Pickleable.dumps()
- Pickleable.encode_config()
- Pickleable.encode_config_node()
- Pickleable.file_exists()
- Pickleable.getsize()
- Pickleable.load()
- Pickleable.loads()
- Pickleable.resolve_file_path()
- Pickleable.save()
- PlotsBuilderMixin.build_subplots_doc()
- PlotsBuilderMixin.override_subplots_doc()
- PlotsBuilderMixin.plots()
- StatsBuilderMixin.build_metrics_doc()
- StatsBuilderMixin.override_metrics_doc()
- StatsBuilderMixin.stats()
- Wrapping.regroup()
- Wrapping.resolve_column_stack_kwargs()
- Wrapping.resolve_row_stack_kwargs()
- Wrapping.resolve_self()
- Wrapping.resolve_stack_kwargs()
- Wrapping.select_col()
- Wrapping.select_col_from_obj()
- Wrapping.split()

### 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,
        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,
        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().

#### Cookie consent

We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better.

- Google Analytics
- GitHub

Manage settings