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