PostgreSQL

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.

Parameters:

host (str | None) – host name/address of a PostgreSQL server, e.g. 'localhost' or '127.0.0.1' (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

Variables:

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]

Examples:

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

`BUILTIN_SCHEMAS`	set: Names of built-in schemas of PostgreSQL.
`DEFAULT_DATABASE`	str: Name of the database that is by default to connect with.
`DEFAULT_DIALECT`	str: Default dialect.
`DEFAULT_DRIVER`	str: Default name of database driver.
`DEFAULT_HOST`	str: Default host name/address (by installation of PostgreSQL).
`DEFAULT_PORT`	str: Default listening port used by PostgreSQL.
`DEFAULT_SCHEMA`	str: Name of the schema that is created by default for the installation of PostgreSQL.
`DEFAULT_USERNAME`	str: 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 whether a database exists.
`disconnect_all_others`()	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_database_names`([names_only])	Get names of all existing databases.
`get_database_size`([database_name])	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.
`schema_exists`(schema_name)	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.