1

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?

tmoisan
  • 1,212
  • 2
  • 13
  • 27

1 Answers1

3

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.

P-Gn
  • 23,115
  • 9
  • 87
  • 104