Python, Psycopg2 - How to insert many rows across different schemas?

Viewed 27

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!

0 Answers
Related