Repository navigation
JSON (output) support for sqlite3 #137974
Description
Feature or enhancement
Proposal:
Here's some code/benchmark that demonstrates the concept.
import sqlite3, json
import time
import subprocess
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
# col[0] is the column name
d[col[0]] = row[idx]
return d
def get_data_to_json():
conn = sqlite3.connect("/home/user/database.sqlite")
conn.row_factory = dict_factory
c = conn.cursor()
c.execute("SELECT * FROM server")
rst = c.fetchall() # rst is a list of dict
return rst
start = time.time()
rst = get_data_to_json()
print(f"normal data load: {time.time() - start}")
start = time.time()
out = subprocess.run(["sqlite3", "--readonly", "/home/user/database.sqlite", ".mode json", "select * from server"], check=True, capture_output=True).stdout
print(f"sqlite3 command: {time.time() - start}")
start = time.time()
loadit = json.loads(out)
print(f"load json from command: {time.time() - start}")
start = time.time()
json.dumps(loadit)
print(f"dump loaded json: {time.time() - start}")$ python benchmark.py
normal data load: 0.7838420867919922
sqlite3 command: 0.19471359252929688
load json from command: 0.21944022178649902
dump loaded json: 0.16810369491577148
I recently found out that sqlite3's CLI has a JSON mode. This JSON mode is considerably faster than any Python option that I'm aware of.
There's lots of cases where this might be useful. Let's say you have an API that needs to dump a decent bit of data from your database. There's no reason to load the JSON, then dump it to send it. In this case, it's about 5x faster to let sqlite dump the JSON and pass that along.
What's crazy is that it's even 2x faster to call sqlite3 and load JSON, than it is to fetchall().
I am not sure how the sqlite interface works and if this would require work on sqlite's part, but to me this is a substantial improvement.
I imagine this might have an interface like:
session.exec(query, mode=json) or session.exec(query, mode=dict)
Curious what you all think.
Thanks!
Has this already been discussed elsewhere?
This is a minor feature, which does not need previous discussion elsewhere
Links to previous discussion of this feature:
No response
Update: There's now a discussion for it: https://discuss.python.org/t/json-output-support-for-sqlite3/103048
Activity
picnixz commentedon Aug 20, 2025
JSON support is only available in SQLite 3.38 and later. The build requirements for Python is 3.15 and later. The JSON functions can be directly used in queries and there is no standard way that we can propose. The benchmarks are also not comparable. One is using the sqlite CLI directly while the other is using the Python interface. In addition you're using two different databases.
Instead of using a dict factory like that, using
sqlite3.Rowand directly transforming the row into a dict might be more efficient.Such feature is not minor and requires a previous discussion on Discourse. Pleae open a topic in https://discuss.python.org/c/ideas/6 and if there's enough support, we may consider adding it (namely supporting the
modeoutput) but this likely requires a new row type. There is also the CSV output type.Implementation-wise, I don't think it's actually possible to do it.
executeandexecutemanyare simple wrappers around the sqlite3 API which only provides a generic entrypoint for executing a statement. This is one reason why we don't have someselect()helper because we don't know what kind of select the user wants. I suspect the way the JSON output is produced is actually through formatting, not by directly querying the API (that is we need a transformation step).ssmycelium commentedon Aug 20, 2025
I appreciate your reply. I should've raised a discussion about this. I'll do that next time.
As for different databases, it was actually the same one. I made a typo in the issue and didn't change both of the filenames to make it clear. The benchmarks are valid, and I'm sure others can reproduce them.
I also wondered if that transformation layer happened in the
sqlite3CLI and wasn't possible from queries. I've not been able to get any of SQLite's JSON functions to work in a similar manner.As far as the different methods not being comparable, that's because I hadn't found a way to do this in Python. In theory, it seems like we should be able to be faster than the CLI method, since there's no wait launching the CLI. But this is if it's possible, and yes it does require a "newer" SQLite.
Thank you!