Gavin Wiggins

Dataclass for SQLite Mapping

Published on May 17, 2026
Tagged with python, sqlite

Python's dataclass decorator allows SQLite data to be handled as native Python objects without third-party dependencies like Pydantic and Django. The example below uses an Item dataclass to represent data stored in a SQLite table that resides in an items.db file. Notice the to_row and from_row methods are used to map the SQLite row data to the Item object.

import sqlite3
from dataclasses import dataclass


@dataclass
class Item:
    name: str
    quantity: int
    id: int | None = None

    def to_row(self) -> tuple:
        """Create tuple for inserting into SQLite database."""
        return (self.name, self.quantity)

    @classmethod
    def from_row(cls, row: tuple) -> Item:
        """Create an Item object from a SQLite row."""
        return cls(name=row[1], quantity=row[2], id=row[0])


def main():
    conn = sqlite3.connect("items.db")
    cursor = conn.cursor()

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        quantity INTEGER NOT NULL
    )
    """)

    sample_items = [
        Item("Apples", 10),
        Item("Bananas", 5),
        Item("Oranges", 8),
    ]

    cursor.executemany(
        "INSERT INTO items (name, quantity) VALUES (?, ?)",
        [item.to_row() for item in sample_items],
    )

    conn.commit()

    cursor.execute("SELECT * FROM items")
    items = [Item.from_row(row) for row in cursor.fetchall()]

    print("All items in database:")
    for item in items:
        print(f"{item}")

    conn.close()


if __name__ == "__main__":
    main()

The output printed to the terminal after running the example with uv is shown below.

$ uv run main.py

All items in database:
Item(name='Apples', quantity=10, id=1)
Item(name='Bananas', quantity=5, id=2)
Item(name='Oranges', quantity=8, id=3)