Python psycog2 | Use a tuple or array in where clause

Question

I have a tuple like as shown below

vilist = (1,2,3,4)

I am trying to use them in Psycopg2 query like as shown below

sql = "select * from temp.table1 where ids in {}"
cur.execute(sql,vilist)

Which should parse a SQL string like

SELECT * FROM temp.table1 WHERE ids IDS (1,2,3,4);

However, I get an error like as shown below

SyntaxError                               Traceback (most recent call last)
<ipython-input-91-64f840fa2abe> in <module>
      1 sql = "select * from temp.table1 where ids in {}"
----> 2 cur.execute(sql,vilist)

SyntaxError: syntax error at or near "{"
LINE 1: ...rom temp.table1 where ids in {}

Can help me resolve this error please.

klin - the example shows for single value but I wnt to pass a tuple containing multiple values. — The Great
– The Great, Commented Sep 20, 2021 at 12:14

klin · Accepted Answer · 2021-09-20 12:34:02Z

2

Use SQL string composition to pass identifiers or literals to a query text.

import psycopg2
import psycopg2.sql as sql
# ...

vilist = (1,2,3,4)
query = sql.SQL("select * from temp.table1 where ids in {}").format(sql.Literal(vilist))
cur.execute(query)

answered Sep 20, 2021 at 12:34

klin

123k15 gold badges241 silver badges263 bronze badges

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

3 Comments

The Great Over a year ago

It results in error like AttributeError: 'str' object has no attribute 'SQL' which am trying to find online as t what is causing this error

The Great Over a year ago

I tried both sql.sql and sql.SQL but it doesn't work..

klin Over a year ago

You have a string variable sql - note, that I have changed it to query. Delete sql variable from the script.

Collectives™ on Stack Overflow

Python psycog2 | Use a tuple or array in where clause

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related