pystratum_mssql package

Submodules

pystratum_mssql.MsSqlConnector module

class pystratum_mssql.MsSqlConnector.MsSqlConnector[source]

Bases: object

Interface for classes for connecting to a MS SQL Server instances.

connect() → Any[source]

Connects to a MS SQL Server instance.

disconnect() → None[source]

Disconnects from a MS SQL Server instance.

pystratum_mssql.MsSqlDataLayer module

class pystratum_mssql.MsSqlDataLayer.MsSqlDataLayer(connector: pystratum_mssql.MsSqlConnector.MsSqlConnector)[source]

Bases: object

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

autocommit(status: bool) → None[source]

Sets auto commit mode. See http://pymssql.org/en/stable/ref/pymssql.html#pymssql.Connection.autocommit.

Parameters:status (bool) – True: Auto commit on. False: Auto commit off.
commit() → None[source]

Commits the current transaction. See http://pymssql.org/en/stable/ref/pymssql.html#pymssql.Connection.commit.

connect() → None[source]

Connects to a MS SQL Server instance.

disconnect() → None[source]

Disconnects from the MS SQL Server instance. See http://pymssql.org/en/stable/ref/pymssql.html#pymssql.Connection.close.

execute_csv(sql: str, filename: str, dialect: str = 'unix', encoding: str = 'utf-8') → int[source]
execute_log(sql: str, *params) → int[source]

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

Parameters:
  • sql (str) – The SQL statement.
  • params (iterable) – The parameters.
Return type:

int

execute_none(sql: str, *params) → None[source]

Executes a query that does not select any rows.

Parameters:
  • sql (str) – The SQL statement.
  • params (iterable) – The parameters.
Return type:

None

execute_row0(sql, *params) → Optional[Dict[str, Any]][source]

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

Parameters:
  • sql (str) – The SQL statement.
  • params (iterable) – The parameters.
Return type:

None|dict[str,*]

execute_row1(sql: str, *params) → Dict[str, Any][source]

Executes a query that selects 1 row. Returns the selected row.

Parameters:
  • sql (str) – The SQL statement.
  • params (iterable) – The parameters.
Return type:

dict[str,*]

execute_rows(sql: str, *params) → List[Dict[str, Any]][source]

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 parameters.
Return type:

list[dict[str,*]]

execute_singleton0(sql: str, *params) → Any[source]

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

Parameters:
  • sql (str) – The SQL statement.
  • params (iterable) – The parameters.
Return type:

execute_singleton1(sql: str, *params) → Any[source]

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

:param str sql:The SQL statement. :param iterable params: The parameters.

Return type:
execute_sp_none(sql: str, *params) → None[source]

Executes a stored routine that does not select any rows.

Parameters:
  • sql (str) – The SQL calling the stored procedure.
  • params (iterable) – The parameters for the stored procedure.
Return type:

None

execute_sp_row0(sql: str, *params) → Optional[Dict[str, Any]][source]

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

Parameters:
  • sql (str) – The SQL call the the stored procedure.
  • params (iterable) – The parameters for the stored procedure.
Return type:

None|dict[str,*]

execute_sp_row1(sql: str, *params) → Dict[str, Any][source]

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

Parameters:
  • sql (str) – The SQL calling the the stored procedure.
  • params (iterable) – The parameters for the stored procedure.
Return type:

dict[str,*]

execute_sp_rows(sql: str, *params) → List[Dict[str, Any]][source]

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 calling the the stored procedure.
  • params (iterable) – The parameters for the stored procedure.
Return type:

list[dict[str,*]]

execute_sp_singleton0(sql: str, *params) → Any[source]

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 calling the stored procedure.
  • params (iterable) – The parameters for the stored procedure.
Return type:

execute_sp_singleton1(sql: str, *params) → Any[source]

Executes a stored routine with designation type “table”, i.e a stored routine that is expected to select 1 row with 1 column.

Parameters:
  • sql (str) – The SQL calling the the stored procedure.
  • params (iterable) – The parameters for the stored procedure.
Return type:

  • The value of the selected column.

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).

Type:bool
rollback() → None[source]

Rolls back the current transaction. See http://pymssql.org/en/stable/ref/pymssql.html#pymssql.Connection.rollback.

static stratum_msg_handler(msgstate: str, severity: int, srvname: str, procname: str, line: int, msgtext: bin) → None[source]

Custom message handler suppressing some superfluous messages.

pystratum_mssql.MsSqlDefaultConnector module

class pystratum_mssql.MsSqlDefaultConnector.MsSqlDefaultConnector(params: Dict[str, Union[str, int]])[source]

Bases: pystratum_mssql.MsSqlConnector.MsSqlConnector

Connects to a MySQL instance using user name and password.

connect() → Any[source]

Connects to the MySQL instance.

disconnect() → None[source]

Disconnects from the MySQL instance.

pystratum_mssql.MsSqlMetadataDataLayer module

class pystratum_mssql.MsSqlMetadataDataLayer.MsSqlMetadataDataLayer(io: pystratum_backend.StratumStyle.StratumStyle, connector: pystratum_mssql.MsSqlConnector.MsSqlConnector)[source]

Bases: pystratum_common.MetadataDataLayer.MetadataDataLayer

Data layer for retrieving metadata and loading stored routines.

commit() → None[source]

Connects to a SQL Server instance.

connect() → None[source]

Connects to a SQL Server instance.

disconnect() → None[source]

Disconnects from the SQL Server instance.

drop_stored_routine(routine_type: str, schema_name: str, routine_name: str) → None[source]

Drops a stored routine if it exists.

Parameters:
  • routine_type (str) – The type of the routine (i.e. procedure or function).
  • schema_name (str) – The name of the schema.
  • routine_name (str) – The name of the routine.
drop_temporary_table(table_name: str) → None[source]

Drops a temporary table.

Parameters:table_name (str) – The name of the table.
execute_none(query: str) → None[source]

Executes a query that does not select any rows.

Parameters:query (str) – The query.
Return type:int
execute_rows(query: str) → List[Dict[str, Any]][source]

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,*]]
get_all_table_columns() → List[Dict[str, Any]][source]

Selects metadata of all columns of all tables.

Return type:list[dict[str,*]]
get_label_tables(regex: str) → List[Dict[str, Any]][source]

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,*]]
get_labels_from_table(database_name: str, schema_name: str, table_name: str, id_column_name: str, label_column_name: str) → List[Dict[str, Any]][source]

Selects all labels from a table with labels.

Parameters:
  • database_name (str) – The name of the database.
  • schema_name (str) – The name of the schema.
  • 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,*]]

get_routine_parameters(schema_name: str, routine_name: str) → List[Dict[str, Any]][source]

Selects metadata of the parameters of a stored routine.

Parameters:
  • schema_name (str) – The name of the schema.
  • routine_name (str) – The name of the routine.
Return type:

list[dict[str,*]]

get_routines() → List[Dict[str, Any]][source]

Selects metadata of all routines.

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

Module contents