pystratum_pgsql package

Subpackages

• pystratum_pgsql.backend package
  • Submodules
  • pystratum_pgsql.backend.PgSqlBackend module
  • pystratum_pgsql.backend.PgSqlConstantWorker module
  • pystratum_pgsql.backend.PgSqlRoutineLoaderWorker module
  • pystratum_pgsql.backend.PgSqlRoutineWrapperGeneratorWorker module
  • pystratum_pgsql.backend.PgSqlWorker module
  • Module contents
• pystratum_pgsql.helper package
  • Submodules
  • pystratum_pgsql.helper.PgSqlDataTypeHelper module
  • pystratum_pgsql.helper.PgSqlRoutineLoaderHelper module
  • Module contents
• pystratum_pgsql.wrapper package
  • Submodules
  • pystratum_pgsql.wrapper.PgSqlFunctionsWrapper module
  • pystratum_pgsql.wrapper.PgSqlLogWrapper module
  • pystratum_pgsql.wrapper.PgSqlNoneWrapper module
  • pystratum_pgsql.wrapper.PgSqlRow0Wrapper module
  • pystratum_pgsql.wrapper.PgSqlRow1Wrapper module
  • pystratum_pgsql.wrapper.PgSqlRowsWithIndexWrapper module
  • pystratum_pgsql.wrapper.PgSqlRowsWithKeyWrapper module
  • pystratum_pgsql.wrapper.PgSqlRowsWrapper module
  • pystratum_pgsql.wrapper.PgSqlSingleton0Wrapper module
  • pystratum_pgsql.wrapper.PgSqlSingleton1Wrapper module
  • pystratum_pgsql.wrapper.PgSqlTableWrapper module
  • pystratum_pgsql.wrapper.PgSqlWrapper module
  • Module contents

Submodules

pystratum_pgsql.PgSqlConnector module

class pystratum_pgsql.PgSqlConnector.PgSqlConnector

Bases: object

Interface for classes for connecting to a PostgreSQL instances.

connect() → Any

Connects to a PostgreSQL instance.

Return type:psycopg2.extensions.connection

disconnect() → None

Disconnects from a PostgreSQL instance.

pystratum_pgsql.PgSqlDataLayer module

class pystratum_pgsql.PgSqlDataLayer.PgSqlDataLayer(connector: pystratum_pgsql.PgSqlConnector.PgSqlConnector)

Bases: object

Class for connecting to a PostgreSQL instance and executing SQL statements. Also, a parent class for classes with static wrapper methods for executing stored procedures and functions.

commit()

Commits the current transaction.

connect() → None

Connects to a PostgreSQL instance.

copy_expert(sql: str, file: object, size: int = 8192) → int

Submit a user-composed COPY statement. Returns the number of rows copied.

Parameters:

• sql (str) – The COPY statement to execute.
• file (T) – A file-like object to read or write (according to sql).
• size (int) – Size of the read buffer to be used in COPY FROM.

Return type:

int

copy_from(file: object, table: str, sep: str = '\t', null: str = '\\N', size: int = 8192, columns: Union[None, Iterable[T_co]] = None) → int

Read data from the file-like object file appending them to the table named table. Returns the number of rows copied.

Parameters:

• file (T) – File-like object to read data from. It must have both read() and readline() methods.
• table (str) – Name of the table to copy data into.
• sep (str) – Columns separator expected in the file. Defaults to a tab.
• null (str) – Textual representation of NULL in the file. The default is the two characters string N.
• size (int) – Size of the buffer used to read from the file.
• columns (iterable) – 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.

Return type:

int

copy_to(file: object, table: str, sep: str = '\t', null: str = '\\N', columns: Union[None, Iterable[T_co]] = None) → int

Write the content of the table named table to the file-like object file. Returns the number of rows copied.

Parameters:

• file (T) – File-like object to write data into. It must have a write() method.
• table (str) – Name of the table to copy data from.
• sep (str) – Columns separator expected in the file. Defaults to a tab.
• null (str) – Textual representation of NULL in the file. The default is the two characters string N.
• columns (iterable) – Iterable with name of the columns to export. If not specified, export all the columns.

Return type:

int

disconnect() → None

Disconnects from the PostgreSQL instance.

execute_none(sql: str, *params) → int

Executes a query that does not select any rows.

Parameters:

• sql (string) – The SQL statement.
• params (tuple) – The values for the statement.

Return type:

int

execute_rows(sql: str, *params) → List[T]

Executes a query that selects 0 or more rows. Returns the selected rows (an empty list if no rows are selected).

Parameters:

• sql (str) – The SQL statement.
• params (iterable) – The arguments for the statement.

Return type:

list[dict]

execute_singleton1(sql: str, *params) → object

Executes query that selects 1 row with 1 column. Returns the value of the selected column.

Parameters:

• sql (str) – The SQL call the the stored procedure.
• params (iterable) – The arguments for the stored procedure.

Return type:

object

execute_sp_log(sql: str, *params) → int

Executes a stored procedure with log statements. Returns the number of lines in the log.

Parameters:

• sql (str) – The SQL call the the stored procedure.
• params (iterable) – The arguments for the stored procedure.

Return type:

int

execute_sp_none(sql: str, *params) → None

Executes a stored procedure that does not select any rows.

Unfortunately, it is not possible to retrieve the number of affected rows of the SQL statement in the stored function as with execute_none (cursor.rowcount is always 1 using cursor.execute and cursor.callproc).

Parameters:

• sql (str) – The SQL statement.
• params (iterable) – The arguments for the statement.

execute_sp_row0(sql: str, *params) → Union[None, Dict[KT, VT]]

Executes a stored procedure that selects 0 or 1 row. Returns the selected row or None.

