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

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

Solution 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

Comments

[Brandon Nadeau]

Thanks Martijin, I didn't know about the IN clause it helps a lot.

[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.

[shadow0359]

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

[shadow0359]

Wow,I thought it was other way around.

[shadow0359]

Is it the same for mysql?

[Martijn Pieters]

You'd have to look at the source code of each MySQL DBAPI2 module; there are several implementations. I don't have time right now to track those down.

[Justian Meyer]

But what about at scale, say hundreds of thousands of items? Is batching the only solution?

[Martijn Pieters]

@JustianMeyer: I'm not sure what you are asking. Database cursors are built for scale, iteration over the cursor produces rows efficiently. As long as you don't try to store all those rows in a single list in memory and instead process them directly, there is no limit to how many items a query returns.

[Justian Meyer]

@MartijnPieters, to clarify, I mean in terms of the size of the actual query and the packet size in the case where all items are listed.

[Martijn Pieters]

@JustianMeyer: still doesn't make much sense. This is a question about sqlite, which is an embedded database. Database drivers for client-server database models generally know how to efficiently transfer query data, regardless of row count.

[spicy.dll]

This answer creates a SQL injection vulnerability. If a user is able to control the id variable at all they theoretically have control over your entire sqlite database. For this reason, I advise against this solution

[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.

[spicy.dll]

@MartijnPieters Whoops I misunderstood this solution. There is no SQL injection vulnerability here