SQLAlchemy Backend Base Classes

BaseAlchemyBackend (BaseSQLBackend)

Backend class for backends that compile to SQLAlchemy expressions.

Attributes

current_database: str property readonly

The name of the current database this client is connected to.

version property readonly

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

Methods

add_operation(self, operation) inherited

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. For example, in SQL backends, a NullLiteral operation could be translated to the string "NULL".

Examples:

>>> @ibis.sqlite.add_operation(ibis.expr.operations.NullLiteral)
... def _null_literal(translator, expression):
...     return 'NULL'

compile(self, expr, limit=None, params=None, timecontext=None) inherited

Compille 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	TimeContext | 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(self, *args, **kwargs) inherited

Connect to the database.

Parameters:

Name	Type	Description	Default
args	None	Connection parameters	()
kwargs	None	Additional connection parameters	{}

Returns:

Type	Description
BaseBackend	An instance of the backend

create_database(self, name, force=False) inherited

Create a new database.

Not all backends implement this method.

Parameters:

Name	Type	Description	Default
name	str	Name of the new database.	required
force	bool	If False, an exception is raised if the database already exists.	False

create_table(self, name, expr=None, schema=None, database=None, force=False)

Create a table.

Parameters:

Name	Type	Description	Default
name	str	Table name to create	required
expr	pd.DataFrame | ir.TableExpr | None	DataFrame or table expression to use as the data source	None
schema	sch.Schema | None	An ibis schema	None
database	str | None	A database	None
force	bool	Check whether a table exists before creating it	False

create_view(self, name, expr, database=None) inherited

Create a view.

Parameters:

Name	Type	Description	Default
name	str	Name for the new view.	required
expr	ir.TableExpr	An Ibis table expression that will be used to extract the query of the view.	required
database	str | None	Name of the database where the view will be created, if not the default.	None

database(self, name=None) inherited

Return a Database object for the name database.

DEPRECATED: database is deprecated; use equivalent methods in the backend

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(self, table_name, database=None, force=False)

Drop a table.

Parameters:

Name	Type	Description	Default
table_name	str	Table to drop	required
database	str | None	Database to drop table from	None
force	bool	Check for existence before dropping	False

drop_view(self, name, database=None, force=False) inherited

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(self, expr, params=None, limit='default', **kwargs) inherited

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.ScalarExpr, 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	TableExpr: pandas.DataFrame ColumnExpr: pandas.Series ScalarExpr: Python scalar value

exists_database(self, name) inherited

Return whether a database name exists in the current connection.

DEPRECATED: exists_database is deprecated as of v2.0; use name in client.list_databases()

Parameters:

Name	Type	Description	Default
name	str	Database to check for existence	required

Returns:

Type	Description
bool	Whether name exists

exists_table(self, name, database=None) inherited

Return whether a table name exists in the database.

DEPRECATED: exists_table is deprecated as of v2.0; use name in client.list_tables()

Parameters:

Name	Type	Description	Default
name	str	Table name	required
database	str | None	Database to check if given	None

Returns:

Type	Description
bool	Whether name is a table

explain(self, expr, params=None) inherited

Explain an expression.

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

Returns:

Type	Description
str	Query plan

insert(self, 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.TableExpr	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

Exceptions:

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

list_databases(self, like=None)

List databases in the current server.

list_schemas(self, like=None)

DEPRECATED: list_schemas is deprecated as of v2.0; use list_databases

list_tables(self, like=None, database=None)

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.

load_data(self, table_name, data, database=None, if_exists='fail')

Load data from a dataframe to the backend.

Parameters:

Name	Type	Description	Default
table_name	str	Name of the table in which to load data	required
data	pd.DataFrame	Pandas DataFrame	required
database	str | None	Database in which the table exists	None
if_exists	Literal['fail', 'replace', 'append']	What to do when data in name already exists	'fail'

Exceptions:

Type	Description
NotImplementedError	Loading data to a table from a different database is not yet implemented

raw_sql(self, query, results=False) inherited

Execute a query string.

Could have unexpected results if the query modifies the behavior of the session in a way unknown to Ibis; be careful.

Parameters:

Name	Type	Description	Default
query	str	DML or DDL statement	required

Returns:

Type	Description
Any	Backend cursor

schema(self, 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
sch.Schema	The ibis schema of name

sql(self, query) inherited

Convert a SQL query to an Ibis table expression.

Parameters:

Name	Type	Description	Default
query	str	SQL string	required

Returns:

Type	Description
ir.TableExpr	Table expression

table(self, 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
ir.TableExpr	Table expression

verify(self, expr, params=None) inherited

Verify expr is an expression that can be compiled.

DEPRECATED: verify is deprecated as of v2.0; compile and capture TranslationError instead

---

Last update: March 1, 2022