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.
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)]
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)
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()
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)
SELECT * nor should you build queries using string concatenation (use prepared statements here).