How to execute a postgres query with multiple dynamic parameters using python

Question

I'm trying to execute a postgres select query using cursor.execute. How can I write the query if the number of parameters change dynamically.

E.g

One instance the query can be

cursor.execute('SELECT name FROM personal_details WHERE id IN (%s, %s)', (3, 4))

an in some other instance the query can be

cursor.execute('SELECT name FROM personal_details WHERE id IN (%s, %s, %s)', (3, 4, 5))

If the parameters are available in a list as ids = [3, 4] or ids = [3, 4, 5] what is the proper way of writing this query

I tried with the following code

cursor.execute("SELECT name FROM personal_details WHERE id IN param = %s", [ids['param']])

But it returned with an error saying TypeError: list indices must be integers or slices, not str

Is ids a list? If it is, the part where it says "param = %s" doesn't seem like it makes sense. Maybe you mean WHERE id in %s ? — StonedTensor
– StonedTensor, Commented Nov 8, 2022 at 9:49
You are right. I though the list ids will be copied to param and then it will be used as the parameter. — Lalas M
– Lalas M, Commented Nov 8, 2022 at 9:51

Mike Scotty · Accepted Answer · 2022-11-08 09:50:51Z

3

You will need to build your statement dynamically:

params = (1, 2, 3, 4, 5)
sql = f"SELECT x FROM tbl WHERE id IN ({', '.join(['%s']*len(params))})"
print(sql)
cursor.execute(sql, params)

Output of print:

SELECT x FROM tbl WHERE id IN (%s, %s, %s, %s, %s)

answered Nov 8, 2022 at 9:50

Mike Scotty

10.8k5 gold badges41 silver badges52 bronze badges

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

2 Comments

Thank for the response .. Thrying this solution and will update here

Actually if you are doing this you need to use the sql module.

Adrian Klaver · Accepted Answer · 2022-11-08 16:02:57Z

3


cursor.execute("SELECT name FROM personal_details WHERE id IN param = ANY(%s)", [ids])

answered Nov 8, 2022 at 15:37

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges