Convert list of strings to tuple and use in postgres sql

Question

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')"

It's unclear to me if you want the left side of the SET assignment or the right side. If it's the left side, you will have to use dynamic SQL. In that case you should show us the complete function where you are using this. — user330315
– user330315, Commented Jun 24, 2020 at 14:08

Adrian Klaver · Accepted Answer · 2020-06-24 14:22:29Z

2

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

answered Jun 24, 2020 at 14:22

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pifor · Accepted Answer · 2020-06-24 14:20:03Z

0

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)

answered Jun 24, 2020 at 14:20

pifor

7,9722 gold badges12 silver badges18 bronze badges

Collectives™ on Stack Overflow

Convert list of strings to tuple and use in postgres sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related