How to write this query?

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,6开发者_如何学C7](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)