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

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

CodePudding user response：

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)

CodePudding user response：


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