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 (docs)
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 (docs)
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
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
- Python LSM database announcement and overview
- Scout announcement and example code
- Guide to extending SQLite with Python, which includes an example Virtual Table implementation exposing a Redis Server as a SQLite table.
- Querying tree structures and other hierarchical data using Python and the Transitive Closure extension
- Building an encrypted diary using Peewee and SQLCipher
- SQLite: Small. Fast. Reliable. Choose any three
- Using SQLite's full-text search with Python, including how to use user-defined functions to sort search results by relevance.
- SQLCipher and Python overview
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.
Comments (0)
Commenting has been closed.