Querying

The Query Object

Query is produced in terms of a given Session, using the query() function:

q = session.query(SomeMappedClass)

Following is the full interface for the Query object.

class sqlalchemy.orm.query.Query(entities, session=None)

ORM-level SQL construction object.

__init__(entities, session=None)

add_column(column)

Add a column expression to the list of result columns

Deprecated. to be returned.

add_columns(*column)

Add one or more column expressions to the list of result columns to be returned.

add_entity(entity, alias=None)

add a mapped entity to the list of result columns to be returned.

all()

Return the results represented by this Query as a list.

This results in an execution of the underlying query.

autoflush(setting)

Return a Query with a specific ‘autoflush’ setting.

Note that a Session with autoflush=False will not autoflush, even if this flag is set to True at the Query level. Therefore this flag is usually used only to disable autoflush for a specific Query.

correlate(*args)

count()

Return a count of rows this Query would return.

For simple entity queries, count() issues a SELECT COUNT, and will specifically count the primary key column of the first entity only. If the query uses LIMIT, OFFSET, or DISTINCT, count() will wrap the statement generated by this Query in a subquery, from which a SELECT COUNT is issued, so that the contract of “how many rows would be returned?” is honored.

For queries that request specific columns or expressions, count() again makes no assumptions about those expressions and will wrap everything in a subquery. Therefore, Query.count() is usually not what you want in this case. To count specific columns, often in conjunction with GROUP BY, use func.count() as an individual column expression instead of Query.count(). See the ORM tutorial for an example.

delete(synchronize_session='evaluate')

Perform a bulk delete query.

Deletes rows matched by this query from the database.

Parameter:synchronize_session –

chooses the strategy for the removal of matched objects from the session. Valid values are:

False - don’t synchronize the session. This option is the most efficient and is reliable once the session is expired, which typically occurs after a commit(), or explicitly using expire_all(). Before the expiration, objects may still remain in the session which were in fact deleted which can lead to confusing results if they are accessed via get() or already loaded collections.

‘fetch’ - performs a select query before the delete to find objects that are matched by the delete query and need to be removed from the session. Matched objects are removed from the session.

‘evaluate’ - Evaluate the query’s criteria in Python straight on the objects in the session. If evaluation of the criteria isn’t implemented, an error is raised. In that case you probably want to use the ‘fetch’ strategy as a fallback.

The expression evaluator currently doesn’t account for differing string collations between the database and Python.

Returns the number of rows deleted, excluding any cascades.

The method does not offer in-Python cascading of relationships - it is assumed that ON DELETE CASCADE is configured for any foreign key references which require it. The Session needs to be expired (occurs automatically after commit(), or call expire_all()) in order for the state of dependent objects subject to delete or delete-orphan cascade to be correctly represented.

Also, the before_delete() and after_delete() MapperExtension methods are not called from this method. For a delete hook here, use the after_bulk_delete() MapperExtension method.

distinct()

Apply a DISTINCT to the query and return the newly resulting Query.

enable_assertions(value)

Control whether assertions are generated.

When set to False, the returned Query will not assert its state before certain operations, including that LIMIT/OFFSET has not been applied when filter() is called, no criterion exists when get() is called, and no “from_statement()” exists when filter()/order_by()/group_by() etc. is called. This more permissive mode is used by custom Query subclasses to specify criterion or other modifiers outside of the usual usage patterns.

Care should be taken to ensure that the usage pattern is even possible. A statement applied by from_statement() will override any criterion set by filter() or order_by(), for example.

enable_eagerloads(value)

Control whether or not eager joins and subqueries are rendered.

When set to False, the returned Query will not render eager joins regardless of joinedload(), subqueryload() options or mapper-level lazy='joined'/lazy='subquery' configurations.

This is used primarily when nesting the Query’s statement into a subquery or other selectable.

except_(*q)

Produce an EXCEPT of this Query against one or more queries.

Works the same way as union(). See that method for usage examples.

except_all(*q)

Produce an EXCEPT ALL of this Query against one or more queries.

Works the same way as union(). See that method for usage examples.

execution_options(**kwargs)

Set non-SQL options which take effect during execution.

The options are the same as those accepted by sqlalchemy.sql.expression.Executable.execution_options().

Note that the stream_results execution option is enabled automatically if the yield_per() method is used.

filter(criterion)

apply the given filtering criterion to the query and return the newly resulting Query

the criterion is any sql.ClauseElement applicable to the WHERE clause of a select.

filter_by(**kwargs)

apply the given filtering criterion to the query and return the newly resulting Query.

first()

Return the first result of this Query or None if the result doesn’t contain any row.

first() applies a limit of one within the generated SQL, so that only one primary entity row is generated on the server side (note this may consist of multiple result rows if join-loaded collections are present).

Calling first() results in an execution of the underlying query.

from_self(*entities)

return a Query that selects from this Query’s SELECT statement.

*entities - optional list of entities which will replace those being selected.

from_statement(statement)

Execute the given SELECT statement and return results.

This method bypasses all internal statement compilation, and the statement is executed without modification.

The statement argument is either a string, a select() construct, or a text() construct, and should return the set of columns appropriate to the entity class represented by this Query.

Also see the instances() method.

get(ident)

Return an instance of the object based on the given identifier, or None if not found.

The ident argument is a scalar or tuple of primary key column values in the order of the table def’s primary key columns.

group_by(*criterion)

apply one or more GROUP BY criterion to the query and return the newly resulting Query

having(criterion)

apply a HAVING criterion to the query and return the newly resulting Query.

instances(cursor, _Query__context=None)

Given a ResultProxy cursor as returned by connection.execute(), return an ORM result as an iterator.

e.g.:

result = engine.execute("select * from users")
for u in session.query(User).instances(result):
    print u

intersect(*q)

Produce an INTERSECT of this Query against one or more queries.

Works the same way as union(). See that method for usage examples.

intersect_all(*q)

Produce an INTERSECT ALL of this Query against one or more queries.

Works the same way as union(). See that method for usage examples.

join(*props, **kwargs)

Create a join against this Query object’s criterion and apply generatively, returning the newly resulting Query.

Each element in *props may be:

• a string property name, i.e. “rooms”. This will join along the relationship of the same name from this Query’s “primary” mapper, if one is present.
• a class-mapped attribute, i.e. Houses.rooms. This will create a join from “Houses” table to that of the “rooms” relationship.
• a 2-tuple containing a target class or selectable, and an “ON” clause. The ON clause can be the property name/ attribute like above, or a SQL expression.

e.g.:

# join along string attribute names
session.query(Company).join('employees')
session.query(Company).join('employees', 'tasks')

# join the Person entity to an alias of itself,
# along the "friends" relationship
PAlias = aliased(Person)
session.query(Person).join((Palias, Person.friends))

# join from Houses to the "rooms" attribute on the
# "Colonials" subclass of Houses, then join to the
# "closets" relationship on Room
session.query(Houses).join(Colonials.rooms, Room.closets)

# join from Company entities to the "employees" collection,
# using "people JOIN engineers" as the target.  Then join
# to the "computers" collection on the Engineer entity.
session.query(Company).                        join((people.join(engineers), 'employees'),
            Engineer.computers)

# join from Articles to Keywords, using the "keywords" attribute.
# assume this is a many-to-many relationship.
session.query(Article).join(Article.keywords)

# same thing, but spelled out entirely explicitly
# including the association table.
session.query(Article).join(
    (article_keywords,
    Articles.id==article_keywords.c.article_id),
    (Keyword, Keyword.id==article_keywords.c.keyword_id)
    )

**kwargs include:

