PostgreSQL
Install
Install Ibis and dependencies for the Postgres backend:
Install with the postgres
extra:
pip install 'ibis-framework[postgres]'
And connect:
import ibis
= ibis.postgres.connect() con
- 1
- Adjust connection parameters as needed.
Install for Postgres:
conda install -c conda-forge ibis-postgres
And connect:
import ibis
= ibis.postgres.connect() con
- 1
- Adjust connection parameters as needed.
Install for Postgres:
mamba install -c conda-forge ibis-postgres
And connect:
import ibis
= ibis.postgres.connect() con
- 1
- Adjust connection parameters as needed.
Connect
ibis.postgres.connect
= ibis.postgres.connect(
con ="username",
user="password",
password="hostname",
host=5432,
port="database",
database )
ibis.postgres.connect
is a thin wrapper around ibis.backends.postgres.Backend.do_connect
.
Connection Parameters
do_connect
do_connect(self, host=None, 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 | None | Hostname | None |
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: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
ibis.connect
URL format
In addition to ibis.postgres.connect
, you can also connect to Postgres by passing a properly formatted Postgres connection URL to ibis.connect
= ibis.connect(f"postgres://{user}:{password}@{host}:{port}/{database}") con
postgres.Backend
create_schema
create_schema(self, name, database=None, force=False)
Create a schema named name
in database
.
Parameters
Name | Type | Description | Default |
---|---|---|---|
name |
str | Name of the schema to create. | required |
database |
str | None | Name of the database in which to create the schema. If None , the current database is used. |
None |
force |
bool | If False , an exception is raised if the schema exists. |
False |
drop_schema
drop_schema(self, name, database=None, force=False)
Drop the schema with name
in database
.
Parameters
Name | Type | Description | Default |
---|---|---|---|
name |
str | Name of the schema to drop. | required |
database |
str | None | Name of the database to drop the schema from. If None , the current database is used. |
None |
force |
bool | If False , an exception is raised if the schema does not exist. |
False |
function
function(self, name, *, schema=None)
list_databases
list_databases(self, like=None)
list_tables
list_tables(self, like=None, database=None, schema=None)
List the tables in the database.
Parameters
Name | Type | Description | Default |
---|---|---|---|
like |
A pattern to use for listing tables. | None |
|
database |
(deprecated) The database to perform the list against. | None |
|
schema |
The schema to perform the list against. ::: {.callout-warning} ## schema refers to database hierarchy The schema parameter does not refer to the column names and types of table . ::: |
None |