My List of Python and SQLite Resources

This post is going to be a greatest hits of my open-source libraries and blog posts concerning the use of SQLite with Python. I'll also share a list of some other neat SQLite projects that you may not have heard of before.

python-lsm-db is a set of Python bindings for SQLite4's LSM database. The SQLite4 LSM db is an embedded, ordered key/value store (like LevelDB) that supports nested transactions, cursors, single-writer/multiple-reader concurrency, and exceptional durability. LevelDB does not have nearly as many features, but in my testing the python-lsm-db library was almost as fast as the plyvel LevelDB bindings (and much faster than KyotoCabinet or BerkeleyDB).

The Python API for working with the LSM db is based on the dict API, with the added benefit that you can index slices of keys in addition to single keys. The API is small but powerful, and I envision the library being used as a foundation of sorts for other, higher-level storage applications.

For a quick introduction, you can check out the announcement blog post

scout is a RESTful full-text search server written in Python and powered by SQLite. You might think of it as a poor man's ElasticSearch. You can store multiple search indexes in a single SQLite database. Each search index consists of indexed documents and any user-defined metadata. Full-text searches can be performed over the index, and you can also filter on metadata values. The project also includes a lightweight Python client to go along with the search server.

sqlite-web is a web-based SQLite database browser and management tool. You can browse tables, run queries, and there are GUIs for modifying the schema. You can even drop and rename columns (something SQLite does not support out of the box).

peewee and various extensions

peewee is a lightweight ORM with clean, predictable APIs for executing queries and working with relational data. There are a number of extension modules packaged with peewee that you might find useful:

SQLite3 extension, with APIs for numerous SQLite-specific functionality.

APSW backend for using Peewee with the APSW SQLite driver.

Closure table support exposes a nice API for using the transitive-closure extension.

SQLCipher backend for working with encrypted SQLite databases.

Schema migrations, including support for dropping and renaming columns.

pwiz, a tool that generates Peewee model definitions from existing database files.

Blog posts

Other cool projects

There are a ton of interesting SQLite projects out there. Here are some of my favorites:

APSW, the "true" Python SQLite driver

BerkeleyDB's SQL API, which gives you SQLite on top of BDB

SQLCipher Python driver for encrypted databases

SQLightning, a SQLite backed by LMDB

UnQL, a wacky project from the SQLite folks (not to be confused with UnQLite)

JSON extension module for SQLite. This is brand new code, but exciting to see that SQLite might be adding JSON support.

libsqlfs, a POSIX filesystem built on top of a SQLite database (FUSE filesystem or shared library)

u1db, a database API for synchronized collections of JSON documents

Thanks for reading

Thanks for taking the time to read this post. I hope you've learned about some projects you might not have heard about. If you're a staunch Postgres or MySQL user, give SQLite a try! It's a fantastic library.

Just a couple additional notes worth highlighting:

BerkeleyDB can be compiled with a SQLite frontend. The benefit is that BerkeleyDB uses page-level locking for writers, as opposed to SQLite's strategy of locking the entire database. This is great for web apps! Instructions here on compiling and building a compatible pysqlite

Although SQLite cannot drop or rename columns on existing tables, peewee's migrations extension, and the sqlite-web tool work around this to allow you to provide these functionalities.

Commenting has been closed, but please feel free to contact me