aliased - when joining, create anonymous aliases of each table. This is used for self-referential joins or multiple joins to the same table. Consider usage of the aliased(SomeClass) construct as a more explicit approach to this.

from_joinpoint - when joins are specified using string property names, locate the property from the mapper found in the most recent previous join() call, instead of from the root entity.

limit(limit)

Apply a LIMIT to the query and return the newly resulting

Query.

merge_result(iterator, load=True)

Merge a result into this Query’s Session.

Given an iterator returned by a Query of the same structure as this one, return an identical iterator of results, with all mapped instances merged into the session using Session.merge(). This is an optimized method which will merge all mapped instances, preserving the structure of the result rows and unmapped columns with less method overhead than that of calling Session.merge() explicitly for each value.

The structure of the results is determined based on the column list of this Query - if these do not correspond, unchecked errors will occur.

The ‘load’ argument is the same as that of Session.merge().

offset(offset)

Apply an OFFSET to the query and return the newly resulting Query.

one()

Return exactly one result or raise an exception.

Raises sqlalchemy.orm.exc.NoResultFound if the query selects no rows. Raises sqlalchemy.orm.exc.MultipleResultsFound if multiple object identities are returned, or if multiple rows are returned for a query that does not return object identities.

Note that an entity query, that is, one which selects one or more mapped classes as opposed to individual column attributes, may ultimately represent many rows but only one row of unique entity or entities - this is a successful result for one().

Calling one() results in an execution of the underlying query. As of 0.6, one() fully fetches all results instead of applying any kind of limit, so that the “unique”-ing of entities does not conceal multiple object identities.

options(*args)

Return a new Query object, applying the given list of MapperOptions.

order_by(*criterion)

apply one or more ORDER BY criterion to the query and return the newly resulting Query

outerjoin(*props, **kwargs)

Create a left outer join against this Query object’s criterion and apply generatively, retunring the newly resulting Query.

Usage is the same as the join() method.

params(*args, **kwargs)

add values for bind parameters which may have been specified in filter().

parameters may be specified using **kwargs, or optionally a single dictionary as the first positional argument. The reason for both is that **kwargs is convenient, however some parameter dictionaries contain unicode keys in which case **kwargs cannot be used.

populate_existing()

Return a Query that will refresh all instances loaded.

This includes all entities accessed from the database, including secondary entities, eagerly-loaded collection items.

All changes present on entities which are already present in the session will be reset and the entities will all be marked “clean”.

An alternative to populate_existing() is to expire the Session fully using session.expire_all().

reset_joinpoint()

return a new Query reset the ‘joinpoint’ of this Query reset back to the starting mapper. Subsequent generative calls will be constructed from the new joinpoint.

Note that each call to join() or outerjoin() also starts from the root.

scalar()

Return the first element of the first result or None if no rows present. If multiple rows are returned, raises MultipleResultsFound.

>>> session.query(Item).scalar()
<Item>
>>> session.query(Item.id).scalar()
1
>>> session.query(Item.id).filter(Item.id < 0).scalar()
None
>>> session.query(Item.id, Item.name).scalar()
1
>>> session.query(func.count(Parent.id)).scalar()
20

This results in an execution of the underlying query.

select_from(*from_obj)

Set the from_obj parameter of the query and return the newly resulting Query. This replaces the table which this Query selects from with the given table.

select_from() also accepts class arguments. Though usually not necessary, can ensure that the full selectable of the given mapper is applied, e.g. for joined-table mappers.

slice(start, stop)

apply LIMIT/OFFSET to the Query based on a ” “range and return the newly resulting Query.

statement

The full SELECT statement represented by this Query.

The statement by default will not have disambiguating labels applied to the construct unless with_labels(True) is called first.

subquery()

return the full SELECT statement represented by this Query, embedded within an Alias.

Eager JOIN generation within the query is disabled.

The statement by default will not have disambiguating labels applied to the construct unless with_labels(True) is called first.

union(*q)

