postgrez¶

Submodules¶

postgrez.postgrez module¶

Main postgrez module, contains 4 core classes: Connection, Cmd, Export and Load.

class postgrez.postgrez.Cmd(setup='default')[source]¶

Bases: postgrez.postgrez.Connection

Class which handles execution of queries.

execute(query, query_vars=None, commit=True, columns=True)[source]¶

Execute the supplied query.

Parameters:	query (str) – Query to be executed. Query can contain placeholders, as long as query_vars are supplied. query_vars (tuple, list or dict) – Variables to be executed with query. See http://initd.org/psycopg/docs/usage.html#query-parameters. commit (bool) – Commit any pending transaction to the database. Defaults to True.
Raises:	`PostgrezConnectionError` – If no connection has been established. `PostgrezExecuteError` – If any error occurs during execution of query.

class postgrez.postgrez.Connection(setup='default')[source]¶

Bases: object

Class which establishes connections to a PostgresSQL database.

Methods used internally by the class are prefixed with a _.

host¶: str – Database host address

port¶: int – Connection port number (defaults to 5432 if not provided)

database¶: str – Name of the database

user¶: str – Username used to authenticate

password¶: str, optional – Password used to authenticate

conn¶: psycopg2 connection – psycopg2 connection object

cursor¶: psycopg2 cursor – psycopg2 cursor object, associated with the connection object

class postgrez.postgrez.Export(setup='default')[source]¶

Bases: postgrez.postgrez.Connection

Class which handles exporting data.

export_to_file(query, filename, columns=None, delimiter=', ', header=True, null=None)[source]¶

Export records from a table or query to a local file.

Parameters:

query (str) – A select query or a table
columns (list) – List of column names to export. columns should only be provided if you are exporting a table (i.e. query = ‘table_name’). If query is a query to export, desired columns should be specified in the select portion of that query (i.e. query = ‘select col1, col2 from …’). Defaults to None.
filename (str) – Filename to copy to.
delimiter (str) – Delimiter to separate columns with. Defaults to ‘,’.
header (boolean) – Specify True to return the column names. Defaults to True.
null (str) – Specifies the string that represents a null value. Defaults to None, which uses the postgres default of an unquoted empty string.

Raises:

PostgrezExportError – If an error occurs while exporting to the file.

export_to_object(query, columns=None, delimiter=', ', header=True, null=None)[source]¶

Export records from a table or query and returns list of records.

Parameters:	query (str) – A select query or a table_name columns (list) – List of column names to export. columns should only be provided if you are exporting a table (i.e. query = ‘table_name’). If query is a query to export, desired columns should be specified in the select portion of that query (i.e. query = ‘select col1, col2 from …’). Defaults to None. delimiter (str) – Delimiter to separate columns with. Defaults to ‘,’ header (boolean) – Specify True to return the column names. Defaults to True.
Returns:	If header is True, returns list of dicts where each dict is in the format {col1: val1, col2:val2, …}. Otherwise, returns a list of lists where each list is [val1, val2, …].
Return type:	data (list)
Raises:	`PostgrezExportError` – If an error occurs while exporting to an object.

class postgrez.postgrez.Load(setup='default')[source]¶

Bases: postgrez.postgrez.Connection

Class which handles loading data functionality.

load_from_file(table_name, filename, header=True, delimiter=', ', columns=None, quote=None, null=None)[source]¶

Parameters:

table_name (str) – name of table to load data into.
filename (str) – name of the file
header (boolean) – Specify True if the first row of the flat file contains the column names. Defaults to True.
delimiter (str) – delimiter with which the columns are separated. Defaults to ‘,’
columns (list) – iterable with name of the columns to import. The length and types should match the content of the file to read. If not specified, it is assumed that the entire table matches the file structure. Defaults to None.
quote (str) – Specifies the quoting character to be used when a data value is quoted. This must be a single one-byte character. Defaults to None, which uses the postgres default of a single double-quote.
null (str) –
Format which nulls (or missing values) are represented. Defaults to None, which corresponds to an empty string. If a CSV file contains a row like:

,1,2017-05-01,25.321

it will treat the first element as missing and inject a Null value into the database for the corresponding column.

Raises:

PostgrezLoadError – If an error occurs while loading.

load_from_object(table_name, data, columns=None, null=None)[source]¶

Load data into a Postgres table from a python list.

Parameters:

table_name (str) – name of table to load data into.
data (list) – list of tuples, where each row is a tuple
columns (list) – iterable with name of the columns to import. The length and types should match the content of the file to read. If not specified, it is assumed that the entire table matches the file structure. Defaults to None.
null (str) – Format which nulls (or missing values) are represented. Defaults to ‘None’. If a row is passed in as [None, 1, ‘2017-05-01’, 25.321], it will treat the first element as missing and inject a Null value into the database for the corresponding column.

Raises:

PostgrezLoadError – If an error occurs while loading.

postgrez.wrapper module¶

Wrapper module which contains wrapper functions for common psycopg2 routines.

postgrez.wrapper.execute(query, query_vars=None, columns=True, setup='default')[source]¶

A wrapper function around Cmd.execute() that returns formatted results.

Parameters:	query (str) – Query to be executed. Query can contain placeholders, as long as query_vars are supplied. query_vars (tuple, list or dict) – Variables to be executed with query. See http://initd.org/psycopg/docs/usage.html#query-parameters. columns (bool) – Return column names in results. Defaults to True. setup (str) – Name of the db setup to use in ~/.postgrez. If no setup is provided, looks for the ‘default’ key in ~/.postgrez which specifies the default configuration to use.
Returns:	Results from query. Returns None if no resultset was generated (i.e. insert into query, update query etc..).
Return type:	results (list)
Raises:	`PostgrezExecuteError` – If any error occurs reading of resultset.

