Python's sqlite3 with extensions

Adding SQLite extensions with Python's sqlite3 module is a breeze. Download a file, call a few functions, and you are good to go. Unless you try to do it on macOS, where sqlite3 is compiled without extension support.

I wanted to make the process even easier (and of course solve the macOS problem). So I created the sqlean.py package: a drop-in replacement for the standard library's sqlite3 module, bundled with the essential extensions.

Installation and usage

All you need to do is pip install the package:

pip install sqlean.py

And use it just like you'd use sqlite3:

import sqlean as sqlite3

# has the same API as the default `sqlite3` module
conn = sqlite3.connect(":memory:")
conn.execute("create table employees(id, name)")

# and comes with the `sqlean` extensions
cur = conn.execute("select median(value) from generate_series(1, 99)")
print(cur.fetchone())
# (50.0,)

conn.close()

Note that the package name is sqlean.py, while the code imports are just sqlean. The sqlean package name was taken by some zomby project and the author seemed to be unavailable, so I had to add the .py suffix.

Extensions

sqlean.py contains 12 essential SQLite extensions:

crypto: Hashing, encoding and decoding data.
define: User-defined functions and dynamic SQL.
fileio: Reading and writing files and catalogs.
fuzzy: Fuzzy string matching and phonetics.
ipaddr: IP address manipulation.
math: Math functions.
regexp: Pattern matching using regular expressions.
stats: Math statistics — median, percentiles, etc.
text: Powerful Unicode-aware string functions.
uuid: Universally Unique IDentifiers.
vsv: CSV files as virtual tables.

Platforms

The package is available for the following operating systems:

Windows (64-bit)
Linux (64-bit)
macOS (both Intel and Apple processors)

You can also compile it from source if necessary.

See the package repo for details.

★ Subscribe to keep up with new posts.