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

Martijn Pieters · Accepted Answer · 2021-03-13T08:44:07.047

18

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

edited Mar 13 '21 at 08:44

answered Jan 03 '13 at 16:07

Martijn Pieters

1,048,767
296
4,058
3,343

Thanks Martijin, I didn't know about the IN clause it helps a lot. – Brandon Nadeau Jan 03 '13 at 16:12
Is the cursor.executemany method implemented by repeatedly calling cursor.execute? – ns15 Apr 06 '17 at 10:18
1

@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](https://github.com/python/cpython/blob/master/Modules/_sqlite/cursor.c#L379) (the only difference between `execute()` and `executemany()` is that `multiple` is set to 1 for the latter). – Martijn Pieters Apr 06 '17 at 10:31
Wow,I thought it was other way around. – ns15 Apr 06 '17 at 10:35
Is it the same for mysql? – ns15 Apr 06 '17 at 10:37
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. – Martijn Pieters Apr 06 '17 at 10:41
But what about at scale, say hundreds of thousands of items? Is batching the only solution? – Justian Meyer Oct 05 '17 at 04:39
@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. – Martijn Pieters Oct 05 '17 at 07:00
@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. – Justian Meyer Oct 05 '17 at 17:11
@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. – Martijn Pieters Oct 05 '17 at 22:09
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 – spicy.dll Mar 13 '21 at 00:05
1

@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 Mar 13 '21 at 08:37
1

@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. – Martijn Pieters Mar 13 '21 at 08:40
@MartijnPieters Whoops I misunderstood this solution. There is no SQL injection vulnerability here – spicy.dll Mar 13 '21 at 20:36

score 6 · Answer 2 · answered Jan 03 '13 at 16:10

6

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

answered Jan 03 '13 at 16:10

ken.ganong

1,008
8
10

This also helps but Martijin's is better for my case, thanks for the help though. – Brandon Nadeau Jan 03 '13 at 16:15
Does this involve reparsing the query for every id? Or does python-sqlite3 cache them? – rsaxvc Feb 06 '15 at 04:35
@rsaxvc According to [the python documentation](https://docs.python.org/2/library/sqlite3.html), "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. – ken.ganong Feb 06 '15 at 16:00

SQLite3 Python: executemany SELECT

ERROR

2 Answers2

Linked