stdlib sqlite3 executemany() does not support RETURNING statement

[zentarim]

Problem

sqlite3.Connection.executemany() does not support a RETURNING statement. All requests containing it fail with an exception:
sqlite3.ProgrammingError: executemany() can only execute DML statements.

Working shell example

#!/bin/sh
rm -f ./test.db
sqlite3 ./test.db  <<EOF
CREATE TABLE releases (
component VARCHAR(64) NOT NULL, 
version VARCHAR(64) NOT NULL, 
os VARCHAR(64) NOT NULL, 
PRIMARY KEY (component, version, os));
INSERT INTO releases VALUES('server', '1.0.0', 'Unix'), ('server', '1.0.0', 'NT') RETURNING *;
EOF

Produces a relevant output:

server|1.0.0|Unix
server|1.0.0|NT

However, a Python example doing similar thing:

#!/bin/env python3.10
from pathlib import Path
import sqlite3

if __name__ == '__main__':
    test_file = Path.cwd() / 'test.db'
    test_file.unlink(missing_ok=True)
    connection = sqlite3.connect(test_file)
    connection.execute(
        'CREATE TABLE releases ('
        'component VARCHAR(64) NOT NULL, '
        'version VARCHAR(64) NOT NULL, '
        'os VARCHAR(64) NOT NULL, '
        'PRIMARY KEY (component, version, os));',
        )
    values = [
        ('server', '1.0.0', 'Unix'),
        ('server', '1.0.0', 'NT')
    ]
    cursor = connection.executemany('INSERT INTO releases VALUES(?, ?, ?) RETURNING *;', values)
    print(cursor.fetchall())

generates an exception:

Traceback (most recent call last):
  File "/home/zentarim/py/test/./sql_many.py", line 20, in <module>
    cursor = connection.executemany('INSERT INTO releases VALUES(?, ?, ?) RETURNING *;', values)
sqlite3.ProgrammingError: executemany() can only execute DML statements.

Environment

Ubuntu 22.04.1 LTS
Kernel 6.0.0-1006-oem
Python 3.10.6
sqlite3 module version: 2.6.0
sqlite3 package version: 3.37.2

Not sure if it is a bug or unimplemented feature.

Thanks in advance!

Linked PRs

• gh-100021: Document that sqlite3's executemany() discards resulting rows #103939
• [3.11] gh-100021: Document that sqlite3's executemany() discards resulting rows (#103939) #103966

[PascalinDe]

Hi,

just to give a quick update on that issue on a more recent version of Python/SQLite module: when executing @zentarim's example script with Python3.11 on Debian bookworm, no exception is raised, but no values are returned either :

/tmp$ cat /tmp/test.py 
#!/bin/env python3.10
from pathlib import Path
import sqlite3

if __name__ == '__main__':
    test_file = Path.cwd() / 'test.db'
    test_file.unlink(missing_ok=True)
    connection = sqlite3.connect(test_file)
    connection.execute(
        'CREATE TABLE releases ('
        'component VARCHAR(64) NOT NULL, '
        'version VARCHAR(64) NOT NULL, '
        'os VARCHAR(64) NOT NULL, '
        'PRIMARY KEY (component, version, os));',
        )
    values = [
        ('server', '1.0.0', 'Unix'),
        ('server', '1.0.0', 'NT')
    ]
    cursor = connection.executemany('INSERT INTO releases VALUES(?, ?, ?) RETURNING *;', values)
    print(cursor.fetchall())
/tmp$ python3 test.py 
[]

The relevant versions are

/tmp$ python3 --version
Python 3.11.2
[...]
In [1]: import sqlite3

In [2]: sqlite3.version
Out[2]: '2.6.0'

In [3]: sqlite3.sqlite_version
Out[3]: '3.40.1'

Thank you for your work!

[erlend-aasland]

executemany was written (back in 2004 IIRC) to deliberately discard any resulting rows, since it was meant to only execute DRM statements where (at that time) no resulting rows were expected. In 2021, support for RETURNING was added; it's a pretty new SQLite feature. If executemany was to be redesigned to be able to return resulting rows, it would be a change of semantics for this API, which might be a breaking change.

TL;DR: I'd prefer to be conservative regarding this, and not alter the current semantics of executemany, which means I lean towards closing this as wont-fix.

Not sure if it is a bug or unimplemented feature.

It's an unimplemented feature.

[PascalinDe]

Thank you for the explanation!