Skip to content

SQL persistence

The SQLDataclass mixin provides SQL ORM (Object-Relational Mapping) functionality to a dataclass. This uses the SQLAlchemy library under the hood.

After applying the register decorator to a custom dataclass, it will register a sqlalchemy.Table, after which you can use the class to perform database CRUD operations in the typical SQLAlchemy way. You can also define constraints and relationships on the tables after they are registered.

Since SQLAlchemy is backend-agnostic, you can use SQLDataclass with many popular SQL backends such as SQLite, MySQL, and PostgreSQL.

See the SQLAlchemy documentation on how to set up engines and sessions.

Usage Example

Define a SQLDataclass corresponding to a SQL table.

from dataclasses import dataclass, field

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from fancy_dataclass.sql import DEFAULT_REGISTRY, SQLDataclass, register


@register()  # register dataclass as a table
class Employee(SQLDataclass):
    first_name: str
    last_name: str
    # set _id as primary key, which will auto-increment if omitted
    _id: int = field(
        default=None,
        metadata={'column': {'primary_key':True}}
    )

# create sqlalchemy engine
engine = create_engine('sqlite:///:memory:')
# create all registered tables
DEFAULT_REGISTRY.metadata.create_all(engine)

Create a SQLAlchemy session and populate the table.

>>> Session = sessionmaker(bind=engine)
>>> session = Session()
>>> person1 = Employee('John', 'Smith')
>>> person2 = Employee('Jane', 'Doe')
>>> session.add_all([person1, person2])
>>> session.commit()
>>> print(session.query(Employee).all())
[Employee(first_name='John', last_name='Smith', _id=1), Employee(first_name='Jane', last_name='Doe', _id=2)]

Details

🚧 Under construction 🚧