SQLAlchemy 0.5 Documentation

Customizing Column Properties

The default behavior of a mapper is to assemble all the columns in the mapped Table into mapped object attributes. This behavior can be modified in several ways, as well as enhanced by SQL expressions.

To load only a part of the columns referenced by a table as attributes, use the include_properties and exclude_properties arguments:

mapper(User, users_table, include_properties=['user_id', 'user_name'])

mapper(Address, addresses_table, exclude_properties=['street', 'city', 'state', 'zip'])

To change the name of the attribute mapped to a particular column, place the Column object in the properties dictionary with the desired key:

mapper(User, users_table, properties={ 
   'id' : users_table.c.user_id,
   'name' : users_table.c.user_name,
})

To change the names of all attributes using a prefix, use the column_prefix option. This is useful for classes which wish to add their own property accessors:

mapper(User, users_table, column_prefix='_')

The above will place attribute names such as _user_id, _user_name, _password etc. on the mapped User class.

To place multiple columns which are known to be "synonymous" based on foreign key relationship or join condition into the same mapped attribute, put them together using a list, as below where we map to a Join:

# join users and addresses
usersaddresses = sql.join(users_table, addresses_table, \
    users_table.c.user_id == addresses_table.c.user_id)

mapper(User, usersaddresses, 
   properties = {
           'id':[users_table.c.user_id, addresses_table.c.user_id],
      })

Deferred Column Loading

This feature allows particular columns of a table to not be loaded by default, instead being loaded later on when first referenced. It is essentially "column-level lazy loading". This feature is useful when one wants to avoid loading a large text or binary field into memory when it's not needed. Individual columns can be lazy loaded by themselves or placed into groups that lazy-load together.

book_excerpts = Table('books', db, 
    Column('book_id', Integer, primary_key=True),
    Column('title', String(200), nullable=False),
    Column('summary', String(2000)),
    Column('excerpt', String),
    Column('photo', Binary)
  )

class Book(object):
    pass

# define a mapper that will load each of 'excerpt' and 'photo' in 
# separate, individual-row SELECT statements when each attribute
# is first referenced on the individual object instance
mapper(Book, book_excerpts, properties = {
  'excerpt' : deferred(book_excerpts.c.excerpt),
  'photo' : deferred(book_excerpts.c.photo)
})

Deferred columns can be placed into groups so that they load together:

book_excerpts = Table('books', db, 
  Column('book_id', Integer, primary_key=True),
  Column('title', String(200), nullable=False),
  Column('summary', String(2000)),
  Column('excerpt', String),
  Column('photo1', Binary),
  Column('photo2', Binary),
  Column('photo3', Binary)
)

class Book(object):
  pass

# define a mapper with a 'photos' deferred group.  when one photo is referenced,
# all three photos will be loaded in one SELECT statement.  The 'excerpt' will 
# be loaded separately when it is first referenced.
mapper(Book, book_excerpts, properties = {
  'excerpt' : deferred(book_excerpts.c.excerpt),
  'photo1' : deferred(book_excerpts.c.photo1, group='photos'),
  'photo2' : deferred(book_excerpts.c.photo2, group='photos'),
  'photo3' : deferred(book_excerpts.c.photo3, group='photos')
})

You can defer or undefer columns at the Query level using the defer and undefer options:

query = session.query(Book)
query.options(defer('summary')).all()
query.options(undefer('excerpt')).all()

And an entire "deferred group", i.e. which uses the group keyword argument to deferred(), can be undeferred using undefer_group(), sending in the group name:

query = session.query(Book)
query.options(undefer_group('photos')).all()

SQL Expressions as Mapped Attributes

To add a SQL clause composed of local or external columns as a read-only, mapped column attribute, use the column_property() function. Any scalar-returning ClauseElement may be used, as long as it has a name attribute; usually, you'll want to call label() to give it a specific name:

mapper(User, users_table, properties={
    'fullname' : column_property(
        (users_table.c.firstname + " " + users_table.c.lastname).label('fullname')
    )
})

Correlated subqueries may be used as well:

mapper(User, users_table, properties={
    'address_count' : column_property(
            select(
                [func.count(addresses_table.c.address_id)], 
                addresses_table.c.user_id==users_table.c.user_id
            ).label('address_count')
        )
})

Overriding Attribute Behavior with Synonyms

A common request is the ability to create custom class properties that override the behavior of setting/getting an attribute. As of 0.4.2, the synonym() construct provides an easy way to do this in conjunction with a normal Python property constructs. Below, we re-map the email column of our mapped table to a custom attribute setter/getter, mapping the actual column to the property named _email:

class MyAddress(object):
   def _set_email(self, email):
      self._email = email
   def _get_email(self):
      return self._email
   email = property(_get_email, _set_email)

mapper(MyAddress, addresses_table, properties = {
    'email':synonym('_email', map_column=True)
})

The email attribute is now usable in the same way as any other mapped attribute, including filter expressions, get/set operations, etc.:

address = sess.query(MyAddress).filter(MyAddress.email == 'some address').one()

address.email = 'some other address'
sess.flush()

q = sess.query(MyAddress).filter_by(email='some other address')

If the mapped class does not provide a property, the synonym() construct will create a default getter/setter object automatically.

Composite Column Types

Sets of columns can be associated with a single datatype. The ORM treats the group of columns like a single column which accepts and returns objects using the custom datatype you provide. In this example, we'll create a table vertices which stores a pair of x/y coordinates, and a custom datatype Point which is a composite type of an x and y column:

vertices = Table('vertices', metadata, 
    Column('id', Integer, primary_key=True),
    Column('x1', Integer),
    Column('y1', Integer),
    Column('x2', Integer),
    Column('y2', Integer),
    )

The requirements for the custom datatype class are that it have a constructor which accepts positional arguments corresponding to its column format, and also provides a method __composite_values__() which returns the state of the object as a list or tuple, in order of its column-based attributes. It also should supply adequate __eq__() and __ne__() methods which test the equality of two instances:

class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y
    def __composite_values__(self):
        return [self.x, self.y]            
    def __eq__(self, other):
        return other.x == self.x and other.y == self.y
    def __ne__(self, other):
        return not self.__eq__(other)

Setting up the mapping uses the composite() function:

class Vertex(object):
    pass

mapper(Vertex, vertices, properties={
    'start':composite(Point, vertices.c.x1, vertices.c.y1),
    'end':composite(Point, vertices.c.x2, vertices.c.y2)
})

We can now use the Vertex instances as well as querying as though the start and end attributes are regular scalar attributes:

sess = Session()
v = Vertex(Point(3, 4), Point(5, 6))
sess.save(v)

v2 = sess.query(Vertex).filter(Vertex.start == Point(3, 4))

The "equals" comparison operation by default produces an AND of all corresponding columns equated to one another. If you'd like to override this, or define the behavior of other SQL operators for your new type, the composite() function accepts an extension object of type sqlalchemy.orm.PropComparator:

from sqlalchemy.orm import PropComparator
from sqlalchemy import sql

class PointComparator(PropComparator):
    def __gt__(self, other):
        """define the 'greater than' operation"""

        return sql.and_(*[a>b for a, b in
                          zip(self.prop.columns,
                              other.__composite_values__())])

maper(Vertex, vertices, properties={
    'start':composite(Point, vertices.c.x1, vertices.c.y1, comparator=PointComparator),
    'end':composite(Point, vertices.c.x2, vertices.c.y2, comparator=PointComparator)
})

Controlling Ordering

As of version 0.5, the ORM does not generate ordering for any query unless explicitly configured.

The "default" ordering for a collection, which applies to list-based collections, can be configured using the order_by keyword argument on relation():

mapper(Address, addresses_table)

# order address objects by address id
mapper(User, users_table, properties = {
    'addresses' : relation(Address, order_by=addresses_table.c.address_id)
})

Note that when using eager loaders with relations, the tables used by the eager load's join are anonymously aliased. You can only order by these columns if you specify it at the relation() level. To control ordering at the query level based on a related table, you join() to that relation, then order by it:

session.query(User).join('addresses').order_by(Address.street)

Ordering for rows loaded through Query is usually specified using the order_by() generative method. There is also an option to set a default ordering for Queries which are against a single mapped entity and where there was no explicit order_by() stated, which is the order_by keyword argument to mapper():

# order by a column
mapper(User, users_table, order_by=users_table.c.user_id)

# order by multiple items
mapper(User, users_table, order_by=[users_table.c.user_id, users_table.c.user_name.desc()])

Above, a Query issued for the User class will use the value of the mapper's order_by setting if the Query itself has no ordering specified.

Mapping Class Inheritance Hierarchies

SQLAlchemy supports three forms of inheritance: single table inheritance, where several types of classes are stored in one table, concrete table inheritance, where each type of class is stored in its own table, and joined table inheritance, where the parent/child classes are stored in their own tables that are joined together in a select. Whereas support for single and joined table inheritance is strong, concrete table inheritance is a less common scenario with some particular problems so is not quite as flexible.

When mappers are configured in an inheritance relationship, SQLAlchemy has the ability to load elements "polymorphically", meaning that a single query can return objects of multiple types.

For the following sections, assume this class relationship:

class Employee(object):
    def __init__(self, name):
        self.name = name
    def __repr__(self):
        return self.__class__.__name__ + " " + self.name

class Manager(Employee):
    def __init__(self, name, manager_data):
        self.name = name
        self.manager_data = manager_data
    def __repr__(self):
        return self.__class__.__name__ + " " + self.name + " " +  self.manager_data

class Engineer(Employee):
    def __init__(self, name, engineer_info):
        self.name = name
        self.engineer_info = engineer_info
    def __repr__(self):
        return self.__class__.__name__ + " " + self.name + " " +  self.engineer_info

employees = Table('employees', metadata, 
   Column('employee_id', Integer, primary_key=True),
   Column('name', String(50)),
   Column('type', String(30), nullable=False)
)

engineers = Table('engineers', metadata, 
   Column('employee_id', Integer, ForeignKey('employees.employee_id'), primary_key=True),
   Column('engineer_info', String(50)),
)

managers = Table('managers', metadata, 
   Column('employee_id', Integer, ForeignKey('employees.employee_id'), primary_key=True),
   Column('manager_data', String(50)),
)

mapper(Employee, employees, polymorphic_on=employees.c.type, polymorphic_identity='employee')
mapper(Engineer, engineers, inherits=Employee, polymorphic_identity='engineer')
mapper(Manager, managers, inherits=Employee, polymorphic_identity='manager')

session.query(Employee).filter(Employee.name=='ed')

session.query(Employee).with_polymorphic(Engineer).filter(Engineer.engineer_info=='some info')

session.query(Employee).with_polymorphic('*').all()

companies = Table('companies', metadata,
   Column('company_id', Integer, primary_key=True),
   Column('name', String(50))
   )

employees = Table('employees', metadata, 
  Column('employee_id', Integer, primary_key=True),
  Column('name', String(50)),
  Column('type', String(30), nullable=False),
  Column('company_id', Integer, ForeignKey('companies.company_id'))
)

class Company(object):
    pass

mapper(Company, companies, properties={
    'employees':relation(Employee)
})

session.query(Company).join(Company.employees.of_type(Engineer)).filter(Engineer.engineer_info=='someinfo')

session.query(Company).join(('employees', employees.join(engineers))).filter(Engineer.engineer_info=='someinfo')

session.query(Company).join(('employees', employees.outerjoin(engineers).outerjoin(managers))).\
    filter(or_(Engineer.engineer_info=='someinfo', Manager.manager_data=='somedata'))

session.query(Company).filter(Company.employees.of_type(Engineer).any(Engineer.engineer_info=='someinfo')).all()

session.query(Company).filter(
    exists([1], 
        and_(Engineer.engineer_info=='someinfo', employees.c.company_id==companies.c.company_id), 
        from_obj=employees.join(engineers)
    )
).all()

session.query(Employee).all()

mapper(Employee, employees, polymorphic_on=employees.c.type, \
    polymorphic_identity='employee', polymorphic_fetch='deferred')
mapper(Engineer, engineers, inherits=Employee, polymorphic_identity='engineer')
mapper(Manager, managers, inherits=Employee, polymorphic_identity='manager')

query = session.query(Employee).with_polymorphic([Engineer, Manager])

query.all()

join = employees.outerjoin(engineers).outerjoin(managers)

mapper(Employee, employees, polymorphic_on=employees.c.type, \
    polymorphic_identity='employee', select_table=join)
mapper(Engineer, engineers, inherits=Employee, polymorphic_identity='engineer')
mapper(Manager, managers, inherits=Employee, polymorphic_identity='manager')

employees_table = Table('employees', metadata, 
    Column('employee_id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('manager_data', String(50)),
    Column('engineer_info', String(50)),
    Column('type', String(20), nullable=False)
)

employee_mapper = mapper(Employee, employees_table, \
    polymorphic_on=employees_table.c.type, polymorphic_identity='employee')
manager_mapper = mapper(Manager, inherits=employee_mapper, polymorphic_identity='manager')
engineer_mapper = mapper(Engineer, inherits=employee_mapper, polymorphic_identity='engineer')

employees_table = Table('employees', metadata, 
    Column('employee_id', Integer, primary_key=True),
    Column('name', String(50)),
)

managers_table = Table('managers', metadata, 
    Column('employee_id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('manager_data', String(50)),
)

engineers_table = Table('engineers', metadata, 
    Column('employee_id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('engineer_info', String(50)),
)

mapper(Employee, employees_table)
mapper(Manager, managers_table)
mapper(Engineer, engineers_table)

pjoin = polymorphic_union({
    'employee':employees_table,
    'manager':managers_table,
    'engineer':engineers_table
}, 'type', 'pjoin')

employee_mapper = mapper(Employee, employees_table, select_table=pjoin, \
    polymorphic_on=pjoin.c.type, polymorphic_identity='employee')
manager_mapper = mapper(Manager, managers_table, inherits=employee_mapper, \
    concrete=True, polymorphic_identity='manager')
engineer_mapper = mapper(Engineer, engineers_table, inherits=employee_mapper, \
    concrete=True, polymorphic_identity='engineer')

session.query(Employee).all()

employees_table = Table('employees', metadata, 
    Column('employee_id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('company_id', Integer, ForeignKey('companies.company_id'))
)

companies = Table('companies', metadata, 
   Column('company_id', Integer, primary_key=True),
   Column('name', String(50)))

class Company(object):
    pass

mapper(Company, companies, properties={
   'employees': relation(Employee, backref='company')
})

companies = Table('companies', metadata, 
   Column('id', Integer, primary_key=True),
   Column('name', String(50