Querying and Updating at the ORM Level - Essential SQLAlchemy, 2nd Edition (2010)

Essential SQLAlchemy, 2nd Edition (2010)

Chapter 7. Querying and Updating at the ORM Level

This chapter introduces the SQLAlchemy Session object. You will learn how to use the Session to perform queries and updates of mapped classes, as well as how to customize the Session class and create a “contextual” session that simplifies session management.

The SQLAlchemy ORM Session Object

SQLAlchemy manages all querying and updating of objects in the ORM with Session objects. The Session is responsible for implementing the unit of work pattern of synchronization between in-memory objects and database tables. Sessions also provide a rich interface for querying the database based on object attributes rather than the underlying SQL database structure.

Creating a Session

The first step in creating a session is to obtain a Session object from SQLAlchemy. One way to do this is to directly instantiate the sqlalchemy.orm.session.Session class. However, this constructor for the SQLAlchemy-provided Session has a number of keyword arguments, making instantiating Sessions in this manner verbose and tedious. In order to alleviate this burden, SQLAlchemy provides the sessionmaker() function, which returns a subclass of orm.session.Session with default arguments set for its constructor.

Once you have this customized Session class, you can instantiate it as many times as necessary in your application without needing to retype the keyword arguments (which in many applications will not change between Session instantiations). If you wish to override the defaults supplied to sessionmaker, you can do so at Session instantiation time. You can also modify the default arguments bound to a particular Session subclass by calling the class method Session.configure():

# Create a Session class with the default

# options

Session = sessionmaker(bind=engine)

# Create an unbound Session class

Session = sessionmaker()

# Bind the Session class once the engine

# is available

Session.configure(bind=engine)

The sessionmaker() and the associated Session subclass’s configure class method and constructor take the following keyword arguments:

bind=None

The database Engine or Connection to which to bind the session.

binds=None

Optional dictionary that provides more detailed binding information. The keys to this dictionary can be mapped classes, mapper() instances, or Tables. The values in the dictionary indicate which Engine or Connectable to use for a given mapped class, overriding the values set in thebind parameter.

autoflush=True

When True, the Session will automatically be flush()ed before executing any queries against the session. This ensures that the results returned from the query match the operations that have been done in-memory in the unit-of-work.

transactional=False

When True, the Session will automatically use transactions. To commit a set of changes, simply use the Session’s commit() method. To revert changes, use the rollback() method. Using transactional=True, it is never necessary to explicitly begin() a transaction on aSession. It is, however, necessary to explicitly call commit() at the end of your transaction.

twophase=False

This tells SQLAlchemy to use two-phase commits on all transactions (on databases that support two-phase commits, currently MySQL and PostgreSQL, soon to include Oracle), which is useful when dealing with multiple database instances. In this case, after flush()ing changes to all databases but before issuing a COMMIT, SQLAlchemy issues a PREPARE to each database, allowing the entire transaction to be rolled back if an error is raised during any of the PREPARE executions.

echo_uow=False

When True, instructs the Session to log all unit-of-work operations. This is the equivalent of setting a log level of logging.DEBUG for the 'sqlalchemy.orm.unitofwork' logger.

extension=None

Optional SessionExtension that receives various session events, similar to the MapperExtension. (SessionExtensions are covered in more detail later in this chapter in Extending Sessions.”)

weak_identity_map=True

The default value uses weak references in the identity map maintained by the session, allowing objects that are a) no longer referenced outside the session and b) have no pending changes to be automatically garbage-collected. If this is set to False, then a regular Python dict is used, and objects will remain in the Session’s identity map until they are explicitly removed using the Session methods expunge(), clear(), or purge().

Saving Objects to the Session

Once you have a Session instance, you can begin persisting in-memory objects. This is accomplished quite simply by calling the save() method on the Session object. Suppose we have the following schema and mapping:

from sqlalchemy import *

from sqlalchemy.orm import *

engine = create_engine('sqlite://')

metadata = MetaData(engine)

product_table = Table(

'product', metadata,

Column('sku', String(20), primary_key=True),

Column('msrp', Numeric))

class Product(object):

def __init__(self, sku, msrp, summary=None):

self.sku = sku

self.msrp = msrp

self.summary = summary

self.categories = []

self.prices = []

def __repr__(self):

return '<Product %s>' % self.sku

mapper(Product, product_table)

To save two products to the database, we can do the following. Note that the echo_uow property on the session as well as the echo property on the Engine are True in order to display exactly what SQLAlchemy is doing in response to our flush() call:

>>> Session = sessionmaker(bind=engine, echo_uow=True)

>>> engine.echo = True

>>> session = Session()

>>>

>>> p1 = Product('123', 11.22)

>>> p2 = Product('456', 33.44)

>>> session.save(p1)

>>> session.save(p2)

>>> session.flush()

2007-10-28 16:55:05,117 INFO

... sqlalchemy.orm.unitofwork.UOWTransaction.0x..90 Task dump:

UOWTask(0xb4e7d0, Product/product/None) (save/update phase)

|- Save Product@0xb4e750

|- Save Product@0xb4e690

|----

2007-10-28 16:55:05,118 INFO sqlalchemy.engine.base.Engine.0x..90

... BEGIN

2007-10-28 16:55:05,119 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO product (sku, msrp) VALUES (?, ?)

2007-10-28 16:55:05,119 INFO sqlalchemy.engine.base.Engine.0x..90

... ['123', '11.22']

2007-10-28 16:55:05,120 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO product (sku, msrp) VALUES (?, ?)

2007-10-28 16:55:05,120 INFO sqlalchemy.engine.base.Engine.0x..90

... ['456', '33.44']

2007-10-28 16:55:05,121 INFO

... sqlalchemy.orm.unitofwork.UOWTransaction.0x..90 Execute Complete

Object States with a Session

Objects can have various states as they relate to Sessions. These states are defined as follows:

Transient

The object exists in memory only. It is not attached to a session, and it has no representation in the database. A Transient object has no relationship to the ORM other than the fact that its class has an associated mapper().

Pending

A Pending object has been marked for insertion into the database at the next flush() operation. Transient objects become Pending when they are save()d to the Session.

Persistent

The object is present in both the session and the database. Persistent objects are created either by flush()ing Pending objects or by querying the database for existing instances.

Detached

The object has a corresponding record in the database, but is not attached to any session. Detached objects cannot issue any SQL automatically to load related objects or attributes, unlike Persistent objects. An object becomes detached if it is explicitly expunge()d from the session.

We can actually save large graphs of objects to the database by using the default cascade value 'save-update' on our relation() objects. For instance, consider the additional schema and mapping:

level_table = Table(

'level', metadata,

Column('id', Integer, primary_key=True),

Column('parent_id', None, ForeignKey('level.id')),

Column('name', String(20)))

category_table = Table(

'category', metadata,

Column('id', Integer, primary_key=True),

Column('level_id', None, ForeignKey('level.id')),

Column('parent_id', None, ForeignKey('category.id')),

Column('name', String(20)))

