How can I use the FTS5 extension with the sqlite3

问题:

To test whether the FTS5 extension with the sqlite3 Python module works, I use this code from Vorsprung durch Technik:

import sqlite3     
conn = sqlite3.connect(':memory:')
conn.execute("""create virtual table fts5test using fts5 (data);""") 
conn.execute("""insert into fts5test (data) 
                values ('this is a test of full-text search');""")
conn.execute("""select * from fts5test where data match 'full';""").fetchall() 

The code works fine on Ubuntu 18.04 and Ubuntu 19.04. For example one can run it via Docker with the following python3.7 interpreter:

docker pull ubuntu:18.04 # This line is optional.
docker run --interactive --tty ubuntu:18.04 bash
apt update
apt install -y software-properties-common
add-apt-repository -y ppa:deadsnakes/ppa
apt update 
apt install -y python3.7
python3.7
# use here the python code given above 

However, if I change the Ubuntu version from 18.04 to 16.04, then the FTS5 extension doesn't work:

docker pull ubuntu:16.04 # This line is optional.
docker run --interactive --tty ubuntu:16.04 bash
apt update
apt install -y software-properties-common
add-apt-repository -y ppa:deadsnakes/ppa
apt update 
apt install -y python3.7
python3.7
# use here the python code given above 

The python code will crash:

[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> conn.execute("""create virtual table fts5test using fts5 (data);""")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such module: fts5

How can I use the FTS5 extension with the sqlite3 Python module with Python 3.7 on Ubuntu 16.04?

回答1:

These two articles (Force Python to forego native sqlite3 and use the (installed) latest sqlite3 version and http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts5-extensions-with-python/) could be a solution to the issue.

Try these steps:

1. Download the SQLite source code and build it with FTS5 enabled.

   $ wget https://www.sqlite.org/2020/sqlite-autoconf-3310100.tar.gz
   $ tar xvzf sqlite-autoconf-3310100.tar.gz
   $ cd sqlite-autoconf-3310100
   $ CFLAGS="-DSQLITE_ENABLE_FTS5" ./configure
   $ make sqlite3.c
   

2. Before you move on, you may want to activate your virtual environment.

   $ source <path to Python3.7 virtual environment>/bin/activate
   (python37env) $
   

3. The author of this article (http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts5-extensions-with-python/) took SQLite3 DB-API 2.0 driver from Python 3 as a separate package (enter link description here). So you can use it to replace the original driver.

   (python37env) $ wget https://github.com/coleifer/pysqlite3/archive/0.4.2.tar.gz
   (python37env) $ tar xvzf 0.4.2.tar.gz
   (python37env) $ cd pysqlite3-0.4.2
   (python37env) $ cp <path to sqlite-autoconf-3310100>/sqlite3.h .
   (python37env) $ cp <path to sqlite-autoconf-3310100>/sqlite3.c .
   (python37env) $ python setup.py build_static build
   (python37env) $ python setup.py install
   

   Now, the seperate pysqlite module should be installed onto your virtual environment.

4. Test it. Instead of using import sqlite3 to import the pysqlite3 module, use from pysqlite3 import dbapi2 as sqlite3. This will import the SQLite3 DB-API 2.0 driver from the custom pysqlite module. And your test code will be like the following:

   # import sqlite3
   from pysqlite3 import dbapi2 as sqlite3
   
   conn = sqlite3.connect(':memory:')
   conn.execute("""create virtual table fts5test using fts5 (data);""")
   conn.execute("""insert into fts5test (data)
                   values ('this is a test of full-text search');""")
   conn.execute("""select * from fts5test where data match 'full';""").fetchall()
   

This method works on my Ubuntu 16.04 VM with Python 3.7.3.