Psycopg2 fully dynamic update statement

Question

I am currently facing an issue with building an update statement to be fully dynamic. I need the table name, table columns, values and to be able to change whenever a for loop is iterated. I have successfully managed to do it for a insert statement but I am unsure how I can achieve the same thing for update statement.

My issue is that I can't figure out how to pass two parameters (%s) to the execute_values call.

Please note take for privacy reasons, the actual database structure is not shown and the the actual data is obtained dynamically. For testing purposes I have just implemented static data. The database tables also has way more fields.

import psycopg2
from psycopg2 import sql
from psycopg2.extras import execute_values


table_names = ["employee_work_details", "employee_personal_details"]
table_columns = {"employee_work_details": ["employee_id", "employee_name", "employee_department"], "employee_personal_details": ["employee_p_id", "employee_name", "employee_nok", "employee_home_address"]}
table_values = {"employee_work_details": [("012", "Raymond C", "IT_Department")], "employee_personal_details": [("921", "Raymond C", "Tracy C", "123 Wonderland Road")]}
table_where_key = {"employee_work_details": "employee_id", "employee_personal_details": "employee_p_id"}


def establish_postgre_connection():

    postgresql_connect = psycopg2.connect(host=XX.XX.XX.XX, database="company_xxx", user="Raymond", password="xxxx", port="5432")
    postgresql_cursor = postgresql_connect.cursor()

    # Dynamically get some data here...

    postgresql_cursor = database_update(postgresql_cursor)

    postgresql_connect.commit()
    postgresql_cursor.close()


def database_update(postgresql_cursor):

    for table_name in table_names:
        query_table_name = sql.Identifier(table_name)
        query_table_columns = sql.SQL(",").join(map(sql.Identifier, table_columns[table_name]))
        query_table_where_key = sql.Identifier(table_where_key[table_name])
        # Sample Query "UPDATE employee_work_details SET (employee_name,employee_department) = %s WHERE employee_id = %s"
        update_statement = sql.SQL("UPDATE {} SET {} = %s WHERE {} = %s").format(query_table_name, query_table_columns, query_table_where_key)
        execute_values(postgresql_cursor, update_statement, table_values[table_name])  # How do I pass in two %s

    return postgresql_cursor

krishna krishna · Accepted Answer · 2020-03-18T14:47:52

The below output is based on the sample query what you mention.

table_names = ["employee_work_details", "employee_personal_details"]
table_columns = {"employee_work_details": ["employee_id", "employee_name", "employee_department"],
                 "employee_personal_details": ["employee_p_id", "employee_name", "employee_nok",
                                               "employee_home_address"]}
table_values = {"employee_work_details": [("012", "Raymond C", "IT_Department")],
                "employee_personal_details": [("921", "Raymond C", "Tracy C", "123 Wonderland Road")]}
table_where_key = {"employee_work_details": "employee_id", "employee_personal_details": "employee_p_id"}


def database_update():
    for table_name in table_names:
        print(f"UPDATE {table_name} SET ({', '.join(table_columns[table_name])}) = %s WHERE {table_where_key[table_name]} = %s")



database_update()

Output:


UPDATE employee_work_details SET (employee_id, employee_name, employee_department) = %s WHERE employee_id = %s
UPDATE employee_personal_details SET (employee_p_id, employee_name, employee_nok, employee_home_address) = %s WHERE employee_p_id = %s

Sql with set condition.

def database_update():
    for table_name in table_names:
        set_sql = ""
        for columns, values in zip(table_columns[table_name], table_values[table_name]):
            set_sql = f"{set_sql}, {columns} = '{values}'" if set_sql else f"{columns} = '{values}'"
        sql = f"UPDATE {table_name} SET {set_sql} WHERE {table_where_key[table_name]} = %s"

        print(sql) # Sql with set condition

Output:

UPDATE employee_work_details SET employee_id = '012', employee_name = 'Raymond C', employee_department = 'IT_Department' WHERE employee_id = %s
UPDATE employee_personal_details SET employee_p_id = '921', employee_name = 'Raymond C', employee_nok = 'Tracy C', employee_home_address = '123 Wonderland Road' WHERE employee_p_id = %s

Psycopg2 fully dynamic update statement

1 Answers