product_category_table = Table(

'product_category', metadata,

Column('product_id', None, ForeignKey('product.sku'),

... primary_key=True),

Column('category_id', None, ForeignKey('category.id'),

... primary_key=True))

class Product(object):

def __init__(self, sku, msrp, summary=None):

self.sku = sku

self.msrp = msrp

self.summary = summary

self.categories = []

self.prices = []

def __repr__(self):

return '<Product %s>' % self.sku

class Level(object):

def __init__(self, name, parent=None):

self.name = name

self.parent = parent

def __repr__(self):

return '<Level %s>' % self.name

class Category(object):

def __init__(self, name, level, parent=None):

self.name = name

self.level = level

self.parent = parent

def __repr__(self):

return '<Category %s.%s>' % (self.level.name, self.name)

# Clear the mappers so we can re-map the Product class

# with an additional property

clear_mappers()

mapper(Product, product_table, properties=dict(

categories=relation(Category, secondary=product_category_table,

backref='products')))

mapper(Level, level_table, properties=dict(

children=relation(Level, backref='parent'),

categories=relation(Category, backref='level')))

mapper(Category, category_table, properties=dict(

children=relation(Category, backref='parent')))

Now we can create a product hierarchy and assign some categories just as if there were no database, and the Session will infer the appropriate operations to persist the entire data model:

>>> department = Level('Department')

>>> tops = Category('Tops', level=department)

>>> bottoms = Category('Bottoms', level=department)

>>>

>>> class_ = Level('Class', parent=department)

>>> shirts = Category('Shirts', level=class_, parent=tops)

>>> pants = Category('Pants', level=class_, parent=bottoms)

>>>

>>> subclass = Level('SubClass', parent=class_)

>>> tshirts = Category('T-Shirts', level=subclass, parent=shirts)

>>> dress_shirts = Category('Dress Shirts', level=subclass,

... parent=shirts)

>>> slacks = Category('Slacks', level=subclass, parent=pants)

>>> denim = Category('Denim', level=subclass, parent=pants)

>>>

>>> # Create two more products

... p3 = Product('111', 55.95)

>>> p4 = Product('222', 15.95)

>>> p3.categories=[denim, pants, bottoms]

>>> p4.categories=[tshirts, shirts, tops]

Now that we have created all the objects and specified the relations between them, we can save one object to the Session, and all related objects will be saved as well (this is due to the default 'save-update' value of the cascade parameter in all the relations() created). In this example, the department object is connected to all the other objects through various relation()s, so it is sufficient to save it alone. Once this is done, we can flush the changes out to the database. For the purposes of brevity, we will use a fresh session with echo_uow set to False:

>>> session = Session(echo_uow=False)

>>> session.save(department)

>>> session.flush()

2007-10-28 18:41:10,042 INFO sqlalchemy.engine.base.Engine.0x..90

... BEGIN

2007-10-28 18:41:10,043 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO product (sku, msrp) VALUES (?, ?)

2007-10-28 18:41:10,043 INFO sqlalchemy.engine.base.Engine.0x..90

... ['111', '55.95']

2007-10-28 18:41:10,045 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO product (sku, msrp) VALUES (?, ?)

2007-10-28 18:41:10,045 INFO sqlalchemy.engine.base.Engine.0x..90

... ['222', '15.95']

2007-10-28 18:41:10,047 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO level (parent_id, name) VALUES (?, ?)

2007-10-28 18:41:10,047 INFO sqlalchemy.engine.base.Engine.0x..90

... [None, 'Department']

2007-10-28 18:41:10,049 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO level (parent_id, name) VALUES (?, ?)

2007-10-28 18:41:10,049 INFO sqlalchemy.engine.base.Engine.0x..90

... [1, 'Class']

2007-10-28 18:41:10,053 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO level (parent_id, name) VALUES (?, ?)

2007-10-28 18:41:10,053 INFO sqlalchemy.engine.base.Engine.0x..90

... [2, 'SubClass']

2007-10-28 18:41:10,057 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO category (level_id, parent_id, name) VALUES (?, ?,

... ?)

2007-10-28 18:41:10,057 INFO sqlalchemy.engine.base.Engine.0x..90

... [1, None, 'Bottoms']

2007-10-28 18:41:10,059 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO category (level_id, parent_id, name) VALUES (?, ?,

... ?)

2007-10-28 18:41:10,059 INFO sqlalchemy.engine.base.Engine.0x..90

... [1, None, 'Tops']

2007-10-28 18:41:10,060 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO category (level_id, parent_id, name) VALUES (?, ?,

... ?)

2007-10-28 18:41:10,060 INFO sqlalchemy.engine.base.Engine.0x..90

... [2, 1, 'Pants']

2007-10-28 18:41:10,062 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO category (level_id, parent_id, name) VALUES (?, ?,

... ?)

2007-10-28 18:41:10,063 INFO sqlalchemy.engine.base.Engine.0x..90

... [2, 2, 'Shirts']

2007-10-28 18:41:10,065 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO category (level_id, parent_id, name) VALUES (?, ?,

... ?)

2007-10-28 18:41:10,065 INFO sqlalchemy.engine.base.Engine.0x..90

... [3, 4, 'T-Shirts']

2007-10-28 18:41:10,066 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO category (level_id, parent_id, name) VALUES (?, ?,

... ?)

2007-10-28 18:41:10,066 INFO sqlalchemy.engine.base.Engine.0x..90

... [3, 4, 'Dress Shirts']

2007-10-28 18:41:10,068 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO category (level_id, parent_id, name) VALUES (?, ?,

... ?)

2007-10-28 18:41:10,068 INFO sqlalchemy.engine.base.Engine.0x..90

... [3, 3, 'Slacks']

2007-10-28 18:41:10,069 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO category (level_id, parent_id, name) VALUES (?, ?,

... ?)

2007-10-28 18:41:10,070 INFO sqlalchemy.engine.base.Engine.0x..90

... [3, 3, 'Denim']

2007-10-28 18:41:10,071 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO product_category (product_id, category_id) VALUES

... (?, ?)

2007-10-28 18:41:10,072 INFO sqlalchemy.engine.base.Engine.0x..90

... [['222', 2], ['111', 1], ['111', 8], ['222', 4], ['111', 3],

... ['222', 5]]

Updating Objects in the Session

If we wish to update Persistent or Pending objects, we can simply modify them in-memory and rely on the Session to figure out the changes required in the database. This even works for related objects. For instance, if we decide to recategorize the product with sku “111”, we would simply update the list of categories:

>>> p3.categories = [ slacks, pants, bottoms ]

2007-10-28 18:48:31,534 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product, product_category

WHERE ? = product_category.category_id AND product.sku =

... product_category.product_id ORDER BY product_category.oid

2007-10-28 18:48:31,534 INFO sqlalchemy.engine.base.Engine.0x..90

... [7]

>>> session.flush()

2007-10-28 18:48:31,554 INFO sqlalchemy.engine.base.Engine.0x..90

... DELETE FROM product_category WHERE product_category.product_id =

... ? AND product_category.category_id = ?

2007-10-28 18:48:31,555 INFO sqlalchemy.engine.base.Engine.0x..90

... ['111', 8]

2007-10-28 18:48:31,558 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO product_category (product_id, category_id) VALUES

... (?, ?)

2007-10-28 18:48:31,558 INFO sqlalchemy.engine.base.Engine.0x..90

... ['111', 7]

Note in particular that SQLAlchemy has inferred the minimum change necessary to update the relationship. Also note that SQLAlchemy allowed us to assign a normal Python list for a relation()-type property. This is in contrast to some other ORMs, which require you to use specialized add/remove functions to change object relationships. One caveat with SQLAlchemy is that you are still required to only use the remove() and append() list when using dynamic relation loaders (declared with dynamic_loader() or lazy='dynamic'). This is due to the fact that SQLAlchemy never implicitly loads the entire list of related objects into memory and so cannot deduce how to update the database if you use other methods of modifying the property.

Embedding SQL expressions in a flush

One feature that can be particularly useful in performing atomic updates to an object is the ability to assign an SQL expression (from the SQL expression language) to a mapped property on an object. For instance, consider a banking application where there is a need to deduct a certain amount from the balance. In many cases, it is unsafe and inefficient to SELECT the balance and then UPDATE it to the previous balance minus some amount. It would be better to simply deduct the amount atomically in one UPDATE statement. So, if we have the following (partial) schema and mapping:

account_table = Table(

'account', metadata,

Column('id', Integer, primary_key=True),

Column('balance', Numeric))

class Account(object): pass

mapper(Account, account_table)

we could deduct a certain amount from an account balance atomically by doing something like the following:

>>> # Create the table for testing purposes

>>> account_table.create()

2007-10-28 19:21:29,498 INFO sqlalchemy.engine.base.Engine.0x..90

CREATE TABLE account (

id INTEGER NOT NULL,

balance NUMERIC(10, 2),

PRIMARY KEY (id)

)

2007-10-28 19:21:29,498 INFO sqlalchemy.engine.base.Engine.0x..90 {}

2007-10-28 19:21:29,498 INFO sqlalchemy.engine.base.Engine.0x..90

... COMMIT

>>> # Create an account for testing purposes

>>> a = Account()

>>> a.balance = 100.00

>>> session.save(a)

>>> session.flush()

2007-10-28 19:21:29,581 INFO sqlalchemy.engine.base.Engine.0x..90

... INSERT INTO account (balance) VALUES (?)

2007-10-28 19:21:29,582 INFO sqlalchemy.engine.base.Engine.0x..90

... ['100.0']

>>>

>>> a.balance = Account.c.balance - 50.0

>>> session.flush()

2007-10-28 19:21:29,700 INFO sqlalchemy.engine.base.Engine.0x..90

... UPDATE account SET balance=(account.balance - ?) WHERE

... account.id = ?

2007-10-28 19:21:29,700 INFO sqlalchemy.engine.base.Engine.0x..90

... ['50.0', 1]

Deleting Objects from the Session

To delete an object from the session, simply use the Session’s delete() method:

>>> session.delete(p3)

>>> session.flush()

2007-10-28 18:58:51,150 INFO sqlalchemy.engine.base.Engine.0x..90

... DELETE FROM product_category WHERE product_category.product_id =

... ? AND product_category.category_id = ?

2007-10-28 18:58:51,150 INFO sqlalchemy.engine.base.Engine.0x..90

... [['111', 1], ['111', 3], ['111', 7]]

2007-10-28 18:58:51,152 INFO sqlalchemy.engine.base.Engine.0x..90

... DELETE FROM product WHERE product.sku = ?

2007-10-28 18:58:51,153 INFO sqlalchemy.engine.base.Engine.0x..90

... ['111']

Notice that SQLAlchemy automatically removed the corresponding entries in the product_category_table. This is because we declared that to be the secondary parameter of a many-to-many relation(). This is a special feature of M:N relations. In 1:N relations, unless you tell SQLAlchemy how to cascade a delete on the parent object, it will not assume that the delete should be cascaded. To cascade delete()s onto the child objects, simply specify cascade='delete' (or 'all') in the relation() function call.

Flushing, Committing, and Rolling Back Session Changes

We have already seen the basic usage of the flush() Session method. flush() can also take an optional parameter objects, which specifies a list of objects to be flushed. If this is omitted, all modified objects are flushed.

SQLAlchemy also provides support for managing transactions on a Session basis via the begin(), commit(), and rollback() methods, and via the transactional=True parameter to the Session constructor. begin() begins a transaction, commit() commits it, and rollback()rolls back to the state of the database at the last begin().

Specifying transactional=True lets SQLAlchemy know that all operations on this Session are intended to be in the context of a transaction, and so there is no need to issue an explicit begin(). SQLAlchemy also supports the use of SAVEPOINTs on supported databases (currently MySQL and PostgreSQL, soon to include Oracle) via the begin_nested() method. In this case, the commit() and rollback() methods apply only to the last “nested” transaction, so you can roll back “part” of a transaction.

Other Session Methods

Sessions have several utilities other than save() and delete() for dealing with objects that they manage. These methods, as well as save(), delete(), and a few query-related methods (covered in detail later in this chapter, in Querying at the ORM Level”), are documented here:

save(self, obj, entity=None)

Save the given object to the session. This operation cascades to related objects according to the 'save-update' cascade rule.

If an entity name is specified, then use the named nonprimary mapper() to persist the object.

delete(self, obj)

Mark the given object for deletion at the next flush().

expire(self, obj)

Mark the given object as no longer up-to-date. This causes any mapped attributes to be refetched from the database the next time they are accessed. This operation cascades to related objects according to the 'refresh-expire' cascade rule.

refresh(self, obj)

Reload the object from the database with a fresh query. This operation cascades to related objects according to the 'refresh-expire' cascade rule.

merge(self, obj, entity=None)

Copy the state of the given object onto a persistent object with the same database identity. This will either load an existing Persistent instance from the database, modify one in memory, or save a copy of the given obj. In none of these cases does the object passed in become associated with the Session. This operation cascades to related objects according to the 'merge' cascade rule.

If an entity name is specified, then use the named nonprimary mapper() to load or save the Persistent object.

expunge(self, obj)

Remove all references to obj from the Session. This operation cascades to related objects according to the 'expunge' cascade rule.

update(self, obj, entity=None)

Bring a given Detached obj into this session. This operation cascades to related objects according to the 'save-or-update' cascade rule.

If an entity name is specified, then use the named nonprimary mapper() to load or save the Detached object.

get(self, class_, ident, **kwargs)

