SQLite¶
Install¶
Install ibis and dependencies for the SQLite backend:
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 |
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 |
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 |
{} |
Returns:
Type | Description |
---|---|
DataFrame | Series | Scalar |
|
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 |
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 |
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 |
False |
Exceptions:
Type | Description |
---|---|
NotImplementedError |
If inserting data from a different database |
ValueError |
If the type of |
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 |
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 |
'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 |
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