Similar questions have been asked on SO several times (the references are at the end of the question). My interest is more focused and seeks additional details, so I decided to ask a new question.

Specifically, I want to upgrade the SQLite library used by the Python sqlite3 module on Windows. I need to make this upgrade for a local Python environment (venv, anaconda, etc.) using either an official binary or a custom-compiled version. I specifically do not want to use any third-party Python SQLite libraries. So the first part of the question is whether this upgrade is doable, how difficult it is, and what specific steps are necessary.

The second part of the question is associated with the modularity of SQLite. A significant portion of the SQLite features are not included in the core and must be enabled or may be disabled during compilation. The Python sqlite3 module does not provide any information about enabled SQLite functionality, except for the SQLite library version. So I am interested in how to gather more detailed information on the SQLite feature set available via the Python sqlite3 module. I want to see what functionality is missing before the upgrade and verify that it becomes available afterward.

The final part of the question is how to verify that the Python sqlite3 module functions correctly after the discussed upgrade.

For reference, here are similar questions previously asked on SO (the latest at the top):

How to make pyenv build a python with a different sqlite version or use different sqlite3 version for a python installed by pyenv?
Multiple versions of Sqlite3 for Python on the same server
How to change SQLite version used by Python?
Python sqlite3: run different sqlite3 version
Compile Python 3.4 with sqlite3
How to upgrade sqlite3 in python 2.7.3 inside a virtualenv?

CodePudding user response：

Swapping the library

The Python sqlite3 module consists of two main components. The first component is the Python bindings for the SQLite C API provided by the "DLLs\_sqlite3.pyd" module (all paths are relative to the Python environment top directory, that is, the directory with the python.exe/pythonw.exe executables). This module implements the SQLite Python DB API interface and is responsible for exposing SQLite C APIs in Python (its sources are in the Modules\_sqlite directory of the Python source distribution).

The other sqlite3 module component, the SQLite library (Library\bin\sqlite3.dll), is independent of Python. This module can be swapped with the official binary (the latest version is available from the download page, and earlier versions can also be used) or with a compatible custom-compiled version (see, for example, this tutorial).

Gathering SQLite engine information

SQLite provides engine-related information via the "PRAGMA" statement or associated pragma functions. The most informative are function, module, and compilation_options lists. The sqlite3 module does not expose this information, so it needs to be retrieved via SQL queries. GUI DBA tools can often facilitate interaction with database engines, but not in this case because most computers probably have numerous SQLite modules, and no two such copies may be alike. For this reason, GUI DBA tools typically do not provide relevant metadata. Instead, a Python script should retrieve engine-related information directly from the target Python environment. This process requires an open database connection, which can use a dummy in-memory database. The following Python code retrieves this information and prints it to the console.

import sqlite3  
import pprint  
  
  
con = sqlite3.connect(":memory:")  
cur = con.cursor()  
    
meta_scalar_sql = {  
    "sqlite_version":   "SELECT sqlite_version();",  
    "sqlite_source_id": "SELECT sqlite_source_id();",  
    "function_count":   "SELECT count(*) FROM pragma_function_list();",  
    "module_count":     "SELECT count(*) FROM pragma_module_list();",  
    "collation_count":  "SELECT count(*) FROM pragma_collation_list();",  
    "pragma_count":     "SELECT count(*) FROM pragma_pragma_list();",  
}  
  
for item, query in meta_scalar_sql.items():  
    print(item   " " * (25 - len(item))   str(cur.execute(query).fetchone()[0]))  
  
meta_list_sql = {  
    "compile_options":  "SELECT * FROM pragma_compile_options() ORDER BY compile_options;",  
    "function_list":    "SELECT name || '__' || narg AS name FROM pragma_function_list() ORDER BY name;",  
    "module_list":      "SELECT * FROM pragma_module_list() ORDER BY name;",  
    "pragma_list":      "SELECT * FROM pragma_pragma_list() ORDER BY name;",  
    "collation_list":   "SELECT name FROM pragma_collation_list();",  
}  
  
pp = pprint.PrettyPrinter(indent=4)  
for item, query in meta_list_sql.items():  
    pp.pprint(item   " " * (25 - len(item)))  
    metas = cur.execute(query).fetchall()  
    pp.pprint([metas[i][0] for i in range(len(metas))])  
  
cur.close()  
con.close()

Verification

The Python distribution includes a suite of unit tests for its sqlite3 module. The test modules are in the "Lib\sqlite3\test" directory and are based on the Python unittest testing framework. To enable the test discovery feature with default options, add the "test" prefix to all module names in that directory. Then execute python -m unittest from within the "test" directory. Alternatively, run the tests from within your Python IDE.