Return a Persistent instance of the object with the given class_ and identifier. (An object identifier is either the primary key value if there is only one primary key in the underlying table, or a tuple of primary keys in the case of a composite primary key.) If an entity_name is specified as part of kwargs, then use the named nonprimary mapper to map the class. The other kwargs are passed unchanged to the underlying query() used to retrieve the object.

load(self, class_, ident, **kwargs)

This is the same as the get() method with one exception: if the object was already in the Session, the session will overwrite any pending changes with fresh values from the database.

query(self, mapper_or_class, *addtl_entities, **kwargs)

Return a new Query object corresponding to this Session and the given mapper_or_class.

close(self)

Clear the session and end any transactions in progress. This restores the Session object to a “pristine” state, exactly the same as when it was initially instantiated.

execute(self, clause, params=None, mapper=None, **kwargs)

This method is a thin wrapper around the underlying engine or connection’s execute() method. (The clause, params, and kwargs parameters are passed through unmodified, for instance.) It is useful for executing SQL-level queries and updates within the same transactional environment as your ORM queries and updates. If the mapper parameter is specified, that mapper is used to determine the engine on which to execute the query.

identity_map

The identity mapping between (class,identity) tuples and objects in the session. Note that Persistent objects have an _instance_key attribute attached to them, which is their Session identity.

new

A collection of all Pending objects added to the Session since the last flush().

dirty

A collection of all Persistent objects that have changes detected.

deleted

A collection of all Persistent objects that have been marked for deletion via the Session delete() method.

Extending Sessions

Similar to the MapperExtension covered in Chapter 6, SessionExtensions can be used to hook into session operations. Unlike MapperExtensions, SessionExtensions cannot modify the process that they “hook into” easily, making SessionExtensions more useful for recordingSession operations than influencing them directly. SessionExtensions are installed via the extension parameter to the Session constructor.

The various methods that a subclass of SessionExtension can implement are described here:

before_commit(self, session)

Called just before a commit is executed.

after_commit(self, session)

Called just after a commit is executed.

after_rollback(self, session)

Called just after a rollback has occurred.

before_flush(self, session, flush_context, objects)

Called just before the flush process starts. The objects parameter is the optional list of objects passed to the Session’s flush() method.

after_flush(self, session, flush_context)

Called just after the flush process completes, but before any commit(). The session’s properties at this point still show their pre-flush state.

after_flush_postexec(self, session, flush_context)

Called just after the flush process completes, as well as after any automatic commit() occurs. (If no explicit transaction is specified, all flush()es generate their own transactions.) The session’s properties at this point show their final, post-flush state.

Querying at the ORM Level

Saving and updating objects via SQLAlchemy’s ORM interface isn’t very useful without the ability to retrieve objects from the database. This is where the Session’s query() method comes in handy. To retrieve an iterator over all the objects of a particular type in the database, simply specify either the class you wish to query or its mapper:

>>> query = session.query(Product)

>>> print query

SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product ORDER BY product.oid

>>> for obj in query:

... print obj

...

2007-11-16 16:19:42,669 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product ORDER BY product.oid

2007-11-16 16:19:42,669 INFO sqlalchemy.engine.base.Engine.0x..90 []

<Product 123>

<Product 456>

<Product 222>

Notice here that the query is generative, as were the SQL-layer queries mentioned in Chapter 5. This means that SQLAlchemy will not actually execute the query on the database until the results are iterated over. You can also retrieve all the results as a list by calling the all() method on the query object:

>>> query.all()

2007-11-16 16:21:35,349 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product ORDER BY product.oid

2007-11-16 16:21:35,349 INFO sqlalchemy.engine.base.Engine.0x..90 []

[<Product 123>, <Product 456>, <Product 222>]

Because retrieving the entire collection of mapped objects isn’t very useful, SQLAlchemy provides various methods to modify the query object. Note that all of these methods actually generate and return a new query object rather than modifying the existing query object. The most useful of these methods are filter() and filter_by(). These methods work, as their names imply, by restricting the set of objects returned from the query. For instance, to retrieve all the products with an MSRP between $10 and $20, we could use filter() as follows:

>>> session.bind.echo = False

>>> query = query.filter(Product.msrp > 10.00)

>>> query = query.filter(Product.msrp < 20.00)

>>> for product in query:

... print product.sku, product.msrp

...

123 11.22

222 15.95

Note that we can use mapped properties just like column objects in SQL expressions. SQLAlchemy also provides access to the c attribute (and all the attached columns) from the mapper’s underlying selectable. In addition to this, SQLAlchemy provides a number of methods on mapped properties to facilitate the construction of complex queries. Some of these methods are summarized in the following lists.

The following are methods on mapped columns:

asc( self )

Return a clause representing the mapped column in ascending order.

between(self, cleft, cright)

Generate a BETWEEN clause with the specified left and right values (column BETWEEN cleft AND cright).

concat(self, other)

Generate a clause that concatenates the value of the column with the value given.

desc( self )

Generate a clause representing the mapped column in ascending order.

distinct( self )

Generate a clause that limits the result set to rows with distinct values for this column.

endswith(self, other)

Generate a clause (using LIKE) that implements the Python endswith() string method.

in_(self, other)

Generate an IN clause with other as the righthand side. other may be either a sequence of literal values or a selectable.

like(self, other)

Generate a LIKE clause with other as the righthand side.

startswith(self, other)

Generate a clause (using LIKE) that implements the Python startswith() string method.

The following are methods on mapped relations:

any(self, criterion=None, **kwargs)

Generate a clause that will be true if any of the related objects satisfy the given criterion. A filter_by()-style criterion (a conjunction of equality constraints) is generated if kwargs is nonempty.

contains(self, other)

Generate a clause that will be true if the specified object is in the list of related objects.

has(self, criterion=None, **kwargs)

For scalar-style relations, generate a clause that will be true if the related object satisfies the given criterion. A filter_by()-style criterion (a conjunction of equality constraints) is generated if kwargs is nonempty.

The filter() method, in fact, takes any valid SQL expression, allowing you to build up complex queries fairly simply. Also note that the two filters were applied as a conjunction: both criteria had to be satisfied to produce an object.

The filter_by() method allows more convenient filtering when the filter criteria are all equality constraints. For instance, to retrieve the products with an MSRP of $11.22, we could use the following query:

>>> query = session.query(Product)

>>> query = query.filter_by(msrp=11.22)

>>> print query.all()

[<Product 123>]

Note that we now specify the filter criteria as keyword arguments to filter_by(). The query then searches for mapped properties with the given name and applies appropriate filtering to the returned query.

The SQLAlchemy Query object also supports applying offsetting and limiting to a query via the offset() and limit() methods, as well as the slicing operator:

>>> query = session.query(Product)

>>> print query.offset(2)

SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product ORDER BY product.oid

LIMIT -1 OFFSET 2

>>> print query.limit(3)

SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product ORDER BY product.oid

LIMIT 3 OFFSET 0

>>> print query[1:2]

SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product ORDER BY product.oid

LIMIT 1 OFFSET 1

In many cases, we want to retrieve only one object from the database. The Query object provides three different ways to do this:

get( ident )

Retrieve an object by its identity (the primary key of the mapped selectable). If there is no object identified by that key, return None. get() is also available as a method on the Session object.

first()

Retrieve the first result from the query. If there are no results, return None. This is equivalent to query.all()[0].

one()

Retrieve the first result from the query, raising an exception unless the query returns exactly one result. This is implemented by executing the query with a limit of 2. If either 0 or 2 rows are returned, an exception is raised. Otherwise, the single object is returned.

ORM Querying with Joins

The true power of the SQLAlchemy ORM query system is really only realized when using it to join across the relations defined in the mapper() configuration. Joins can be performed across mapped properties by using the join() method on the Query object. Once a new class has been joined to the query, all its properties are available for use in the filter() and filter_by() methods:

>>> query = session.query(Product)

>>> query = query.join('categories')

>>> query = query.filter_by(name='T-Shirts')

>>> print query.all()

[<Product 222>]

>>> print query

SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product JOIN product_category ON product.sku =

... product_category.product_id JOIN category ON category.id =

... product_category.category_id

WHERE category.name = ? ORDER BY product.oid

SQLAlchemy also allows you to join across multiple property “hops.” For instance, if we wish to see all the products with some categorization under the “Class” level, we could do the following:

>>> query = session.query(Product)

>>> query = query.join(['categories', 'level'])

>>> query = query.filter_by(name='Class')

>>> print query

SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product JOIN product_category ON product.sku =

... product_category.product_id JOIN category ON category.id =

... product_category.category_id JOIN level ON level.id =

... category.level_id

WHERE level.name = ? ORDER BY product.oid

>>> print query.all()

[<Product 222>]

Note that filter_by() used the Level’s name property, rather than the Category’s name property, when performing the filter. SQLAlchemy keeps track of a “joinpoint,” the last class referenced in an ORM join, and applies any filter_by() criteria to that joinpoint until the joinpoint changes. To manually reset the joinpoint to the “root” class, simply call the reset_joinpoint() method.

Any new join() calls will also reset the joinpoint to the root of the query. To disable this behavior (and continue joining from the current joinpoint), simply specify from_joinpoint=True in the call to join().

As you may have noticed, the join() method constructs inner joins. SQLAlchemy also provides an outerjoin() method for constructing left outer joins. So, if we wanted to get a list of all products that have no “Class” categorization or have a “Class” of “Pants,” we could execute the following query:

>>> query = session.query(Product)

>>> query = query.outerjoin('categories')

>>> query = query.filter(or_(Category.c.name=='Pants',

... Category.c.name==None))

>>> print query

SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product LEFT OUTER JOIN product_category ON product.sku =

... product_category.product_id LEFT OUTER JOIN category ON

... category.id = product_category.category_id

WHERE category.name = ? OR category.name IS NULL ORDER BY

... product.oid

>>> print query.all()

[<Product 123>, <Product 456>]

When constructing complex queries using joins, it is often useful to join to the same table twice. In this case, we can specify that the join() method use an alias for the table being joined:

>>> query = session.query(Product)

>>> query = query.join('categories')

>>> query = query.filter_by(name='T-Shirts')

>>> query = query.join('categories', aliased=True)

>>> query = query.filter_by(name='Shirts')

>>> print query

SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product JOIN product_category ON product.sku =

... product_category.product_id JOIN category ON category.id =

... product_category.category_id JOIN product_category AS

... product_category_1 ON product.sku =

... product_category_1.product_id JOIN category AS category_2 ON

... category_2.id = product_category_1.category_id

WHERE category.name = ? AND category_2.name = ? ORDER BY product.oid

>>> print query.all()

[<Product 222>]

One of the more powerful features of the SQLAlchemy ORM is that it allows properties to be defined as either “lazily” loaded or “eagerly” loaded (via the lazy parameter to the relation() function). It is often useful, however, to customize the load strategy of various properties on a query-by-query basis. To facilitate this, SQLAlchemy provides the options() method on the Query object and various functions, including eagerload(name), lazyload(name), and eagerload_all(name) to customize the loading strategy of relations on a query-by-query basis.eagerload() and lazyload() each change the default loading strategy for the named property. eagerload_all() makes an entire “property chain” eager-loaded.

For instance, suppose we are generating a table of all the products in the system, along with their categorization and the level name. If we use the default lazy loading approach, we will execute one query per object to read its categories and one query per category to read its levels:

>>> session.bind.echo=True

>>> query = session.query(Product)

>>> session.clear()

>>> for prod in query:

... print prod.sku, prod.categories

...

2007-11-16 17:30:08,356 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product ORDER BY product.oid

2007-11-16 17:30:08,357 INFO sqlalchemy.engine.base.Engine.0x..90 []

1232007-11-16 17:30:08,360 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT category.id AS category_id, category.level_id AS

... category_level_id, category.parent_id AS category_parent_id,

... category.name AS category_name

FROM category, product_category

WHERE ? = product_category.product_id AND category.id =

... product_category.category_id ORDER BY product_category.oid

2007-11-16 17:30:08,361 INFO sqlalchemy.engine.base.Engine.0x..90

... [u'123']

[]

4562007-11-16 17:30:08,364 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT category.id AS category_id, category.level_id AS

... category_level_id, category.parent_id AS category_parent_id,

... category.name AS category_name

FROM category, product_category

WHERE ? = product_category.product_id AND category.id =

... product_category.category_id ORDER BY product_category.oid

2007-11-16 17:30:08,365 INFO sqlalchemy.engine.base.Engine.0x..90

... [u'456']

[]

2222007-11-16 17:30:08,367 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT category.id AS category_id, category.level_id AS

... category_level_id, category.parent_id AS category_parent_id,

... category.name AS category_name

FROM category, product_category

WHERE ? = product_category.product_id AND category.id =

... product_category.category_id ORDER BY product_category.oid

2007-11-16 17:30:08,368 INFO sqlalchemy.engine.base.Engine.0x..90

... [u'222']

2007-11-16 17:30:08,371 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT level.id AS level_id, level.parent_id AS level_parent_id,

... level.name AS level_name

FROM level

WHERE level.id = ? ORDER BY level.oid

2007-11-16 17:30:08,371 INFO sqlalchemy.engine.base.Engine.0x..90

... [1]

2007-11-16 17:30:08,373 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT level.id AS level_id, level.parent_id AS level_parent_id,

... level.name AS level_name

FROM level

WHERE level.id = ? ORDER BY level.oid

2007-11-16 17:30:08,374 INFO sqlalchemy.engine.base.Engine.0x..90

... [2]

2007-11-16 17:30:08,380 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT level.id AS level_id, level.parent_id AS level_parent_id,

... level.name AS level_name

FROM level

WHERE level.id = ? ORDER BY level.oid

