API Reference

Client

class timescale_access.client.TimescaleAccess(db_url)[source]

Bases: object

Convenience wrapper around database operations using SQLAlchemy.

Parameters:: db_url (str)

check_connection()[source]

Test whether a connection to the database can be established.

Returns:: True if the connection check succeeds, False otherwise.
Return type:: bool

dispose_connection()[source]

Explicitly dispose the underlying SQLAlchemy engine and free resources.

Return type:: None

drop_table(schema_name, table_name)[source]

Drop a table in the given schema.

Parameters:

schema_name (str) – Schema name.
table_name (str) – Name of the table to drop.

Return type:

None

ensure_schema_exists(schema_name)[source]

Create the given schema if it does not already exist.

Parameters:: schema_name (str) – Name of the schema to ensure.
Return type:: None

get_active_connections()[source]

Return information about active database connections.

Returns:: List of active connections with database name, user and client IP.
Return type:: List[dict]

get_column_names(schema_name, table_name)[source]

Return all column names for a given table.

Parameters:

schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

List of column names.

Return type:

List[str]

get_databases()[source]

Return all non-template databases in the PostgreSQL instance.

Returns:: List of database names.
Return type:: List[str]

get_distinct_values(schema_name, table_name, column_name)[source]

Return all distinct values of a column in a table.

Parameters:

schema_name (str) – Schema name.
table_name (str) – Table name.
column_name (str) – Column name.

Returns:

List of distinct values in the column.

Return type:

List[str]

get_duplicate_rows(schema_name, table_name)[source]

Return all duplicate rows where (instrument_name, trade_seq) occurs multiple times.

Parameters:

schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

DataFrame containing all duplicate rows.

Return type:

pd.DataFrame

get_existing_timestamps(schema_name, table_name, column='timestamp')[source]

Return a sorted list of distinct timestamps from a specific column.

Parameters:

schema_name (str) – Schema name.
table_name (str) – Table name.
column (str, optional) – Column name to query (default: "timestamp").

Returns:

Sorted list of timestamps.

Return type:

List[datetime]

get_hypertable_size(schema_name, table_name)[source]

Return the total size of a TimescaleDB hypertable as a formatted string (for example "123 MB").

The implementation: 1. Retrieves the internal hypertable ID from _timescaledb_catalog.hypertable. 2. Summarizes the size of all chunks in _timescaledb_internal.

Parameters:

schema_name (str) – Schema name of the hypertable (for example "raw_data").
table_name (str) – Hypertable name (for example "btc_weekly").

Returns:

Total size formatted by pg_size_pretty (for example "512 MB", "3 GB").

Return type:

str

Raises:

ValueError – If the hypertable cannot be found.

get_indexes(schema_name, table_name)[source]

Return all indexes for a given table.

Parameters:

schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

List of index metadata dictionaries.

Return type:

List[dict]

get_missing_trade_seq(schema_name, table_name)[source]

Return all expected but missing trade_seq values per instrument_name.

Parameters:

schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

Rows describing missing sequence values per instrument.

Return type:

pd.DataFrame

get_nonconsecutive_trade_seq(schema_name, table_name)[source]

Return all trade_seq rows where the sequence is not consecutive.

Parameters:

schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

Rows with gaps in trade_seq per instrument.

Return type:

pd.DataFrame

get_null_summary(schema_name, table_name)[source]

Create (if necessary) and invoke a function that summarizes NULL values in a table.

The created function is named according to the pattern: check_nulls_in_{schema_name}_{table_name}.

Parameters:

schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

Summary of NULL counts per column and instrument name.

Return type:

pd.DataFrame

get_role_memberships()[source]

Return all role memberships in the database.

Returns:: List of mappings from member to role.
Return type:: List[dict]

get_roles()[source]

Return all roles and their privileges.

Returns:: List of role metadata, including superuser and createdb flags.
Return type:: List[dict]

get_row_count(schema_name, table_name)[source]

Return the number of rows in a table.

Parameters:

schema_name (str) – Schema name (for example "raw_data").
table_name (str) – Table name (for example "btc_weekly").

Returns:

Number of rows in the table.

Return type:

int

get_schemas()[source]

Return a list of all user-defined schemas.

Returns:: List of schema names.
Return type:: List[str]

get_table(schema_name, table_name, filters=None)[source]

