SQLite Logo Recently, I was working on a LinkedIn spy , I have used the database to store data , this tool need to support a full text search fonctionnality for application. tool SQLite Googling on the internet and I found that already support full text searching natively. SQLite This one done via three evolutions of the full text search extension / / . The original FTS3 code were contributed to the SQLite project by of Google. FTS3 FTS4 FTS5 Scott Hess FTS1 and FTS2 are obsolete full-text search modules for SQLite. There are known issues with these older modules and their use should be avoided. Let’s play with this extension, I choose this Moroccan beautiful “ , the idea is to extract some reviews and search on them. Riad Riad Al Mamoune ” — TripAdvisor Riad Al Mamoune To manipulate the SQLite database, I use the sqlite3 command in ubuntu, but we need to update it as doesn’t support the FTS5, so, to update it run the following commands lines on terminal: sudo add-apt-repository ppa:jonathonf/backportssudo apt-get update && sudo apt-get install sqlite3 After update, then go to your workspace and create the SQLite database file by running: sqlite3 hotels_reviews.db Now, copy and paste every SQL part on this gist and run it on the sqlite3 prompt, please read comments to understand more: After you have set properly your database, now, we can try to execute some queries searches: SELECT rowid, review FROM hotels_reviews_index WHERE hotels_reviews_index MATCH 'review:help'; You can also order by relevance which is the rank score: SELECT rowid, rank, review FROM hotels_reviews_index WHERE hotels_reviews_index MATCH 'review:square AND location' ORDER BY rank; Please check the official documentation for syntax and operators supported by the FTS5 extension by following this link: https://www.sqlite.org/fts5.html#full_text_query_syntax You can read more about this topic here: _In this post I will show how to use SQLite full-text search with Python (and a lot of help from peewee ORM). We will…_charlesleifer.com Using SQLite Full-Text Search with Python _Using full text search with python and sqlite for tutorial, beginner, sqlite3, fts, database, query and fts4 Code…_code.runnable.com Using full text search with python and sqlite for tutorial, beginner, sqlite3, fts, database, query… ❤ If this post was helpful, please hit the little green heart and follow me using the follow buttons below!