2007-11-16 17:30:08,381 INFO sqlalchemy.engine.base.Engine.0x..90

... [3]

[<Category Department.Tops>, <Category Class.Shirts>, <Category

... SubClass.T-Shirts>]

If we eagerly load the categories property, however, we execute only a single query:

>>> session.clear()

>>> query = session.query(Product)

>>> query = query.options(eagerload_all('categories.level'))

>>> for prod in query:

... print prod.sku, prod.categories

...

2007-11-16 17:30:09,392 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT category_1.id AS category_1_id, category_1.level_id AS

... category_1_level_id, category_1.parent_id AS

... category_1_parent_id, category_1.name AS category_1_name,

... level_2.id AS level_2_id, level_2.parent_id AS

... level_2_parent_id, level_2.name AS level_2_name, product.sku AS

... product_sku, product.msrp AS product_msrp

FROM product LEFT OUTER JOIN product_category AS product_category_3

... ON product.sku = product_category_3.product_id LEFT OUTER JOIN

... category AS category_1 ON category_1.id =

... product_category_3.category_id LEFT OUTER JOIN level AS level_2

... ON level_2.id = category_1.level_id ORDER BY product.oid,

... product_category_3.oid, level_2.oid

2007-11-16 17:30:09,393 INFO sqlalchemy.engine.base.Engine.0x..90 []

123 []

456 []

222 [<Category Department.Tops>, <Category Class.Shirts>, <Category

... SubClass.T-Shirts>]

The options() method can also be used with a variety of other options. Notice how the eager/lazy loading can also be specified on the mapper itself. From SQLAlchemy’s point of view, the options() method is changing the view of the mapper that the query is based on. Thus other options can be specified that “morph” the mapper as well. These options are summarized here:

extension( ext )

Add the MapperExtension ext into the beginning of the list of extensions that will be called in the context of the query.

eagerload( name )

Set the load strategy on the named relation() property to be eager (equivalent to specifying lazy=False in the mapper() call). For mapped column properties, use undefer() instead.

eagerload_all( name )

name is a string containing a list of dot-separated names that represent a chain of relation() properties to be eager loaded. For mapped column properties, use undefer() instead.

lazyload( name )

Set the load strategy on the named relation() property to be lazy (equivalent to specifying lazy=True in the mapper() call). For mapped column properties, use defer() instead.

noload( name )

Set the load strategy on the named property to be nonloading (equivalent to specifying lazy=None in the mapper() calls).

contains_alias( alias )

Indicates to the query that the main table in the underlying select statement has been aliased to the given alias (which is a string or Alias object).

contains_eager(key, alias=None, decorator=None)

Indicates that an attribute (the key parameter) will be eagerly loaded. This is used in conjunction with feeding SQL result sets directly into the instances() method on queries (covered next in Customizing the Select Statement in ORM Queries”). The alias parameter is the alias (either a string or an Alias object) representing aliased columns in the query. The decorator parameter, mutually exclusive of alias, is a function used to preprocess rows before passing them to the eager-loading handler. This can be used to do arbitrary processing on the row before it passes to the eager loader.

defer( name )

Convert the named column property into a deferred column (lazily loaded). For relation()s, use lazyload() instead.

undefer( name )

Convert the named column property into a deferred column (eagerly loaded). For relation()s, use eagerload() or eagerload_all() instead.

undefer_group( name )

Convert the named deferred group of column properties into an undeferred group.

Note that the addition of the eagerload_all() option (and all other options) is completely transparent; the only difference in the code that uses the results of such a query is in its performance.

Customizing the Select Statement in ORM Queries

Although SQLAlchemy is quite flexible in the types of queries it can generate at the ORM level, it is sometimes necessary to either modify the generated query or to even replace it entirely while still generating SQLAlchemy ORM objects. One of the simplest query modifications is replacing the underlying selectable using the select_from() method. For instance, if we wish to manually perform some joins and then select from the joined table, we can do so as follows:

>>> joined_product = product_table.join(product_category_table)

>>> joined_product = joined_product.join(category_table)

>>> query = session.query(Product).select_from(joined_product)

>>> query = query.filter(category_table.c.name=='T-Shirts')

>>> print query

SELECT product.sku AS product_sku, product.msrp AS product_msrp

FROM product JOIN product_category ON product.sku =

... product_category.product_id JOIN category ON category.id =

... product_category.category_id

WHERE category.name = ? ORDER BY product.oid

>>> print query.all()

[<Product 222>]

If we wish to completely replace the SQL underlying the query object, we can do so with the from_statement() method. When using from_statement(), it’s important to make certain that all the necessary columns are returned by the underlying query. If a mapped column is omitted, then the mapped property will be set to None:

>>> session.clear()

>>> stmt = select([product_table.c.sku])

>>> query = session.query(Product).from_statement(stmt)

>>> for prod in query:

... print prod, prod.msrp

...

<Product 123> None

<Product 456> None

<Product 222> None

Using from_statement() also interferes with SQLAlchemy’s eager-loading mechanism because SQLAlchemy has no way of tacking on its LEFT OUTER JOINs to retrieve the eagerly loaded objects. To support this condition, SQLAlchemy provides the contains_eager() mapper option, which allows you to make SQLAlchemy aware of the LEFT OUTER JOINs that have already been added to the underlying SQL:

>>> session.clear()

>>> joined_product = product_table.outerjoin(product_category_table)

>>> joined_product = joined_product.outerjoin(category_table)

>>> stmt = select([product_table, category_table],

... from_obj=[joined_product])

>>> query = session.query(Product).from_statement(stmt)

>>> query = query.options(contains_eager('categories'))

>>> session.bind.echo = True

>>> for prod in query:

... print prod, [c.name for c in prod.categories ]

...

2007-11-17 09:52:13,730 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT product.sku AS product_sku, product.msrp AS product_msrp,

... category.id AS category_id, category.level_id AS

... category_level_id, category.parent_id AS category_parent_id,

... category.name AS category_name

FROM product LEFT OUTER JOIN product_category ON product.sku =

... product_category.product_id LEFT OUTER JOIN category ON

... category.id = product_category.category_id LEFT OUTER JOIN level

... ON level.id = category.level_id

2007-11-17 09:52:13,731 INFO sqlalchemy.engine.base.Engine.0x..90 []

<Product 123> []

<Product 456> []

<Product 222> [u'Tops', u'Shirts', u'T-Shirts']

It is also possible to eagerly load where the LEFT OUTER JOIN is with an alias. In this case, simply supply the alias (either as a string or as an Alias object) to the contains_eager() alias parameter:

>>> session.clear()

>>> alias = category_table.alias('cat1')

>>> joined_product = product_table.outerjoin(product_category_table)

>>> joined_product = joined_product.outerjoin(alias)

>>> stmt = select([product_table, alias],

... from_obj=[joined_product])

>>> query = session.query(Product).from_statement(stmt)

