`mt.sql.psql`

Useful modules for accessing PostgreSQL

Functions 

pg_get_locked_transactions(): Obtains a dataframe representing transactions which have been locked by the server.
pg_cancel_backend(): Cancels a backend transaction given its pid.
pg_cancel_all_backends(): Cancels all backend transactions.
compliance_check(): Checks if a dataframe is compliant to PSQL.
as_column_name(): Converts a string into a PSQL-compliant column name.
to_sql(): Writes records stored in a DataFrame to a PostgreSQL database.
rename_schema(): Renames a schema.
list_matviews(): Lists all materialized views of a given schema.
list_foreign_tables(): Lists all foreign tables of a given schema.
list_frames(): Lists all dataframes (tables/views/materialized views/foreign tables) of a given schema.
list_all_frames(): Lists all dataframes (tables/views/materialized views/foreign tables) across all schemas.
get_frame_length(): Gets the number of rows of a dataframes (tables/views/materialized views).
get_frame_dependencies(): Gets the list of all frames that depend on the given frame.
get_view_sql_code(): Gets the SQL string of a view.
rename_table(): Renames a table of a schema.
vacuum_table(): Vacuums a table of a schema.
drop_table(): Drops a table if it exists, with restrict or cascade options.
rename_view(): Renames a view of a schema.
drop_view(): Drops a view if it exists, with restrict or cascade options.
rename_matview(): Renames a materialized view of a schema.
refresh_matview(): Refreshes a materialized view of a schema.
drop_matview(): Drops a mateiralized view if it exists, with restrict or cascade options.
frame_exists(): Checks if a frame exists.
drop_frame(): Drops a frame (table/view/mateiralized view) if it exists, with restrict or cascade options.
list_columns_ext(): Lists all columns of a given table of a given schema.
list_columns(): Lists all columns of a given table of a given schema.
list_primary_columns_ext(): Lists all primary columns of a given frame of a given schema.
list_primary_columns(): Lists all primary columns of a given frame of a given schema.
rename_column(): Renames a column of a table.
drop_column(): Drops a column of a table.
make_primary(): Removes all duplicate records from an unindexed table based on a list of keys and then make the keys primary.
comparesync_table(): Compares a local CSV table with a remote PostgreSQL to find out which rows are the same or different.
readsync_table(): Reads and updates a local CSV table from a PostgreSQL table by updating only rows which have been changed.
writesync_table(): Writes and updates a remote PostgreSQL table from a local CSV table by updating only rows which have been changed.

mt.sql.psql.pg_get_locked_transactions(engine, schema: str | None = None)

Obtains a dataframe representing transactions which have been locked by the server.

Parameters:

engine (sqlalchemy.engine.Engine) – connection engine
schema (str or None) – If None, then all schemas are considered and not just the public schema. Else, scope down to a single schema.

Returns:

A table containing the current backend transactions

Return type:

pd.DataFrame

mt.sql.psql.pg_cancel_backend(engine, pid)

Cancels a backend transaction given its pid.

Parameters:

engine (sqlalchemy.engine.Engine) – connection engine
pid (int) – the backend pid to be cancelled

mt.sql.psql.pg_cancel_all_backends(engine, schema: str | None = None, logger: IndentedLoggerAdapter | None = None)

Cancels all backend transactions.

Parameters:

engine (sqlalchemy.engine.Engine) – connection engine
schema (str or None) – If None, then all schemas are considered and not just the public schema. Else, scope down to a single schema.
logger (mt.logg.IndentedLoggerAdapter, optional) – logger for debugging

mt.sql.psql.compliance_check(df: DataFrame)

Checks if a dataframe is compliant to PSQL.

It must have no index, or indices which do not match with any column.

Parameters:: df (pandas.DataFrame) – the input dataframe
Raises:: ValueError – when an error is encountered.

mt.sql.psql.as_column_name(s)

Converts a string into a PSQL-compliant column name.

Parameters:: s (str) – a string
Returns:: s2 – a lower-case alpha-numeric and underscore-only string
Return type:: str
Raises:: ValueError if the string cannot be converted. –

mt.sql.psql.to_sql(df, name, engine, schema: str | None = None, if_exists='fail', nb_trials: int = 3, logger: IndentedLoggerAdapter | None = None, **kwargs)

Writes records stored in a DataFrame to a PostgreSQL database.

With a number of trials to overcome OperationalError.

Parameters:

df (pandas.DataFrame) – dataframe to be sent to the server
name (str) – name of the table to be written to
engine (sqlalchemy.engine.Engine) – connection engine to the server
schema (string, optional) – Specify the schema. If None, use default schema.
if_exists (str) – what to do when the table exists. Beside all options available from pandas.to_sql(), a new option called ‘gently_replace’ is introduced, in which it will avoid dropping the table by trying to delete all entries and then inserting new entries. But it will only do so if the remote table contains exactly all the columns that the local dataframe has, and vice-versa.
nb_trials (int) – number of query trials
logger (mt.logg.IndentedLoggerAdapter, optional) – logger for debugging

Raises:

sqlalchemy.exc.ProgrammingError if the local and remote frames do not have the same structure –

Notes

The original pandas.DataFrame.to_sql() function does not turn any index into a primary key in PSQL. This function attempts to fix that problem. It takes as input a PSQL-compliant dataframe (see compliance_check()). It ignores any input index or index_label keyword. Instead, it considers 2 cases. If the dataframe’s has an index or indices, then the tuple of all indices is turned into the primary key. If not, there is no primary key and no index is uploaded.

mt.sql.psql

Functions

`mt.sql.psql`

Functions 