pythonsqlpython-3.xdatabasedbconnection

How to build a generic function in Python to Insert and update records?


I wanted to build a generic function in Python to Insert a record into table A from table B if the record does not exists in table A else update with the data if the primary key is identified. I am not able to get the result with the below code:

def insert_update_record(table_A, table_B):
    insert_query = "INSERT INTO {} SELECT * FROM {} WHERE NOT EXISTS (SELECT 1 FROM {} WHERE {}.id = {}.id)".format(table_A, table_B, table_A, table_B, table_A)
    update_query = "UPDATE {} SET {} = {} FROM {} WHERE {}.id = {}.id".format(table_A, table_A, table_B, table_A, table_B, table_A)
    
    cur.execute(insert_query)
    cur.execute(update_query)
    conn.commit()

Solution

  • First, I should say SELECT 1 in your insert_query is not ok and you probably need to change it to something like this:

    insert_query = "INSERT INTO {} SELECT * FROM {} WHERE NOT EXISTS (SELECT * FROM {} WHERE {}.id = {}.id) LIMIT 1".format(table_A, table_B, table_A, table_B, table_A)
    

    But what you want to implement is a concept called Upsert in database concepts and I do it for you using INSERT ON CONFLICT statement like below:

    import psycopg2
    
    
    def connect():
        try:
            conn = psycopg2.connect(database ="name_of_your_database",
                                user = "user_of_this_database",
                                password = "password_forthis_user",
                                host = "localhost",
                                port = "5432")
    
            cur = conn.cursor()
        
        except (Exception, psycopg2.DatabaseError) as error:
            print ("Error while creating PostgreSQL table", error)
        
        return conn, cur
    
    def insert_update_record(table_A, table_B):
        conn, cur = connect()
        upsert_query="""
    INSERT INTO {}
    SELECT * FROM {} LIMIT 1
    ON CONFLICT (id) 
    DO 
       UPDATE SET field_name = EXCLUDED.field_name || ';' || {}.field_name;
    """.format(table_A, table_B, table_B)
    
        cur.execute(upsert_query)
        conn.commit()
    
    if __name__ == '__main__':
        insert_update_record("table_name_1", "table_name_2")
    

    Reference:

    To learn more about this concept in Postgres or other databases you can tour the below links:

    Psycopg2: how to insert and update on conflict using psycopg2 with python? [stackoverflow]

    Insert, on duplicate update in PostgreSQL? [stackoverflow]

    PostgreSQL Upsert Using INSERT ON CONFLICT statement [POSTGRESQL TUTORIAL]

    How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL? [stackoverflow]

    Idiomatic way to implement UPSERT in PostgreSQL [dba-stackexchange]