Python 2.5 + sqlite full text search possible?

[Guillermo]

Hi!

Is it possible to use the full-text module of SQLite with the sqlite3
module? I've done a bit of investigation and it seems the stand-alone
distribution of SQLite is compiled without it, and so does the version
bundled with Python.

Regards,

Guillermo

 

[Guilherme Polo]

Hi!

Is it possible to use the full-text module of SQLite with the sqlite3
module? I've done a bit of investigation and it seems the stand-alone
distribution of SQLite is compiled without it, and so does the version
bundled with Python.

Support for loading extensions was only added in pysqlite 2.5, so you
will have to install it.

Python 2.5 and 2.6 comes with pysqlite 2.3.2, future 3.0 will come
with pysqlite 2.4.1.

 

[Gerhard Häring]

Hi!

Is it possible to use the full-text module of SQLite with the sqlite3
module? I've done a bit of investigation and it seems the stand-alone
distribution of SQLite is compiled without it,

Yes, though compiling using the amalgamation and defining
SQLITE_ENABLE_FTS3 helps.

and so does the version bundled with Python.

True.

I'm too lazy to build a SQLite3 DLL with FTS enabled, I'm pretty sure
those can be found on the net.

But I've just patched pysqlite with one line:

+ ext.define_macros.append(("SQLITE_ENABLE_FTS3", "1")) #
build with fulltext search enabled

which helped its super-crazy script mkwin32.py to build Windows binaries
*on Linux* that fetch the SQLite amalgamation and build Windows
binaries for Python 2.3, 2.4 and 2.5 (no 2.6, yet).

Just go here
http://oss.itsystementwicklung.de/download/pysqlite/2.5/2.5.0/win32_fts/

download and install the binary for Python 2.5 and off you go:

from pysqlite2 import dbapi2 as sqlite3

con = sqlite3.connect(":memory:")

# example from SQLite wiki
con.execute("create virtual table recipe using fts3(name, ingredients)")
con.executescript("""
insert into recipe (name, ingredients) values ('broccoli stew', 'broccoli peppers cheese tomatoes');
insert into recipe (name, ingredients) values ('pumpkin stew', 'pumpkin onions garlic celery');
insert into recipe (name, ingredients) values ('broccoli pie', 'broccoli cheese onions flour');
insert into recipe (name, ingredients) values ('pumpkin pie', 'pumpkin sugar flour butter');
""")
for row in con.execute("select rowid, name, ingredients from recipe where name match 'pie'"):
print row

-- Gerhard