How can I write an SQL query using a bunch of id's (a python list of integers representing id) to query a SQL database table for some values?

Question

I am writing code in Python, and I have a list of id's: [14503, 14504, 14505, ...]

And I would like to write a SQL query to query a database, specifically the following very basic table

    id           val
  14501           0
  14502           1
  14504           0
  ....

The table consists of a bunch of id's (I'm not sure if they are unique), and val, which is either a 0 or 1.

For my list of id's I would like to write a query that returns any id that has a val = 1 - is this possible?

I suppose the difficult part for me is incorporating the list of Python id's, as there are several hundred thousand of them.

Otherwise the SQL code is trivial:

SELECT id from sval where val=1

Maybe consider writing your list of ids to a table on the database to allow the db to join the list of ids to the existing table and return those rows where val=1. Alternatively, pull all rows back from the db where val=1 into some python structure and compare the ids in the structure to the list you mention above. — v0rl0n, Aug 22 '23 at 12:14
Does this work? `query = "SELECT * FROM sval where val = 1 and id in (%s)" % ', '.join(map(str, ids))` — jlgarcia, Aug 22 '23 at 12:14
Does this answer your question? [Python list in SQL query as parameter](https://stackoverflow.com/questions/283645/python-list-in-sql-query-as-parameter) — JonSG, Aug 22 '23 at 13:29

score 1 · Accepted Answer · answered Aug 22 '23 at 13:10

I would do it using jsonb since, in my experience, passing an in list blows up after some number of elements.

This code works for me on my machine and database:

import json
import psycopg2

with psycopg2.connect() as conn, conn.cursor() as cur:
    ids_to_check = [1, 10, 20, 900]
    cur.execute("""
      select jsonb_agg(u.id) 
        from jsonb_array_elements_text(%s) as e(id)
             join users u on u.id = e.id::int   
                """, (json.dumps(ids_to_check), )
    )
    result = cur.fetchone()[0]
    print(result)

How can I write an SQL query using a bunch of id's (a python list of integers representing id) to query a SQL database table for some values?

1 Answers1