CODEWITHSUNDEEP
pythonselectsqliteexecutemany
Brandon Nadeau
Brandon Nadeau

Reputation: 3716

SQLite3 Python: executemany SELECT

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

Upvotes: 9

Views: 27902

Answers (2)

Martijn Pieters
Martijn Pieters

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

ken.ganong
ken.ganong

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

Related Questions