Question 113858 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    You need to use ANY and psycopg2 list adaption:

    
cursor.execute("SELECT name FROM personal_details WHERE id IN param = ANY(%s)", [ids])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search