SQL Backend Base Classes

BaseSQLBackend (BaseBackend)

Base backend class for backends that compile to SQL.

Attributes

current_database: str | None inherited property readonly

Return the name of the current database.

Backends that don't support different databases will return None.

Returns:

Type	Description
str | None	Name of the current database.

version: str inherited 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)

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

Create a new table.

Not all backends implement this method.

Parameters:

Name	Type	Description	Default
name	str	Name of the new table.	required
obj	pd.DataFrame | ir.TableExpr | 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

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, name, database=None, force=False) inherited

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(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)

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)

Explain an expression.

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

Returns:

Type	Description
str	Query plan

has_operation(operation) classmethod

Return whether the backend implements support for operation.

Parameters:

Name	Type	Description	Default
operation	type[ops.ValueOp]	A class corresponding to an operation.	required

Examples:

>>> import ibis
>>> import ibis.expr.operations as ops
>>> ibis.sqlite.has_operation(ops.ArrayIndex)
False
>>> ibis.postgres.has_operation(ops.ArrayIndex)
True

Returns:

Type	Description
bool	Whether the backend implements the operation.

list_databases(self, like=None) inherited

List existing databases in the current connection.

Parameters:

Name	Type	Description	Default
like	str	A pattern in Python's regex format to filter returned database names.	None

Returns:

Type	Description
list[str]	The database names that exist in the current connection, that match the like pattern if provided.

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

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 | None	A pattern in Python's regex format.	None
database	str | None	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(self, query, results=False)

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

sql(self, query)

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)

Construct a table expression.

Parameters:

Name	Type	Description	Default
name	str	Table name	required
database	str | None	Database name	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