SQLite3 Python: executemany SELECT

Question

I'm trying to get all the rows out of a table in one line with some WHERE constraints using the executemany function

import sqlite3
con = sqlite3.connect('test.db')
cur = con.cursor()
cur.execute('CREATE TABLE IF NOT EXISTS Genre (id INTEGER PRIMARY KEY, genre TEXT NOT NULL)')
values = [
 (None, 'action'),
 (None, 'adventure'),
 (None, 'comedy'),
 ]
cur.executemany('INSERT INTO Genre VALUES(?, ?)', values)
ids=[1,2]
cur.executemany('SELECT * FROM Genre WHERE id=?', ids)
rows = cur.fetchall()
print rows

ERROR

cur.executemany('SELECT * FROM Genre WHERE id=?', ids)
sqlite3.ProgrammingError: You cannot execute SELECT statements in executemany()

Answer 1

Use execute() to execute a query that returns data.

You'll either have to use a loop, or use a IN (id1, id2, id3) where clause:

cur.execute('SELECT * FROM Genre WHERE id in ({0})'.format(', '.join('?' for _ in ids)), ids)

The above expression interpolates a separate ? placeholder for every item in ids (separated with commas).

• Brandon Nadeau: Thanks Martijin, I didn't know about the IN clause it helps a lot.
• ns15: Is the cursor.executemany method implemented by repeatedly calling cursor.execute?
• Martijn Pieters: @shadow0359: no, cursor.execute() is actually implemented in terms of putting the params in a list then executing cursor.executemany(). See the _pysqlite_query_execute function (the only difference between execute() and executemany() is that multiple is set to 1 for the latter).
• Martijn Pieters: @MasonSchmidgall it does not create an injection vulnerability. It specifically generates SQL parameters which prevent SQL injections. E.g. ids = (42, "Robert'; DROP TABLE Students; --",) results in the query string SELECT * FROM Genre WHERE id in (?, ?), executed with 2 SQL parameters which the database quotes, safely, as values. At no point will ids[1] be executed as SQL commands.
• Martijn Pieters: @MasonSchmidgall please carefully read exactly what text is being interpolated here. If the contents of the ids variable were to be interpolated into the query string then yes, there would be a vulnerability, but that is not what this code does. We merely use the length of the sequence to generate a number of ? parameter placeholders here and put those into the query. The ids values themselves are never interpolated by this code and are instead handed over to the database as parameter values.

Answer 2

The error message you received is straightforward, You cannot execute SELECT statements in executemany()

Simply change your executemany to execute:

ids=[1,2]
for id in ids:
 cur.execute('SELECT * FROM Genre WHERE id=?', id)
 rows = cur.fetchall()
 print rows

• Brandon Nadeau: This also helps but Martijin's is better for my case, thanks for the help though.
• rsaxvc: Does this involve reparsing the query for every id? Or does python-sqlite3 cache them?
• ken.ganong: @rsaxvc According to the python documentation, "The sqlite3 module internally uses a statement cache to avoid SQL parsing overhead." To me, that means that it will not reparse the query every time.