Using pysqlite3 on macOS#

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

1
>>> import pysqlite3
2
Traceback (most recent call last):
3
  File "<stdin>", line 1, in <module>
4
  File "/Users/simon/.local/share/virtualenvs/datasette-AWNrQs95/lib/python3.8/site-packages/pysqlite3/__init__.py", line 23, in <module>
5
    from pysqlite3.dbapi2 import *
6
  File "/Users/simon/.local/share/virtualenvs/datasette-AWNrQs95/lib/python3.8/site-packages/pysqlite3/dbapi2.py", line 28, in <module>
7
    from pysqlite3._sqlite3 import *
8
ImportError: dlopen(/Users/simon/.local/share/virtualenvs/datasette-AWNrQs95/lib/python3.8/site-packages/pysqlite3/_sqlite3.cpython-38-darwin.so, 2): Symbol not found: _sqlite3_enable_load_extension
9
  Referenced from: /Users/simon/.local/share/virtualenvs/datasette-AWNrQs95/lib/python3.8/site-packages/pysqlite3/_sqlite3.cpython-38-darwin.so
10
  Expected in: flat namespace
11
 in /Users/simon/.local/share/virtualenvs/datasette-AWNrQs95/lib/python3.8/site-packages/pysqlite3/_sqlite3.cpython-38-darwin.so

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:

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

Then:

1
>>> import pysqlite3
2
>>> pysqlite3.connect(":memory:").execute("select sqlite_version()").fetchall()
3
[('3.36.0',)]

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:

1
% which sqlite3
2
/usr/bin/sqlite3
3
% echo "select sqlite_version()" | /usr/bin/sqlite3
4
3.28.0
5
% echo "select sqlite_version()" | /usr/local/opt/sqlite3/bin/sqlite3
6
3.36.0