SQL Backend Base Classes¶

`BaseSQLBackend` ¶

Bases: BaseBackend

Base backend class for backends that compile to SQL.

Attributes¶

`db_identity: str` `cached` `property` ¶

Return the identity of the database.

Multiple connections to the same database will return the same value for db_identity.

The default implementation assumes connection parameters uniquely specify the database.

Returns:

Type	Description
`Hashable`	Database identity

`tables` `cached` `property` ¶

An accessor for tables in the database.

Tables may be accessed by name using either index or attribute access:

Examples:

>>> con = ibis.sqlite.connect("example.db")
>>> people = con.tables['people']  # access via index
>>> people = con.tables.people  # access via attribute

`version: str` `abstractmethod` `property` ¶

Return the version of the backend engine.

For database servers, return the server version.

For others such as SQLite and pandas return the version of the underlying library or application.

Returns:

Type	Description
`str`	The backend version

Functions¶

`add_operation(operation)` ¶

Add a translation function to the backend for a specific operation.

Operations are defined in ibis.expr.operations, and a translation function receives the translator object and an expression as parameters, and returns a value depending on the backend.

`compile(expr, limit=None, params=None, timecontext=None)` ¶

Compile an Ibis expression.

Parameters:

Name	Type	Description	Default
`expr`	`ir.Expr`	Ibis expression	required
`limit`	`str \| None`	For expressions yielding result sets; retrieve at most this number of values/rows. Overrides any limit already set on the expression.	`None`
`params`	`Mapping[ir.Expr, Any] \| None`	Named unbound parameters	`None`
`timecontext`	`tuple[pd.Timestamp, pd.Timestamp] \| None`	Additional information about data source time boundaries	`None`

Returns:

Type	Description
`Any`	The output of compilation. The type of this value depends on the backend.

`connect(*args, **kwargs)` ¶

Connect to the database.

Parameters:

Name	Type	Description	Default
`*args`		Mandatory connection parameters, see the docstring of `do_connect` for details.	`()`
`**kwargs`		Extra connection parameters, see the docstring of `do_connect` for details.	`{}`

Notes¶

This creates a new backend instance with saved args and kwargs, then calls reconnect and finally returns the newly created and connected backend instance.

Returns:

Type	Description
`BaseBackend`	An instance of the backend

`create_table(name, obj=None, *, schema=None, database=None, temp=False, overwrite=False)` `abstractmethod` ¶

Create a new table.

Parameters:

Name	Type	Description	Default
`name`	`str`	Name of the new table.	required
`obj`	`pd.DataFrame \| pa.Table \| ir.Table \| None`	An Ibis table expression or pandas table that will be used to extract the schema and the data of the new table. If not provided, `schema` must be given.	`None`
`schema`	`ibis.Schema \| None`	The schema for the new table. Only one of `schema` or `obj` can be provided.	`None`
`database`	`str \| None`	Name of the database where the table will be created, if not the default.	`None`
`temp`	`bool`	Whether a table is temporary or not	`False`
`overwrite`	`bool`	Whether to clobber existing data	`False`

Returns:

Type	Description
`Table`	The table that was created.

`create_view(name, obj, *, database=None, overwrite=False)` `abstractmethod` ¶

Create a new view from an expression.

Parameters:

Name	Type	Description	Default
`name`	`str`	Name of the new view.	required
`obj`	`ir.Table`	An Ibis table expression that will be used to create the view.	required
`database`	`str \| None`	Name of the database where the view will be created, if not provided the database's default is used.	`None`
`overwrite`	`bool`	Whether to clobber an existing view with the same name	`False`

Returns:

Type	Description
`Table`	The view that was created.

`database(name=None)` ¶

Return a Database object for the name database.

Parameters:

Name	Type	Description	Default
`name`	`str \| None`	Name of the database to return the object for.	`None`

Returns:

Type	Description
`Database`	A database object for the specified database.

`drop_table(name, *, database=None, force=False)` `abstractmethod` ¶

Drop a table.

Parameters:

Name	Type	Description	Default
`name`	`str`	Name of the table to drop.	required
`database`	`str \| None`	Name of the database where the table exists, if not the default.	`None`
`force`	`bool`	If `False`, an exception is raised if the table does not exist.	`False`

`drop_view(name, *, database=None, force=False)` `abstractmethod` ¶

Drop a view.

Parameters:

Name	Type	Description	Default
`name`	`str`	Name of the view to drop.	required
`database`	`str \| None`	Name of the database where the view exists, if not the default.	`None`
`force`	`bool`	If `False`, an exception is raised if the view does not exist.	`False`

`execute(expr, params=None, limit='default', **kwargs)` ¶

Compile and execute an Ibis expression.

Compile and execute Ibis expression using this backend client interface, returning results in-memory in the appropriate object type

Parameters:

Name	Type	Description	Default
`expr`	`ir.Expr`	Ibis expression	required
`limit`	`str`	For expressions yielding result sets; retrieve at most this number of values/rows. Overrides any limit already set on the expression.	`'default'`
`params`	`Mapping[ir.Scalar, Any] \| None`	Named unbound parameters	`None`
`kwargs`	`Any`	Backend specific arguments. For example, the clickhouse backend uses this to receive `external_tables` as a dictionary of pandas DataFrames.	`{}`