Produce a UNION of this Query against one or more queries.

e.g.:

q1 = sess.query(SomeClass).filter(SomeClass.foo=='bar')
q2 = sess.query(SomeClass).filter(SomeClass.bar=='foo')

q3 = q1.union(q2)

The method accepts multiple Query objects so as to control the level of nesting. A series of union() calls such as:

x.union(y).union(z).all()

will nest on each union(), and produces:

SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION 
                SELECT * FROM y) UNION SELECT * FROM Z)

Whereas:

x.union(y, z).all()

produces:

SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION 
                SELECT * FROM Z)

union_all(*q)

Produce a UNION ALL of this Query against one or more queries.

Works the same way as union(). See that method for usage examples.

update(values, synchronize_session='evaluate')

Perform a bulk update query.

Updates rows matched by this query in the database.

Parameters:

• values – a dictionary with attributes names as keys and literal values or sql expressions as values.
• synchronize_session –

  chooses the strategy to update the attributes on objects in the session. Valid values are:

  False - don’t synchronize the session. This option is the most efficient and is reliable once the session is expired, which typically occurs after a commit(), or explicitly using expire_all(). Before the expiration, updated objects may still remain in the session with stale values on their attributes, which can lead to confusing results.

  ‘fetch’ - performs a select query before the update to find objects that are matched by the update query. The updated attributes are expired on matched objects.

  ‘evaluate’ - Evaluate the Query’s criteria in Python straight on the objects in the session. If evaluation of the criteria isn’t implemented, an exception is raised.

  The expression evaluator currently doesn’t account for differing string collations between the database and Python.

Returns the number of rows matched by the update.

The method does not offer in-Python cascading of relationships - it is assumed that ON UPDATE CASCADE is configured for any foreign key references which require it.

The Session needs to be expired (occurs automatically after commit(), or call expire_all()) in order for the state of dependent objects subject foreign key cascade to be correctly represented.

Also, the before_update() and after_update() MapperExtension methods are not called from this method. For an update hook here, use the after_bulk_update() SessionExtension method.

value(column)

Return a scalar result corresponding to the given column expression.

values(*columns)

Return an iterator yielding result tuples corresponding to the given list of columns

whereclause

The WHERE criterion for this Query.

with_hint(selectable, text, dialect_name=None)

Add an indexing hint for the given entity or selectable to this Query.

Functionality is passed straight through to with_hint(), with the addition that selectable can be a Table, Alias, or ORM entity / mapped class /etc.

with_labels()

Apply column labels to the return value of Query.statement.

Indicates that this Query’s statement accessor should return a SELECT statement that applies labels to all columns in the form <tablename>_<columnname>; this is commonly used to disambiguate columns from multiple tables which have the same name.

When the Query actually issues SQL to load rows, it always uses column labeling.

with_lockmode(mode)

Return a new Query object with the specified locking mode.

with_parent(instance, property=None)

Add a join criterion corresponding to a relationship to the given parent instance.

instance

a persistent or detached instance which is related to class represented by this query.

property

string name of the property which relates this query’s class to the instance. if None, the method will attempt to find a suitable property.

Currently, this method only works with immediate parent relationships, but in the future may be enhanced to work across a chain of parent mappers.

with_polymorphic(cls_or_mappers, selectable=None, discriminator=None)

Load columns for descendant mappers of this Query’s mapper.

Using this method will ensure that each descendant mapper’s tables are included in the FROM clause, and will allow filter() criterion to be used against those tables. The resulting instances will also have those columns already loaded so that no “post fetch” of those columns will be required.

Parameters:

