Overview of Lightweight Python Databases and Their Usage

Python is a high‑level language widely used for various databases, including relational, NoSQL, embedded, and object‑oriented types. This article introduces several lightweight Python databases—PickleDB, TinyDB, ZODB, Durus, Buzhug, Gadfly, and PyTables—detailing their main features, typical use cases, cautions, and basic usage examples.

PickleDB

PickleDB is a lightweight key‑value store written in Python that uses JSON files for persistence, offering a simple dictionary‑like API suitable for small projects.

Main Features

Very lightweight, not suited for large datasets or high concurrency.

Simple dictionary‑style API.

Data stored as JSON, easy to read and modify.

Persistent storage on disk.

No external dependencies.

Use Cases

Configuration storage.

Small‑scale projects.

Learning and teaching basic database operations.

Temporary data storage during development.

Precautions

Performance and scalability limitations.

Lacks encryption and access control.

<code>import pickledb

# Create or open a database
db = pickledb.load('example.db', auto_dump=True)

# Insert data
db.set('key1', 'value1')

# Retrieve data
value = db.get('key1')
print(value)  # output: value1

# Check if key exists
exists = db.exists('key1')
print(exists)  # output: True

# Delete data
db.rem('key1')

# Get all keys
keys = db.getall()
print(keys)

# Force dump to disk
db.dump()
</code>

TinyDB

TinyDB is a pure‑Python, document‑oriented NoSQL database that stores data in JSON files, providing a simple API and a powerful query language.

Main Features

Lightweight and embedded; no external server required.

Document‑oriented storage with nested structures.

Simple Python‑like API for CRUD operations.

Rich query language supporting complex conditions and regex.

Extensible via plugins.

Basic transaction support.

Use Cases

Small projects needing quick data persistence.

Embedded applications.

Prototyping and early‑stage development.

Configuration storage.

Precautions

Performance degrades with large datasets.

File‑based storage can become slow as file size grows.

<code>from tinydb import TinyDB, Query

# Create or open a database
db = TinyDB('db.json')

# Insert data
db.insert({'name': 'John', 'age': 22})
db.insert({'name': 'Jane', 'age': 25})

# Query data
User = Query()
result = db.search(User.name == 'John')
print(result)  # [{'name': 'John', 'age': 22}]

# Update data
db.update({'age': 23}, User.name == 'John')

# Delete data
db.remove(User.name == 'Jane')

# Get all data
all_data = db.all()
print(all_data)

db.close()
</code>

ZODB

ZODB (Zope Object Database) is an object‑oriented database for Python that stores Python objects directly, providing transparent persistence and ACID transaction support.

Main Features

Object‑oriented storage without schema.

Transparent persistence.

ACID transaction support.

Versioning and history.

Extensible storage backends.

No fixed schema.

Use Cases

Persisting complex object graphs.

Python‑centric applications.

Applications requiring transaction safety.

Precautions

Not suited for very large datasets or high‑performance needs.

Learning curve for developers accustomed to relational databases.

Smaller community and ecosystem.

<code>import transaction
from ZODB import FileStorage, DB
import persistent

# Define a persistent object class
class Person(persistent.Persistent):
    def __init__(self, name, age):
        self.name = name
        self.age = age

# Create a file storage and database
storage = FileStorage.FileStorage('mydata.fs')
db = DB(storage)

# Open a connection
connection = db.open()
root = connection.root()

# Add an object to the database
root['person'] = Person('John Doe', 30)

# Commit the transaction
transaction.commit()

# Retrieve the object
person = root['person']
print(person.name, person.age)

# Close connection and storage
connection.close()
db.close()
storage.close()
</code>

Durus

Durus is a lightweight, pure‑Python object persistence system offering simple transaction support and no external dependencies.

Main Features

Object‑oriented storage.

Persistent to disk.

Basic transaction support.

Simple API.

Very lightweight.

Use Cases

Small projects needing simple persistence.

Python applications that store objects.

Rapid prototyping.

Precautions

Performance and scale limits for large data.

Limited query and indexing features.

Small community support.

<code>from durus.persistent import Persistent
from durus.connection import Connection
from durus.storage import FileStorage

