SQLite

Install

Install ibis and dependencies for the SQLite backend:

pipcondamamba

pip install 'ibis-framework[sqlite]'

conda install -c conda-forge ibis-sqlite

mamba install -c conda-forge ibis-sqlite

Connect

API

Create a client by passing in a path to a SQLite database to ibis.sqlite.connect.

See ibis.backends.sqlite.Backend.do_connect for connection parameter information.

ibis.sqlite.connect is a thin wrapper around ibis.backends.sqlite.Backend.do_connect.

Connection Parameters

do_connect(self, path=None)

Create an Ibis client connected to a SQLite database.

Multiple database files can be created using the attach() method

Parameters:

Name	Type	Description	Default
path	str | Path | None	File path to the SQLite database file. If None, creates an in-memory transient database and you can use attach() to add more files	None

Examples:

>>> import ibis
>>> ibis.sqlite.connect("path/to/my/sqlite.db")

Backend API

Backend (BaseAlchemyBackend)

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'

attach(self, name, path)

Connect another SQLite database file to the current connection.

Parameters:

Name	Type	Description	Default
name	str	Database name within SQLite	required
path	str | Path	Path to sqlite3 database file	required

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

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

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

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

List databases in the current server.

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	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') inherited

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

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

table(self, name, database=None)

Create a table expression from a table in the SQLite database.

Parameters:

Name	Type	Description	Default
name	str	Table name	required
database	str | None	Name of the attached database that the table is located in.	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 23, 2022