• cls_or_mappers – a single class or mapper, or list of class/mappers, which inherit from this Query’s mapper. Alternatively, it may also be the string '*', in which case all descending mappers will be added to the FROM clause.
• selectable – a table or select() statement that will be used in place of the generated FROM clause. This argument is required if any of the desired mappers use concrete table inheritance, since SQLAlchemy currently cannot generate UNIONs among tables automatically. If used, the selectable argument must represent the full set of tables and columns mapped by every desired mapper. Otherwise, the unaccounted mapped columns will result in their table being appended directly to the FROM clause which will usually lead to incorrect results.
• discriminator – a column to be used as the “discriminator” column for the given selectable. If not given, the polymorphic_on attribute of the mapper will be used, if any. This is useful for mappers that don’t have polymorphic loading behavior by default, such as concrete table mappers.

yield_per(count)

Yield only count rows at a time.

WARNING: use this method with caution; if the same instance is present in more than one batch of rows, end-user changes to attributes will be overwritten.

In particular, it’s usually impossible to use this setting with eagerly loaded collections (i.e. any lazy=’joined’ or ‘subquery’) since those collections will be cleared for a new load when encountered in a subsequent result batch. In the case of ‘subquery’ loading, the full result for all rows is fetched which generally defeats the purpose of yield_per().

Also note that many DBAPIs do not “stream” results, pre-buffering all rows before making them available, including mysql-python and psycopg2. yield_per() will also set the stream_results execution option to True, which currently is only understood by psycopg2 and causes server side cursors to be used.

ORM-Specific Query Constructs

sqlalchemy.orm.aliased

alias of AliasedClass

class sqlalchemy.orm.util.AliasedClass(cls, alias=None, name=None)

Represents an “aliased” form of a mapped class for usage with Query.

The ORM equivalent of a sqlalchemy.sql.expression.alias() construct, this object mimics the mapped class using a __getattr__ scheme and maintains a reference to a real Alias object.

Usage is via the aliased() synonym:

# find all pairs of users with the same name
user_alias = aliased(User)
session.query(User, user_alias).\
                join((user_alias, User.id > user_alias.id)).\
                filter(User.name==user_alias.name)

sqlalchemy.orm.join(left, right, onclause=None, isouter=False, join_to_left=True)

Produce an inner join between left and right clauses.

In addition to the interface provided by join(), left and right may be mapped classes or AliasedClass instances. The onclause may be a string name of a relationship(), or a class-bound descriptor representing a relationship.

join_to_left indicates to attempt aliasing the ON clause, in whatever form it is passed, to the selectable passed as the left side. If False, the onclause is used as is.

sqlalchemy.orm.outerjoin(left, right, onclause=None, join_to_left=True)

Produce a left outer join between left and right clauses.

In addition to the interface provided by outerjoin(), left and right may be mapped classes or AliasedClass instances. The onclause may be a string name of a relationship(), or a class-bound descriptor representing a relationship.

Query Options

Options which are passed to query.options(), to affect the behavior of loading.

sqlalchemy.orm.contains_alias(alias)

Return a MapperOption that will indicate to the query that the main table has been aliased.

alias is the string name or Alias object representing the alias.

sqlalchemy.orm.contains_eager(*keys, **kwargs)

Return a MapperOption that will indicate to the query that the given attribute should be eagerly loaded from columns currently in the query.

Used with options().

The option is used in conjunction with an explicit join that loads the desired rows, i.e.:

sess.query(Order).\
        join(Order.user).\
        options(contains_eager(Order.user))

The above query would join from the Order entity to its related User entity, and the returned Order objects would have the Order.user attribute pre-populated.

contains_eager() also accepts an alias argument, which is the string name of an alias, an alias() construct, or an aliased() construct. Use this when the eagerly-loaded rows are to come from an aliased table:

user_alias = aliased(User)
sess.query(Order).\
        join((user_alias, Order.user)).\
        options(contains_eager(Order.user, alias=user_alias))

See also eagerload() for the “automatic” version of this functionality.

sqlalchemy.orm.defer(*keys)

Return a MapperOption that will convert the column property of the given name into a deferred load.

Used with options().

sqlalchemy.orm.eagerload(*args, **kwargs)

