SQliteDict: Reducing SQLite Complexity with Dictionary-Style Operations

Khuyen Tran

Working with SQLite databases in Python can be tedious, especially when dealing with complex objects or frequent schema changes. The traditional approach involves writing verbose SQL statements, managing schema definitions, and handling type conversions. However, there’s a simpler way to interact with SQLite databases using SqliteDict.

The Problem with Traditional SQLite Interactions

Let’s consider an example where we need to store and retrieve a list of products with their respective details. Using the traditional approach, we would write SQL statements to create a table, insert data, and retrieve it.

import sqlite3

products_to_update = [
    ("P1", "Laptop", 999.99, 50),
    ("P2", "Mouse", 29.99, 100),
    ("P3", "Keyboard", 59.99, 75),
]

with sqlite3.connect("example.db") as conn:
    cursor = conn.cursor()
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS products 
                     (id TEXT PRIMARY KEY, name TEXT, price REAL, stock INTEGER)"""
    )
    cursor.executemany(
        """INSERT OR REPLACE INTO products (id, name, price, stock) 
                         VALUES (?, ?, ?, ?)""",
        products_to_update,
    )

with sqlite3.connect("example.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, price, stock FROM products")
    for row in cursor.fetchall():
        product_data = {"name": row[1], "price": row[2], "stock": row[3]}
        print(f"{row[0]}={product_data}")

This approach requires us to write explicit SQL statements, manage cursors, and handle type conversions, which can lead to complex and error-prone code.

Simplifying SQLite Interactions with SqliteDict

SqliteDict provides a Pythonic interface to interact with SQLite databases, eliminating the need for explicit SQL statements, cursor management, and serialization. Let’s rewrite the previous example using SqliteDict:

from sqlitedict import SqliteDict

products_to_update = {
    "P1": {"name": "Laptop", "price": 999.99, "stock": 50},
    "P2": {"name": "Mouse", "price": 29.99, "stock": 100},
    "P3": {"name": "Keyboard", "price": 59.99, "stock": 75},
}

with SqliteDict("example2.db") as db:
    # Update multiple records in a batch
    for product_id, product_data in products_to_update.items():
        db[product_id] = product_data

    # Single commit for all updates
    db.commit()

with SqliteDict("example2.db") as db:
    for key, item in db.items():
        print(f"{key}={item}")

As you can see, SqliteDict simplifies the interaction with SQLite databases by providing a dictionary-like interface. It handles schema creation, data type conversion, and connection management internally, making it an ideal choice for storing and retrieving complex Python objects.

Link to SqliteDict.