SQLAlchemy Generator

Generate SQLAlchemy Table objects from Flycatcher schemas.

create_sqlalchemy_table(schema_cls, table_name=None, metadata=None)

Generate a SQLAlchemy Table from a Schema class.

Parameters:

Name	Type	Description	Default
schema_cls	type[Schema]	A subclass of Schema used to define the table schema.	required
table_name	str	The name of the SQL table to create. If not provided, defaults to the lowercase schema class name with trailing 's' added. Note: uses simple pluralization (e.g., 'person' -> 'persons').	None
metadata	MetaData	An existing MetaData instance. If not provided, a new MetaData object is created.	None

Returns:

Type	Description
Table	An instance of SQLAlchemy Table corresponding to the schema.

Source code in src/flycatcher/generators/sqlalchemy.py

def create_sqlalchemy_table(
    schema_cls: "type[Schema]",
    table_name: str | None = None,
    metadata: MetaData | None = None,
) -> Table:
    """
    Generate a SQLAlchemy Table from a Schema class.

    Parameters
    ----------
    schema_cls : type[Schema]
        A subclass of Schema used to define the table schema.
    table_name : str, optional
        The name of the SQL table to create. If not provided, defaults to
        the lowercase schema class name with trailing 's' added. Note: uses
        simple pluralization (e.g., 'person' -> 'persons').
    metadata : sqlalchemy.MetaData, optional
        An existing MetaData instance. If not provided, a new MetaData
        object is created.

    Returns
    -------
    sqlalchemy.Table
        An instance of SQLAlchemy Table corresponding to the schema.
    """
    if metadata is None:
        metadata = MetaData()

    if table_name is None:
        table_name = schema_cls.__name__.removesuffix("Schema").lower() + "s"

    fields = schema_cls.fields()
    columns = []

    for field_name, field in fields.items():
        sa_type = field.get_sqlalchemy_type()

        # Build column arguments
        column_kwargs = {}

        # Handle primary key
        if field.primary_key:
            column_kwargs["primary_key"] = True

        # Handle nullable
        column_kwargs["nullable"] = field.nullable

        # Handle autoincrement (explicit control over SQLAlchemy's behavior)
        if field.autoincrement is not None:
            column_kwargs["autoincrement"] = field.autoincrement

        # Handle unique
        if field.unique:
            column_kwargs["unique"] = True

        # Handle index
        if field.index:
            column_kwargs["index"] = True

        # Handle default
        if field.default is not _MISSING:
            column_kwargs["default"] = field.default

        # Create column
        # sa_type can be either a class (callable) or instance (when max_length is set)
        # Column accepts dynamic kwargs that mypy can't verify statically
        if callable(sa_type):
            col = Column(field_name, sa_type(), **column_kwargs)  # type: ignore[arg-type]
        else:
            col = Column(field_name, sa_type, **column_kwargs)  # type: ignore[arg-type]
        columns.append(col)

    return Table(table_name, metadata, *columns)

Usage

The create_sqlalchemy_table function is typically called via the Schema.to_sqlalchemy() method:

from flycatcher import Schema, Field
from sqlalchemy import create_engine, MetaData

class UserSchema(Schema):
    id: int = Field(primary_key=True)
    name: str = Field(min_length=1, max_length=100)

# Generate SQLAlchemy table
metadata = MetaData()
users_table = UserSchema.to_sqlalchemy(table_name="users", metadata=metadata)

# Use with SQLAlchemy
engine = create_engine("sqlite:///example.db")
metadata.create_all(engine)

Table Naming

If table_name is not provided, the table name is automatically generated from the schema class name:

• Removes "Schema" suffix if present
• Converts to lowercase
• Adds "s" for pluralization (simple: UserSchema → users)

# Automatic naming
users_table = UserSchema.to_sqlalchemy()  # Table name: "users"

# Custom naming
users_table = UserSchema.to_sqlalchemy(table_name="app_users")

Metadata Management

You can use a shared MetaData instance to manage multiple tables:

from sqlalchemy import MetaData

metadata = MetaData()

users_table = UserSchema.to_sqlalchemy(metadata=metadata)
posts_table = PostSchema.to_sqlalchemy(metadata=metadata)

# Create all tables at once
metadata.create_all(engine)