Load a table as a pandas DataFrame with optional filters.

Parameters:

schema_name (str) – Schema name.
table_name (str) – Table name.
filters (Optional[Dict[str, Any]], optional) –
Filter specification for the WHERE clause. .. rubric:: Examples
- Single/multiple values: {"instrument_name": ["BTC-14MAR25", "ETH-14MAR25"]}
- Range filter: {"trade_seq": {"between": (100, 200)}}

Returns:

Filtered table contents.

Return type:

pd.DataFrame

get_table_names(schema_name)[source]

Return all table names in the given schema.

Parameters:: schema_name (str) – Schema name.
Returns:: List of table names.
Return type:: List[str]

insert_hypertable(schema_name, table_name, df, index=False, chunksize=500, time_column='timestamp')[source]

Insert a pandas DataFrame into a TimescaleDB hypertable.

This method is tailored to time-series data. If the target table does not exist, it is automatically created as a TimescaleDB hypertable. The time column is set to "timestamp" by default.

Parameters:

schema_name (str) – Name of the target schema.
table_name (str) – Name of the target table.
df (pd.DataFrame) – DataFrame to insert. Must contain a time column.
index (bool, optional) – Whether to persist the pandas index (default: False).
chunksize (int, optional) – Number of rows per batch insert (default: 500).
time_column (str, optional) – Name of the time column in the DataFrame (default: "timestamp").

Raises:

ValueError – If the given time column is not present in df.
RuntimeError – If insertion or hypertable creation fails.

Return type:

None

insert_hypertable_on_conflict(schema_name, table_name, df, time_column='timestamp')[source]

Insert a DataFrame into a TimescaleDB hypertable and avoid duplicates via ON CONFLICT.

The function creates the table and columns as needed and ensures a UNIQUE index on (instrument_name, trade_seq, timestamp). All index columns must be present in the DataFrame.

Parameters:

schema_name (str) – Target schema.
table_name (str) – Target table name.
df (pd.DataFrame) – Data to insert.
time_column (str) – Name of the time column used by TimescaleDB.

Return type:

None

Low-level modules

timescale_access.engine.check_connection(engine)[source]

Test whether a connection to the database can be established.

Parameters:: engine (Engine) – SQLAlchemy engine to test.
Returns:: True if the connection test succeeds, False otherwise.
Return type:: bool

timescale_access.engine.get_engine(db_url)[source]

Create a SQLAlchemy Engine for the given database URL.

Parameters:: db_url (str) – Database URL.
Returns:: SQLAlchemy engine instance used for database connections.
Return type:: Engine

timescale_access.read.get_active_connections(engine)[source]

Return information about active database connections.

Parameters:: engine (Engine) – SQLAlchemy engine.
Returns:: List of active connections with database name, user and client IP.
Return type:: List[dict]

timescale_access.read.get_column_names(engine, schema_name, table_name)[source]

Return all column names of a given table.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

List of column names.

Return type:

List[str]

timescale_access.read.get_databases(engine)[source]

Return all non-template databases in the PostgreSQL instance.

Parameters:: engine (Engine) – SQLAlchemy engine.
Returns:: List of database names.
Return type:: List[str]

timescale_access.read.get_distinct_values(engine, schema_name, table_name, column_name)[source]

Return all distinct, non-null values of a column in a table.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name.
table_name (str) – Table name.
column_name (str) – Column name.

Returns:

List of distinct values in the column.

Return type:

List[str]

timescale_access.read.get_existing_timestamps(engine, schema_name, table_name, column)[source]

Return a sorted list of distinct timestamps from a given column.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name.
table_name (str) – Table name.
column (str) – Column name.

Returns:

Sorted list of timestamps.

Return type:

List[datetime]

timescale_access.read.get_indexes(engine, schema_name, table_name)[source]

Return index information for a given table.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

List of index metadata dictionaries.

Return type:

List[dict]

timescale_access.read.get_role_memberships(engine)[source]

Return all role memberships in the database.

Parameters:: engine (Engine) – SQLAlchemy engine.
Returns:: List of mappings from member to role.
Return type:: List[dict]

timescale_access.read.get_roles(engine)[source]

Return all roles and their basic privileges.

Parameters:: engine (Engine) – SQLAlchemy engine.
Returns:: List of dictionaries with role metadata.
Return type:: List[dict]

