I have a batch of Objects and must perform a remote procedure on each individual object. I want to write the statuses of each procedure to a status table. Each object in the batch could belong to a different schema based on some metadata.
I could write the status one at a time or I could separate the batch by schema into separate lists and then execute a separate SQL statement for each list. However, I feel like these solutions are not the best and there must be a better way. In the past when I only needed to write to one schema, I use the function execute_values to insert many rows.
Here is some simplified code of what I currently have.
class Message:
messageId: str
schema: str
def process_messages(messages):
results = list()
for m in messages:
status = process(m) # some remote API call
results.append((status, m))
write_statuses(result)
return results
from psycopg2.extras import execute_values
def write_statuses(results):
conn = psycopg2.connect() #imagine these are filled
cur = conn.cursor()
args_list = list()
for status, message in result:
args_list.append(tuple([status, message.messageId]))
sql = """
UPDATE t_process_status
SET process_status = data.v1
FROM (VALUES %s) as data(id, v1)
WHERE notification_id::text = data.id
"""
execute_values(cur, sql, args_list, template=None, page_size=100)
conn.commit()
cur.close()
conn.close()
Essentially what I want is do have an efficient way to do the following:
UPDATE {message.schema}.t_process_status
SET process_status = data.v1
FROM (VALUES %s) as data(id, v1)
WHERE notification_id::text = data.id
Where {message.schema} is dynamic based on the metadata for this particular object.
What is the best way to quickly insert many rows across different schema? Any help would be appreciated. Thank you!