Python sqlite operation,SQL statement 'where field in (1,2)' syntax error

Question

The error is:sqlite3.OperationalError: near ":id": syntax error

My search of the Official Python documentation and Google failed to find the answer:

https://docs.python.org/3/library/sqlite3.html

How should arguments be passed?

'''first create test.db:table and field
CREATE TABLE test_tab (
    id         INTEGER PRIMARY KEY ASC,
    test_num   INT,
    test_field TEXT
);
'''

import sqlite3
con = sqlite3.connect('test.db')
con.set_trace_callback(print)  # start the debug
d = [
    (111,'aaa'),
    (111,'bbb'),
    (111,'ccc'),
    (444,'ddd')
]
sql = "insert into `test_tab` (`test_num`, `test_field`) values (?,?)"
cursor = con.executemany(sql, d)
con.commit()  # Execute successfully

#####################

# wrong code begin,why sql 'in ()' is wrong?
sql = "SELECT * from `test_tab` where `test_num`=:num AND `id` in :id"
par = {'num': 111, 'id': (1,2)}  # The number of 'id' parameters is uncertain
x = con.execute(sql, par)
print(x.fetchall())

Tim Biegeleisen · Accepted Answer · 2022-01-06 02:00:56Z

1

In the second query, you would need actually separate placeholders for every value in the IN clause. In addition, I would use ? here:

num = 111
ids = (1, 2)
par = (num,) + ids
sql = "select * from test_tab where test_num = ? AND id in "
in_clause = '(?' + ', ?'*(len(ids) - 1) + ')'
sql = sql + in_clause
x = con.execute(sql, par)
print(x.fetchall())

The SQL query generated by the above script is:

select * from test_tab where test_num = ? AND in (?, ?)

and we bind (111, 1, 2) to the three ? placeholders.

answered Jan 6, 2022 at 2:00

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

5 Comments

dong Over a year ago

Thank you. It works, but it's so clumsy, is it the only way? I don't understand why tuples aren't officially supported, it's very clean.

Tim Biegeleisen Over a year ago

At the end of the day, a prepared statement needs a separate ? placeholder for every bound value; there is no way around this. You could place some of my logic into a helper function and then just call it.

dong Over a year ago

Thank a lot,i know it.

Parfait Over a year ago

Maybe join inside an f-string: in_clause = f"({', '.join('?' for _ in ids)})"?

dong Over a year ago

@parfait,This is essentially the same as the code above,It's all concatenated strings, but it does look better

Collectives™ on Stack Overflow

Python sqlite operation,SQL statement 'where field in (1,2)' syntax error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related