Basic Use

See also

This section describes specifics about how the Declarative systeminteracts with the SQLAlchemy ORM. For a general introductionto class mapping, see Object Relational Tutorial as well asMapper Configuration.

SQLAlchemy object-relational configuration involves thecombination of Table, mapper(), and classobjects to define a mapped class.declarative allows all three to beexpressed at once within the class declaration. As much aspossible, regular SQLAlchemy schema and ORM constructs areused directly, so that configuration between “classical” ORMusage and declarative remain highly similar.

As a simple example:

from sqlalchemy import Column, Integer, String
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 fromwhich all mapped classes should inherit. When the class definition iscompleted, a new Table and mapper() will have been generated.

The resulting table and mapper are accessible viatable and mapper attributes on theSomeClass class:

# access the mapped Table
SomeClass.__table__
 
# access the Mapper
SomeClass.__mapper__

Defining Attributes

In the previous example, the Column objects areautomatically named with the name of the attribute to which they areassigned.

To name columns explicitly with a name distinct from their mapped attribute,just give the column a name. Below, column “sometable_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 beadded to the underlying Table andmapper() definitions as appropriate:

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

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

Using MyPy with SQLAlchemy models

If you are using PEP 484 static type checkers for Python, a MyPyplugin is included withtype stubs for SQLAlchemy. The pluginis tailored towards SQLAlchemy declarative models.

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

Accessing the MetaData

The declarative_base() base class contains aMetaData object where newly definedTable objects are collected. This object isintended to be accessed directly forMetaData-specific operations. Such as, to issueCREATE statements for all tables:

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

declarative_base() can also receive a pre-existingMetaData object, which allows adeclarative setup to be associated with an alreadyexisting traditional collection of Tableobjects:

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

Class Constructor

As a convenience feature, the declarative_base() sets a defaultconstructor on classes which takes keyword arguments, and assigns themto the named attributes:

e = Engineer(primary_language='python')

Mapper Configuration

Declarative makes use of the mapper() function internallywhen it creates the mapping to the declared table. The optionsfor mapper() are passed directly through via themapper_args class attribute. As always, arguments which referencelocally mapped columns can reference them directly from within theclass 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 declarativelymapping attributes to SQL expressions.