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!

[erlend-aasland]

FTR, I see there's room for improvement in the docs, so I reverted the PR title change I did yesterday and I've made this a docs issue. See gh-103939.

I'll create a topic on Discourse about the feature request.

[zentarim]

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.

I deliberately don't talk about internals for I know less than nothing about them. I just want to point out that from the user point of view there are barely any breaking changes expected. If somebody uses an executemany() statement, they already do not expect it to return any data.

Anyway, thanks for the explanation!

[erlend-aasland]

I just want to point out that from the user point of view there are barely any breaking changes expected.

You are probably correct, but people rely on any kind of behaviour in unexpected ways. If we change the semantics of executemany(), it might break existing code. As I said, I want to move this discussion to Discourse, in order to get more attention to it. I'm not strictly opposed to changing the semantics, but for now I'm on the conservative side.

Anyway, thanks for the explanation!

Anytime! Thanks for the report :)

[erlend-aasland]

Oh, please take a look at the PR, if you find the time.

[erlend-aasland]

@PascalinDe, @zentarim: I created a topic on Discourse regarding the feature request, in case you want to participate in the discussion over there.

[PascalinDe]

@PascalinDe, @zentarim: I created a topic on Discourse regarding the feature request, in case you want to participate in the discussion over there.

Thank you, I'm not familiar enough with the internals to actually participate, but I'll be sure to follow the discussion!