Full-text search (FTS) is a powerful feature that allows you to search text-heavy databases quickly. With SQLite, you can use FTS to search large bodies of text more efficiently than standard queries. This is particularly useful when you have datasets like articles, product descriptions, or any other textual data that needs to be searched frequently. In this article, we’ll explore how to enable FTS in SQLite using Python, create a dummy database, and demonstrate some search queries.
Setting Up SQLite with FTS
First, ensure you have SQLite installed, along with Python’s sqlite3 library. For this tutorial, we’ll use the built-in sqlite3 module, which comes with Python by default.
To get started, create a database with a table specifically designed for full-text search. SQLite provides the fts5 extension, which allows you to create virtual tables optimized for searching text. Let’s walk through creating a dummy database that stores a collection of articles.
Step 1: Creating a Database and FTS Table
import sqlite3
# Connect to the database (creates one if it doesn't exist)
conn = sqlite3.connect('articles.db')
cursor = conn.cursor()
# Create an FTS5 virtual table for articles
cursor.execute('''
CREATE VIRTUAL TABLE IF NOT EXISTS articles USING fts5(title, content)
''')
# Insert some dummy data into the articles table
articles = [
("Python Full-Text Search", "Learn how to enable full-text search in SQLite using Python."),
("Introduction to SQLite", "SQLite is a lightweight database engine. It’s great for local storage."),
("Working with Databases", "Database management is key for application development."),
("Advanced SQL Techniques", "Mastering SQL queries can significantly improve your application performance.")
]
cursor.executemany('INSERT INTO articles (title, content) VALUES (?, ?)', articles)
# Commit changes and close the connection
conn.commit()Here, we’ve created a database called articles.db and defined an FTS5 virtual table named articles. The table has two fields: title and content. After creating the table, we inserted four articles as examples.
Step 2: Searching Using FTS
Now that we have a database with some content, let’s search it. Using FTS, you can query the database with natural language queries. For example, let’s search for articles that contain the word “Python”.
# Search for articles that mention 'Python'
query = "SELECT title, content FROM articles WHERE articles MATCH 'Python'"
cursor.execute(query)
# Fetch and print the results
results = cursor.fetchall()
for title, content in results:
print(f"Title: {title}\nContent: {content}\n")Running this code will return any articles that mention the word “Python”. Here’s what you’ll see:
Title: Python Full-Text Search
Content: Learn how to enable full-text search in SQLite using Python.Step 3: Searching with Multiple Terms
FTS allows you to search for multiple terms at once. You can search for phrases or use logical operators like AND and OR. For example, let’s search for articles that mention either “Python” or “SQLite”.
# Search for articles mentioning 'Python' or 'SQLite'
query = "SELECT title, content FROM articles WHERE articles MATCH 'Python OR SQLite'"
cursor.execute(query)
# Fetch and display the results
results = cursor.fetchall()
for title, content in results:
print(f"Title: {title}\nContent: {content}\n")In this case, we are searching for articles that contain either “Python” or “SQLite”. You will see results for both terms:
Title: Python Full-Text Search
Content: Learn how to enable full-text search in SQLite using Python.
Title: Introduction to SQLite
Content: SQLite is a lightweight database engine. It’s great for local storage.Step 4: Using Advanced Search Queries
FTS provides more advanced search capabilities, including phrase searches and prefix searches. To search for an exact phrase, you can use quotes. For example, let’s search for articles that mention the exact phrase “database management”:
# Search for an exact phrase
query = "SELECT title, content FROM articles WHERE articles MATCH '\"database management\"'"
cursor.execute(query)
# Fetch and print the results
results = cursor.fetchall()
for title, content in results:
print(f"Title: {title}\nContent: {content}\n")This query will return any articles where “database management” appears as a complete phrase:
Title: Working with Databases
Content: Database management is key for application development.If you want to perform a prefix search (searching for words that begin with a specific prefix), simply add an asterisk * to the end of the term. For example, searching for words starting with “adv”:
# Prefix search for words starting with 'adv'
query = "SELECT title, content FROM articles WHERE articles MATCH 'adv*'"
cursor.execute(query)
# Fetch and display the results
results = cursor.fetchall()
for title, content in results:
print(f"Title: {title}\nContent: {content}\n")This will return any articles containing words like “advanced” or “advantage”:
Title: Advanced SQL Techniques
Content: Mastering SQL queries can significantly improve your application performance.Step 5: Handling Search Results
FTS queries can return large amounts of data. To make results easier to manage, consider using pagination or limiting the number of results:
# Limit the number of results to 2
query = "SELECT title, content FROM articles WHERE articles MATCH 'SQL' LIMIT 2"
cursor.execute(query)
# Fetch and print the limited results
results = cursor.fetchall()
for title, content in results:
print(f"Title: {title}\nContent: {content}\n")Here’s the complete code:
import sqlite3
# Connect to the database (creates one if it doesn't exist)
conn = sqlite3.connect('articles.db')
cursor = conn.cursor()
# Create an FTS5 virtual table for articles
cursor.execute('''
CREATE VIRTUAL TABLE IF NOT EXISTS articles USING fts5(title, content)
''')
# Insert some dummy data into the articles table
articles = [
("Python Full-Text Search", "Learn how to enable full-text search in SQLite using Python."),
("Introduction to SQLite", "SQLite is a lightweight database engine. It’s great for local storage."),
("Working with Databases", "Database management is key for application development."),
("Advanced SQL Techniques", "Mastering SQL queries can significantly improve your application performance.")
]
cursor.executemany('INSERT INTO articles (title, content) VALUES (?, ?)', articles)
# Commit changes to the database
conn.commit()
# Search for articles that mention 'Python'
query = "SELECT title, content FROM articles WHERE articles MATCH 'Python'"
cursor.execute(query)
# Fetch and print the results
results = cursor.fetchall()
print("\n--- Articles that mention 'Python' ---")
for title, content in results:
print(f"Title: {title}\nContent: {content}\n")
# Search for articles mentioning 'Python' or 'SQLite'
query = "SELECT title, content FROM articles WHERE articles MATCH 'Python OR SQLite'"
cursor.execute(query)
# Fetch and print the results
results = cursor.fetchall()
print("\n--- Articles that mention 'Python' or 'SQLite' ---")
for title, content in results:
print(f"Title: {title}\nContent: {content}\n")
# Search for an exact phrase 'database management'
query = "SELECT title, content FROM articles WHERE articles MATCH '\"database management\"'"
cursor.execute(query)
# Fetch and print the results
results = cursor.fetchall()
print("\n--- Articles that mention the exact phrase 'database management' ---")
for title, content in results:
print(f"Title: {title}\nContent: {content}\n")
# Prefix search for words starting with 'adv'
query = "SELECT title, content FROM articles WHERE articles MATCH 'adv*'"
cursor.execute(query)
# Fetch and print the results
results = cursor.fetchall()
print("\n--- Articles with words starting with 'adv' ---")
for title, content in results:
print(f"Title: {title}\nContent: {content}\n")
# Limit the number of results to 2
query = "SELECT title, content FROM articles WHERE articles MATCH 'SQL' LIMIT 2"
cursor.execute(query)
# Fetch and print the limited results
results = cursor.fetchall()
print("\n--- Limited search results for 'SQL' (max 2) ---")
for title, content in results:
print(f"Title: {title}\nContent: {content}\n")
# Close the connection
conn.close()This complete code walks through the entire process:
- Creating an SQLite database and a full-text search (FTS5) virtual table.
- Inserting sample articles into the table.
- Running several types of searches, including basic, multiple term, phrase, and prefix searches.
- Fetching and displaying search results in a formatted manner.
Thank you for following along with this tutorial. We hope you found it helpful and informative. If you have any questions, or if you would like to suggest new Python code examples or topics for future tutorials/articles, please feel free to join and comment. Your feedback and suggestions are always welcome!
You can find the same tutorial on Medium.com.
