Using pysqlite3 on macOS

While trying to use pysqlite3 on macOS I got the following error:

>>> import pysqlite3
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/simon/.local/share/virtualenvs/datasette-AWNrQs95/lib/python3.8/site-packages/pysqlite3/", line 23, in <module>
    from pysqlite3.dbapi2 import *
  File "/Users/simon/.local/share/virtualenvs/datasette-AWNrQs95/lib/python3.8/site-packages/pysqlite3/", line 28, in <module>
    from pysqlite3._sqlite3 import *
ImportError: dlopen(/Users/simon/.local/share/virtualenvs/datasette-AWNrQs95/lib/python3.8/site-packages/pysqlite3/, 2): Symbol not found: _sqlite3_enable_load_extension
  Referenced from: /Users/simon/.local/share/virtualenvs/datasette-AWNrQs95/lib/python3.8/site-packages/pysqlite3/
  Expected in: flat namespace
 in /Users/simon/.local/share/virtualenvs/datasette-AWNrQs95/lib/python3.8/site-packages/pysqlite3/

Thanks to this tip on Stack Overflow I found that installing a fresh SQLite with brew install sqlite3 and then running the following first fixed the error:

export DYLD_LIBRARY_PATH=/usr/local/opt/sqlite/lib:/usr/lib


>>> import pysqlite3
>>> pysqlite3.connect(":memory:").execute("select sqlite_version()").fetchall()

This is because the Homebrew SQLite is "keg-only", which means that it does not become the default on the system to avoid intefering with the existing SQLite that ships with macOS.

As illustrated by the following:

% which sqlite3
% echo "select sqlite_version()" | /usr/bin/sqlite3
% echo "select sqlite_version()" | /usr/local/opt/sqlite3/bin/sqlite3 

Created 2021-07-10T12:21:23-07:00 · Edit