1. Michael Bayer
  2. sqlalchemy

Wiki

Clone wiki

sqlalchemy / UsageRecipes / NamingConventions

AutomatedNamingConventions

Note: The strategy of using events to name constraints and indexes is included as of SQLAlchemy 0.9.2. See Configuring Constraint Naming Conventions.


These are some techniques to automate the naming conventions used for certain kinds of columns as well as constraint names. A large scale application can use these techniques so that SQLAlchemy-declared database schemas conform automatically to an organization's standards and practices.

As of SQLAlchemy 0.7, functions and events can be used so that naming schemes for Column objects can be derived from the parent table. When a scenario like this occurs:

t = Table('foo', metadata,
       Column('bar', Integer),
)

the Column object is created first, then an event called "before_parent_attach" occurs before the Column is associated with the Table. There are a few attributes we can set at this stage, including the name and the "docstring". The type can also be manipulated here.

Below we define two macros pk_col and reference_col which automate the creation of a standard primary key column and a standard foreign key reference column:

from sqlalchemy import Column, Integer, event, ForeignKey
def pk_col(**kw):
    """Produce a primary key column for a table.

    e.g.::

        pk_col()

    is equivalent to::

        Column("<tablename>_id", Integer, 
                        doc = "Primary key column for <tablename>",
                        primary_key=True
                        )

    """
    kw['primary_key'] = True
    c = Column(Integer, **kw)

    @event.listens_for(c, "before_parent_attach")
    def on_table_attach(column, table):
        column.name = column.key = '%s_id' % table.name
        column.doc = "Primary key column for %r" % table.name

    c._creation_order = 0 # forces it to the top when using declarative
    return c

def reference_col(tablename, *args, **kw):
    """Produce a column that references the primary key col of another table,
       assuming the naming convention of pk_col().

    e.g.::

        reference_col("remote_table")

    is equivalent to::

        Column("remote_table_id", Integer, 
                              ForeignKey("remote_table.remote_table_id"),
                              doc = "Foreign key referencing <tablename>.<tablename>_id"
                        )
    """

    kw['doc'] = "Foreign key referencing %s.%s_id" % (tablename, tablename)
    use_alter = kw.pop('use_alter', False)
    return Column("%s_id" % tablename,
                        Integer, 
                        ForeignKey('%s.%s_id' % (tablename, tablename),
                                    use_alter=use_alter), 
                        *args, **kw)

Constraint naming conventions can be established using attachment events.
Below we define a naming scheme for primary key, foreign key, unique, and check constraints, globally across the application. The "after_parent_attach" event is used so that the constraints have access to their columns:

from sqlalchemy.schema import (
    PrimaryKeyConstraint, ForeignKeyConstraint, UniqueConstraint, 
    CheckConstraint, Table
)
from sqlalchemy import event

@event.listens_for(PrimaryKeyConstraint, "after_parent_attach")
def _pk_constraint_name(const, table):
    const.name = "pk_%s" % (table.name, )

@event.listens_for(ForeignKeyConstraint, "after_parent_attach")
def _fk_constraint_name(const, table):
    if const.name:
        return
    fk = const.elements[0]
    reftable, refcol = fk.target_fullname.split(".")
    const.name = "fk_%s_%s_%s" % (
                                table.name,
                                fk.parent.name,
                                reftable
                                )

@event.listens_for(UniqueConstraint, "after_parent_attach")
def _unique_constraint_name(const, table):
    const.name = "uq_%s_%s" % (
        table.name,
        list(const.columns)[0].name
    )

@event.listens_for(CheckConstraint, "after_parent_attach")
def _ck_constraint_name(const, table):
    if const.name is not None:
        const.name = "ck_%s_%s" % (
            table.name,
            const.name
        )

A similar approach can be taken for columns that should be applied to all tables, such as a "created" timestamp column:

@event.listens_for(Table, "after_parent_attach")
def _add_created_col(table, metadata):
    table.append_column(Column("created", DateTime, server_default=func.now()))

Combining the above with a declarative mixin approach:

from sqlalchemy import (Column, DateTime, Integer, 
                          Enum, String, func)
from sqlalchemy.ext.declarative import declarative_base, declared_attr

class Base(object):
    @declared_attr
    def id(cls):
        # use a method so that the pk_col() returned
        # here is the one used instead of a copy
        return pk_col()

Base = declarative_base(cls=Base)

# elsewhere, in main model code:

class Order(Base):
    __tablename__ = 'order'
    item_id = reference_col('item')
    order_type = Column(Enum("online", "instore", name="order_type"))

class Item(Base):
    __tablename__ = 'item'
    name = Column(String(100), unique=True)
    description = Column(String(200))

A create_all for the above sample looks like:

CREATE TABLE item (
    item_id INTEGER NOT NULL, 
    name VARCHAR(100), 
    description VARCHAR(200), 
    created DATETIME DEFAULT CURRENT_TIMESTAMP, 
    CONSTRAINT pk_item PRIMARY KEY (item_id), 
    CONSTRAINT uq_item_name UNIQUE (name)
)

CREATE TABLE "order" (
    order_id INTEGER NOT NULL, 
    item_id INTEGER, 
    order_type VARCHAR(7), 
    created DATETIME DEFAULT CURRENT_TIMESTAMP, 
    CONSTRAINT pk_order PRIMARY KEY (order_id), 
    CONSTRAINT fk_order_item_id_item FOREIGN KEY(item_id) REFERENCES item (item_id), 
    CONSTRAINT ck_order_order_type CHECK (order_type IN ('online', 'instore'))
)

Updated