class pyhelpers.dbms.PostgreSQL(host=None, port=None, username=None, password=None, database_name=None, confirm_db_creation=False, verbose=True)[source]

A class for basic communication with PostgreSQL databases.

  • host (str | None) – host name/address of a PostgreSQL server, e.g. 'localhost' or '' (default by installation of PostgreSQL); when host=None (default), it is initialized as 'localhost'

  • port (int | None) – listening port used by PostgreSQL; when port=None (default), it is initialized as 5432 (default by installation of PostgreSQL)

  • username (str | None) – username of a PostgreSQL server; when username=None (default), it is initialized as 'postgres' (default by installation of PostgreSQL)

  • password (str | int | None) – user password; when password=None (default), it is required to mannually type in the correct password to connect the PostgreSQL server

  • database_name (str | None) – name of a database; when database=None (default), it is initialized as 'postgres' (default by installation of PostgreSQL)

  • confirm_db_creation (bool) – whether to prompt a confirmation before creating a new database (if the specified database does not exist), defaults to False

  • verbose (bool | int) – whether to print relevant information in console, defaults to True

  • host (str) – host name/address

  • port (str) – listening port used by PostgreSQL

  • username (str) – username

  • database_name (str) – name of a database

  • credentials (dict) – basic information about the server/database being connected

  • address (str) – representation of the database address

  • engine (sqlalchemy.engine.Engine) – SQLAlchemy connectable engine to a PostgreSQL server; see also [DBMS-PS-2]


>>> from pyhelpers.dbms import PostgreSQL

>>> # Connect the default database 'postgres'
>>> # postgres = PostgreSQL('localhost', 5432, 'postgres', database_name='postgres')
>>> postgres = PostgreSQL()
Password (postgres@localhost:5432): ***
Connecting postgres:***@localhost:5432/postgres ... Successfully.

>>> postgres.address

>>> # Connect a database 'testdb' (which will be created if it does not exist)
>>> testdb = PostgreSQL(database_name='testdb')
Password (postgres@localhost:5432): ***
Creating a database: "testdb" ... Done.
Connecting postgres:***@localhost:5432/testdb ... Successfully.

>>> testdb.address

>>> testdb.drop_database(verbose=True)
To drop the database "testdb" from postgres:***@localhost:5432
? [No]|Yes: yes
Dropping "testdb" ... Done.

>>> testdb.address

Define a proxy object that inherits from this class:

>>> class ExampleProxyObj(PostgreSQL):
...     def __init__(self, **kwargs):
...         super().__init__(**kwargs)

>>> example_proxy = ExampleProxyObj(database_name='testdb')
Password (postgres@localhost:5432): ***
Creating a database: "testdb" ... Done.
Connecting postgres:***@localhost:5432/testdb ... Successfully.

>>> example_proxy.address
>>> example_proxy.database_name

>>> example_proxy.drop_database(verbose=True)
To drop the database "testdb" from postgres:***@localhost:5432
? [No]|Yes: yes
Dropping "testdb" ... Done.

>>> example_proxy.database_name



set: Names of built-in schemas of PostgreSQL.


str: Name of the database that is by default to connect with.


str: Default dialect.


str: Default name of database driver.


str: Default host name/address (by installation of PostgreSQL).


str: Default listening port used by PostgreSQL.


str: Name of the schema that is created by default for the installation of PostgreSQL.


str: Default username.


add_primary_keys(primary_keys, table_name[, ...])

Add a primary key or multiple primary keys to a table.

alter_table_schema(table_name, schema_name, ...)

Move a table from one schema to another within the currently-connected database.

connect_database([database_name, verbose])

Establish a connection to a database.

create_database(database_name[, verbose])

Create a database.

create_schema(schema_name[, verbose])

Create a schema.

create_table(table_name, column_specs[, ...])

Create a table.


Check whether a database exists.


Kill connections to all databases except the currently-connected one.

disconnect_database([database_name, verbose])

Disconnect a database.

drop_database([database_name, ...])

Delete/drop a database.

drop_schema(schema_names[, ...])

Delete/drop one or multiple schemas.

drop_table(table_name[, schema_name, ...])

Delete/drop a table.

get_column_dtype(table_name[, column_names, ...])

Get information about data types of all or specific columns of a table.

get_column_info(table_name[, schema_name, ...])

Get information about columns of a table.


Get names of all existing databases.


Get the size of a database.

get_primary_keys(table_name[, schema_name, ...])

Get the primary keys of a table.

get_schema_info([names_only, include_all, ...])

Get the names of existing schemas.

get_table_names([schema_name, verbose])

Get the names of all tables in a schema.

import_data(data, table_name[, schema_name, ...])

Import tabular data into a table.

null_text_to_empty_string(table_name[, ...])

Convert null values (in text columns) to empty strings.

psql_insert_copy(sql_table, sql_db_engine, ...)

A callable using PostgreSQL COPY clause for executing inserting data.

read_sql_query(sql_query[, method, ...])

Read table data by SQL query (recommended for large table).

read_table(table_name[, schema_name, ...])

Read data from a table.


Check whether a schema exists.

table_exists(table_name[, schema_name])

Check whether a table exists.

validate_column_names(table_name[, ...])

Validate column names for query statement.