Types of Mappings

Modern SQLAlchemy features two distinct styles of mapper configuration.The “Classical” style is SQLAlchemy’s original mapping API, whereas“Declarative” is the richer and more succinct system that builds on topof “Classical”. Both styles may be used interchangeably, as the endresult of each is exactly the same - a user-defined class mapped by themapper() function onto a selectable unit, typically a Table.

Declarative Mapping

The Declarative Mapping is the typical way thatmappings are constructed in modern SQLAlchemy.Making use of the Declarativesystem, the components of the user-defined class as well as theTable metadata to which the class is mapped are definedat once:

  1. from sqlalchemy.ext.declarative import declarative_base
  2. from sqlalchemy import Column, Integer, String, ForeignKey
  3.  
  4. Base = declarative_base()
  5.  
  6. class User(Base):
  7.     __tablename__ = 'user'
  8.  
  9.     id = Column(Integer, primary_key=True)
  10.     name = Column(String)
  11.     fullname = Column(String)
  12.     nickname = Column(String)

Above, a basic single-table mapping with four columns. Additionalattributes, such as relationships to other mapped classes, are alsodeclared inline within the class definition:

  1. class User(Base):
  2.     __tablename__ = 'user'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.     name = Column(String)
  6.     fullname = Column(String)
  7.     nickname = Column(String)
  8.  
  9.     addresses = relationship("Address", backref="user", order_by="Address.id")
  10.  
  11. class Address(Base):
  12.     __tablename__ = 'address'
  13.  
  14.     id = Column(Integer, primary_key=True)
  15.     user_id = Column(ForeignKey('user.id'))
  16.     email_address = Column(String)

The declarative mapping system is introduced in theObject Relational Tutorial. For additional details on how this systemworks, see Declarative.

Classical Mappings

A Classical Mapping refers to the configuration of a mapped class using themapper() function, without using the Declarative system. This isSQLAlchemy’s original class mapping API, and is still the base mappingsystem provided by the ORM.

In “classical” form, the table metadata is created separately with theTable construct, then associated with the User class viathe mapper() function:

  1. from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
  2. from sqlalchemy.orm import mapper
  3.  
  4. metadata = MetaData()
  5.  
  6. user = Table('user', metadata,
  7.             Column('id', Integer, primary_key=True),
  8.             Column('name', String(50)),
  9.             Column('fullname', String(50)),
  10.             Column('nickname', String(12))
  11.         )
  12.  
  13. class User(object):
  14.     def __init__(self, name, fullname, nickname):
  15.         self.name = name
  16.         self.fullname = fullname
  17.         self.nickname = nickname
  18.  
  19. mapper(User, user)

Information about mapped attributes, such as relationships to other classes, are providedvia the properties dictionary. The example below illustrates a second Tableobject, mapped to a class called Address, then linked to User via relationship():

  1. address = Table('address', metadata,
  2.             Column('id', Integer, primary_key=True),
  3.             Column('user_id', Integer, ForeignKey('user.id')),
  4.             Column('email_address', String(50))
  5.             )
  6.  
  7. mapper(User, user, properties={
  8.     'addresses' : relationship(Address, backref='user', order_by=address.c.id)
  9. })
  10.  
  11. mapper(Address, address)

When using classical mappings, classes must be provided directly without the benefitof the “string lookup” system provided by Declarative. SQL expressions are typicallyspecified in terms of the Table objects, i.e. address.c.id abovefor the Address relationship, and not Address.id, as Address may notyet be linked to table metadata, nor can we specify a string here.

Some examples in the documentation still use the classical approach, but note thatthe classical as well as Declarative approaches are fully interchangeable. Bothsystems ultimately create the same configuration, consisting of a Table,user-defined class, linked together with a mapper(). When we talk about“the behavior of mapper()”, this includes when using the Declarative systemas well - it’s still used, just behind the scenes.

Runtime Introspection of Mappings, Objects

The Mapper object is available from any mapped class, regardlessof method, using the Runtime Inspection API system. Using theinspect() function, one can acquire the Mapper from amapped class:

  1. >>> from sqlalchemy import inspect
  2. >>> insp = inspect(User)

Detailed information is available including Mapper.columns:

  1. >>> insp.columns
  2. <sqlalchemy.util._collections.OrderedProperties object at 0x102f407f8>

This is a namespace that can be viewed in a list format orvia individual names:

  1. >>> list(insp.columns)
  2. [Column('id', Integer(), table=<user>, primary_key=True, nullable=False), Column('name', String(length=50), table=<user>), Column('fullname', String(length=50), table=<user>), Column('nickname', String(length=50), table=<user>)]
  3. >>> insp.columns.name
  4. Column('name', String(length=50), table=<user>)

Other namespaces include Mapper.all_orm_descriptors, which includes all mappedattributes as well as hybrids, association proxies:

  1. >>> insp.all_orm_descriptors
  2. <sqlalchemy.util._collections.ImmutableProperties object at 0x1040e2c68>
  3. >>> insp.all_orm_descriptors.keys()
  4. ['fullname', 'nickname', 'name', 'id']

As well as Mapper.column_attrs:

  1. >>> list(insp.column_attrs)
  2. [<ColumnProperty at 0x10403fde0; id>, <ColumnProperty at 0x10403fce8; name>, <ColumnProperty at 0x1040e9050; fullname>, <ColumnProperty at 0x1040e9148; nickname>]
  3. >>> insp.column_attrs.name
  4. <ColumnProperty at 0x10403fce8; name>
  5. >>> insp.column_attrs.name.expression
  6. Column('name', String(length=50), table=<user>)

See also

Runtime Inspection API

Mapper

InstanceState