timescale_access.read.get_schemas(engine)[source]

Return a list of user-defined schemas, excluding system and TimescaleDB internals.

Parameters:: engine (Engine) – SQLAlchemy engine.
Returns:: List of schema names.
Return type:: List[str]

timescale_access.read.get_table(engine, schema_name, table_name, filters=None)[source]

Load a table as a pandas DataFrame with optional filter conditions.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name.
table_name (str) – Table name.
filters (Optional[Dict[str, Union[Any, Dict[str, Any]]]], optional) –
Filter specification for the WHERE clause.

Examples
- Single/multiple values: {"instrument_name": ["BTC-14MAR25", "ETH-14MAR25"]}
- Range filter: {"trade_seq": {"between": (100, 200)}}

Returns:

Filtered table.

Return type:

pd.DataFrame

timescale_access.read.get_table_names(engine, schema_name)[source]

Return all table names in a given schema.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name.

Returns:

List of table names.

Return type:

List[str]

timescale_access.write.drop_table(engine, schema_name, table_name)[source]

Drop a table in the given schema.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name.
table_name (str) – Target table name.

Return type:

None

timescale_access.write.ensure_schema_exists(engine, schema_name)[source]

Create the given schema if it does not already exist.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name to ensure.

Return type:

None

timescale_access.write.insert_hypertable(engine, schema_name, table_name, df, index=False, chunksize=1000, time_column='timestamp')[source]

Insert a pandas DataFrame into a TimescaleDB hypertable.

The table and its columns are created automatically if they do not exist yet.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Target schema name.
table_name (str) – Target table name.
df (pd.DataFrame) – Data to insert.
index (bool) – Whether to persist the DataFrame index.
chunksize (int) – Batch size for inserts.
time_column (str) – Name of the time column.

Return type:

None

timescale_access.analysis.get_duplicate_rows(engine, schema_name, table_name)[source]

Return all rows where the combination (instrument_name, trade_seq) occurs multiple times.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

DataFrame containing all duplicate rows.

Return type:

pd.DataFrame

timescale_access.analysis.get_hypertable_size(engine, schema_name, table_name)[source]

Return the total size of a TimescaleDB hypertable as a formatted string (for example "123 MB").

The implementation: 1. Retrieves the internal hypertable ID from _timescaledb_catalog.hypertable. 2. Summarizes the size of all chunks in _timescaledb_internal.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name, e.g. "raw_data".
table_name (str) – Hypertable name, e.g. "btc_weekly".

Returns:

Total formatted size (for example "512 MB", "3 GB").

Return type:

str

Raises:

ValueError – If the hypertable cannot be found.

timescale_access.analysis.get_missing_trade_seq(engine, schema_name, table_name)[source]

Return all expected but missing trade_seq values per instrument_name.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

Missing sequence values per instrument.

Return type:

pd.DataFrame

timescale_access.analysis.get_nonconsecutive_trade_seq(engine, schema_name, table_name)[source]

Return all trade_seq rows where the sequence is not consecutive.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

Rows with gaps in trade_seq per instrument.

Return type:

pd.DataFrame

timescale_access.analysis.get_null_summary(engine, schema_name, table_name)[source]

Create (if necessary) and call a function that summarizes NULL values for any table in a schema.

The function name is generated using the pattern: check_nulls_in_{schema_name}_{table_name}.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name.
table_name (str) – Table name.

Returns:

Summary of NULL counts per column and instrument name.

Return type:

pd.DataFrame

timescale_access.analysis.get_row_count(engine, schema_name, table_name)[source]

Return the number of rows in a table.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Schema name (for example "raw_data").
table_name (str) – Table name (for example "btc_weekly").

Returns:

Number of rows in the table.

Return type:

int

timescale_access.analysis.insert_hypertable_on_conflict(engine, schema_name, table_name, df, time_column)[source]

Insert a DataFrame into a TimescaleDB hypertable and avoid duplicates via ON CONFLICT.

The function creates the table and columns as needed and ensures a UNIQUE index on (instrument_name, trade_seq, timestamp). All index columns must be present as columns in the DataFrame.

Parameters:

engine (Engine) – SQLAlchemy engine.
schema_name (str) – Target schema.
table_name (str) – Target table name.
df (pd.DataFrame) – Data to insert.
time_column (str) – Time column for TimescaleDB.

Return type:

None