Skip to content

Instantly share code, notes, and snippets.

@simonw
Created February 2, 2025 19:03
Show Gist options
  • Save simonw/adf64108d65cd5c10ac9fce953ab437e to your computer and use it in GitHub Desktop.
Save simonw/adf64108d65cd5c10ac9fce953ab437e to your computer and use it in GitHub Desktop.

Py-Limbo

Py-Limbo is a lightweight, in-process, OLTP (Online Transaction Processing) database management system built as a Python extension module on top of Rust. It is designed to be compatible with SQLite in both usage and API, while offering an opportunity to experiment with Rust-backed database functionality.

Note: Py-Limbo is a work-in-progress (Alpha stage) project. Some features (e.g. transactions, executemany, fetchmany) are not yet supported.


Table of Contents


Features

  • SQLite-Compatible Interface:
    Provides a familiar DB-API for Python users. Import the module and use connect(), cursor(), execute(), fetchone(), and fetchall() just like you would with the built-in SQLite library.

  • Rust Backed:
    Leverages the safety and performance of Rust via the pyo3 bindings to deliver a robust, low-level database engine.

  • Custom Exception Hierarchy:
    Implements a rich exception hierarchy (e.g. DatabaseError, OperationalError, ProgrammingError, etc.) to capture and raise errors in a way that mirrors Python’s database API standards.

  • Cross-Platform I/O:
    Supports both in-memory and file-based databases.


Installation

Prerequisites

  • Rust: Make sure you have the latest stable Rust toolchain installed.

  • Python: Python 3.9 or higher.

  • Maturin: Used to build Python extensions written in Rust.

    Install maturin via pip:

    pip install maturin

Using Maturin

For local development, you can build and install Py-Limbo directly into your Python environment:

# Clone the repository
git clone https://github.com/penberg/limbo.git
cd limbo/py-limbo

# Build and install in editable/development mode
maturin develop

This command builds the Rust extension module and installs it in your active Python environment.

Using Makefile

The repository provides a convenient Makefile with common tasks. For example, to install the Python requirements (both production and development), run:

make install

Other available targets:

  • make test: Run tests using pytest.
  • make lint: Run linters (via ruff).
  • make check-requirements: Validate that the requirements.txt files are in sync with pyproject.toml.
  • make compile-requirements: Compile the requirements.txt files using pip-tools.

Usage

Py-Limbo provides an API that closely resembles the standard Python DB-API. Below are several usage examples.

Basic Example

import limbo

# Connect to a file-based database.
conn = limbo.connect("my_database.db")

# Create a cursor object.
cur = conn.cursor()

# Create a table (DDL statement).
cur.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, username TEXT)")

# Insert some users (DML statements).
cur.execute("INSERT INTO users (id, username) VALUES (1, 'alice')")
cur.execute("INSERT INTO users (id, username) VALUES (2, 'bob')")

# Query the database.
cur.execute("SELECT * FROM users")
rows = cur.fetchall()
print("All users:", rows)

# Fetch one row at a time.
cur.execute("SELECT * FROM users")
user = cur.fetchone()
while user is not None:
    print("User:", user)
    user = cur.fetchone()

Using In-Memory Databases

In-memory databases are useful for testing or transient data storage:

import limbo

# Connect to an in-memory database.
conn = limbo.connect(":memory:")

cur = conn.cursor()
cur.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT)")
cur.execute("INSERT INTO users (id, username) VALUES (1, 'alice')")

# Fetch and display a record.
user = cur.fetchone()
print("In-memory user:", user)

Error Handling

Py-Limbo provides custom exceptions that inherit from a base error, similar to other database modules. Catch errors as needed:

import limbo

try:
    # Attempt to connect to a non-existent database file (or a file with issues)
    conn = limbo.connect("non_existent.db")
    cur = conn.cursor()
    cur.execute("SELECT * FROM nonexistent_table")
except limbo.ProgrammingError as pe:
    print("A programming error occurred:", pe)
except limbo.OperationalError as oe:
    print("An operational error occurred:", oe)
except limbo.Error as e:
    # Catch all database-related errors
    print("A database error occurred:", e)

Note: Some functions like executemany(), fetchmany(), and transactions (commit() and rollback()) are not supported in this version. Calling these methods will raise a NotSupportedError.


Testing & Development

The project includes a comprehensive test suite to ensure the proper functioning of the database API. To run tests:

make test

The tests cover both the standard SQLite interface (using Python's built-in sqlite3) and the Py-Limbo implementation. For example, in tests/test_database.py the following is verified for both providers:

  • Fetching multiple rows
  • Fetching a single row
  • Using an in-memory database

Linters and code formatters are available via Makefile targets:

make lint

If you need to update the Python requirements, compile the requirements with:

make compile-requirements

Project Structure

Below is an overview of the key files and directories in the repository:

  • Cargo.toml – Rust package configuration file.
  • Makefile – Common tasks for installation, testing, linting, and requirement checking.
  • build.rs – Build script for configuring PyO3.
  • pyproject.toml – Python project configuration for building with maturin.
  • example.py – A simple example demonstrating how to use the Py-Limbo module.
  • limbo/ – Contains the Python wrapper and type hint files for the database API.
  • src/ – Contains Rust source code for errors and database functionality.
  • tests/ – Test suite for verifying database functionality.

Contributing

Contributions to Py-Limbo are welcome! Please follow these guidelines:

  1. Fork the repository and create a new branch for your feature or bug fix.
  2. Ensure that your code adheres to the existing style and passes all tests.
  3. Update documentation as necessary.
  4. Submit a pull request for review.

For more details, see the CONTRIBUTING guidelines in the repository.


License

Py-Limbo is distributed under the MIT License.


Additional Resources

  • Repository: https://github.com/penberg/limbo
  • Issue Tracker: Please submit issues and feature requests via GitHub.
  • Discussion: Engage with the community and contribute feedback.

Enjoy using Py-Limbo for your SQLite-compatible database needs in Python!
Happy coding!

@simonw
Copy link
Author

simonw commented Feb 2, 2025

Here's how I generated these, using LLM and files-to-prompt:

git clone https://github.com/tursodatabase/limbo
cd limbo/bindings/python
files-to-prompt . -c | llm -m o3-mini \
  -o reasoning_effort high \
  --system 'Formatting re-enabled. Write a detailed README with extensive usage examples.'

See also: https://simonwillison.net/2025/Feb/2/openai-reasoning-models-advice-on-prompting/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment