PostgreSQL

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

A class for basic communication with PostgreSQL databases.

Parameters:
  • host (str | None) – Host name/address of a PostgreSQL server, e.g. 'localhost' or '127.0.0.1'. If host=None, it defaults to 'localhost'.

  • port (int | None) – Listening port used by PostgreSQL. If port=None, it defaults to 5432.

  • username (str | None) – Username of the PostgreSQL server. If username=None, it defaults to 'postgres'.

  • password (str | int | None) – User password. If password=None, it must be manually entered to connect to the PostgreSQL server.

  • database_name (str | None) – Name of the database. If database_name=None, it defaults to 'postgres'.

  • confirm_db_creation (bool) – Whether to prompt for confirmation before creating a new database if the specified database does not exist; defaults to False.

  • verbose (bool | int) – Whether to print relevant information to the console; defaults to False.

Variables:
  • host (str) – Host name/address.

  • port (int) – Listening port used by PostgreSQL.

  • username (str) – Username.

  • database_name (str) – Name of the 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].

Examples:

>>> from pyhelpers.dbms import PostgreSQL
>>> # Connect the default database 'postgres'
>>> postgres = PostgreSQL(verbose=True)
Password (postgres@localhost:5432): ***
Connecting postgres:***@localhost:5432/postgres ... Successfully.
>>> postgres.address
'postgres:***@localhost:5432/postgres'
>>> # Connect a database 'testdb' (which will be created if it does not exist)
>>> testdb = PostgreSQL(database_name='testdb', verbose=True)
Password (postgres@localhost:5432): ***
Creating a database: "testdb" ... Done.
Connecting postgres:***@localhost:5432/testdb ... Successfully.
>>> testdb.address
'postgres:***@localhost:5432/testdb'
>>> testdb.drop_database(verbose=True)
To drop the database "testdb" from postgres:***@localhost:5432
? [No]|Yes: yes
Dropping "testdb" ... Done.
>>> testdb.address
'postgres:***@localhost:5432/postgres'

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', verbose=True)
Password (postgres@localhost:5432): ***
Creating a database: "testdb" ... Done.
Connecting postgres:***@localhost:5432/testdb ... Successfully.
>>> example_proxy.address
'postgres:***@localhost:5432/testdb'
>>> example_proxy.database_name
'testdb'
>>> 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
'postgres'

Attributes

BUILTIN_SCHEMAS

Built-in schemas of PostgreSQL.

DEFAULT_DATABASE

Default database name (usually created during PostgreSQL installation).

DEFAULT_DIALECT

Default dialect used by SQLAlchemy to communicate with PostgreSQL; see also [DBMS-PS-1].

DEFAULT_DRIVER

Default name of the database driver.

DEFAULT_HOST

Default host name/address (typically localhost).

DEFAULT_PORT

Default listening port used by PostgreSQL.

DEFAULT_SCHEMA

Default schema name created during PostgreSQL installation.

DEFAULT_USERNAME

Default username.

Methods

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.

database_exists([database_name])

Check if a specified database exists.

disconnect_all_others()

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

disconnect_database([database_name, verbose])

Disconnect from 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 specified table.

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

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

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

Retrieve information about columns of a table.

get_database_names([names_only])

Retrieve the names of all existing databases.

get_database_size([database_name])

Retrieve the size of a database.

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

Retrieve the primary keys of a table.

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

Retrieve information about existing schemas.

get_table_names([schema_name, verbose])

Retrieve 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, ...)

Callable function using PostgreSQL COPY clause for executing data insertion.

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

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

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

Read data from a specified table.

schema_exists(schema_name)

Check if a schema exists.

table_exists(table_name[, schema_name])

Check if a table exists.

validate_column_names(table_name[, ...])

Validate column names for a query statement.