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()
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).