actio_python_utils.database_functions.SavepointCursor

class actio_python_utils.database_functions.SavepointCursor(*args, log_level='INFO', log_name='sql_debug', **kwargs)

Bases: LoggingCursor

Wraps LoggingCursor methods to use a savepoint context manager

__init__(*args, log_level='INFO', log_name='sql_debug', **kwargs)

Methods

__init__(*args[, log_level, log_name])
callproc(procname[, vars])	callproc(procname, parameters=None) -- Execute stored procedure.
cast(oid, s)	Convert the string s to a Python object according to its oid.
close	close() -- Close the cursor.
confirm_table_and_file_columns_match(...[, ...])	Confirm that the field names in a table match those of a file.
copy_expert(*args, **kwargs)	Logs the sql statement and executes it if dry_run = False
copy_from(*args, **kwargs)	copy_from(file, table, sep='t', null='\N', size=8192, columns=None) -- Copy table from file.
copy_to(*args, **kwargs)	copy_to(file, table, sep='t', null='\N', columns=None) -- Copy table to file.
copy_to_csv(sql, file, *args[, dry_run])	Logs the sql statement and executes it if dry_run = False
drop_table_constraints(*args, **kwargs)	Takes a list of tables and finds all constraints defined on them, then drops them.
drop_table_keys(*args, **kwargs)	Takes a list of tables and drops all indexes defined on them.
execute(*args, **kwargs)	Logs the query and executes it if dry_run = False
executemany	executemany(query, vars_list) -- Execute many queries with bound vars.
fetchall()	Return all the remaining rows of a query result set.
fetchmany([size])	Return the next size rows of a query result set in the form of a list of tuples (by default) or using the sequence factory previously set in the row_factory attribute.
fetchone()	Return the next row of a query result set in the form of a tuple (by default) or using the sequence factory previously set in the row_factory attribute.
get_db_table_columns(*args, **kwargs)	Get the list of all non-generated column names for a PostgreSQL table.
get_table_constraint_statements(*args, **kwargs)	Takes a list of tables and returns a list of all the SQL definitions of constraints defined on the tables.
import_table(*args, **kwargs)	Load a PostgreSQL CSV or TEXT format file to the database
mogrify(query[, vars])
nextset	nextset() -- Skip to next set of data.
scroll	scroll(value, mode='relative') -- Scroll to new position according to mode.
setinputsizes	setinputsizes(sizes) -- Set memory areas before execute.
setoutputsize	setoutputsize(size, column=None) -- Set column buffer size.

Attributes

arraysize	Number of records fetchmany() must fetch if not explicitly specified.
binary_types
closed	True if cursor is closed, False if cursor is open
connection	The connection where the cursor comes from.
description	Cursor description as defined in DBAPI-2.0.
itersize	Number of records iter(cur) must fetch per network roundtrip.
lastrowid	The oid of the last row inserted by the cursor.
name
pgresult_ptr	pgresult_ptr -- Get the PGresult structure pointer.
query	The last query text sent to the backend.
row_factory
rowcount	Number of rows read from the backend in the last command.
rownumber	The current row position.
scrollable	Set or return cursor use of SCROLL
statusmessage	The return message of the last command.
string_types
typecaster
tzinfo_factory
withhold	Set or return cursor use of WITH HOLD

arraysize

Number of records fetchmany() must fetch if not explicitly specified.

callproc(procname, vars=None)

callproc(procname, parameters=None) – Execute stored procedure.

cast(oid, s) → value

Convert the string s to a Python object according to its oid.

Look for a typecaster first in the cursor, then in its connection,then in the global register. If no suitable typecaster is found,leave the value as a string.

close()

close() – Close the cursor.

closed

True if cursor is closed, False if cursor is open

confirm_table_and_file_columns_match(table_fn, table_name, sep=',', sanitize=False, allow_columns_subset=False)

Confirm that the field names in a table match those of a file.

Parameters:

• table_fn (str) – The file name of the data source to check

• table_name (str) – The name of the database table to check

• sep (str, default: ',') – The column separator to use

• sanitize (bool, default: False) – Whether to sanitize column names with get_csv_fields()

• allow_columns_subset (bool, default: False) – Whether to allow loading to the table with only a subset of the columns

Raises:

ValueError – If file columns do not match database table columns

Return type:

list[str]

Returns:

The list of column names to load

connection

The connection where the cursor comes from.

copy_expert(*args, **kwargs)

Logs the sql statement and executes it if dry_run = False

Parameters:

• sql – The SQL statement to execute

• file – The path to the file to import

• *args – Any positional arguments

• dry_run – Do a dry run

• **kwargs – Any named arguments

