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