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)

CL. · Accepted Answer · 2017-11-05 06:50:02Z

2

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

answered Nov 5, 2017 at 6:50

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

steveH Over a year ago

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.

Collectives™ on Stack Overflow

Variable Substitution in SQLite using Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related