fusetools.db_etl_tools.Postgres

class fusetools.db_etl_tools.Postgres

Bases: object

Generic functions for Postgres SQL queries and ETL.

Methods

insert_df_pg	Executes an INSERT INTO statement for a given Pandas DataFrame into a Postgres table..
insert_val_pg	Creates SQL to run an INSERT operation of a given Postgres table.
make_df_tbl_pg	Creates SQL to run a CREATE TABLE statement based on a Pandas DataFrame.
make_tbl_complete_pg	Executes a series of SQL statements to CREATE and INSERT into a table from a Pandas DataFrame.
run_query_pg	Executes a SQL statement with a Postgres database connection.
sequential_load_pg	param override
sequential_load_pg_wk	param rptg_dates
upsert_tbl_pg	Creates SQL to run an UPSERT (INSERT new records or UPDATE existing records) operation of a given Postgres table.
upsert_val_pg	Creates SQL to run an UPSERT (INSERT new records or UPDATE existing records) operation of a given Postgres table.

classmethod insert_df_pg(cursor, conn, df, tbl_name, return_statement=None)

Executes an INSERT INTO statement for a given Pandas DataFrame into a Postgres table..

Parameters

• cursor – Postgres database cursor object.

• conn – Postgres database connection object.

• df – Pandas DataFrame to insert into a Postgres table.

• tbl_name – Postgres table name.

Returns

Elapsed time to execute query.

classmethod insert_val_pg(col_list, val_list, tbl_name)

Creates SQL to run an INSERT operation of a given Postgres table.

Parameters

• col_list – List of columns to INSERT or UPDATE.

• val_list – List of values to INSERT or UPDATE.

• tbl_name – Name of Postgres table.

Returns

SQL to run an INSERT statement.

classmethod make_df_tbl_pg(tbl_name, df)

Creates SQL to run a CREATE TABLE statement based on a Pandas DataFrame.

Parameters

• tbl_name – Postgres table name.

• df – Pandas DataFrame.

Returns

CREATE TABLE SQL statement.

classmethod make_tbl_complete_pg(df, tbl_name, conn, cursor, batch_size=False)

Executes a series of SQL statements to CREATE and INSERT into a table from a Pandas DataFrame.

Parameters

• df – Pandas DataFrame to create a table from.

• tbl_name – Name of table to be created.

• conn – Postgres database connection object.

• cursor – Postgres database cursor object.

• batch_size – Records to load per batch.

Returns

Elapsed time to execute query.

classmethod run_query_pg(conn, sql)

Executes a SQL statement with a Postgres database connection.

Parameters

• conn – Postgres database connection object,

• sql – SQL Statement to execute.

Returns

Elapsed time to execute query.

classmethod sequential_load_pg(override, tgt_tbl, conn, dt_start, dt_end, saved_day_id_range_placeholder, dt1_interval, dt2_interval, sql_loop_fn, sql_loop_fn_type, filter_day_id_field1=False, sql_loop_fn_dt_placeholder1=False, filter_day_id_field2=False, filter_id_type2=False, sql_loop_fn_dt_placeholder2=False, filter_day_id_field3=False, filter_id_type3=False, sql_loop_fn_dt_placeholder3=False, loop_src1=False, loop_src2=False, loop_src3=False, log_dir=False)

Parameters

• override –

• tgt_tbl –

• conn –

• dt_start –

• dt_end –

• saved_day_id_range_placeholder –

• dt1_interval –

• dt2_interval –

• sql_loop_fn –

• sql_loop_fn_type –

• filter_day_id_field1 –

• sql_loop_fn_dt_placeholder1 –

• filter_day_id_field2 –

• filter_id_type2 –

• sql_loop_fn_dt_placeholder2 –

• filter_day_id_field3 –

• filter_id_type3 –

• sql_loop_fn_dt_placeholder3 –

• loop_src1 –

• loop_src2 –

• loop_src3 –

• log_dir –

Returns

classmethod sequential_load_pg_wk(rptg_dates, override, tgt_tbl, conn, rptg_wk, rptg_wk_start, rptg_wk_end, sql_loop_fn, filter_dt_field1=False, filter_dt_type1=False, filter_dt_placeholder1=False, filter_dt_field2=False, filter_dt_type2=False, filter_dt_placeholder2=False, filter_dt_field3=False, filter_dt_type3=False, filter_dt_placeholder3=False, log_dir=False)

Parameters

• rptg_dates –

• override –

• tgt_tbl –

• conn –

• rptg_wk –

• rptg_wk_start –

• rptg_wk_end –

• sql_loop_fn –

• filter_dt_field1 –

• filter_dt_type1 –

• filter_dt_placeholder1 –

• filter_dt_field2 –

• filter_dt_type2 –

• filter_dt_placeholder2 –

• filter_dt_field3 –

• filter_dt_type3 –

• filter_dt_placeholder3 –

• log_dir –

Returns

classmethod upsert_tbl_pg(src_tbl, tgt_tbl, src_join_cols, src_insert_cols, src_update_cols=False, update_compare_cols=False)

Creates SQL to run an UPSERT (INSERT new records or UPDATE existing records) operation of a given Postgres table.

Parameters

• src_tbl – Postgres source table that contains data to be merged from.

• tgt_tbl – Postgres target table to receive UPSERT operation.

• src_join_cols – Columns to use to join source and target tables.

• src_insert_cols – Columns to be inserted from source table.

• src_update_cols – Columns to be updated from source table.

• update_compare_cols – Columns to use to compare values across source and target tables.

Returns

A SQL Insert statement and a SQL Update statement.

classmethod upsert_val_pg(col_list, val_list, tbl_name, constraint_col)

Creates SQL to run an UPSERT (INSERT new records or UPDATE existing records) operation of a given Postgres table.

Parameters

• col_list – List of columns to INSERT or UPDATE.

• val_list – List of values to INSERT or UPDATE.

• constraint_col – Column/value logic to check against for INSERT or UPDATE.

• tbl_name – Name of Postgres table.

Returns

SQL to run an UPSERT statement.