Python+Sqlite字符串相似度度量方法咨询（含Levenshtein距离/编辑距离）

Absolutely! You can use string similarity metrics in Python's sqlite3 environment — SQLite just doesn't include these functions out of the box, so we'll register custom Python functions to use directly in our SQL queries. Let's walk through two common approaches to solve your exact scenario.

Approach 1: Levenshtein Edit Distance #

The Levenshtein distance measures how many edits (insertions, deletions, substitutions) are needed to turn one string into another. We'll define this function in Python, register it with SQLite, then use it to filter rows based on a similarity threshold.

Full Code Example #

import sqlite3

# Define the Levenshtein distance function
def levenshtein(s1, s2):
    if len(s1) < len(s2):
        return levenshtein(s2, s1)
    if len(s2) == 0:
        return len(s1)
    previous_row = range(len(s2) + 1)
    for i, c1 in enumerate(s1):
        current_row = [i + 1]
        for j, c2 in enumerate(s2):
            insertions = previous_row[j + 1] + 1
            deletions = current_row[j] + 1
            substitutions = previous_row[j] + (c1 != c2)
            current_row.append(min(insertions, deletions, substitutions))
        previous_row = current_row
    return previous_row[-1]

# Set up the in-memory SQLite database
conn = sqlite3.connect(':memory:')
c = conn.cursor()

# Register our Levenshtein function with SQLite
conn.create_function("levenshtein", 2, levenshtein)

# Create table and insert sample data
c.execute('CREATE TABLE mytable (id integer, description text)')
c.execute('INSERT INTO mytable VALUES (1, "hello world, guys")')
c.execute('INSERT INTO mytable VALUES (2, "hello there everybody")')

# Query: Match rows where description is close to "hello world" (distance ≤ 6)
c.execute('SELECT * FROM mytable WHERE levenshtein(description, "hello world") <= 6')
results = c.fetchall()
print("Levenshtein Results:", results)  # Will only return row with ID 1

# Cleanup
conn.close()

Approach 2: Jaccard Similarity (N-Grams) #

Jaccard similarity compares the overlap of n-grams (substrings of length n) between two strings. It's great for measuring how "similar" two text phrases are in terms of shared character patterns.

Full Code Example #

import sqlite3

# Define Jaccard similarity function with n-gram support
def jaccard_similarity(s1, s2, n=2):
    def get_ngrams(s, n):
        s = s.lower().strip()
        return set([s[i:i+n] for i in range(len(s)-n+1)]) if len(s) >= n else set()
    
    ngrams1 = get_ngrams(s1, n)
    ngrams2 = get_ngrams(s2, n)
    
    if not ngrams1 and not ngrams2:
        return 1.0
    return len(ngrams1 & ngrams2) / len(ngrams1 | ngrams2)

# Set up database
conn = sqlite3.connect(':memory:')
c = conn.cursor()

# Register the Jaccard function (3 parameters: s1, s2, n)
conn.create_function("jaccard", 3, jaccard_similarity)

# Create table and insert data (same as before)
c.execute('CREATE TABLE mytable (id integer, description text)')
c.execute('INSERT INTO mytable VALUES (1, "hello world, guys")')
c.execute('INSERT INTO mytable VALUES (2, "hello there everybody")')

# Query: Match rows where Jaccard similarity to "hello world" is > 0.3
c.execute('SELECT * FROM mytable WHERE jaccard(description, "hello world", 2) > 0.3')
results = c.fetchall()
print("Jaccard Results:", results)  # Only returns row with ID 1

# Cleanup
conn.close()

Key Notes #

• Custom Functions: SQLite lets you register Python functions via conn.create_function() — this is the magic that lets you use Python's logic directly in SQL.
• Threshold Tuning: Adjust the distance/similarity thresholds (like <=6 or >0.3) based on your exact use case.
• Performance: For large datasets, these custom functions might be slower than built-in SQLite functions, but they work perfectly for small to medium-sized tables.

内容的提问来源于stack exchange，提问作者Basj