CODEWITHSUNDEEP
pythonsqlite
xralf
xralf

Reputation: 3642

SQLite query selecting number from list

When you want to write a query in Python that will select (from SQLite database) all the books that have 300 pages, you will write:

numsPages = 300
cur.execute("select * from  books where number_of_pages = :numsPages", locals())

The problem is when you want to select books that have number of pages from certain set

lst = computeListFromVariousInputs() # lst is list containing natural numbers
cur.execute("select * from  books where number_of_pages in :lst", locals())

The above statement is not possible.

It's difficult to write many or operators in the statement, so I'd like to use rather the in operator.

How would you write the query without using many or operators and using some Python list or other data structure?

Maybe, the example seems not practical (and it is) but this question is more understandable when I use this example.

Upvotes: 0

Views: 271

Answers (2)

aweis
aweis

Reputation: 5596

You can format the statement youself, e.g.:

a = [100, 200, 300, 400]
## The a bit more formatting is used because the list contains integers, i.e., the join with string casting
stmt = 'select * from books where number_of_pages in ({0})'.format(', '.join([str(v) for v in a]))

print stmt
>>> select * from books where number_of_pages in (100, 200, 300, 400)

Upvotes: 1

conrad10781
conrad10781

Reputation: 2273

A good breakdown of the problem you're running into, and your options for solutions can be found here:

http://www.javaranch.com/journal/200510/Journal200510.jsp#a2

This was taken from:

https://stackoverflow.com/a/189399/1232478

Upvotes: 1

Related Questions