A synonym for joinedload().

sqlalchemy.orm.eagerload_all(*args, **kwargs)

A synonym for joinedload_all()

sqlalchemy.orm.extension(ext)

Return a MapperOption that will insert the given MapperExtension to the beginning of the list of extensions that will be called in the context of the Query.

Used with options().

sqlalchemy.orm.joinedload(*keys, **kw)

Return a MapperOption that will convert the property of the given name into an joined eager load.

Note

This function is known as eagerload() in all versions of SQLAlchemy prior to version 0.6beta3, including the 0.5 and 0.4 series. eagerload() will remain available for the foreseeable future in order to enable cross-compatibility.

Used with options().

examples:

# joined-load the "orders" colleciton on "User"
query(User).options(joinedload(User.orders))

# joined-load the "keywords" collection on each "Item",
# but not the "items" collection on "Order" - those 
# remain lazily loaded.
query(Order).options(joinedload(Order.items, Item.keywords))

# to joined-load across both, use joinedload_all()
query(Order).options(joinedload_all(Order.items, Item.keywords))

joinedload() also accepts a keyword argument innerjoin=True which indicates using an inner join instead of an outer:

query(Order).options(joinedload(Order.user, innerjoin=True))

Note that the join created by joinedload() is aliased such that no other aspects of the query will affect what it loads. To use joined eager loading with a join that is constructed manually using join() or join(), see contains_eager().

See also: subqueryload(), lazyload()

sqlalchemy.orm.joinedload_all(*keys, **kw)

Return a MapperOption that will convert all properties along the given dot-separated path into an joined eager load.

Note

This function is known as eagerload_all() in all versions of SQLAlchemy prior to version 0.6beta3, including the 0.5 and 0.4 series. eagerload_all() will remain available for the foreseeable future in order to enable cross-compatibility.

Used with options().

For example:

query.options(joinedload_all('orders.items.keywords'))...

will set all of ‘orders’, ‘orders.items’, and ‘orders.items.keywords’ to load in one joined eager load.

Individual descriptors are accepted as arguments as well:

query.options(joinedload_all(User.orders, Order.items, Item.keywords))

The keyword arguments accept a flag innerjoin=True|False which will override the value of the innerjoin flag specified on the relationship().

See also: subqueryload_all(), lazyload()

sqlalchemy.orm.lazyload(*keys)

Return a MapperOption that will convert the property of the given name into a lazy load.

Used with options().

See also: eagerload(), subqueryload()

sqlalchemy.orm.subqueryload(*keys)

Return a MapperOption that will convert the property of the given name into an subquery eager load.

Note

This function is new as of SQLAlchemy version 0.6beta3.

Used with options().

examples:

# subquery-load the "orders" colleciton on "User"
query(User).options(subqueryload(User.orders))

# subquery-load the "keywords" collection on each "Item",
# but not the "items" collection on "Order" - those 
# remain lazily loaded.
query(Order).options(subqueryload(Order.items, Item.keywords))

# to subquery-load across both, use subqueryload_all()
query(Order).options(subqueryload_all(Order.items, Item.keywords))

See also: joinedload(), lazyload()

sqlalchemy.orm.subqueryload_all(*keys)

Return a MapperOption that will convert all properties along the given dot-separated path into a subquery eager load.

Note

This function is new as of SQLAlchemy version 0.6beta3.

Used with options().

For example:

query.options(subqueryload_all('orders.items.keywords'))...

will set all of ‘orders’, ‘orders.items’, and ‘orders.items.keywords’ to load in one subquery eager load.

Individual descriptors are accepted as arguments as well:

query.options(subqueryload_all(User.orders, Order.items, Item.keywords))

See also: joinedload_all(), lazyload()

sqlalchemy.orm.undefer(*keys)

Return a MapperOption that will convert the column property of the given name into a non-deferred (regular column) load.

Used with options().