Returns:

Type	Description
`DataFrame \| Series \| Scalar`	`Table`: pandas.DataFrame `Column`: pandas.Series `Scalar`: Python scalar value

`explain(expr, params=None)` ¶

Explain an expression.

Return the query plan associated with the indicated expression or SQL query.

Returns:

Type	Description
`str`	Query plan

`list_tables(like=None, database=None)` `abstractmethod` ¶

Return the list of table names in the current database.

For some backends, the tables may be files in a directory, or other equivalent entities in a SQL database.

Parameters:

Name	Type	Description	Default
`like`	`str`	A pattern in Python's regex format.	`None`
`database`	`str`	The database to list tables of, if not the current one.	`None`

Returns:

Type	Description
`list[str]`	The list of the table names that match the pattern `like`.

`raw_sql(query)` ¶

Execute a query string.

The returned cursor object must be manually released if results are returned.

Parameters:

Name	Type	Description	Default
`query`	`str`	DDL or DML statement	required

`read_csv(path, table_name=None, **kwargs)` ¶

Register a CSV file as a table in the current backend.

Parameters:

Name	Type	Description	Default
`path`	`str \| Path`	The data source. A string or Path to the CSV file.	required
`table_name`	`str \| None`	An optional name to use for the created table. This defaults to a sequentially generated name.	`None`
`**kwargs`	`Any`	Additional keyword arguments passed to the backend loading function.	`{}`

Returns:

Type	Description
`ir.Table`	The just-registered table

`read_parquet(path, table_name=None, **kwargs)` ¶

Register a parquet file as a table in the current backend.

Parameters:

Name	Type	Description	Default
`path`	`str \| Path`	The data source.	required
`table_name`	`str \| None`	An optional name to use for the created table. This defaults to a sequentially generated name.	`None`
`**kwargs`	`Any`	Additional keyword arguments passed to the backend loading function.	`{}`

Returns:

Type	Description
`ir.Table`	The just-registered table

`register_options()` `classmethod` ¶

Register custom backend options.

`sql(query, schema=None, dialect=None)` ¶

Convert a SQL query to an Ibis table expression.

Parameters:

Name	Type	Description	Default
`query`	`str`	SQL string	required
`schema`	`sch.Schema \| None`	The expected schema for this query. If not provided, will be inferred automatically if possible.	`None`
`dialect`	`str \| None`	Optional string indicating the dialect of `query`. The default value of `None` will use the backend's native dialect.	`None`

Returns:

Type	Description
`Table`	Table expression

`table(name, database=None)` ¶

Construct a table expression.

Parameters:

Name	Type	Description	Default
`name`	`str`	Table name	required
`database`	`str \| None`	Database name	`None`

Returns:

Type	Description
`Table`	Table expression

`to_csv(expr, path, *, params=None, **kwargs)` ¶

Write the results of executing the given expression to a CSV file.

This method is eager and will execute the associated expression immediately.

Parameters:

Name	Type	Description	Default
`expr`	`ir.Table`	The ibis expression to execute and persist to CSV.	required
`path`	`str \| Path`	The data source. A string or Path to the CSV file.	required
`params`	`Mapping[ir.Scalar, Any] \| None`	Mapping of scalar parameter expressions to value.	`None`
`kwargs`	`Any`	Additional keyword arguments passed to pyarrow.csv.CSVWriter	`{}`

`to_delta(expr, path, *, params=None, **kwargs)` ¶

Write the results of executing the given expression to a Delta Lake table.

This method is eager and will execute the associated expression immediately.

Parameters:

Name	Type	Description	Default
`expr`	`ir.Table`	The ibis expression to execute and persist to Delta Lake table.	required
`path`	`str \| Path`	The data source. A string or Path to the Delta Lake table.	required
`params`	`Mapping[ir.Scalar, Any] \| None`	Mapping of scalar parameter expressions to value.	`None`
`kwargs`	`Any`	Additional keyword arguments passed to deltalake.writer.write_deltalake method	`{}`

`to_parquet(expr, path, *, params=None, **kwargs)` ¶

Write the results of executing the given expression to a parquet file.

This method is eager and will execute the associated expression immediately.

Parameters:

Name	Type	Description	Default
`expr`	`ir.Table`	The ibis expression to execute and persist to parquet.	required
`path`	`str \| Path`	The data source. A string or Path to the parquet file.	required
`params`	`Mapping[ir.Scalar, Any] \| None`	Mapping of scalar parameter expressions to value.	`None`
`**kwargs`	`Any`	Additional keyword arguments passed to pyarrow.parquet.ParquetWriter	`{}`

`to_pyarrow(expr, *, params=None, limit=None, **kwargs)` ¶

Execute expression and return results in as a pyarrow table.

This method is eager and will execute the associated expression immediately.

Parameters:

