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

Postgresql – How to use WHERE IN with psycopg?

I have a table like this:

id topic_id
2 ‘1’
3 ‘1.1’
… …
21 ‘4’

and I would like to get a list of ids corresponding to their topic_ids.

I can query the database directly just fine:

SELECT id FROM topics WHERE topic_id IN ('1', '4')

# returns [2, 21]

But when I do it via psycopg (as recommended here), I get no results:

topic_ids = ['1', '4']

with psycopg.connect(os.environ['DB_CONNECTION']) as conn:
    with conn.cursor() as cur:
        sql = "SELECT id FROM topics WHERE topic_id IN (%(ids)s)"
        cur.execute(sql, {"ids": tuple(topic_ids)})

        cur.fetchall() # returns []

I can only get a single id out with

        cur.execute(sql, {"ids": topic_ids[0]})

        cur.fetchall() # I get [(2,)]

How can I get the whole list out?

Tags:

2

Answers


  1. 0

    You may build a dynamic WHERE IN (...) clause, with a variable number of %s placeholders, and then bind the correct tuple.

    where = "WHERE topic_id IN (?" + ", ?"*(len(topic_ids) - 1) + ")"
sql = "SELECT id FROM topics " + where
cur.execute(sql, tuple(topic_ids))
cur.fetchall()
    Login or Signup to reply.
  2. 0

    Instead of using IN, you can use =ANY and then have python bind the entire array to one placeholder.

    sql = "SELECT id FROM topics WHERE topic_id =ANY (%(ids)s)"
cur.execute(sql, {"ids": topic_ids})
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search