Examples of FullTextSearch, spellfix, FullTextSearch+spellfix together, with Python and Sqlite

fulltextsearch.py

import sqlite3
db = sqlite3.connect(':memory:')
c = db.cursor()
c.execute('CREATE TABLE mytable (description text)')
c.execute('INSERT INTO mytable VALUES ("Riemann")')
c.execute('INSERT INTO mytable VALUES ("All the Carmichael numbers")')

print '1) EQUALITY'
c.execute('SELECT * FROM mytable WHERE description == "Riemann"'); print 'Riemann:', c.fetchall()

print '\n2) LEVENSHTEIN DISTANCE (STRING SIMILARITY)'  # ok but slow because needs a *full traversal* of DB rows
db.enable_load_extension(True)
db.load_extension('./spellfix')
c.execute('SELECT * FROM mytable WHERE editdist3(description, "Riehmand") < 300'); print 'Riehmand:', c.fetchall()

print '\n3) WITH SPELLFIX VIRTUAL TABLE (ACCEPT SPELLING MISTAKES)'  # same than 2) but more optimized
c.execute('CREATE VIRTUAL TABLE mytable3 USING spellfix1')
c.execute('INSERT INTO mytable3(word) VALUES ("Riemann")')
c.execute('SELECT * FROM mytable3 WHERE word MATCH "Riehmand"'); print 'Riehmand:', c.fetchall()

print '\n4) FIND ROW WITH MULTIPLE WORDS WITH QUERY=1 WORD, NAIVE VERSION'  # slow! case insensitive
c.execute('SELECT * FROM mytable WHERE description LIKE "%Carmichael%"'); print '%Carmichael%:', c.fetchall()
c.execute('SELECT * FROM mytable WHERE description LIKE "%carmichael%"'); print '%carmichael%:', c.fetchall()
c.execute('SELECT * FROM mytable WHERE description LIKE "%carmichA%"'); print '%carmichA%:', c.fetchall()

print '\n5) FIND ROW WITH MULTIPLE WORDS WITH QUERY=1 WORD, USING FTS EXTENSION'  # 750x faster! case insensitive
c.execute('CREATE VIRTUAL TABLE mytable2 USING fts4(description text)')
c.execute('INSERT INTO mytable2 VALUES ("All the Carmichael numbers")')
c.execute('SELECT * FROM mytable2 WHERE description MATCH "Carmichael"'); print 'Carmichael:', c.fetchall() 
c.execute('SELECT * FROM mytable2 WHERE description MATCH "NUMBERS carmichael"'); print 'NUMBERS carmichael', c.fetchall()
c.execute('SELECT * FROM mytable2 WHERE description MATCH "carmichA"'); print 'carmichA:', c.fetchall()   # !!not found!!
c.execute('SELECT * FROM mytable2 WHERE description MATCH "Carmickaeel"'); print 'Carmickaeel:', c.fetchall()   # !!not found!!

print '\n6) FIND ROW WITH MULTIPLE WORDS WITH QUERY=1 WORD + ACCEPT SPELLING MISTAKES'  # to do!
'Carmickaeel'
'number Carmickaeel'