Anton Zhiyanov
projects blog about

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.

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
  • fuzzy: Fuzzy string matching and phonetics
  • ipaddr: IP address manipulation
  • math: Math functions
  • regexp: Regular expressions
  • stats: Math statistics
  • text: Advanced string functions
  • unicode: Unicode support
  • 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.