CODEWITHSUNDEEP
pythonsqlite
Srinivas Kalleti
Srinivas Kalleti

Reputation: 46

Parameter substitution for a SQLite with multiple “IN” clause

I am trying to execute the below query in SQLite from Python.

conn = sqlite3.connect('test.db')
c = conn.cursor()
names = ['name1','name2']
ids = ['id1','id2']
query = 'SELECT * FROM STUDENT_DETAILS WHERE NAME IN (%s)' % ','.join('?'*len(names)) + ' AND ID IN (%s)' % ','.join('?'*len(ids))
c.execute(query,(names, ids))
conn.close()

When I execute the above code, I am getting this below error.

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 4, and there are 2 supplied.

I am able to execute the query with single IN clause in the query as shown below.

conn = sqlite3.connect('test.db')
c = conn.cursor()
names = ['name1','name2']
query = 'SELECT * FROM STUDENT_DETAILS WHERE NAME IN (%s)' % ','.join('?'*len(names))
c.execute(query,names)
conn.close()

I would like to know, how to substitute values, If I use multiple IN clause in the query.

Upvotes: 0

Views: 984

Answers (1)

Alex Hall
Alex Hall

Reputation: 36043

The number of ?s in the SQL query needs to match the number of items in the parameters tuple. In your first example there are two items in the tuple: names and ids. But there are four question marks. Try tuple(names) + tuple(ids), or define the variables as tuples using round brackets and then just use names + ids.

In the second example it's one tuple names containing two names, hence it works.

Upvotes: 1

Related Questions