SQLAlchemy Backend Base Classes

BaseAlchemyBackend

Bases: BaseSQLBackend

Backend class for backends that compile to SQLAlchemy expressions.

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

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)

Create a 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	sch.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	Should the table be temporary for the session.	False
overwrite	bool	Clobber existing data	False

Returns:

Type	Description
Table	The table 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)

Drop a table.

Parameters:

Name	Type	Description	Default
name	str	Table to drop	required
database	str | None	Database to drop table from	None
force	bool	Check for existence before dropping	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

insert(table_name, obj, database=None, overwrite=False)

Insert data into a table.

Parameters:

Name	Type	Description	Default
table_name	str	The name of the table to which data needs will be inserted	required
obj	pd.DataFrame | ir.Table | list | dict	The source data or expression to insert	required
database	str | None	Name of the attached database that the table is located in.	None
overwrite	bool	If True then replace existing contents of table	False

Raises:

Type	Description
NotImplementedError	If inserting data from a different database
ValueError	If the type of obj isn't supported

list_databases(like=None)

List databases in the current server.

raw_sql(query)

Execute a query string.

The returned cursor object must be manually released.

Parameters:

Name	Type	Description	Default
query		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.

schema(name)

Get an ibis schema from the current database for the table name.

Parameters:

Name	Type	Description	Default
name	str	Table name	required

Returns:

Type	Description
Schema	The ibis schema of name

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, schema=None)

Create a table expression from a table in the database.

Parameters:

Name	Type	Description	Default
name	str	Table name	required
database	str | None	The database the table resides in	None
schema	str | None	The schema inside database where the table resides. schema refers to database organization The schema parameter does not refer to the column names and types of table.	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 RecordBatchs.

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