The Utilities Module

List To SQL String

list_to_sql_string(lst)

turns python lists into a string that can be put into a postgres tuple and serve as a list in postgres

Parameters

lst (Tuple[str]) – a list of strings like ['pg_catalog','information_schema']

Return type

str

Returns

a string of items in SQL format "'pg_catalog','information_schema','hi'"

Make Qualified Schema Name

make_qualified_schema_name(schema_name, table_name)

produces a qualified schema name given a schema and table

Parameters

  • schema_name (str) – the schema name

  • table_name (str) – the table name

Return type

str

Returns

a quoted dot separated qualified schema name

Check Nth Arg

check_nth_arg_is_of_type(n, type_to_check)

this decorator allows us to do some basic type checking

Parameters

  • n (int) – the “nth” argument of the decorated function

  • type_to_check (type) – the type to ensure that the “nth” value is of

Raises

plpy_wrapper.exceptions.TypeException

Create PLPython Triggers

create_plpython_triggers(plpy_wrapper, schema, table_name, trigger_template_path=PosixPath('/home/docs/checkouts/readthedocs.org/user_builds/plpy-wrapper/envs/latest/lib/python3.7/site-packages/plpy_wrapper/trigger_process_template.txt'), trigger_func_definition='', trigger_func_name='')

sets up triggers and the trigger function for a table. After running this function, triggers will now be routed to the custom function where you can run arbitrary python inside of the plpython.trigger.Trigger methods. Should be executed from the DB like so:

do $$
import plpy_wrapper
wrapper = plpy_wrapper.PLPYWrapper(globals())
plpy_wrapper.utilities.create_plpython_triggers(wrapper,schema,table_name)
$$ language plpython3u;
Parameters

  • trigger_func_name (Optional[str]) – if you provided a trigger_func_definition and your function name is different than the default function name generated by this function, provide the value for the function name

  • trigger_func_definition (Optional[str]) – if provided, the value from trigger_template_path is ignored. The trigger_func_definition is sql that will be run before the CREATE trigger statements

  • trigger_template_path (Union[str, Path, None]) – the path of the template that will be the base for trigger procedures. The file text must contain the following keyword parameters: schema, table, func_name otherwise unexpected behavior can be expected

  • plpy_wrapper (PLPYWrapper) – instance of plpy_wrapper.plpy_wrapper.PLPYWrapper

  • schema (str) – the schema name the table is located in

  • table_name (str) – the table to add the triggers and function to

Get All Tables

get_all_tables(plpy_wrapper, exclude_schemas=, exclude_tables=)

Queries postgres for all tables

Parameters

  • plpy_wrapper (PLPYWrapper) – PLPYWrapper instance

  • exclude_schemas (Tuple[str]) – schemas to exclude from the query

  • exclude_tables (Tuple[str]) – tables to exclude from the query

Return type

ResultSet

Returns

Execute Per Table

execute_per_table(plpy_wrapper, execute_definition, exclude_schemas='pg_catalog', 'information_schema', exclude_tables=)

Run an SQL command per table. The execute_definition string must contain either {table} and/or {schema_qualified_table_name} as keyword parameters

Parameters

  • plpy_wrapper (PLPYWrapper) – PLPYWrapper instance

  • execute_definition (str) – the SQL to run per table

  • exclude_schemas (Tuple[str]) – schemas to exclude from the execution

  • exclude_tables (Tuple[str]) – tables to exclude from the execution

Return type

Dict[str, ResultSet]

Returns

a dictionary with the table’s qualified schema name as the key and the ResultSet as the value