Variable Substitution in SQLite using Python

Question

I am trying to figure out how to combine two SQLite queries. Both of them work fine independently but when I put them together with AND they do not work. I think the problem is that I do not know how to pass the variables properly. First query that works:

var1 = 10
mylist = ['A', 'B', 'C', 'AB', 'AC']
c.execute("SELECT * FROM my_table WHERE column1=(?) ORDER BY RANDOM() LIMIT 1", (mylist[2],))

This line also works:

params = [5,0,1]
query = ("SELECT * FROM my_table WHERE column2 NOT IN (%s)" % ','.join('?' * len(params)))
c.execute(query, params)

I have been trying to combine these two statements without success:

query = ("SELECT * FROM my_table WHERE column2 NOT IN (%s) AND column1=(?)" % ','.join('?' * len(params)))
c.execute(query, params, mylist[2])

In case anyone finds this helpful, my final solution looked like this:

query = ("SELECT * FROM my_table WHERE column1 = (?) AND column2 NOT IN (%s) ORDER BY RANDOM() LIMIT 1" % ','.join('?' * len(params)))
 c.execute(query, [mylist[2]] + params)

Answer 1

The second parameter of execute() must be a sequence containing all the SQL parameters.

So you have to construct a single list with the values from both original lists:

c.execute(query, params + [list[2]])

• steveH: Thanks. Worked perfectly. I deleted my earlier comment because the fault was how I was formatting my query. I'll adjust my question above so it is formatted correctly.