datasette-rapidfuzz 0.1.0

Datasette plugin adding SQL functions for fuzzy text matching powered by RapidFuzz

datasette-rapidfuzz

Datasette plugin adding SQL functions for fuzzy text matching powered by RapidFuzz.

It complements datasette-jellyfish, which provides edit-distance and phonetic metrics (Levenshtein, Damerau-Levenshtein, Jaro, Jaro-Winkler, Soundex, Metaphone). RapidFuzz adds the token- and partial-ratio family — wratio, token_sort_ratio, token_set_ratio, partial_ratio — which tolerate word reordering and partial overlaps that a plain edit distance cannot express, on a fast C++ core. Use jellyfish for distance/phonetics, this for ratio-style scoring.

Installation

Install it into the same environment as Datasette:

datasette install datasette-rapidfuzz

Usage

This plugin registers five SQLite functions:

rapidfuzz_ratio(a, b)
rapidfuzz_partial_ratio(a, b)
rapidfuzz_token_sort_ratio(a, b)
rapidfuzz_token_set_ratio(a, b)
rapidfuzz_wratio(a, b)

Example:

select rapidfuzz_wratio('Candollea', 'Candollea 37') as score;

Returns:

95.0

Finding the closest matches

To rank rows by similarity to a search term, score each row and sort with order by score desc — the default ascending order returns the least similar rows first. The functions return null for a null input, and null sorts first when ascending, so a plain order by score tends to surface blank rows. Add distinct and filter out nulls to see the closest distinct values rather than repeated copies of the top hit:

select distinct
  name,
  rapidfuzz_wratio(name, 'Ginkgo') as score
from things
where name is not null
order by score desc
limit 20;

Fuzzy scoring is evaluated row-by-row by SQLite, so on large tables narrow the rows first (with a where clause) or fuzz against a deduplicated helper table:

create table distinct_names as select distinct name from things;

Finding near-duplicates (self-join)

Comparing a column against itself finds values that are similar but not identical — likely typos or spelling variants. A self-join is O(n²), so for anything but a small table you need to block: only compare values that share a cheap key. Blocking on the first two characters works well, because typos and variants almost always differ inside a word rather than at the start, so a variant still shares its opening letters — while two characters keep each block small:

with items as (
  select distinct name from things
  where name is not null and substr(name, 1, 2) = 'Ca'
),
pairs as (
  select a.name as a, b.name as b, rapidfuzz_wratio(a.name, b.name) as score
  from items a join items b on a.name < b.name
)
select a, b, score
from pairs
where score >= 90 and score < 100
order by score desc
limit 30;

a.name < b.name compares each pair once and skips self-pairs; score < 100 drops exact matches so only genuine variants remain. Swap the literal 'Ca' for :prefix to make it an interactive parameter in Datasette.

Blocking trades coverage for speed: a shorter key (substr(name, 1, 1)) casts a wider net but forms far more pairs, while a longer key is faster but misses variants that differ within the blocked prefix. Raise Datasette's limit for large blocks (--setting sql_time_limit_ms 30000).

To sweep a whole column in one query, move the block into the join and run it against a deduplicated, indexed helper table:

create table distinct_names as select distinct name from things;
create index idx_block on distinct_names (substr(name, 1, 2), name);

select a.name as a, b.name as b, rapidfuzz_wratio(a.name, b.name) as score
from distinct_names a join distinct_names b
  on substr(a.name, 1, 2) = substr(b.name, 1, 2) and a.name < b.name
where rapidfuzz_wratio(a.name, b.name) >= 90
  and rapidfuzz_wratio(a.name, b.name) < 100
order by score desc;

The index lets SQLite seek each block instead of forming the full cross product — the difference between a query that finishes in seconds and one that times out. Index a small helper table rather than the source table, so the main database stays untouched.

For developers

Clone this repository and install your local copy in editable mode, so your edits take effect without reinstalling:

pip install -e . --no-build-isolation

Run the tests:

python -m unittest discover