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 id
s corresponding to their topic_id
s.
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?
CodePudding user response:
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()
CodePudding user response:
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})