PostgreSQL

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

A class for basic communication with a PostgreSQL server.

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

  • port (int or None) – listening port used by PostgreSQL; if port=None (default), initialized as 5432 (default by installation of PostgreSQL)

  • username (str or None) – username of a PostgreSQL server; if username=None (default), initialized as 'postgres' (default by installation of PostgreSQL)

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

  • database_name (str or None) – name of a database; if database=None (default), 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 or int) – whether to print relevant information in console as the function runs, defaults to True

Variables
  • host (str) – host name/address

  • port (str) – listening port used by PostgreSQL

  • username (str) – username

  • database_name (str) – name of a database

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

  • url (sqlalchemy.engine.URL) – PostgreSQL database URL; see also [SQL-P-SP-2]

  • address (str) – representation of the database address

  • engine (sqlalchemy.engine.Engine) – SQLAlchemy engine class; see also [SQL-P-SP-3]

Examples:

>>> from pyhelpers.dbms import PostgreSQL

>>> # Connect the default database 'postgres'
>>> postgres = PostgreSQL('localhost', 5432, 'postgres', database_name='postgres')
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')
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')
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

DEFAULT_DATABASE

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

DEFAULT_DIALECT

Default dialect.

DEFAULT_DRIVER

Default name of database driver

DEFAULT_HOST

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

DEFAULT_PORT

Default listening port used by PostgreSQL.

DEFAULT_SCHEMA

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

DEFAULT_USERNAME

Default username.

Methods

alter_table_schema(table_name, schema_name, ...)

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

connect_database([database_name, verbose])

Establish a connection to a database of the PostgreSQL server being connected.

create_database(database_name[, verbose])

An alternative to sqlalchemy_utils.create_database.

create_schema(schema_name[, verbose])

Create a new schema in the database being connected.

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

Create a new table for the database being connected.

database_exists([database_name])

Check if a database exists in the PostgreSQL server being connected.

disconnect_all_others()

Kill connections to all other databases being connected.

disconnect_database([database_name, verbose])

Kill the connection to a database in the PostgreSQL server being connected.

drop_database([database_name, ...])

Delete/drop a database from the PostgreSQL server being connected.

drop_schema(schema_names[, ...])

Delete/drop a schema from the database being connected.

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

Delete/drop a table from the database being connected.

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

Get information about columns of a table.

get_database_size([database_name])

Get the size of a database in the PostgreSQL server being connected.

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

Import tabular data into the database being connected.

list_table_names([schema_name, verbose])

List the names of all tables in a schema.

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 of the database being connected.

schema_exists(schema_name)

Check if a schema exists in the PostgreSQL server being connected.

table_exists(table_name[, schema_name])

Check if a table exists in the database being connected.