CODEWITHSUNDEEP
pythonsqlite
jck
jck

Reputation: 2078

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,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.

Upvotes: 2

Views: 253

Answers (4)

Remy Blank
Remy Blank

Reputation: 4285

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()

Upvotes: 2

Brian Goldman
Brian Goldman

Reputation: 736

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)]

Upvotes: 3

Yannick Loiseau
Yannick Loiseau

Reputation: 1454

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)

Upvotes: 3

Aif
Aif

Reputation: 11220

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)

Upvotes: 0

Related Questions