Return type:

None

copy_from(*args, **kwargs)

copy_from(file, table, sep=’t’, null=’\N’, size=8192, columns=None) – Copy table from file.

Return type:

None

copy_to(*args, **kwargs)

copy_to(file, table, sep=’t’, null=’\N’, columns=None) – Copy table to file.

Return type:

None

copy_to_csv(sql, file, *args, dry_run=False, **kwargs)

Logs the sql statement and executes it if dry_run = False

Parameters:

• sql (str) – The SQL statement to execute

• file (str) – The path to the file to write to

• *args – Any positional arguments

• dry_run (default: False) – Do a dry run

• **kwargs – Any named arguments

Return type:

None

description

Cursor description as defined in DBAPI-2.0.

drop_table_constraints(*args, **kwargs)

Takes a list of tables and finds all constraints defined on them, then drops them. Foreign key constraints are processed first because attempting to drop a unique key on a column that is referenced in a foreign key results in an error.

Parameters:

• table_list – The list of tables to get constraints on

• dry_run – Do a dry run

Return type:

None

drop_table_keys(*args, **kwargs)

Takes a list of tables and drops all indexes defined on them.

Parameters:

• table_list – The list of tables to drop index on

• dry_run – Do a dry run

Return type:

None

execute(*args, **kwargs)

Logs the query and executes it if dry_run = False

Parameters:

• query – The SQL query to execute

• vars – Variables to bind to the query

• *args – Any positional arguments

• dry_run – Do a dry run

• dont_use_savepoint – Ignored

• **kwargs – Any named arguments

Return type:

None

executemany()

executemany(query, vars_list) – Execute many queries with bound vars.

fetchall() → list of tuple

Return all the remaining rows of a query result set.

Rows are returned in the form of a list of tuples (by default) or using the sequence factory previously set in the row_factory attribute. Return !None when no more data is available.

fetchmany(size=self.arraysize) → list of tuple

Return the next size rows of a query result set in the form of a list of tuples (by default) or using the sequence factory previously set in the row_factory attribute.

Return an empty list when no more data is available.

fetchone() → tuple or None

Return the next row of a query result set in the form of a tuple (by default) or using the sequence factory previously set in the row_factory attribute. Return !None when no more data is available.

get_db_table_columns(*args, **kwargs)

Get the list of all non-generated column names for a PostgreSQL table.

Parameters:

table_name – The possibly schema qualified table name

Return type:

list[str]

Returns:

The list of non-generated columns from the table, ordered by their position in the database

get_table_constraint_statements(*args, **kwargs)

Takes a list of tables and returns a list of all the SQL definitions of constraints defined on the tables. The constraints are ordered by keys, then other constraints on the tables, and lastly foreign keys defined on other tables that reference one of the tables specified. The purpose of this is to be able to drop these constraints, load data, and recreate them for efficiency.

Parameters:

table_list – The list of tables to get constraints on

Return type:

list[str]

Returns:

The list of constraints

import_table(*args, **kwargs)

Load a PostgreSQL CSV or TEXT format file to the database

Parameters:

• table_fn – The file name of the data source to load

• table_name – The name of the database table to load to

• csv_format – Whether to use CSV format (otherwise TEXT)

• sep – The column separator to use

• sanitize – Whether to sanitize column names with get_csv_fields()

• truncate – Whether to truncate the table before loading

• header – Whether the file has a header row

• quote – The quote character

• escape – The escape character

• allow_columns_subset – Whether to allow loading to the table with only a subset of the columns

• fields – A list of fields for the file; this value is required if there is no header

Raises:

ValueError – If header and fields specified or if neither is specified

Return type:

None

itersize

Number of records iter(cur) must fetch per network roundtrip.

lastrowid

The oid of the last row inserted by the cursor.

mogrify(query, vars=None) → str -- Return query after vars binding.

nextset()

nextset() – Skip to next set of data.

This method is not supported (PostgreSQL does not have multiple data sets) and will raise a NotSupportedError exception.

pgresult_ptr

pgresult_ptr – Get the PGresult structure pointer.

query

The last query text sent to the backend.

rowcount

Number of rows read from the backend in the last command.

rownumber

The current row position.

scroll()

scroll(value, mode=’relative’) – Scroll to new position according to mode.

scrollable

Set or return cursor use of SCROLL

setinputsizes()

setinputsizes(sizes) – Set memory areas before execute.

This method currently does nothing but it is safe to call it.

setoutputsize()

setoutputsize(size, column=None) – Set column buffer size.

This method currently does nothing but it is safe to call it.

statusmessage

The return message of the last command.

withhold

Set or return cursor use of WITH HOLD