Psycopg2完全动态更新语句

Question

我目前面临一个问题，需要构建一个完全动态的更新语句。我需要表名、表列、值，并且能够在每次迭代for循环时进行更改。我已经成功地为insert语句实现了这一点，但我不确定如何为update语句实现同样的功能。

我的问题是，我不知道如何将两个参数(%s(传递给execute_values调用。

请注意，出于隐私原因，没有显示实际的数据库结构，实际数据是动态获取的。出于测试目的，我刚刚实现了静态数据。数据库表还有更多的字段。

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

Answer 1

下面的输出基于您提到的示例查询。

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()

输出：

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。

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

输出：

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