>>> query = query.options(contains_eager('categories',

... alias='cat1'))

>>> session.bind.echo = True

>>> for prod in query:

... print prod, [c.name for c in prod.categories ]

...

2008-01-27 19:51:55,567 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT product.sku AS product_sku, product.msrp AS product_msrp,

... cat1.id AS cat1_id, cat1.level_id AS cat1_level_id,

... cat1.parent_id AS cat1_parent_id, cat1.name AS cat1_name

FROM product LEFT OUTER JOIN product_category ON product.sku =

... product_category.product_id LEFT OUTER JOIN category AS cat1 ON

... cat1.id = product_category.category_id

2008-01-27 19:51:55,567 INFO sqlalchemy.engine.base.Engine.0x..90 []

<Product 123> []

<Product 456> []

<Product 222> [u'Tops', u'Shirts', u'T-Shirts']

SQLAlchemy also supports creating objects from SQL where the main table is aliased to another name. In this case, you must use the contains_alias() mapper option. Again, you can pass either a string name of the alias or the Alias object itself:

>>> alias = product_table.alias()

>>> stmt = alias.select()

>>> query = session.query(Product).from_statement(stmt)

>>> query = query.options(contains_alias(alias))

>>> print query.all()

[<Product 123>, <Product 456>, <Product 222>]

We can also use the from_statement() method with string-based queries. In this case, it is a good idea to use bind parameters for performance and to avoid SQL injection attacks. Bind parameters for SQLAlchemy are always specified using the :name notation, and they are bound to particular values using the params() method of the Query object:

>>> query = session.query(Product)

