SQLAlchemy 0.3 Documentation

One to Many

So with our User class, lets also define the User has having one or more mailing addresses. First, the table metadata:

from sqlalchemy import *

metadata = MetaData()

# define user table
users_table = Table('users', metadata, 
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(16)),
    Column('password', String(20))
)

# define user address table
addresses_table = Table('addresses', metadata,
    Column('address_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey("users.user_id")),
    Column('street', String(100)),
    Column('city', String(80)),
    Column('state', String(2)),
    Column('zip', String(10))
)

Of importance here is the addresses table's definition of a foreign key relationship to the users table, relating the user_id column into a parent-child relationship. When a Mapper wants to indicate a relation of one object to another, the ForeignKey relationships are the default method by which the relationship is determined (options also exist to describe the relationships explicitly).

So then lets define two classes, the familiar User class, as well as an Address class:

class User(object):
    def __init__(self, user_name, password):
        self.user_name = user_name
        self.password = password

class Address(object):
    def __init__(self, street, city, state, zip):
        self.street = street
        self.city = city
        self.state = state
        self.zip = zip

And then a Mapper that will define a relationship of the User and the Address classes to each other as well as their table metadata. We will add an additional mapper keyword argument properties which is a dictionary relating the names of class attributes to database relationships, in this case a relation object against a newly defined mapper for the Address class:

mapper(Address, addresses_table)
mapper(User, users_table, properties = {
        'addresses' : relation(Address)
    }
  )

Lets do some operations with these classes and see what happens:

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

session = create_session(bind_to=engine)

u = User('jane', 'hihilala')
u.addresses.append(Address('123 anywhere street', 'big city', 'UT', '76543'))
u.addresses.append(Address('1 Park Place', 'some other city', 'OK', '83923'))

session.save(u)
session.flush()

A lot just happened there! The Mapper figured out how to relate rows in the addresses table to the users table, and also upon flush had to determine the proper order in which to insert rows. After the insert, all the User and Address objects have their new primary and foreign key attributes populated.

Also notice that when we created a Mapper on the User class which defined an addresses relation, the newly created User instance magically had an "addresses" attribute which behaved like a list. This list is in reality a property function which returns an instance of sqlalchemy.util.HistoryArraySet. This object fulfills the full set of Python list accessors, but maintains a unique set of objects (based on their in-memory identity), and also tracks additions and deletions to the list:

del u.addresses[1]
u.addresses.append(Address('27 New Place', 'Houston', 'TX', '34839'))

session.flush()

Note that when creating a relation with the relation() function, the target can either be a class, in which case the primary mapper for that class is used as the target, or a Mapper instance itself, as returned by the mapper() function.

Lifecycle Relations

In the previous example, a single address was removed from the addresses attribute of a User object, resulting in the corresponding database row being updated to have a user_id of None. But now, theres a mailing address with no user_id floating around in the database of no use to anyone. How can we avoid this ? This is acheived by using the cascade parameter of relation:

clear_mappers()  # clear mappers from the previous example
mapper(Address, addresses_table)
mapper(User, users_table, properties = {
        'addresses' : relation(Address, cascade="all, delete-orphan")
    }
  )

del u.addresses[1]
u.addresses.append(Address('27 New Place', 'Houston', 'TX', '34839'))

session.flush()

In this case, with the delete-orphan cascade rule set, the element that was removed from the addresses list was also removed from the database. Specifying cascade="all, delete-orphan" means that every persistence operation performed on the parent object will be cascaded to the child object or objects handled by the relation, and additionally that each child object cannot exist without being attached to a parent. Such a relationship indicates that the lifecycle of the Address objects are bounded by that of their parent User object.

Cascading is described fully in Cascade rules.

Backreferences

By creating relations with the backref keyword, a bi-directional relationship can be created which will keep both ends of the relationship updated automatically, independently of database operations. Below, the User mapper is created with an addresses property, and the corresponding Address mapper receives a "backreference" to the User object via the property name user:

Address = mapper(Address, addresses_table)
User = mapper(User, users_table, properties = {
                'addresses' : relation(Address, backref='user')
            }
          )

u = User('fred', 'hi')
a1 = Address('123 anywhere street', 'big city', 'UT', '76543')
a2 = Address('1 Park Place', 'some other city', 'OK', '83923')

# append a1 to u
u.addresses.append(a1)

# attach u to a2
a2.user = u

# the bi-directional relation is maintained
>>> u.addresses == [a1, a2]
True
>>> a1.user is user and a2.user is user
True

The backreference feature also works with many-to-many relationships, which are described later. When creating a backreference, a corresponding property (i.e. a second relation()) is placed on the child mapper. The default arguments to this property can be overridden using the backref() function:

mapper(User, users_table)
mapper(Address, addresses_table, properties={
    'user':relation(User, backref=backref('addresses', cascade="all, delete-orphan"))
})

The backref() function is often used to set up a bi-directional one-to-one relationship. This is because the relation() function by default creates a "one-to-many" relationship when presented with a primary key/foreign key relationship, but the backref() function can redefine the uselist property to make it a scalar:

mapper(User, users_table)
mapper(Address, addresses_table, properties={
    'user' : relation(User, backref=backref('address', uselist=False))
})

Selecting With Joins

When using mappers that have relationships to other mappers, the need to specify query criterion across multiple tables arises. SQLAlchemy provides several core techniques which offer this functionality.

When specifying columns to the select method of Query, if the columns are attached to a table other than the mapped table, that table is automatically added to the "FROM" clause of the query. This is the same behavior that occurs when creating a non-ORM select object. Using this feature, joins can be created when querying:

sqll = session.query(User).select(and_(users.c.user_id==addresses.c.user_id, 
             addresses.c.street=='123 Green Street'))

Above, we specified selection criterion that included columns from both the users and the addresses table. Note that in this case, we had to specify not just the matching condition to the street column on addresses, but also the join condition between the users and addresses table. Even though the User mapper has a relationship to the Address mapper where the join condition is known, the select method does not assume how you want to construct joins. If we did not include the join clause, we would get:

# this is usually not what you want to do
sqll = session.query(User).select(addresses.c.street=='123 Green Street')

The above join will return all rows of the users table, even those that do not correspond to the addresses table, in a cartesian product with the matching rows from the addresses table.

Another way to specify joins more explicitly is to use the from_obj parameter of select(). This allows you to explicitly place elements in the FROM clause of the query, which could include lists of tables and/or Join constructs:

sqll = session.query(User).select(addresses.c.street=='123 Green Street', 
                from_obj=[users.join(addresses)])

In the above example, the join function by default creates a natural join between the two tables, so we were able to avoid having to specify the join condition between users and addresses explicitly.

Creating Joins Using select_by()

Another way that joins can be created is by using the select_by method of Query, which has the ability to create joins across relationships automatically. This method is in many circumstances more convenient than, but not as flexible as, the more SQL-level approach using the select() method described in the previous section.

To issue a join using select_by, just specify a key in the argument list which is not present in the primary mapper's list of properties or columns, but is present in the property list of some relationship down the line of objects. The Query object will recursively traverse along the mapped relationships starting with the lead class and descending into child classes, until it finds a property matching the given name. For each new mapper it encounters along the path to the located property, it constructs a join across that relationship:

sqll = session.query(User).select_by(street='123 Green Street')

The above example is shorthand for:

l = session.query(User).select(and_(
         Address.c.user_id==User.c.user_id, 
         Address.c.street=='123 Green Street')
   )

All keyword arguments sent to select_by are used to create query criterion. This means that familiar select keyword options like order_by and limit are not directly available. To enable these options with select_by, you can try the SelectResults extension which offers transitive methods off the result of a select or select_by such as order_by(), limit(), etc.

Note that the select_by method, while it primarily uses keyword arguments, also can accomodate ClauseElement objects positionally; recall that a ClauseElement is genearated when producing a comparison off of a Column expression, such as users.c.name=='ed'. When using ClauseElements with select_by, these clauses are passed directly to the generated SQL and are not used to further locate join criterion. If criterion is being constructed with these kinds of expressions, consider using the select() method which is better designed to accomodate these expressions.

As of SA 0.3.4, select_by() and related functions can compare not only column-based attributes to column-based values, but also relations to object instances:

# get an instance of Address
someaddress = session.query(Address).get_by(street='123 Green Street')

# look for User instances which have the 
# "someaddress" instance in their "addresses" collection
l = session.query(User).select_by(addresses=someaddress)

Where above, the comparison denoted by addresses=someaddress is constructed by comparing all the primary key columns in the Address mapper to each corresponding primary key value in the someaddress entity. In other words, its equivalent to saying select_by(address_id=someaddress.address_id) (Alpha API).

Generating Join Criterion Using join_to, join_via

Feature Status: Alpha API

The join_to method of Query is a component of the select_by operation, and is given a keyname in order to return a "join path" from the Query's mapper to the mapper which is referenced by a relation() of the given name:

>>> q = session.query(User)
>>> j = q.join_to('addresses')
>>> print j
users.user_id=addresses.user_id

join_to can also be given the name of a column-based property, in which case it will locate a path to the nearest mapper which has that property as a column:

>>> q = session.query(User)
>>> j = q.join_to('street')
>>> print j
users.user_id=addresses.user_id

Also available is the join_via function, which is similar to join_to, except instead of traversing through all properties to find a path to the given key, its given an explicit path to the target property:

>>> q = session.query(User)
>>> j = q.join_via(['orders', 'items'])
>>> print j
users.c.user_id==orders.c.user_id AND orders.c.item_id==items.c.item_id

Expressions produced by join_to and join_via can be used with select to create more complicated query criterion across multiple relations:

>>> l = q.select(
    (addresses_table.c.street=='some address') &
    (items_table.c.item_name=='item #4') &
    q.join_to('addresses') &
    q.join_via(['orders', 'items'])
    )

Note that the from_obj parameter of select(), described previously, allows finer grained control of joins, allowing any combination of inner and outer joins.

Eager Loading

Eager Loading describes the loading of parent and child objects across a relation using a single query. The purpose of eager loading is strictly one of performance enhancement; eager loading has no impact on the results of a query, except that when traversing child objects within the results, lazy loaders will not need to issue separate queries to load those child objects.

Eager Loading is enabled on a per-relationship basis, either as the default for a particular relationship, or for a single query using query options, described later.

With just a single parameter lazy=False specified to the relation object, the parent and child SQL queries can be joined together.

mapper(Address, addresses_table)
mapper(User, users_table, properties = {
        'addresses' : relation(Address, lazy=False)
    }
  )

sqlusers = session.query(User).select(User.c.user_name=='Jane')

for u in users:
    print repr(u)
    for a in u.addresses:
        print repr(a)

Above, a pretty ambitious query is generated just by specifying that the User should be loaded with its child Addresses in one query. When the mapper processes the results, it uses an Identity Map to keep track of objects that were already loaded, based on their primary key identity. Through this method, the redundant rows produced by the join are organized into the distinct object instances they represent.

Recall that eager loading has no impact on the results of the query. What if our query included our own join criterion? The eager loading query accomodates this using aliases, and is immune to the effects of additional joins being specified in the original query. To use our select_by example above, joining against the "addresses" table to locate users with a certain street results in this behavior:

sqlusers = session.query(User).select_by(street='123 Green Street')

The join implied by passing the "street" parameter is separate from the join produced by the eager join, which is "aliasized" to prevent conflicts.

Using Options to Change the Loading Strategy

The options method on the Query object provides an easy way to get alternate forms of a mapper query from an original one. The most common use of this feature is to change the "eager/lazy" loading behavior of a particular mapper, via the functions eagerload(), lazyload() and noload():

# user mapper with lazy addresses
mapper(User, users_table, properties = {
             'addresses' : relation(mapper(Address, addresses_table))
         }
)

# query object
query = session.query(User)

# make an eager loading query
eagerquery = query.options(eagerload('addresses'))
u = eagerquery.select()

# make another query that wont load the addresses at all
plainquery = query.options(noload('addresses'))

# multiple options can be specified
myquery = oldquery.options(lazyload('tracker'), noload('streets'), eagerload('members'))

# to specify a relation on a relation, separate the property names by a "."
myquery = oldquery.options(eagerload('orders.items'))