A one-liner to output details of the current Python’s SQLite#

In investigating llm/issues/164 I found myself needing to know more precise details of the Python SQLite environment used by the reporter of the bug.

I came up with this one-liner for pasting into a terminal:

1
python -c '
2
import sqlite3
3
import sys
4
conn = sqlite3.connect(":memory:")
5
print("Python version:", sys.version)
6
print("SQLite version:", conn.execute("select sqlite_version()").fetchone()[0])
7
print("SQLite compile options\n  " + "\n  ".join(r[0] for r in conn.execute("pragma compile_options").fetchall()))
8
for pragma in (
9
        "foreign_keys", "defer_foreign_keys", "ignore_check_constraints", "legacy_alter_table",
10
        "recursive_triggers", "writable_schema",
11
    ):
12
    output = conn.execute("pragma %s" % pragma).fetchone()
13
    print("Pragma {}: {}".format(pragma, output[0]))
14
'

The output on my machine looks like this:

1
Python version: 3.10.10 (main, Mar 21 2023, 13:41:05) [Clang 14.0.6 ]
2
SQLite version: 3.41.1
3
SQLite compile options
4
  ATOMIC_INTRINSICS=1
5
  COMPILER=clang-14.0.6
6
  DEFAULT_AUTOVACUUM
7
  DEFAULT_CACHE_SIZE=-2000
8
  DEFAULT_FILE_FORMAT=4
9
  DEFAULT_JOURNAL_SIZE_LIMIT=-1
10
  DEFAULT_MMAP_SIZE=0
11
  DEFAULT_PAGE_SIZE=4096
12
  DEFAULT_PCACHE_INITSZ=20
13
  DEFAULT_RECURSIVE_TRIGGERS
14
  DEFAULT_SECTOR_SIZE=4096
15
  DEFAULT_SYNCHRONOUS=2
16
  DEFAULT_WAL_AUTOCHECKPOINT=1000
17
  DEFAULT_WAL_SYNCHRONOUS=2
18
  DEFAULT_WORKER_THREADS=0
19
  ENABLE_COLUMN_METADATA
20
  ENABLE_DBSTAT_VTAB
21
  ENABLE_FTS3
22
  ENABLE_FTS3_TOKENIZER
23
  ENABLE_FTS4
24
  ENABLE_FTS5
25
  ENABLE_GEOPOLY
26
  ENABLE_MATH_FUNCTIONS
27
  ENABLE_RTREE
28
  ENABLE_UNLOCK_NOTIFY
29
  MALLOC_SOFT_LIMIT=1024
30
  MAX_ATTACHED=10
31
  MAX_COLUMN=2000
32
  MAX_COMPOUND_SELECT=500
33
  MAX_DEFAULT_PAGE_SIZE=8192
34
  MAX_EXPR_DEPTH=10000
35
  MAX_FUNCTION_ARG=127
36
  MAX_LENGTH=1000000000
37
  MAX_LIKE_PATTERN_LENGTH=50000
38
  MAX_MMAP_SIZE=0x7fff0000
39
  MAX_PAGE_COUNT=1073741823
40
  MAX_PAGE_SIZE=65536
41
  MAX_SQL_LENGTH=1000000000
42
  MAX_TRIGGER_DEPTH=1000
43
  MAX_VARIABLE_NUMBER=250000
44
  MAX_VDBE_OP=250000000
45
  MAX_WORKER_THREADS=8
46
  MUTEX_PTHREADS
47
  SECURE_DELETE
48
  SYSTEM_MALLOC
49
  TEMP_STORE=1
50
  THREADSAFE=1
51
Pragma foreign_keys: 0
52
Pragma defer_foreign_keys: 0
53
Pragma ignore_check_constraints: 0
54
Pragma legacy_alter_table: 0
55
Pragma recursive_triggers: 0
56
Pragma writable_schema: 0

The script works by querying pragma compile_options for the compilation options, then looping through and checking some boolean pragmas as well.

Full documentation on available SQLite pragmas is here.