tmoisan tmoisan - 1 year ago 61
Python Question

sqlite select query including a "VALUES" in the WHERE clause returns correctly with sqlite but not with python sqlite3

I have the following SQL query that selects the column 'item' and 'feature' from the table 'my_table' where the pair of columns 'item' and 'other_feature' match some pair of values.

select item, feature from my_table
where (item, other_feature) in (VALUES ('item1', 'A'), ('item1', 'B'));


Here is an example of the dataset used.

This query works as expected in the sqlite3 command line interface. However, using the same database, when using the
sqlite3
module in python using the following code I get an error.

import sqlite3
query = "select item, feature from my_table where (item, other_feature) in (VALUES ('item1', 'A'), ('item1', 'B'))"
conn = sqlite3.connect("data/my_database.db")
conn.execute(query)


I obtain the following error:

sqlite3.OperationalError: near ",": syntax error


Why is this query not working as expected with the python sqlite3 module?

Answer Source

Try quoting your strings with ":

query = 'select item, feature from my_table where (item, other_feature) in (VALUES ("item1", "A"), ("item1", "B"))'

You also might want to check that the sqlite version

select sqlite_version();

or in python

import sqlite3
sqlite3.sqlite_version

of you python distribution is not lower than that of your command line interface, and in particular if it is earlier than 3.15.2.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download