Python running SQL query With WHERE IN {list} and list is too long

Question

Here is a part of my script :

sql1 = """
                    SELECT distinct th.name, 'MMS' as Status
                    FROM t1 
                    JOIN t2 on t1.id = t2.tid
                    WHERE t2.station= 'MatS'
                    AND t1.name IN ({listthinglist})
        """.format(listthinglist=', '.join("'" + item + "'" for item in list_of_things))
    cur.execute(sql1)

The list_of_things contains 22016 items and I am unable to run this query in python. Is there an alternative to run queries like this in python.

What does "unable to run" mean? Are you seeing an error message? — Patrick Haugh
– Patrick Haugh, Commented Jun 1, 2018 at 22:26
@PatrickHaugh my guess is the size of the query string is larger than the DBMS can handle. — cs95
– cs95, Commented Jun 1, 2018 at 22:31
Create a temporary table and use that: stackoverflow.com/a/48392389/2681632. And don't use string formatting for passing values to SQL queries. — Ilja Everilä
– Ilja Everilä, Commented Jun 1, 2018 at 22:56

Nick · Accepted Answer · 2018-06-01 22:49:11Z

0

Please do not use format for this. The execute function accepts a 'parameters' argument that you would pass your list to. You can modify it like so:

sql1 = """
  SELECT distinct th.name, 'MMS' as Status
  FROM t1 
  JOIN t2 on t1.id = t2.tid
  WHERE t2.station= 'MatS'
  AND t1.name IN (%s)
"""
cur.execute(sql1, (list_of_things,))

answered Jun 1, 2018 at 22:49

Nick

7,4912 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

cs95 Over a year ago

Won't this still execute an insanely sized query string?

Nick Over a year ago

Try it and if it produces and error, post the exact error message.

cs95 Over a year ago

That's not my job, that's either yours, or OP's. I asked you since you posted the answer, you should know what it does.

Nick Over a year ago

I know what it does, but cant possibly know how it runs on OPs particular server.

cs95 Over a year ago

Not refuting that, but you'd know whether it inserts the parameters into the string or executes them in some other way, wouldn't you? That's what I was asking. I'm not an sql guy, I'm a pandas guy.

|

Collectives™ on Stack Overflow

Python running SQL query With WHERE IN {list} and list is too long

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related