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(host='localhost', user=None, password=None, port=5432, database=None, schema=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
schema	str | None	PostgreSQL schema to use. If None, use the default search_path.	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

Bases: BaseAlchemyBackend

Functions

udf(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		Python function	required
in_types		Input types	required
out_type		Output type	required
schema		The postgres schema in which to define the UDF	None
replace		replace UDF in database if already exists	False
name		name for the UDF to be defined in database	None
language		Language extension to use for PL/Python	'plpythonu'

Returns:

Type	Description
Callable	A callable ibis expression

Function that takes in Column arguments and returns an instance inheriting from PostgresUDFNode

---

Last update: March 1, 2022