PostgreSQL ¶

Install¶

Install ibis and dependencies for the PostgreSQL backend:

pipcondamamba

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`, 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)` ¶

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 `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`

`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 `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

`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

Last update: March 1, 2022

PostgreSQL¶

Install¶

Connect¶

API¶

Connection Parameters¶

do_connect(self, host='localhost', user=None, password=None, port=5432, database=None, url=None, driver='psycopg2') ¶

Backend API¶

Backend (BaseAlchemyBackend) ¶

Attributes¶

current_database: str inherited property readonly ¶

version inherited property readonly ¶

Methods¶

add_operation(self, operation) inherited ¶

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

connect(self, *args, **kwargs) inherited ¶

create_database(self, name, force=False) inherited ¶

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

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

database(self, name=None) inherited ¶

drop_table(self, table_name, database=None, force=False) inherited ¶

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

execute(self, expr, params=None, limit='default', **kwargs) inherited ¶

exists_database(self, name) inherited ¶

exists_table(self, name, database=None) inherited ¶

explain(self, expr, params=None) inherited ¶

insert(self, table_name, obj, database=None, overwrite=False) inherited ¶

list_databases(self, like=None) ¶

list_schemas(self, like=None) ¶

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

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

raw_sql(self, query, results=False) inherited ¶

schema(self, name) inherited ¶

sql(self, query) inherited ¶

table(self, name, database=None, schema=None) inherited ¶

udf(self, pyfunc, in_types, out_type, schema=None, replace=False, name=None, language='plpythonu') ¶

verify(self, expr, params=None) inherited ¶

PostgreSQL ¶

`do_connect(self, host='localhost', user=None, password=None, port=5432, database=None, url=None, driver='psycopg2')` ¶

`Backend (BaseAlchemyBackend)` ¶

`current_database: str` `inherited` `property` `readonly` ¶

`version` `inherited` `property` `readonly` ¶

`add_operation(self, operation)` `inherited` ¶

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

`connect(self, *args, **kwargs)` `inherited` ¶

`create_database(self, name, force=False)` `inherited` ¶

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

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

`database(self, name=None)` `inherited` ¶

`drop_table(self, table_name, database=None, force=False)` `inherited` ¶

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

`execute(self, expr, params=None, limit='default', **kwargs)` `inherited` ¶

`exists_database(self, name)` `inherited` ¶

`exists_table(self, name, database=None)` `inherited` ¶

`explain(self, expr, params=None)` `inherited` ¶

`insert(self, table_name, obj, database=None, overwrite=False)` `inherited` ¶

`list_databases(self, like=None)` ¶

`list_schemas(self, like=None)` ¶

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

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

`raw_sql(self, query, results=False)` `inherited` ¶

`schema(self, name)` `inherited` ¶

`sql(self, query)` `inherited` ¶

`table(self, name, database=None, schema=None)` `inherited` ¶

`udf(self, pyfunc, in_types, out_type, schema=None, replace=False, name=None, language='plpythonu')` ¶

`verify(self, expr, params=None)` `inherited` ¶