# Define a persistent object class
class Person(Persistent):
    def __init__(self, name, age):
        self.name = name
        self.age = age

# Create a file storage and connection
storage = FileStorage('mydata.durus')
connection = Connection(storage)

# Get the root object
root = connection.get_root()

# Add an object to the database
root['person'] = Person('John Doe', 30)

# Commit the transaction
connection.commit()

# Retrieve the object
person = root['person']
print(person.name, person.age)

# Close connection and storage
connection.close()
storage.close()
</code>

Buzhug

Buzhug is a pure‑Python lightweight database that provides a simple SQL‑like query language, ideal for small projects and teaching.

Main Features

Pure Python implementation, no external dependencies.

SQL‑like query syntax.

Lightweight and easy to use.

No strict schema.

Use Cases

Small projects with simple data needs.

Educational purposes for learning basic database concepts.

Rapid prototyping.

Precautions

Performance limited to small datasets.

Small community and ecosystem.

Lacks advanced transaction and indexing features.

<code>from buzhug import Base

# Create or open a database
db = Base('people').create(('name', str), ('age', int))

# Insert data
db.insert(name='John Doe', age=30)
db.insert(name='Jane Doe', age=25)

# Query data
for person in db.select():
    print(person.name, person.age)

# Update data
db.update(db.name == 'John Doe', age=31)

# Delete data
db.delete(db.name == 'Jane Doe')

db.close()
</code>

Gadfly

Gadfly is a pure‑Python lightweight relational database that implements a subset of SQL, suitable for teaching, learning, and small‑scale projects.

Main Features

Pure Python implementation.

Standard SQL support.

Lightweight and embeddable.

No server required.

Use Cases

Educational environments for SQL basics.

Small projects needing simple relational storage.

Rapid prototyping.

Precautions

Performance and scalability limited.

Small community and limited maintenance.

Potential compatibility adjustments for modern Python versions.

<code>from gadfly import gadfly

# Create or connect to a database
connection = gadfly('mydb', 'mydb_directory')

# Get a cursor
cursor = connection.cursor()

# Create a table
cursor.execute('CREATE TABLE people (name VARCHAR, age INTEGER)')

# Insert data
cursor.execute("INSERT INTO people (name, age) VALUES ('John Doe', 30)")
cursor.execute("INSERT INTO people (name, age) VALUES ('Jane Doe', 25)")

# Query data
cursor.execute('SELECT * FROM people')
for row in cursor.fetchall():
    print(row)

# Update data
cursor.execute("UPDATE people SET age = 31 WHERE name = 'John Doe'")

# Delete data
cursor.execute("DELETE FROM people WHERE name = 'Jane Doe'")

# Commit and close
connection.commit()
connection.close()
</code>

PyTables

PyTables is an open‑source library built on HDF5 for managing large scientific datasets, offering efficient storage, compression, hierarchical organization, and integration with NumPy.

Main Features

Based on HDF5 file format.

Supports multiple compression algorithms.

Hierarchical groups and tables.

Handles datasets larger than memory.

Supports many data types.

Powerful query capabilities.

Seamless NumPy integration.

Installation

Install via pip: pip install tables

Basic Usage

<code>import numpy as np
import tables

# Create an HDF5 file
with tables.open_file('example.h5', mode='w') as file:
    # Create a group
    group = file.create_group('/', 'data_group', 'Data Group')

    # Create a structured NumPy array
    data = np.array([(1, b'Hello'), (2, b'World')], dtype=[('number', 'i4'), ('word', 'S10')])
    table = file.create_table(group, 'example_table', description=data.dtype, title='Example Table')

    # Insert data
    row = table.row
    for item in data:
        row['number'] = item['number']
        row['word'] = item['word']
        row.append()
    table.flush()

    # Query data
    for row in table.where('number > 1'):
        print(row['number'], row['word'].decode('utf-8'))

    # Read all data into a NumPy array
    np_data = table.read()
    print(np_data)
</code>

Use Cases

Scientific computing and data analysis.

Managing very large datasets that exceed RAM.

Data archiving and sharing using the portable HDF5 format.

Precautions

Performance may require tuning of data structures and compression.

Ensure compatible HDF5 library versions across environments.

PyTables depends on the native HDF5 library, which must be correctly installed.