>>> query = query.from_statement('SELECT * FROM product WHERE

... sku=:sku')

>>> query = query.params(sku='123')

>>> print query.all()

[<Product 123>]

Up until now, we have been using the Query object to generate a sequence of mapped objects. In some cases, we may want a query to retrieve several objects per “row,” where the objects retrieved may either be fully mapped ORM objects or simple SQL columns. SQLAlchemy supports this via the add_entity() and add_column() Query methods:

>>> query = session.query(Product)

>>> query = query.add_entity(Category)

>>> query =

... query.filter(Product.sku==product_category_table.c.product_id)

>>> query =

... query.filter(Category.id==product_category_table.c.category_id)

>>> for row in query:

... print row

...

(<Product 222>, <Category Department.Tops>)

(<Product 222>, <Category Class.Shirts>)

(<Product 222>, <Category SubClass.T-Shirts>)

>>> query = query.add_column(category_table.c.level_id)

>>> for row in query:

... print row

...

(<Product 222>, <Category Department.Tops>, 1)

(<Product 222>, <Category Class.Shirts>, 2)

(<Product 222>, <Category SubClass.T-Shirts>, 3)

If you know a priori what objects you wish to construct, you can create the query initially with this knowledge, rather than using the add_entity() method:

>>> query = session.query(Product, Category)

>>> query =

... query.filter(Product.sku==product_category_table.c.product_id)

>>> query =

... query.filter(Category.id==product_category_table.c.category_id)

>>> for row in query:

... print row

...

(<Product 222>, <Category Department.Tops>)

(<Product 222>, <Category Class.Shirts>)

(<Product 222>, <Category SubClass.T-Shirts>)

Other Query Methods

The Query object has a number of other methods that allow great flexibility. Some useful Query methods are summarized here:

add_column(self, column, id=None)

Add the named column to the query, making the query return a tuple including the named column. id, if supplied, specifies that the column will be correlated with the id parameter given to a matching join() or outerjoin() method.

add_entity(self, entity, alias=None, id=None)

Add a class or mapper to the query, making the query return a tuple including the given entity. If alias is supplied, the entity will be aliased using the given alias. If id is supplied, the entity will be selected from the join() or outerjoin() in the query with a matching id parameter.

all( self )

Retrieve a list of results from the query (simply returns list(self)).

autoflush(self, setting)

Sets the autoflushing behavior of the query (True or False). If the query is autoflushing, the session will be flushed before the query is executed, guaranteeing that in-memory objects are consistent with query results. The default autoflush behavior of the query is inherited from the session.

apply_avg(self, col)

Apply the SQL AVG() function to the given column and return the resulting query.

apply_max(self, col)

Apply the SQL MAX() function to the given column and return the resulting query.

apply_min(self, col)

Apply the SQL MIN() function to the given column and return the resulting query.

apply_sum(self, col)

Apply the SQL SUM() function to the given column and return the resulting query.

avg(self, col)

Execute the SQL AVG() function against the given column and return the result.

count( self )

Execute the SQL COUNT() function against this query and return the result. (count() takes other parameters, but they are deprecated in SQLAlchemy 0.4.)

distinct( self )

Apply a SQL DISTINCT modifier to the query and return the resulting query.

filter(self, criterion)

Apply the given SQL filtering criterion to the query and return the resulting query. All filters are conjoined (combined using the SQL AND operator).

filter_by(self, **kwargs)

Apply equality filtering criteria to the query and return the result. The criteria are constructed based on the name, value pairs supplied to the kwargs parameter.

first( self )

Execute the query and return the first result, or None if the query has no results.

from_statement(self, statement)

Replace the underlying statement used by the query with the given statement, which may be either a string of SQL or a query constructed using the SQL expression language.

get(self, ident, reload=False, lockmode=None)

Retrieve an object based on the given identity from the session. If the object is not currently loaded in the session, it will be loaded. If reload is True, the object will be refreshed, regardless of whether it is in the session. If lockmode is specified, the object will be loaded with the givenlockmode. The locking mode is based around the idea of SELECT...FOR UPDATE and related constructs. The lockmode value is inserted after the FOR keyword.

group_by(self, criterion)

Apply a SQL GROUP BY clause to the query and return the resulting query. This is generally useful in ORM queries only when you are grouping by the main class and aggregating over some related class. For instance, if a Product had many Recommendations, you might group by the product’s sku and add a having() clause to return products with three or more recommendations.

having(self, criterion)

Apply a SQL HAVING clause to the query and return the resulting query.

instances(self, cursor)

Return a list of mapped instances corresponding to rows in the given cursor (generally a ResultProxy). instances() takes other parameters, but they are deprecated in SQLAlchemy 0.4.

join(self, prop, id=None, aliased=False, from_joinpoint=False)

Create a join of this query based on a mapped property prop and return the resulting query. prop can be either a string property name or a list of string property names specifying a join path. If id is specified, it should be a string for use in matching add_column() or add_entity⁠(⁠⁠) idparameters. If aliased is True, the joined entity will be aliased in the underlying query. If from_joinpoint is True, the join will be from the last-joined entity. Otherwise, it will be from the “root” entity of the query. This method is typically used to add a filter based on some related class.

limit(self, limit)

Apply a LIMIT modifier to the query and return the resulting query. Note that SQLAlchemy generates appropriate SQL to make the LIMIT apply to the objects generated, not the rows. This is done to return the specified number of objects even in the presence of JOINs.

load(self, ident, raiseerr=True, lockmode=None)

Return an instance of the object based on the given ident, refreshing the object from the database. This is similar to get() with reload=True, but will raise an error if the object is not found in the database.

max(self, col)

Execute the SQL MAX() function against the given column and return the result.

min(self, col)

Execute the SQL MIN() function against the given column and return the result.

offset(self, offset)

Apply an OFFSET modifier to the query and return the resulting query. Note that SQLAlchemy generates appropriate SQL to make the OFFSET apply to the objects generated, not the rows, in order to skip the specified number of objects even in the presence of JOINs.

one( self )

Return the first result of the query, raising an exception if the query does not return exactly one result.

options(self, *args)

Return a new query with the mapper options (such as eagerload(), etc.) listed in args applied.

order_by(self, criterion)

Apply a SQL ORDER BY modifier to the query and return the resulting query.

outerjoin(self, prop, id=None, aliased=False, from_joinpoint=False)

Create a LEFT OUTER JOIN of this query based on a mapped property prop and return the resulting query. prop can be either a string property name or a list of string property names specifying a join path. If id is specified, it should be a string for use in matching add_column() oradd_entity⁠(⁠⁠) id parameters. If aliased is True, the joined entity will be aliased in the underlying query. If from_joinpoint is True, the join will be from the last-joined entity. Otherwise, it will be from the “root” entity of the query. This method is typically used to add a filter based on some related class.

params(self, *args, **kwargs)

Add values for bind parameters that exist in the underlying query. The binding dictionary may be passed as keyword arguments or as a dict in the first positional argument.

populate_existing( self )

Return a query that will refresh all objects loaded. Normally, when a query executes, it will not modify any objects already in memory. This option changes that behavior.

query_from_parent(cls, instance, property, **kwargs) (classmethod)

Create a new Query object that returns objects with a relationship to a given object instance through the named property. The kwargs are passed along unmodified to the Query constructor. This is mainly used internally to SQLAlchemy, to construct queries for lazily loaded properties.

reset_joinpoint( self )

Reset the joinpoint of the query to the “root” mapper. This affects subsequent calls to filter_by() and possibly to join() and outerjoin().

sum(self, col)

Execute the SQL SUM() function against the given column and return the result.

with_lockmode(self, mode)

Return a new Query object using the specified locking mode.

with_parent(self, instance, property=None)

Add a join criterion based on a relationship to a mapped instance via the named property. If property is not supplied, SQLAlchemy attempts to infer an appropriate property.

__getitem__(self, item) (indexing)

If item is a slice object, apply appropriate OFFSET and LIMIT modifers to the query to emulate the Python slicing operation. If item is an integer, apply an appropriate OFFSET with a LIMIT of 1, execute the query, and return the result.

__iter__(self)(iteration)

Returns an iterator that will build mapped objects from the query.

Contextual or Thread-Local Sessions

Although the SQLAlchemy ORM is extremely flexible and powerful, it can be somewhat repetitive in some cases. One of these cases is constructing the Session object. Fortunately, SQLAlchemy provides the ability to manage sessions for you in such a way that a Session object can be shared among various parts of your application without explicitly passing it around as a parameter. This is useful in web frameworks in particular, where you generally want all the code servicing a given web request to use the same Session object. SQLAlchemy achieves implicit Sessionobject sharing via “contextual” sessions.

The idea of a contextual session is that there is one session that is available in a given “context,” where the default context is the thread. When you need a session, rather than constructing one yourself, you simply ask SQLAlchemy for the session that is appropriate to the current context. You can generate a contextual Session object by using the scoped_session() function:

>>> Session = scoped_session(sessionmaker(

... bind=engine, autoflush=True, transactional=True))

>>>

>>> session = Session()

>>> session2 = Session()

>>> session is session2

As mentioned earlier, the default context is the current thread. To override this and supply a different context, simply pass a scopefunc parameter to the scoped_session() function. scopefunc should be a callable that returns a key that uniquely identifies the context. By default, thescopefunc is the get_ident() function from the thread module.

The contextual Session class also supplies class methods for all the Session instance methods. These class methods simply proxy to the contextual Session object. This means that we can use scoped_session() to declare the contextual Session class globally and use it anywhere we would normally need a Session object, without explicitly constructing the Session object. So, if we want to save a new Product to the contextual Session object, we can simply save it to the (globally declared) contextual Session class:

>>> prod = Product(sku='333', msrp=44.55)

>>> Session.save(prod)

>>> Session.flush()

To use contextual sessions effectively, they must be periodically “cleared out” of the objects they manage, or else they will grow beyond all reasonable bounds. In the context of a web framework, for instance, the contextual session should be cleared between requests. This can be accomplished by using either the close() method, which frees all resources maintained by the contextual session, or the remove() method, which actually removes the session from the current context altogether. close() should be used when the current context is “permanent,” as in web servers that use a never-shrinking pool of threads to handle requests. remove() should be used if the context may “go away,” as the session object will be “leaked” if the context is not reused. This is the appropriate choice in web frameworks, which may stop threads that previously handled requests.

Using Contextual Sessions with Mappers and Classes

The contextual session allows us to dispense with explicit references to sessions in many cases by instrumenting our mapped classes with a query() and modifying the mapped class’s constructor to automatically save() it to the session when it is created. This very nice feature is accomplished by using the contextual Session’s mapper() method rather than the mapper() function when defining our object mappers. So, where previously our mappers were declared as follows:

mapper(Product, product_table, properties=dict(

categories=relation(Category, secondary=product_category_table,

backref='products')))

mapper(Level, level_table, properties=dict(

children=relation(Level, backref='parent'),

categories=relation(Category, backref='level')))

mapper(Category, category_table, properties=dict(

children=relation(Category, backref='parent')))

we can now declare them like this (assuming that Session has already been declared globally as a contextual Session):

Session.mapper(Product, product_table, properties=dict(

categories=relation(Category, secondary=product_category_table,

backref='products')))

Session.mapper(Level, level_table, properties=dict(

children=relation(Level, backref='parent'),

categories=relation(Category, backref='level')))

Session.mapper(Category, category_table, properties=dict(

children=relation(Category, backref='parent')))

Once we have mapped the classes as shown, we can use the mapped classes themselves to perform session-like functions:

>>> Product.query().all()

[<Product 123>, <Product 456>, <Product 222>, <Product 333>]

>>> prod = Product('444', msrp=55.66)

>>> Product.query().all()

[<Product 123>, <Product 456>, <Product 222>, <Product 333>,

... <Product 444>]

Using the contextual session mapper() method also gives us one other benefit: a reasonably usable default constructor. This constructor allows us to provide values for any of the properties defined in the mapped class via keyword arguments. So, if we omitted the Product constructor and used Session.mapper() to map it, we could initialize products as follows:

>>> p = Product(sku='555', msrp=22.11)