postgrez.wrapper.export(query, filename=None, columns=None, delimiter=', ', header=True, null=None, setup='default')[source]¶

A wrapper function around Export.export_to methods. If a filename is provided, the records will be written to that file. Otherwise, records will be returned.

Parameters:	query (str) – A select query or a table_name filename (str, optional) – Filename to copy to. Defaults to None. columns (list) – List of column names to export. columns should only be provided if you are exporting a table (i.e. query = ‘table_name’). If query is a query to export, desired columns should be specified in the select portion of that query (i.e. query = ‘select col1, col2 from …’). Defaults to None. delimiter (str) – Delimiter to separate columns with. Defaults to ‘,’ header (boolean) – Specify True to return the column names. Defaults to True. null (str) – Specifies the string that represents a null value. Defaults to None, which uses the postgres default of an unquoted empty string. setup (str) – Name of the db setup to use in ~/.postgrez. If no setup is provided, looks for the ‘default’ key in ~/.postgrez which specifies the default configuration to use.
Returns:	If noe filename is provided, records will be returned. If header is True, returns list of dicts where each dict is in the format {col1: val1, col2:val2, …}. Otherwise, returns a list of lists where each list is [val1, val2, …].
Return type:	data (list)

postgrez.wrapper.load(table_name, filename=None, data=None, delimiter=', ', columns=None, quote=None, null=None, header=True, setup='default')[source]¶

A wrapper function around Load.load_from methods. If a filename is provided, the records will loaded from that file. Otherwise, records will be loaded from the supplied data arg.

Parameters:

table_name (str) – name of table to load data into.
filename (str, optional) – name of the file. Defaults to None.
data (list, optional) – list of tuples, where each row is a tuple. Defaults to None.
delimiter (str, optional) – If a filename is provided, delimiter with which the columns are separated can be specified. Defaults to ‘,’
columns (list) – iterable with name of the columns to import. The length and types should match the content of the file to read. If not specified, it is assumed that the entire table matches the file structure. Defaults to None.
null (str) – Format which nulls (or missing values) are represented. Defaults to ‘’ if a file is provided, ‘None’ if an object is provided. See a more detailed explanation in the Load.load_from_file() or Load.load_from_object() functions.
quote (str) – Specifies the quoting character to be used when a data value is quoted. This must be a single one-byte character. Defaults to None, which uses the postgres default of a single double-quote.
header (boolean) – Specify True if the first row of the flat file contains the column names. Defaults to True.
setup (str) – Name of the db setup to use in ~/.postgrez. If no setup is provided, looks for the ‘default’ key in ~/.postgrez which specifies the default configuration to use.

postgrez.utils module¶

Utils module, contains utility functions used throughout the postgrez codebase.

class postgrez.utils.IteratorFile(it)[source]¶

Bases: io.TextIOBase

Given an iterator which yields strings, return a file like object for reading those strings. Taken from: https://gist.github.com/jsheedy/ed81cdf18190183b3b7d

_it¶: Iterator of a data object.

_f¶: File-like object

read(length=9223372036854775807)[source]¶

Read file-like object.

Parameters:	length (integer) – maximum file length to read to
Returns:	string representation of file
Return type:	data (str)

postgrez.utils.build_copy_query(mode, query, columns=None, delimiter=', ', header=True, quote=None, null=None)[source]¶

Build query used in the cursor.copy_expert() method. Refer to https://www.postgresql.org/docs/9.2/static/sql-copy.html for more information.

Parameters:	mode (str) – Specifies whether the copy query is a COPY TO or a COPY FROM query. Accepts ‘load’ for copy to queries or ‘export’ for copy from queries. query (str) – A select query or a table name columns (list) – List of column names to export. columns should only be provided if you are exporting/loading a table (i.e. query = ‘table_name’). If query is a query to export, desired columns should be specified in the select portion of that query (i.e. query = ‘select col1, col2 from …’). Defaults to None, in which case all columns will be exported/loaded. delimiter (str) – Delimiter to separate columns with. Defaults to ‘,’. header (boolean) – Specify True to return the column names when exporting, or when column names are at the top of the flat file being loaded. Defaults to True. quote (str) – Specifies the quoting character to be used when a data value is quoted. This must be a single one-byte character. Defaults to None, which uses the postgres default of a single double-quote. null (str) – Specifies the string that represents a null value. Defaults to None, which uses the postgres default of an unquoted empty string.
Returns:	Formatted query to run in copy_expert()
Return type:	copy_query (str)
Raises:	`Exception` – If an error occurs while building hte query.

postgrez.utils.read_yaml(yaml_file)[source]¶

Read a yaml file.

Parameters:	yaml_file (str) – Full path of the yaml file.
Returns:	Dictionary of yaml_file contents. None is returned if an error occurs while reading.
Return type:	data (dict)
Raises:	`Exception` – If the yaml_file cannot be opened.

postgrez.logger module¶

Logger module used to create logging objects.

postgrez.logger.create_logger(name=None, log_level='INFO', log_format='%(asctime)s - %(module)s - %(levelname)s - %(message)s', log_filename=None)[source]¶

Create a logging object.

Parameters:	name (str, optional) – The name of the logger. Defaults to `None` log_level (str, optional) – The logging level. Defaults to `INFO` Accepts: DEBUG, INFO, WARNING, ERROR, CRITICAL format (str, optional) – Logging format. Defaults to `%(asctime)s - %(module)s - %(levelname)s - %(message)s` log_filename (str, optional) – Filename to stream log to. Defaults to None and streams to sys.stdout
Returns:	Logger object, configured with the passed in parameters.
Return type:	logging.Logger