CODEWITHSUNDEEP
pythonsqlite
tmoisan
tmoisan

Reputation: 1221

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?

Upvotes: 1

Views: 2317

Answers (1)

P-Gn
P-Gn

Reputation: 24581

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.

Upvotes: 3

Related Questions