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'
. Ifhost=None
, it defaults to'localhost'
.port (int | None) – Listening port used by PostgreSQL. If
port=None
, it defaults to5432
.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
Built-in schemas of PostgreSQL.
Default database name (usually created during PostgreSQL installation).
Default dialect used by SQLAlchemy to communicate with PostgreSQL; see also [DBMS-PS-1].
Default name of the database driver.
Default host name/address (typically localhost).
Default listening port used by PostgreSQL.
Default schema name created during PostgreSQL installation.
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.
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.