Skip to content

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