How to write this query?

Question

Suppose I have an sqlite table like this:

Id|B|C|D

And I have a list containing some values of Id. Maybe [1,3,45,67](something like that.)

I want to get the rows with Id's in the list, whose B values are greater than 5 and eventually order it by C.

I think the title I put for this is horrible, if you can think of a better one please edit it.

Yannick Loiseau · Answer 1 · 2011-03-21T14:40:58.747

3

Assuming ids_list is your list of ids and tablename is your table:

c = sqlite3.connect('foo').cursor()
c.execute("""
select Id, B, C, D
from tablename
where 
    Id in (%s) and 
    B >= 5
order by C
""" % ",".join( str(int(id)) for id in ids_list ))

The %s replacement is bad practice because subject to SQL Injection. ? should be used instead, but it doesn't seems to work with in clause. Thus the str(int(id)) trick, to "sanitize" (or check) the id values (will fail if not valid values)

edited Mar 21 '11 at 14:40

answered Mar 21 '11 at 14:35

Yannick Loiseau

1,394
8
8

Why use a trick when you can do it properly using placeholders? – Remy Blank Mar 21 '11 at 15:39
1

yea, it would actually be better to build the placeholders dynamically like you proposed, if the length of the list is small enough. The main purpose of placeholders being to prevent SQL injection, if the ids list is not from user but a other query for example, if should be ok. Note that in this latter case, a join whould be even better :) – Yannick Loiseau Mar 21 '11 at 15:44

score 3 · Accepted Answer · edited May 23 '17 at 09:58

3

Related question, from which I will steal Alex Martelli's answer:

Parameter substitution for a SQLite "IN" clause

Here's the data I created:

sqlite> create table x(a,b,c,d);
sqlite> insert into x values(1, 10, 2, null);
sqlite> insert into x values(2, 10, 3, null); 
sqlite> insert into x values(3, 10, 4, null);

And the Python to fetch it:

>>> ids = [2, 3]
>>> query = "SELECT b, c, d FROM x WHERE a IN ({0}) AND b > 5 ORDER BY c".format(','.join('?' for i in ids))
>>> query
'SELECT b, c, d FROM x WHERE a IN (?,?) AND b > 5 ORDER BY c'
>>> conn.execute(query, ids).fetchall()
[(10, 3, None), (10, 4, None)]

edited May 23 '17 at 09:58

Community

1
1

answered Mar 21 '11 at 15:19

Brian Goldman

716
3
11

1

+1, though this solution will fail when surpassing SQLITE_MAX_VARIABLE_NUMBER, that is, the number of place holders in the statement (default is 999). – XORcist Mar 21 '11 at 15:35

score 2 · Answer 3 · answered Mar 21 '11 at 15:32

I would use the following:

ids = [1, 3, 45, 67]
cnx = sqlite3.connect('my_database.db')
cursor = cnx.cursor()
cursor.execute("""
    SELECT Id, B, C, D FROM table
    WHERE Id IN (%s) AND B > 5 ORDER BY C
    """ % ','.join('?' * len(ids)), tuple(ids))
results = cursor.fetchall()

score 0 · Answer 4 · answered Mar 21 '11 at 14:21

0

I don't know the API between python and sqlite, but this pseudo code should help:

list_id = [1,3,45,67]
ids = ",".join(["%s" % el for el in list_id])
print 'SELECT * FROM table WHERE B>5 AND ID IN (%s) ORDER BY C DESC' % (ids)

answered Mar 21 '11 at 14:21

Aif

11,015
1
30
44

2

Keep in mind that you should neither use `SELECT *` nor should you build queries using string concatenation (use prepared statements here). – Björn Pollex Mar 21 '11 at 14:22

How to write this query?

4 Answers4