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 PostgreSQL databases.
- 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); whenhost=None
(default), it is initialized as'localhost'
port (int or None) – listening port used by PostgreSQL; when
port=None
(default), it is initialized as5432
(default by installation of PostgreSQL)username (str or None) – username of a PostgreSQL server; when
username=None
(default), it is initialized as'postgres'
(default by installation of PostgreSQL)password (str or int or None) – user password; when
password=None
(default), it is required to mannually type in the correct password to connect the PostgreSQL serverdatabase_name (str or 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 or 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
Name of the database that is by default to connect with.
Default dialect.
Default name of database driver.
Default host name/address (by installation of PostgreSQL).
Default listening port used by PostgreSQL.
Name of the schema that is created by default for the installation of PostgreSQL.
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.
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_names
([include_all, names_only, ...])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.