I have to write a query to update a record if it exists else insert it. I am doing this update/insert into a postgres DB. I have looked into the upsert examples and most of them use maximum of two fields to update. However, I want to update multiple columns. Example:
query="""INSERT INTO table (col1,col2,col3,col4,col5,col6,col7,col8,col9,..col20) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
ON CONFLICT(col2) DO UPDATE SET (col1,col2,col3,col4,col5,col6,col7,col8,col9,..col20) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
In the query above assume that col2 is a unique key, I am inserting and updating the same number of columns. I have to execute this query using pymysql(python library). In a simple insert statement, I know how to pass the tuple containing the parameters dynamically.
cursor.execute(insert_query, data_tuple)
But in this case, I have both places(insert and update) input to be dynamic. Considering the above upsert query, the way I pass the parameters to the cursor
cursor.execute(upsert_query,data_tuple,data_tuple)
However, this one throws up an error with the number of arguments being in the execute function. So how do I pass? Moreover, I am trying to use this way to pass parameters because using the assignment(=) would be a laborious thing to do for 20 columns.
Is there any other alternative way to do this? Like a simple "replace into" statement in mysql.
The direct answer to your question is, you do a tuple + tuple to double the tuple.
cursor.execute(upsert_query, data_tuple + data_tuple)
If you have individual values and you are constructing the tuple, you can directly construct a tuple with twice the number of values.
query="""INSERT INTO table (col1,col2,col3,col4,col5,col6,col7,col8,col9,..col20) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
ON CONFLICT(col2) DO UPDATE SET col1=%s, col3=%s, col4=%s, ..."""
cur.execute(query, (c1, c2, c3, ... c20, c1, c3, c4, ... c20))
You will have to specify the values (except col2) twice.
If you already have the tuple, which is what you originally asked, then you will use a + to merge the same tuple twice.
If you have the individual values and not the tuple, you could also use named parameters like a dictionary.
query="""INSERT INTO table (col1,col2,col3,col4...) VALUES(%(c1)s, %(c2)s, %(c3)s, %(c4)s...) ON CONFLICT(col2) DO UPDATE SET col1=%(c1)s, col3=%(c3)s, col4=%(c4)s, ..."""
cur.execute(query, {'c1': c1val, 'c2': c2val, 'c3': c3val, 'c4': c4val, ...})
This form is good for readability, passes parameters only once, and is easy to maintain (increase in columns, etc) if the number of columns change in the future.