CODEWITHSUNDEEP
python-3.xpostgresql
user1609085
user1609085

Reputation: 885

Convert list of strings to tuple and use in postgres sql

I have a list of strings of format:

cols = ['col1', 'col2', 'col3', 'col4']

converting the above to tuple(cols) gives me:

('col1', 'col2', 'col3', 'col4')

but I need them without quotes, (col1, col2, col3, col4) Reason why is I use the above in postgres query like:

UPDATE table SET (col1,col2,col3,col4)=('v1','v2','v3','v4') where id = 99

Using with quotes gives syntax error

What exactly I have before send the query:

sql_stmt = 'UPDATE public.sample_table SET %s = %s ' + where_clause
logger.info(cur.mogrify(sql_stmt,(columns,values)))

which logs:

"UPDATE public.sample_table SET ('col1', 'col2', 'col3', 'col4') = ('v1', 'v2', 'v3'::timestamp, 'v4') WHERE col1 IN ('ABC',) AND col2 IN ('ASDF', 'BSDF')"

Upvotes: 1

Views: 1423

Answers (2)

Adrian Klaver
Adrian Klaver

Reputation: 19665

Assuming you are using psycopg2 to execute the query:

import psycopg2
from psycopg2 import sql 

cols = ['col1', 'col2', 'col3', 'col4']

query = sql.SQL("""UPDATE table SET ({}) =('v1','v2','v3','v4') where id = 99""").format(sql.SQL(', ').join(map(sql.Identifier, cols)))

con = psycopg2.connect("dbname=test host=localhost user=aklaver") 

print(query.as_string(con))                                                                                                                                                
UPDATE table SET ("col1", "col2", "col3", "col4") =('v1','v2','v3','v4') where id = 99

For more information see:

https://www.psycopg.org/docs/sql.html

Upvotes: 2

pifor
pifor

Reputation: 7882

If you use PL/pgSQL you could do:

select * from mytable;
 col1 | col2 | col3 | col4 
------+------+------+------
 c1   | c2   | c3   | c4
(1 row)

do
$$
declare
 ca text[];
 va text[];
begin
 ca  = array ['col1','col2','col3','col4'];
 va  = array ['v1','v2','v3','v4'];
 execute format('update mytable set (%I,%I,%I,%I) = (%L,%L,%L,%L)', 
                 ca[1], ca[2], ca[3], ca[4], va[1], va[2], va[3], va[4]);
end;
$$;
DO

select * from mytable;
 col1 | col2 | col3 | col4 
------+------+------+------
 v1   | v2   | v3   | v4
(1 row)

Upvotes: 0

Related Questions