PostgreSQL¶
Install¶
Install ibis and dependencies for the PostgreSQL backend:
pip install 'ibis-framework[postgres]'
conda install -c conda-forge ibis-postgres
mamba install -c conda-forge ibis-postgres
Connect¶
API¶
Create a client by passing in a SQLAlchemy-style URI to ibis.postgres.connect
.
See ibis.backends.postgres.Backend.do_connect
for connection parameter information.
ibis.postgres.connect
is a thin wrapper around ibis.backends.postgres.Backend.do_connect
.
Connection Parameters¶
do_connect(self, host='localhost', user=None, password=None, port=5432, database=None, url=None, driver='psycopg2')
¶
Create an Ibis client connected to PostgreSQL database.
Parameters:
Name | Type | Description | Default |
---|---|---|---|
host |
str |
Hostname |
'localhost' |
user |
str | None |
Username |
None |
password |
str | None |
Password |
None |
port |
int |
Port number |
5432 |
database |
str | None |
Database to connect to |
None |
url |
str | None |
SQLAlchemy connection string. If passed, the other connection arguments are ignored. |
None |
driver |
Literal['psycopg2'] |
Database driver |
'psycopg2' |
Examples:
>>> import os
>>> import getpass
>>> import ibis
>>> host = os.environ.get('IBIS_TEST_POSTGRES_HOST', 'localhost')
>>> user = os.environ.get('IBIS_TEST_POSTGRES_USER', getpass.getuser())
>>> password = os.environ.get('IBIS_TEST_POSTGRES_PASSWORD')
>>> database = os.environ.get('IBIS_TEST_POSTGRES_DATABASE',
... 'ibis_testing')
>>> con = connect(
... database=database,
... host=host,
... user=user,
... password=password
... )
>>> con.list_tables()
[...]
>>> t = con.table('functional_alltypes')
>>> t
PostgreSQLTable[table]
name: functional_alltypes
schema:
index : int64
Unnamed: 0 : int64
id : int32
bool_col : boolean
tinyint_col : int16
smallint_col : int16
int_col : int32
bigint_col : int64
float_col : float32
double_col : float64
date_string_col : string
string_col : string
timestamp_col : timestamp
year : int32
month : int32
Backend API¶
Backend (BaseAlchemyBackend)
¶
Attributes¶
current_database: str
inherited
property
readonly
¶
The name of the current database this client is connected to.
version
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)
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)
¶
List databases in the current server.
list_schemas(self, like=None)
¶
List all the schemas in the current database.
DEPRECATED: list_schemas
is deprecated as of v2.0; use list_databases
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 |
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)
inherited
¶
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
The |
None |
Returns:
Type | Description |
---|---|
ir.TableExpr |
Table expression |
udf(self, pyfunc, in_types, out_type, schema=None, replace=False, name=None, language='plpythonu')
¶
Decorator that defines a PL/Python UDF in-database.
Parameters:
Name | Type | Description | Default |
---|---|---|---|
pyfunc |
None |
Python function |
required |
in_types |
None |
Input types |
required |
out_type |
None |
Output type |
required |
schema |
None |
The postgres schema in which to define the UDF |
None |
replace |
None |
replace UDF in database if already exists |
False |
name |
None |
name for the UDF to be defined in database |
None |
language |
None |
Language extension to use for PL/Python |
'plpythonu' |
Returns:
Type | Description |
---|---|
Callable |
A callable ibis 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