Python SQLAlchemy SQLite FTS unicode6.1 tokenizer

Question

This is the code to attempt SQLAlchemy database reflection on a SQLite3 database with FTS tables that use a unicode61 tokenizer:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///database.sqlite')
Base = declarative_base()
metadata = Base.metadata
metadata.reflect(bind=engine, views=True)

This error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unknown tokenizer: unicode61

As of v3.8.6 this tokenizer is included by default and is not optionally compiled.

v3.8.6 The unicode61 tokenizer is now included in FTS4 by default.

Meanwhile when I run:

$ python
>>> import sqlite3
>>> sqite3.sqlite_version
'3.8.5'

... frick.

However:

$ sqlite3
SQLite version 3.9.2 2015-11-02 18:31:45

How do I get Python or SQLAlchemy in particular to reference a more modern or even custom compiled sqlite3 binary?

Additional note: I am trying to run on an OSX (10.10.5 Yosemite) system so as far as I can tell SQLAlchemy and Python are referencing pysqlite which is the dbapi2 wrapper for sqlite3.dylib. I have just found the following article which I will try and return with my results.

http://sqlite.1065341.n5.nabble.com/How-to-build-a-new-sqlite3-dylib-td63635.html

Results so far:

• Renaming /usr/lib/libsqlite3.dylib in prep for replacing it with the custom build will brick OSX and you need to perform a System Restore. So I'm going to try a virtualenv approach instead.

• This SO question attempts to replace the sqlite3 library linked in a virtualenv but I haven't figured out how to apply this to SQLAlchemy How to upgrade sqlite3 in python 2.7.3 inside a virtualenv?

Answer 1

Answer derived from this StackOverflow Answer:

https://stackoverflow.com/a/1546162/622276

1. Download pysqlite2 source code from https://pypi.python.org/pypi/pysqlite
2. Download latest SQLite amalgamation source code (v3.9.2 at time of writing) http://www.sqlite.org/download.html (sqlite-amalgamation-3XXXXXX.zip)
3. Extract pysqlite2 source code.
4. Extract sqlite3 amalgamation source code.
5. copy sqlite3 source code into root of where you extracted the pysqlite2, eg path-to/pysqlite-2.X.X.
6. cd path-to/pysqlite-2.X.X

7. python setup.py build_static

   This will take the sqlite3 source code you dumped in the folder and build a static library with the pysqlite2 module.

8. This will take a minute but there will be a new directory build and inside there will be a folder like lib.macosx-10.10-intel-2.7. Inside that you are after the pysqlite2 folder.

   eg. path-to/pysqlite2.X.X/build/lib.macosx-10.10-intel-2.7/pysqlite2

9. NOTE: DO THE FOLLOWING AT YOUR OWN RISK. TRY TO USE virtulenv WHERE POSSIBLE. copy this pysqlite2 folder into /Library/Python/2.7/site-packages/ and overwrite the existing package.

10. SQLAlchemy points to this particular site-package of pysqlite2 for a SQLite wrapper and also the provided static library.

And finally:

$ python
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.8.5'
>>> from pysqlite2 import dbapi2 as sqlite3
>>> sqlite3.sqlite_version
'3.9.2'