Parameters:

• sql (str) – The SQL statement.
• params (iterable) – The arguments for the statement.

Return type:

None|dict[str,object]

execute_sp_row1(sql: str, *params) → Dict[KT, VT]

Executes a stored procedure that selects 1 row. Returns the selected row.

Parameters:

• sql (str) – The SQL call the the stored procedure.
• params (iterable) – The arguments for the stored procedure.

Return type:

dict[str,object]

execute_sp_rows(sql: str, *params) → List[T]

Executes a stored procedure that selects 0 or more rows. Returns the selected rows (an empty list if no rows are selected).

Parameters:

• sql (str) – The SQL call the the stored procedure.
• params (iterable) – The arguments for the stored procedure.

Return type:

list[dict[str,object]]

execute_sp_singleton0(sql: str, *params) → object

Executes a stored procedure that selects 0 or 1 row with 1 column. Returns the value of selected column or None.

Parameters:

• sql (str) – The SQL call the the stored procedure.
• params (iterable) – The arguments for the stored procedure.

Return type:

object

execute_sp_singleton1(sql: str, *params) → object

Executes a stored procedure that selects 1 row with 1 column. Returns the value of the selected column.

Parameters:

• sql (str) – The SQL call the the stored procedure.
• params (iterable) – The arguments for the stored procedure.

Return type:

object

execute_sp_table(sql: str, *params) → int

Executes a stored routine with designation type “table”. Returns the number of rows.

Parameters:

• sql (str) – The SQL calling the the stored procedure.
• params (iterable) – The arguments for calling the stored routine.

Return type:

int

line_buffered = True

If True log messages from stored procedures with designation type ‘log’ are line buffered (Note: In python sys.stdout is buffered by default).

rollback() → None

Rolls back the current transaction.

sp_log_fetch = 'stratum_log_fetch'

The name of the stored routine that must be run after a store routine with designation type “log”.

sp_log_init = 'stratum_log_init'

The name of the stored routine that must be run before a store routine with designation type “log”.

start_transaction(isolation_level: str = 'READ-COMMITTED', readonly: bool = False) → None

Starts a transaction.

Parameters:

• isolation_level (str) – The isolation level.
• readonly (bool) –

pystratum_pgsql.PgSqlDefaultConnector module

class pystratum_pgsql.PgSqlDefaultConnector.PgSqlDefaultConnector(params: Dict[str, Union[str, int]])

Bases: pystratum_pgsql.PgSqlConnector.PgSqlConnector

Connects to a PostgreSQL instance using user name and password.

connect() → Any

Connects to a PostgreSQL instance.

Return type:psycopg2.extensions.connection

disconnect() → None

Disconnects from a PostgreSQL instance.

pystratum_pgsql.PgSqlMetadataDataLayer module

class pystratum_pgsql.PgSqlMetadataDataLayer.PgSqlMetadataDataLayer(io: pystratum_backend.StratumStyle.StratumStyle, connector: pystratum_pgsql.PgSqlConnector.PgSqlConnector)

Bases: pystratum_common.MetadataDataLayer.MetadataDataLayer

Data layer for retrieving metadata and loading stored routines.

call_stored_routine(routine_name: str) → Optional[int]

Class a stored procedure without arguments.

Parameters:routine_name (str) – The name of the procedure. Return type:int|None

check_table_exists(table_name: str) → Optional[int]

Checks if a table exists in the current schema.

Parameters:table_name (str) – The name of the table. Return type:int|None

commit() → None

Commits the current transaction.

connect() → None

Connects to a PostgreSQL instance.

describe_table(table_name: str) → List[T]

Describes a table.

Parameters:table_name (str) – The name of the table. Return type:list[dict[str,Object]]

disconnect() → None

Disconnects from the PostgreSQL instance.

drop_stored_routine(routine_type: str, routine_name: str, routine_args: str) → None

Drops a stored routine if it exists.

Parameters:

• routine_type (str) – The type of the routine (i.e. PROCEDURE or FUNCTION).
• routine_name (str) – The name of the routine.
• routine_args (str) – The routine arguments types as comma separated list.

drop_temporary_table(table_name: str) → None

Drops a temporary table.

Parameters:table_name (str) – The name of the table.

execute_none(query: str) → int

Executes a query that does not select any rows.

Parameters:query (str) – The query. Return type:int

execute_rows(query: str) → List[T]

Executes a query that selects 0 or more rows. Returns the selected rows (an empty list if no rows are selected).

Parameters:query (str) – The query. Return type:list[dict[str,Object]]

execute_singleton1(query: str) → object

Executes SQL statement that selects 1 row with 1 column. Returns the value of the selected column.

Parameters:query (str) – The query. Return type:Object

get_all_table_columns() → List[T]

Selects metadata of all columns of all tables.

Return type:list[dict[str,Object]]

get_label_tables(regex: str) → List[T]

Selects metadata of tables with a label column.

Parameters:regex (str) – The regular expression for columns which we want to use. Return type:list[dict[str,Object]]

get_labels_from_table(table_name: str, id_column_name: str, label_column_name: str) → List[T]

Selects all labels from a table with labels.

Parameters:

• table_name (str) – The name of the table.
• id_column_name (str) – The name of the auto increment column.
• label_column_name (str) – The name of the column with labels.

Return type:

list[dict[str,Object]]

get_routine_parameters(routine_name: str) → List[T]

Selects metadata of the parameters of a stored routine.

Parameters:routine_name (str) – The name of the routine. Return type:list[dict[str,Object]]

get_routines() → List[T]

Selects metadata of all routines in the current schema.

Return type:list[dict[str,Object]]

rollback() → None

Rollbacks the current transaction.

Module contents