Name	Type	Description	Default
`expr`	`ir.Expr`	Ibis expression to export to pyarrow	required
`params`	`Mapping[ir.Scalar, Any] \| None`	Mapping of scalar parameter expressions to value.	`None`
`limit`	`int \| str \| None`	An integer to effect a specific row limit. A value of `None` means "no limit". The default is in `ibis/config.py`.	`None`
`kwargs`	`Any`	Keyword arguments	`{}`

Returns:

Type	Description
`Table`	A pyarrow table holding the results of the executed expression.

`to_pyarrow_batches(expr, *, params=None, limit=None, chunk_size=1000000, **_)` ¶

Execute expression and return an iterator of pyarrow record batches.

This method is eager and will execute the associated expression immediately.

Parameters:

Name	Type	Description	Default
`expr`	`ir.Expr`	Ibis expression to export to pyarrow	required
`limit`	`int \| str \| None`	An integer to effect a specific row limit. A value of `None` means "no limit". The default is in `ibis/config.py`.	`None`
`params`	`Mapping[ir.Scalar, Any] \| None`	Mapping of scalar parameter expressions to value.	`None`
`chunk_size`	`int`	Maximum number of rows in each returned record batch.	`1000000`

Returns:

Type	Description
`RecordBatchReader`	Collection of pyarrow `RecordBatch`s.

`to_torch(expr, *, params=None, limit=None, **kwargs)` ¶

Execute an expression and return results as a dictionary of torch tensors.

Parameters:

Name	Type	Description	Default
`expr`	`ir.Expr`	Ibis expression to execute.	required
`params`	`Mapping[ir.Scalar, Any] \| None`	Parameters to substitute into the expression.	`None`
`limit`	`int \| str \| None`	An integer to effect a specific row limit. A value of `None` means no limit.	`None`
`kwargs`	`Any`	Keyword arguments passed into the backend's `to_torch` implementation.	`{}`

Returns:

Type	Description
`dict[str, torch.Tensor]`	A dictionary of torch tensors, keyed by column name.

Last update: June 22, 2023

SQL Backend Base Classes¶

BaseSQLBackend ¶

Attributes¶

db_identity: str cached property ¶

tables cached property ¶

version: str abstractmethod property ¶

Functions¶

add_operation(operation) ¶

compile(expr, limit=None, params=None, timecontext=None) ¶

connect(*args, **kwargs) ¶

Notes¶

create_table(name, obj=None, *, schema=None, database=None, temp=False, overwrite=False) abstractmethod ¶

create_view(name, obj, *, database=None, overwrite=False) abstractmethod ¶

database(name=None) ¶

drop_table(name, *, database=None, force=False) abstractmethod ¶

drop_view(name, *, database=None, force=False) abstractmethod ¶

execute(expr, params=None, limit='default', **kwargs) ¶

explain(expr, params=None) ¶

list_tables(like=None, database=None) abstractmethod ¶

raw_sql(query) ¶

read_csv(path, table_name=None, **kwargs) ¶

read_parquet(path, table_name=None, **kwargs) ¶

register_options() classmethod ¶

sql(query, schema=None, dialect=None) ¶

table(name, database=None) ¶

to_csv(expr, path, *, params=None, **kwargs) ¶

to_delta(expr, path, *, params=None, **kwargs) ¶

to_parquet(expr, path, *, params=None, **kwargs) ¶

to_pyarrow(expr, *, params=None, limit=None, **kwargs) ¶

to_pyarrow_batches(expr, *, params=None, limit=None, chunk_size=1000000, **_) ¶

to_torch(expr, *, params=None, limit=None, **kwargs) ¶

`BaseSQLBackend` ¶

`db_identity: str` `cached` `property` ¶

`tables` `cached` `property` ¶

`version: str` `abstractmethod` `property` ¶

`add_operation(operation)` ¶

`compile(expr, limit=None, params=None, timecontext=None)` ¶

`connect(*args, **kwargs)` ¶

`create_table(name, obj=None, *, schema=None, database=None, temp=False, overwrite=False)` `abstractmethod` ¶

`create_view(name, obj, *, database=None, overwrite=False)` `abstractmethod` ¶

`database(name=None)` ¶

`drop_table(name, *, database=None, force=False)` `abstractmethod` ¶

`drop_view(name, *, database=None, force=False)` `abstractmethod` ¶

`execute(expr, params=None, limit='default', **kwargs)` ¶

`explain(expr, params=None)` ¶

`list_tables(like=None, database=None)` `abstractmethod` ¶

`raw_sql(query)` ¶

`read_csv(path, table_name=None, **kwargs)` ¶

`read_parquet(path, table_name=None, **kwargs)` ¶

`register_options()` `classmethod` ¶

`sql(query, schema=None, dialect=None)` ¶

`table(name, database=None)` ¶

`to_csv(expr, path, *, params=None, **kwargs)` ¶

`to_delta(expr, path, *, params=None, **kwargs)` ¶

`to_parquet(expr, path, *, params=None, **kwargs)` ¶

`to_pyarrow(expr, *, params=None, limit=None, **kwargs)` ¶

`to_pyarrow_batches(expr, *, params=None, limit=None, chunk_size=1000000, **_)` ¶

`to_torch(expr, *, params=None, limit=None, **kwargs)` ¶