Basic Use¶

SQLAlchemy object-relational configuration involves the combination of Table, mapper(), and class objects to define a mapped class. declarative allows all three to be expressed at once within the class declaration. As much as possible, regular SQLAlchemy schema and ORM constructs are used directly, so that configuration between “classical” ORM usage and declarative remain highly similar.

As a simple example:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class SomeClass(Base):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)
    name =  Column(String(50))

Above, the declarative_base() callable returns a new base class from which all mapped classes should inherit. When the class definition is completed, a new Table and mapper() will have been generated.

The resulting table and mapper are accessible via __table__ and __mapper__ attributes on the SomeClass class:

# access the mapped Table
SomeClass.__table__

# access the Mapper
SomeClass.__mapper__

Defining Attributes¶

In the previous example, the Column objects are automatically named with the name of the attribute to which they are assigned.

To name columns explicitly with a name distinct from their mapped attribute, just give the column a name. Below, column “some_table_id” is mapped to the “id” attribute of SomeClass, but in SQL will be represented as “some_table_id”:

class SomeClass(Base):
    __tablename__ = 'some_table'
    id = Column("some_table_id", Integer, primary_key=True)

Attributes may be added to the class after its construction, and they will be added to the underlying Table and mapper() definitions as appropriate:

SomeClass.data = Column('data', Unicode)
SomeClass.related = relationship(RelatedInfo)

Classes which are constructed using declarative can interact freely with classes that are mapped explicitly with mapper().

It is recommended, though not required, that all tables share the same underlying MetaData object, so that string-configured ForeignKey references can be resolved without issue.

Accessing the MetaData¶

The declarative_base() base class contains a MetaData object where newly defined Table objects are collected. This object is intended to be accessed directly for MetaData-specific operations. Such as, to issue CREATE statements for all tables:

engine = create_engine('sqlite://')
Base.metadata.create_all(engine)

declarative_base() can also receive a pre-existing MetaData object, which allows a declarative setup to be associated with an already existing traditional collection of Table objects:

mymetadata = MetaData()
Base = declarative_base(metadata=mymetadata)

Class Constructor¶

As a convenience feature, the declarative_base() sets a default constructor on classes which takes keyword arguments, and assigns them to the named attributes:

e = Engineer(primary_language='python')

Mapper Configuration¶

Declarative makes use of the mapper() function internally when it creates the mapping to the declared table. The options for mapper() are passed directly through via the __mapper_args__ class attribute. As always, arguments which reference locally mapped columns can reference them directly from within the class declaration:

from datetime import datetime

class Widget(Base):
    __tablename__ = 'widgets'

    id = Column(Integer, primary_key=True)
    timestamp = Column(DateTime, nullable=False)

    __mapper_args__ = {
                    'version_id_col': timestamp,
                    'version_id_generator': lambda v:datetime.now()
                }

Defining SQL Expressions¶

See SQL Expressions as Mapped Attributes for examples on declaratively mapping attributes to SQL expressions.

SQLAlchemy 1.1 Documentation