2

How can I use the FTS5 extension with the sqlite3 python module with Python 3.7?

I tried to run the following code in Python with python testFTS5.py:

import sqlite3
conn = sqlite.connect('some_db.db')
sqlite.enable_load_extension(True)
sqlite.load_extension('fts5')

which results in the error message:

Traceback (most recent call last):
  File "./src/test.py", line 3, in <module>
    sqlite.enable_load_extension(True)
AttributeError: module 'sqlite3' has no attribute 'enable_load_extension'

I tried sqlite.load_extension('FTS5') and sqlite.load_extension('ENABLE_FTS5') but it unsurprisingly yields the same error message (with the corresponding filename being not found). I also tried running the code with LD_LIBRARY_PATH=/usr/local/bin python testFTS5.py but I get the same error message.

I checked the sqlite3 location by running the following code in the terminal:

derno@ompn:/mnt/ilcompn0d1/user/dernonco/fts-test$ which sqlite3
/usr/local/bin/sqlite3

and I listed the installed sqlite3 extensions:

derno@ompn:/mnt/ilcompn0d1/user/dernonco/fts-test$ sqlite3
SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma compile_options;
COMPILER=gcc-5.4.0 20160609
DEFAULT_SYNCHRONOUS=2
DEFAULT_WAL_SYNCHRONOUS=2
ENABLE_FTS5
ENABLE_RTREE
SYSTEM_MALLOC
THREADSAFE=1

which seems to indicate that the FTS5 is available in my /usr/local/bin/sqlite3 version.

However when I run

import sqlite3

con = sqlite3.connect(':memory:')
cur = con.cursor()
cur.execute('pragma compile_options;')
available_pragmas = cur.fetchall()
con.close()

print(available_pragmas)

it outputs:

[('COMPILER=gcc-5.4.0 20160609',), ('DEFAULT_SYNCHRONOUS=2',), ('DEFAULT_WAL_SYNCHRONOUS=2',), 
('ENABLE_FTS3',), ('ENABLE_RTREE',), ('SYSTEM_MALLOC',), ('THREADSAFE=1',)]

There is no ENABLE_FTS5 in that list.

I tried with Python 3.7.6 (default, Dec 19 2019, 23:49:42) and Python 3.6.7 (default, Oct 25 2018, 09:16:13).

Franck Dernoncourt
  • 77,520
  • 72
  • 342
  • 501
  • 2
    Call `.enable_load_extension(True)` and `.load_extension('fts5')` on `conn`, and not on the module. – mechanical_meat Feb 16 '20 at 02:58
  • @VorsprungdurchTechnik Whoops, thanks! Sorry the [code I was following](https://stackoverflow.com/a/36656216/395857) had a typo apparently or was for prior versions of `sqlite3`. Your comment fixed the issue. You are welcome to convert your comment into an answer. I am now facing `sqlite3.OperationalError: fts5.so: cannot open shared object file: No such file or directory` when running `conn.load_extension('fts5')`, which am now trying to understand (I thought my `sqlite3` version had `ENABLE_FTS5` but perhaps the `sqlite3` python package doesn't use `/usr/local/bin/sqlite3`?), ideas welcome! – Franck Dernoncourt Feb 16 '20 at 03:07
  • 1
    @VorsprungdurchTechnik Thanks very much, do you mean compiling `sqlite3` the Python package or compiling `sqlite3` the binary file that is in `/usr/local/bin`? If the latter, it does seem to support FTS5 on my side (it says `ENABLE_FTS5` when I run `sqlite> pragma compile_options;`). – Franck Dernoncourt Feb 16 '20 at 03:29
  • 1
    Yes, nevermind. I do think it's supported. I'm now trying to cobble together an example to test that it's working on my side. – mechanical_meat Feb 16 '20 at 03:31

2 Answers2

1

You would call .enable_load_extension(True) and .load_extension('fts5') on the connection object, and not on the module.

However, this shouldn't be necessary since -- as you've seen -- your installation supports full-text search.

Here's a way you can test that out just to be sure:

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()

Result:

In [67]: conn.execute("""select * from fts5test where data match 'full';""").fetchall()
Out[67]: [('this is a test of full-text search',)]
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • Thanks, interestingly on my side when I run your code I get `sqlite3.OperationalError: no such module: fts5` when running the line ` conn.execute("""create virtual table fts5test using fts5 (data);""")`. Do you use the default `sqlite3` python package that comes with the package installation? If so, which version of python do you use? – Franck Dernoncourt Feb 16 '20 at 03:51
  • 1
    I use the default version of sqlite3 that comes with Python, yes. I am running Python 3.8.0 and 3.6.9... Let me test on 3.6.9 right now. – mechanical_meat Feb 16 '20 at 03:54
  • Thanks, what's the output of `python -c "import sqlite3; print(sqlite3.sqlite_version)"` (I have 3.18.0) and do you know [how can I see which sqlite3 binary does the sqlite3 Python module use on Ubuntu 16.04?](https://stackoverflow.com/q/60245354/395857) – Franck Dernoncourt Feb 16 '20 at 04:14
  • 1
    ok the issue comes from Ubuntu 16.04 (everything looks fine on Ubuntu 18.04), so I opened a question focused on it [How can I use the FTS5 extension with the sqlite3 python module with Python 3.7 on Ubuntu 16.04?](https://stackoverflow.com/q/60246821/395857) with reproducible docker+code (yours) showing the issue. – Franck Dernoncourt Feb 16 '20 at 08:43
  • Ah, thanks for notifying me. I'm indeed running Ubuntu 18.04. I hope you get an answer to your 16.04 question. – mechanical_meat Feb 16 '20 at 13:56
0

The easiest way I have come across to use sqlite enable with FTS5 is to use python in a conda environment. By default, sqlite comes compiled, installed with latest version.

You can do the following (on your terminal):

# create a conda environment py_env
conda create -n py_env python=3.7

# activate your environment
conda activate py_env

Now, all you need to do is launch python from this environment to use sqlite3.

YOLO
  • 20,181
  • 5
  • 20
  • 40