Insert or update json object in psycopg2
def insert_update_json(db_table, column, obj, json_key, search_value):
try:
# Check if a record with the specified value in the search_column already exists
cursor.execute(f"SELECT id FROM {db_table} WHERE {column}->>'{json_key}' = %s", (search_value,))
existing_data = cursor.fetchone()
if existing_data:
# Update existing record
cursor.execute(
f"UPDATE {db_table} SET {column} = %s WHERE id = %s",
(json.dumps({**obj}, ensure_ascii=False, cls=DecimalEncoder), existing_data[0])
)
else:
# Insert new record
cursor.execute(
f"INSERT INTO {db_table} ({column}) VALUES (%s)",
(json.dumps({**obj}, ensure_ascii=False, cls=DecimalEncoder),)
)
# Commit the transaction
connection.commit()
except Exception as e:
# Handle exceptions, log errors, and rollback the transaction
print(f"Error: {e}")
connection.rollback()
