Skip to content

Instantly share code, notes, and snippets.

@josephernest
Created October 14, 2018 18:26
Show Gist options
  • Save josephernest/40f19c9750aff9bebe89827ea329fec8 to your computer and use it in GitHub Desktop.
Save josephernest/40f19c9750aff9bebe89827ea329fec8 to your computer and use it in GitHub Desktop.
Examples of FullTextSearch, spellfix, FullTextSearch+spellfix together, with Python and Sqlite
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'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment