SQLAlchemy 0.3 Documentation

Overriding Column Names

When mappers are constructed, by default the column names in the Table metadata are used as the names of attributes on the mapped class. This can be customzed within the properties by stating the key/column combinations explicitly:

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

In the situation when column names overlap in a mapper against multiple tables, columns may be referenced together with a list:

# join users and addresses
usersaddresses = sql.join(users_table, addresses_table, users_table.c.user_id == addresses_table.c.user_id)
m = mapper(User, usersaddresses,   
    properties = {
        'id' : [users_table.c.user_id, addresses_table.c.user_id],
    }
    )

Overriding Properties

A common request is the ability to create custom class properties that override the behavior of setting/getting an attribute. Currently, the easiest way to do this in SQLAlchemy is how it would be done in any Python program; define your attribute with a different name, such as "_attribute", and use a property to get/set its value. The mapper just needs to be told of the special name:

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

mapper(MyClass, mytable, properties = {
   # map the '_email' attribute to the "email" column
   # on the table
   '_email': mytable.c.email
})

It is also possible to route the the select_by and get_by functions on Query using the new property name, by establishing a synonym:

mapper(MyClass, mytable, properties = {
    # map the '_email' attribute to the "email" column
    # on the table
    '_email': mytable.c.email,

    # make a synonym 'email'
    'email' : synonym('_email')
})

# now you can select_by(email)
result = session.query(MyClass).select_by(email='john@smith.com')

Synonym can be established with the flag "proxy=True", to create a class-level proxy to the actual property. This has the effect of creating a fully functional synonym on class instances:

mapper(MyClass, mytable, properties = {
    '_email': mytable.c.email
    'email' : synonym('_email', proxy=True)
})

x = MyClass()
x.email = 'john@doe.com'

>>> x._email
'john@doe.com'

Custom List Classes

Feature Status: Alpha API

A one-to-many or many-to-many relationship results in a list-holding element being attached to all instances of a class. The actual list is an "instrumented" list, which transparently maintains a relationship to a plain Python list. The implementation of the underlying plain list can be changed to be any object that implements a list-style append and __iter__ method. A common need is for a list-based relationship to actually be a dictionary. This can be achieved by subclassing dict to have list-like behavior.

In this example, a class MyClass is defined, which is associated with a parent object MyParent. The collection of MyClass objects on each MyParent object will be a dictionary, storing each MyClass instance keyed to its name attribute.

# a class to be stored in the list
class MyClass(object):
    def __init__(self, name):
        self.name = name

# create a dictionary that will act like a list, and store
# instances of MyClass
class MyDict(dict):
    def append(self, item):
        self[item.name] = item
    def __iter__(self):
        return self.values()

# parent class
class MyParent(object):
    pass

# mappers, constructed normally
mapper(MyClass, myclass_table)
mapper(MyParent, myparent_table, properties={
    'myclasses' : relation(MyClass, collection_class=MyDict)
})

# elements on 'myclasses' can be accessed via string keyname
myparent = MyParent()
myparent.myclasses.append(MyClass('this is myclass'))
myclass = myparent.myclasses['this is myclass']

Note: SQLAlchemy 0.4 has an overhauled and much improved implementation for custom list classes, with some slight API changes.

Custom Join Conditions

When creating relations on a mapper, most examples so far have illustrated the mapper and relationship joining up based on the foreign keys of the tables they represent. in fact, this "automatic" inspection can be completely circumvented using the primaryjoin and secondaryjoin arguments to relation, as in this example which creates a User object which has a relationship to all of its Addresses which are in Boston:

class User(object):
    pass
class Address(object):
    pass

mapper(Address, addresses_table)
mapper(User, users_table, properties={
    'boston_addresses' : relation(Address, primaryjoin=
                and_(users_table.c.user_id==Address.c.user_id, 
                Addresses.c.city=='Boston'))
})

Many to many relationships can be customized by one or both of primaryjoin and secondaryjoin, shown below with just the default many-to-many relationship explicitly set:

class User(object):
    pass
class Keyword(object):
    pass
mapper(Keyword, keywords_table)
mapper(User, users_table, properties={
    'keywords':relation(Keyword, secondary=userkeywords_table,
        primaryjoin=users_table.c.user_id==userkeywords_table.c.user_id,
        secondaryjoin=userkeywords_table.c.keyword_id==keywords_table.c.keyword_id
        )
})

Lazy/Eager Joins Multiple Times to One Table

The previous example leads in to the idea of joining against the same table multiple times. Below is a User object that has lists of its Boston and New York addresses:

mapper(User, users_table, properties={
    'boston_addresses' : relation(Address, primaryjoin=
                and_(users_table.c.user_id==Address.c.user_id, 
                Addresses.c.city=='Boston')),
    'newyork_addresses' : relation(Address, primaryjoin=
                and_(users_table.c.user_id==Address.c.user_id, 
                Addresses.c.city=='New York')),
})

Both lazy and eager loading support multiple joins equally well.

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 essentailly "column-level lazy loading". This feature is useful when one wants to avoid loading a large text or binary field into memory when its 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 with the options method:

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

Working with Large Collections

SQLAlchemy relations are generally simplistic; the lazy loader loads in the full list of child objects when accessed, and the eager load builds a query that loads the full list of child objects. Additionally, when you are deleting a parent object, SQLAlchemy ensures that it has loaded the full list of child objects so that it can mark them as deleted as well (or to update their parent foreign key to NULL). It does not issue an en-masse "delete from table where parent_id=?" type of statement in such a scenario. This is because the child objects themselves may also have further dependencies, and additionally may also exist in the current session in which case SA needs to know their identity so that their state can be properly updated.

So there are several techniques that can be used individually or combined together to address these issues, in the context of a large collection where you normally would not want to load the full list of relationships:

• Use lazy=None to disable child object loading (i.e. noload)

  mapper(MyClass, table, properties=relation{
      'children':relation(MyOtherClass, lazy=None)
  })

• To load child objects, just use a query. Of particular convenience is that Query is a generative object, so you can return it as is, allowing additional criterion to be added as needed:

  class Organization(object):
      def __init__(self, name):
          self.name = name
      member_query = property(lambda self: object_session(self).query(Member).with_parent(self))
  
  myorg = sess.query(Organization).get(5)
  
  # get all members
  members = myorg.member_query.list()
  
  # query a subset of members using LIMIT/OFFSET
  members = myorg.member_query[5:10]

• Use passive_deletes=True to disable child object loading on a DELETE operation, in conjunction with "ON DELETE (CASCADE|SET NULL)" on your database to automatically cascade deletes to child objects. Note that "ON DELETE" is not supported on SQLite, and requires InnoDB tables when using MySQL:

  mytable = Table('mytable', meta,
      Column('id', Integer, primary_key=True),
      )
  
  myothertable = Table('myothertable', meta,
      Column('id', Integer, primary_key=True),
      Column('parent_id', Integer),
      ForeignKeyConstraint(['parent_id'],['mytable.id'], ondelete="CASCADE"),
      )
  
  mmapper(MyOtherClass, myothertable)
  
  mapper(MyClass, mytable, properties={
      'children':relation(MyOtherClass, passive_deletes=True)
  })

• As an alternative to using "ON DELETE CASCADE", for very simple scenarios you can create a simple MapperExtension that will issue a DELETE for child objects before the parent object is deleted:

  class DeleteMemberExt(MapperExtension):
      def before_delete(self, mapper, connection, instance):
          connection.execute(member_table.delete(member_table.c.org_id==instance.org_id))
  
  mapper(Organization, org_table, extension=DeleteMemberExt(), properties = {
      'members' : relation(Member, lazy=None, passive_deletes=True, cascade="all, delete-orphan")
  })

Note that this approach is not nearly as efficient or general-purpose as "ON DELETE CASCADE", since the database itself can cascade the operation along any number of tables.

The latest distribution includes an example examples/collection/large_collection.py which illustrates most of these techniques.

Relation Options

Options which can be sent to the relation() function. For arguments to mapper(), see Mapper Keyword Arguments.

• association - Deprecated; as of version 0.3.0 the association keyword is synonomous with applying the "all, delete-orphan" cascade to a "one-to-many" relationship. SA can now automatically reconcile a "delete" and "insert" operation of two objects with the same "identity" in a flush() operation into a single "update" statement, which is the pattern that "association" used to indicate. See the updated example of association mappings in datamapping_association.
• backref - indicates the name of a property to be placed on the related mapper's class that will handle this relationship in the other direction, including synchronizing the object attributes on both sides of the relation. Can also point to a backref() construct for more configurability. See Backreferences.
• cascade - a string list of cascade rules which determines how persistence operations should be "cascaded" from parent to child. For a description of cascade rules, see Lifecycle Relations and Cascade rules.
• collection_class - a class or function that returns a new list-holding object. will be used in place of a plain list for storing elements. See Custom List Classes.
• foreign_keys - a list of columns which are to be used as "foreign key" columns. this parameter should be used in conjunction with explicit primaryjoin and secondaryjoin (if needed) arguments, and the columns within the foreign_keys list should be present within those join conditions. Normally, relation() will inspect the columns within the join conditions to determine which columns are the "foreign key" columns, based on information in the Table metadata. Use this argument when no ForeignKey's are present in the join condition, or to override the table-defined foreign keys.
• foreignkey - deprecated. use the foreign_keys argument for foreign key specification, or remote_side for "directional" logic.
• lazy=True - specifies how the related items should be loaded. a value of True indicates they should be loaded lazily when the property is first accessed. A value of False indicates they should be loaded by joining against the parent object query, so parent and child are loaded in one round trip (i.e. eagerly). A value of None indicates the related items are not loaded by the mapper in any case; the application will manually insert items into the list in some other way. In all cases, items added or removed to the parent object's collection (or scalar attribute) will cause the appropriate updates and deletes upon flush(), i.e. this option only affects load operations, not save operations.
  
  
• order_by - indicates the ordering that should be applied when loading these items. See the section Controlling Ordering for details.
• passive_deletes=False - Indicates if lazy-loaders should not be executed during the flush() process, which normally occurs in order to locate all existing child items when a parent item is to be deleted. Setting this flag to True is appropriate when ON DELETE CASCADE rules have been set up on the actual tables so that the database may handle cascading deletes automatically. This strategy is useful particularly for handling the deletion of objects that have very large (and/or deep) child-object collections. See the example in Working with Large Collections.
• post_update - this indicates that the relationship should be handled by a second UPDATE statement after an INSERT or before a DELETE. Currently, it also will issue an UPDATE after the instance was UPDATEd as well, although this technically should be improved. This flag is used to handle saving bi-directional dependencies between two individual rows (i.e. each row references the other), where it would otherwise be impossible to INSERT or DELETE both rows fully since one row exists before the other. Use this flag when a particular mapping arrangement will incur two rows that are dependent on each other, such as a table that has a one-to-many relationship to a set of child rows, and also has a column that references a single child row within that list (i.e. both tables contain a foreign key to each other). If a flush() operation returns an error that a "cyclical dependency" was detected, this is a cue that you might want to use post_update to "break" the cycle.
• primaryjoin - a ClauseElement that will be used as the primary join of this child object against the parent object, or in a many-to-many relationship the join of the primary object to the association table. By default, this value is computed based on the foreign key relationships of the parent and child tables (or association table).
• private=False - deprecated. setting private=True is the equivalent of setting cascade="all, delete-orphan", and indicates the lifecycle of child objects should be contained within that of the parent. See the example in Lifecycle Relations.
• remote_side - used for self-referential relationships, indicates the column or list of columns that form the "remote side" of the relationship. See the examples in Self Referential Mappers.
• secondary - for a many-to-many relationship, specifies the intermediary table. The secondary keyword argument should generally only be used for a table that is not otherwise expressed in any class mapping. In particular, using the Association Object Pattern is generally mutually exclusive against using the secondary keyword argument.
• secondaryjoin - a ClauseElement that will be used as the join of an association table to the child object. By default, this value is computed based on the foreign key relationships of the association and child tables.
• uselist=(True|False) - a boolean that indicates if this property should be loaded as a list or a scalar. In most cases, this value is determined automatically by relation(), based on the type and direction of the relationship - one to many forms a list, many to one forms a scalar, many to many is a list. If a scalar is desired where normally a list would be present, such as a bi-directional one-to-one relationship, set uselist to False.
• viewonly=False - when set to True, the relation is used only for loading objects within the relationship, and has no effect on the unit-of-work flush process. Relations with viewonly can specify any kind of join conditions to provide additional views of related objects onto a parent object. Note that the functionality of a viewonly relationship has its limits - complicated join conditions may not compile into eager or lazy loaders properly. If this is the case, use an alternative method, such as those described in Working with Large Collections, Statement and Result-Set ORM Queries, or Mapping a Class against Arbitrary Selects.

Single Table Inheritance

This will support polymorphic loading via the Employee mapper.

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))
)

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

Concrete Table Inheritance

Without polymorphic loading, you just define a separate mapper for each class.

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)),
)

manager_mapper = mapper(Manager, managers_table)
engineer_mapper = mapper(Engineer, engineers_table)

With polymorphic loading, the SQL query to do the actual polymorphic load must be constructed, usually as a UNION. There is a helper function to create these UNIONS called polymorphic_union.

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

employee_mapper = mapper(Employee, pjoin, polymorphic_on=pjoin.c.type)
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')

Joined Table Inheritance

Like concrete table inheritance, this can be done non-polymorphically, or with a little more complexity, polymorphically:

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

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

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

person_mapper = mapper(Employee, employees)
mapper(Engineer, engineers, inherits=person_mapper)
mapper(Manager, managers, inherits=person_mapper)

Polymorphically, joined-table inheritance is easier than concrete, as a simple outer join can usually work:

person_join = people.outerjoin(engineers).outerjoin(managers)

person_mapper = mapper(Person, people, select_table=person_join,polymorphic_on=people.c.type, polymorphic_identity='person')
mapper(Engineer, engineers, inherits=person_mapper, polymorphic_identity='engineer')
mapper(Manager, managers, inherits=person_mapper, polymorphic_identity='manager')

In SQLAlchemy 0.4, the above mapper setup can load polymorphically without the join as well, by issuing distinct queries for each subclasses' table.

The join condition in a joined table inheritance structure can be specified explicitly, using inherit_condition:

AddressUser.mapper = mapper(
        AddressUser,
        addresses_table, inherits=User.mapper, 
        inherit_condition=users_table.c.user_id==addresses_table.c.user_id
    )

Combining Eager Loads with Statement/Result Set Queries

When full statement/result loads are used with Query, SQLAlchemy does not affect the SQL query itself, and therefore has no way of tacking on its own LEFT [OUTER] JOIN conditions that are normally used to eager load relationships. If the query being constructed is created in such a way that it returns rows not just from a parent table (or tables) but also returns rows from child tables, the result-set mapping can be notified as to which additional properties are contained within the result set. This is done using the contains_eager() query option, which specifies the name of the relationship to be eagerly loaded.

# mapping is the users->addresses mapping
mapper(User, users_table, properties={
    'addresses':relation(Address, addresses_table)
})

# define a query on USERS with an outer join to ADDRESSES
statement = users_table.outerjoin(addresses_table).select(use_labels=True)

# construct a Query object which expects the "addresses" results 
query = session.query(User).options(contains_eager('addresses'))

# get results normally
r = query.instances(statement.execute())

If the "eager" portion of the statement is "alisaed", the alias keyword argument to contains_eager() may be used to indicate it. This is a string alias name or reference to an actual Alias object:

# use an alias of the addresses table
adalias = addresses_table.alias('adalias')

# define a query on USERS with an outer join to adalias
statement = users_table.outerjoin(adalias).select(use_labels=True)

# construct a Query object which expects the "addresses" results 
query = session.query(User).options(contains_eager('addresses', alias=adalias))

# get results normally
sqlr = query.from_statement(query).all()

In the case that the main table itself is also aliased, the contains_alias() option can be used:

# define an aliased UNION called 'ulist'
statement = users.select(users.c.user_id==7).union(users.select(users.c.user_id>7)).alias('ulist')

# add on an eager load of "addresses"
statement = statement.outerjoin(addresses).select(use_labels=True)

# create query, indicating "ulist" is an alias for the main table, "addresses" property should
# be eager loaded
query = create_session().query(User).options(contains_alias('ulist'), contains_eager('addresses'))

# results
r = query.instances(statement.execute())