Reputation: 3716
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
cur.executemany('SELECT * FROM Genre WHERE id=?', ids)
sqlite3.ProgrammingError: You cannot execute SELECT statements in executemany()
Upvotes: 9
Views: 27902
Reputation: 1125398
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).
Upvotes: 18
Reputation: 1028
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
Upvotes: 6