Query API

This section presents the API reference for the ORM Query object. For a walkthroughof how to use this object, see Object Relational Tutorial.

The Query Object

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

  1. 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.

Query is the source of all SELECT statements generated by theORM, both those formulated by end-user query operations as well as byhigh level internal operations such as related collection loading. Itfeatures a generative interface whereby successive calls return a newQuery object, a copy of the former with additionalcriteria and options associated with it.

Query objects are normally initially generated using thequery() method of Session, and inless common cases by instantiating the Query directly andassociating with a Session using the Query.with_session()method.

For a full walkthrough of Query usage, see theObject Relational Tutorial.

  • init(entities, session=None)
  • Construct a Query directly.

E.g.:

  1. q = Query([User, Address], session=some_session)

The above is equivalent to:

  1. q = some_session.query(User, Address)
  1. - Parameters
  2. - 
  3.   -

entities – a sequence of entities and/or SQL expressions.

  1.   -

session – a Session with which the Querywill be associated. Optional; a Query can be associatedwith a Session generatively via theQuery.with_session() method as well.

See also

Session.query()

Query.with_session()

  • addcolumn(_column)
  • Add a column expression to the list of result columns to bereturned.

Pending deprecation: add_column() will be superseded byadd_columns().

  • addcolumns(*column_)

  • Add one or more column expressions to the listof result columns to be returned.

  • addentity(_entity, alias=None)

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

  • all()

  • Return the results represented by this Query as a list.

This results in an execution of the underlying SQL statement.

Warning

The Query object, when asked to return eithera sequence or iterator that consists of full ORM-mapped entities,will deduplicate entries based on primary key. See the FAQ formore details.

  • as_scalar()
  • Return the full SELECT statement represented by thisQuery, converted to a scalar subquery.

Analogous to sqlalchemy.sql.expression.SelectBase.as_scalar().

  • autoflush(setting)
  • Return a Query with a specific ‘autoflush’ setting.

Note that a Session with autoflush=False willnot autoflush, even if this flag is set to True at theQuery level. Therefore this flag is usually used onlyto disable autoflush for a specific Query.

  • property column_descriptions
  • Return metadata about the columns which would bereturned by this Query.

Format is a list of dictionaries:

  1. user_alias = aliased(User, name='user2')
  2. q = sess.query(User, User.id, user_alias)
  3.  
  4. # this expression:
  5. q.column_descriptions
  6.  
  7. # would return:
  8. [
  9.     {
  10.         'name':'User',
  11.         'type':User,
  12.         'aliased':False,
  13.         'expr':User,
  14.         'entity': User
  15.     },
  16.     {
  17.         'name':'id',
  18.         'type':Integer(),
  19.         'aliased':False,
  20.         'expr':User.id,
  21.         'entity': User
  22.     },
  23.     {
  24.         'name':'user2',
  25.         'type':User,
  26.         'aliased':True,
  27.         'expr':user_alias,
  28.         'entity': user_alias
  29.     }
  30. ]
  • correlate(*args)
  • Return a Query construct which will correlate the givenFROM clauses to that of an enclosing Query orselect().

The method here accepts mapped classes, aliased() constructs,and mapper() constructs as arguments, which are resolved intoexpression constructs, in addition to appropriate expressionconstructs.

The correlation arguments are ultimately passed toSelect.correlate() after coercion to expression constructs.

The correlation arguments take effect in such casesas when Query.from_self() is used, or whena subquery as returned by Query.subquery() isembedded in another select() construct.

  • count()
  • Return a count of rows this the SQL formed by this Querywould return.

This generates the SQL for this Query as follows:

  1. SELECT count(1) AS count_1 FROM (
  2.     SELECT <rest of query follows...>
  3. ) AS anon_1

The above SQL returns a single row, which is the aggregate valueof the count function; the Query.count() method then returnsthat single integer value.

Warning

It is important to note that the value returned bycount() is not the same as the number of ORM objects that thisQuery would return from a method such as the .all() method.The Query object, when asked to return full entities,will deduplicate entries based on primary key, meaning if thesame primary key value would appear in the results more than once,only one object of that primary key would be present. This doesnot apply to a query that is against individual columns.

See also

My Query does not return the same number of objects as query.count() tells me - why?

Returning Lists and Scalars

For fine grained control over specific columns to count, to skip theusage of a subquery or otherwise control of the FROM clause, or to useother aggregate functions, use funcexpressions in conjunction with query(), i.e.:

  1. from sqlalchemy import func
  2.  
  3. # count User records, without
  4. # using a subquery.
  5. session.query(func.count(User.id))
  6.  
  7. # return count of user "id" grouped
  8. # by "name"
  9. session.query(func.count(User.id)).\
  10.         group_by(User.name)
  11.  
  12. from sqlalchemy import distinct
  13.  
  14. # count distinct "name" values
  15. session.query(func.count(distinct(User.name)))
  • cte(name=None, recursive=False)
  • Return the full SELECT statement represented by thisQuery represented as a common table expression (CTE).

Parameters and usage are the same as those of theSelectBase.cte() method; see that method forfurther details.

Here is the PostgreSQL WITHRECURSIVE example.Note that, in this example, the included_parts cte and theincl_alias alias of it are Core selectables, whichmeans the columns are accessed via the .c. attribute. Theparts_alias object is an orm.aliased() instance of thePart entity, so column-mapped attributes are availabledirectly:

  1. from sqlalchemy.orm import aliased
  2.  
  3. class Part(Base):
  4.     __tablename__ = 'part'
  5.     part = Column(String, primary_key=True)
  6.     sub_part = Column(String, primary_key=True)
  7.     quantity = Column(Integer)
  8.  
  9. included_parts = session.query(
  10.                 Part.sub_part,
  11.                 Part.part,
  12.                 Part.quantity).\
  13.                     filter(Part.part=="our part").\
  14.                     cte(name="included_parts", recursive=True)
  15.  
  16. incl_alias = aliased(included_parts, name="pr")
  17. parts_alias = aliased(Part, name="p")
  18. included_parts = included_parts.union_all(
  19.     session.query(
  20.         parts_alias.sub_part,
  21.         parts_alias.part,
  22.         parts_alias.quantity).\
  23.             filter(parts_alias.part==incl_alias.c.sub_part)
  24.     )
  25.  
  26. q = session.query(
  27.         included_parts.c.sub_part,
  28.         func.sum(included_parts.c.quantity).
  29.             label('total_quantity')
  30.     ).\
  31.     group_by(included_parts.c.sub_part)

See also

HasCTE.cte()

  • delete(synchronize_session='evaluate')
  • Perform a bulk delete query.

Deletes rows matched by this query from the database.

E.g.:

  1. sess.query(User).filter(User.age == 25).\
  2.     delete(synchronize_session=False)
  3.  
  4. sess.query(User).filter(User.age == 25).\
  5.     delete(synchronize_session='evaluate')

Warning

The Query.delete() method is a “bulk” operation,which bypasses ORM unit-of-work automation in favor of greaterperformance. Please read all caveats and warnings below.

  1. - Parameters
  2. -

synchronize_session –

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

False - don’t synchronize the session. This option is the mostefficient and is reliable once the session is expired, whichtypically occurs after a commit(), or explicitly usingexpire_all(). Before the expiration, objects may still remain inthe session which were in fact deleted which can lead to confusingresults if they are accessed via get() or already loadedcollections.

'fetch' - performs a select query before the delete to findobjects that are matched by the delete query and need to beremoved from the session. Matched objects are removed from thesession.

'evaluate' - Evaluate the query’s criteria in Python straighton the objects in the session. If evaluation of the criteria isn’timplemented, an error is raised.

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

  1. - Returns
  2. -

the count of rows matched as returned by the database’s“row count” feature.

Warning

Additional Caveats for bulk query deletes

  1. -

This method does not work for joinedinheritance mappings, since the multiple tabledeletes are not supported by SQL as well as that thejoin condition of an inheritance mapper is notautomatically rendered. Care must be taken in anymultiple-table delete to first accommodate via some other meanshow the related table will be deleted, as well as toexplicitly include the joiningcondition between those tables, even in mappings wherethis is normally automatic. E.g. if a class Engineersubclasses Employee, a DELETE against the Employeetable would look like:

  1. session.query(Engineer).\
  2.     filter(Engineer.id == Employee.id).\
  3.     filter(Employee.name == 'dilbert').\
  4.     delete()

However the above SQL will not delete from the Engineer table,unless an ON DELETE CASCADE rule is established in the databaseto handle it.

Short story, do not use this method for joined inheritancemappings unless you have taken the additional steps to makethis feasible.

  1. -

The polymorphic identity WHERE criteria is not includedfor single- orjoined- table updates - this must be added manually evenfor single table inheritance.

  1. -

The method does not offer in-Python cascading ofrelationships - it is assumed that ON DELETE CASCADE/SETNULL/etc. is configured for any foreign key referenceswhich require it, otherwise the database may emit anintegrity violation if foreign key references are beingenforced.

After the DELETE, dependent objects in theSession which were impacted by an ON DELETEmay not contain the current state, or may have beendeleted. This issue is resolved once theSession is expired, which normally occurs uponSession.commit() or can be forced by usingSession.expire_all(). Accessing an expiredobject whose row has been deleted will invoke a SELECTto locate the row; when the row is not found, anObjectDeletedError israised.

  1. -

The 'fetch' strategy results in an additionalSELECT statement emitted and will significantly reduceperformance.

  1. -

The 'evaluate' strategy performs a scan ofall matching objects within the Session; if thecontents of the Session are expired, such asvia a proceeding Session.commit() call, this willresult in SELECT queries emitted for every matching object.

  1. -

The MapperEvents.before_delete() andMapperEvents.after_delete()events are not invoked from this method. Instead, theSessionEvents.after_bulk_delete() method is provided toact upon a mass DELETE of entity rows.

See also

Query.update()

Inserts, Updates and Deletes - Core SQL tutorial

  • distinct(*expr)
  • Apply a DISTINCT to the query and return the newly resultingQuery.

Note

The distinct() call includes logic that will automaticallyadd columns from the ORDER BY of the query to the columnsclause of the SELECT statement, to satisfy the common needof the database backend that ORDER BY columns be part of theSELECT list when DISTINCT is used. These columns _are not_added to the list of columns actually fetched by theQuery, however, so would not affect results.The columns are passed through when using theQuery.statement accessor, however.

  1. - Parameters
  2. -

*expr – optional column expressions. When present,the PostgreSQL dialect will render a DISTINCT ON (<expressions>)construct.

  • enableassertions(_value)
  • Control whether assertions are generated.

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

Care should be taken to ensure that the usagepattern is even possible. A statement appliedby from_statement() will override any criterionset by filter() or order_by(), for example.

  • enableeagerloads(_value)
  • Control whether or not eager joins and subqueries arerendered.

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

This is used primarily when nesting the Query’sstatement into a subquery or otherselectable, or when using Query.yield_per().

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

  • exceptall(*q_)
  • Produce an EXCEPT ALL of this Query against one or more queries.

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

  • executionoptions(**kwargs_)
  • Set non-SQL options which take effect during execution.

The options are the same as those accepted byConnection.execution_options().

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

See also

Query.get_execution_options()

  • exists()
  • A convenience method that turns a query into an EXISTS subqueryof the form EXISTS (SELECT 1 FROM … WHERE …).

e.g.:

  1. q = session.query(User).filter(User.name == 'fred')
  2. session.query(q.exists())

Producing SQL similar to:

  1. SELECT EXISTS (
  2.     SELECT 1 FROM users WHERE users.name = :name_1
  3. ) AS anon_1

The EXISTS construct is usually used in the WHERE clause:

  1. session.query(User.id).filter(q.exists()).scalar()

Note that some databases such as SQL Server don’t allow anEXISTS expression to be present in the columns clause of aSELECT. To select a simple boolean value based on the existsas a WHERE, use literal():

  1. from sqlalchemy import literal
  2.  
  3. session.query(literal(True)).filter(q.exists()).scalar()
  • filter(*criterion)
  • apply the given filtering criterion to a copyof this Query, using SQL expressions.

e.g.:

  1. session.query(MyClass).filter(MyClass.name == 'some name')

Multiple criteria may be specified as comma separated; the effectis that they will be joined together using the and_()function:

  1. session.query(MyClass).\
  2.     filter(MyClass.name == 'some name', MyClass.id > 5)

The criterion is any SQL expression object applicable to theWHERE clause of a select. String expressions are coercedinto SQL expression constructs via the text() construct.

See also

Query.filter_by() - filter on keyword expressions.

  • filterby(**kwargs_)
  • apply the given filtering criterion to a copyof this Query, using keyword expressions.

e.g.:

  1. session.query(MyClass).filter_by(name = 'some name')

Multiple criteria may be specified as comma separated; the effectis that they will be joined together using the and_()function:

  1. session.query(MyClass).\
  2.     filter_by(name = 'some name', id = 5)

The keyword expressions are extracted from the primaryentity of the query, or the last entity that was thetarget of a call to Query.join().

See also

Query.filter() - filter on SQL expressions.

  • first()
  • Return the first result of this Query orNone if the result doesn’t contain any row.

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

Calling Query.first() results in an execution of the underlyingquery.

See also

Query.one()

Query.one_or_none()

  • fromself(*entities_)
  • return a Query that selects from this Query’sSELECT statement.

Query.from_self() essentially turns the SELECT statementinto a SELECT of itself. Given a query such as:

  1. q = session.query(User).filter(User.name.like('e%'))

Given the Query.from_self() version:

  1. q = session.query(User).filter(User.name.like('e%')).from_self()

This query renders as:

  1. SELECT anon_1.user_id AS anon_1_user_id,
  2.        anon_1.user_name AS anon_1_user_name
  3. FROM (SELECT "user".id AS user_id, "user".name AS user_name
  4. FROM "user"
  5. WHERE "user".name LIKE :name_1) AS anon_1

There are lots of cases where Query.from_self() may be useful.A simple one is where above, we may want to apply a row LIMIT tothe set of user objects we query against, and then apply additionaljoins against that row-limited set:

  1. q = session.query(User).filter(User.name.like('e%')).\
  2.     limit(5).from_self().\
  3.     join(User.addresses).filter(Address.email.like('q%'))

The above query joins to the Address entity but only against thefirst five results of the User query:

  1. SELECT anon_1.user_id AS anon_1_user_id,
  2.        anon_1.user_name AS anon_1_user_name
  3. FROM (SELECT "user".id AS user_id, "user".name AS user_name
  4. FROM "user"
  5. WHERE "user".name LIKE :name_1
  6.  LIMIT :param_1) AS anon_1
  7. JOIN address ON anon_1.user_id = address.user_id
  8. WHERE address.email LIKE :email_1

Automatic Aliasing

Another key behavior of Query.from_self() is that it appliesautomatic aliasing to the entities inside the subquery, whenthey are referenced on the outside. Above, if we continue torefer to the User entity without any additional aliasing appliedto it, those references wil be in terms of the subquery:

  1. q = session.query(User).filter(User.name.like('e%')).\
  2.     limit(5).from_self().\
  3.     join(User.addresses).filter(Address.email.like('q%')).\
  4.     order_by(User.name)

The ORDER BY against User.name is aliased to be in terms of theinner subquery:

  1. SELECT anon_1.user_id AS anon_1_user_id,
  2.        anon_1.user_name AS anon_1_user_name
  3. FROM (SELECT "user".id AS user_id, "user".name AS user_name
  4. FROM "user"
  5. WHERE "user".name LIKE :name_1
  6.  LIMIT :param_1) AS anon_1
  7. JOIN address ON anon_1.user_id = address.user_id
  8. WHERE address.email LIKE :email_1 ORDER BY anon_1.user_name

The automatic aliasing feature only works in a limited way,for simple filters and orderings. More ambitious constructionssuch as referring to the entity in joins should prefer to useexplicit subquery objects, typically making use of theQuery.subquery() method to produce an explicit subquery object.Always test the structure of queries by viewing the SQL to ensurea particular structure does what’s expected!

Changing the Entities

Query.from_self() also includes the ability to modify whatcolumns are being queried. In our example, we want User.idto be queried by the inner query, so that we can join to theAddress entity on the outside, but we only wanted the outerquery to return the Address.email column:

  1. q = session.query(User).filter(User.name.like('e%')).\
  2.     limit(5).from_self(Address.email).\
  3.     join(User.addresses).filter(Address.email.like('q%'))

yielding:

  1. SELECT address.email AS address_email
  2. FROM (SELECT "user".id AS user_id, "user".name AS user_name
  3. FROM "user"
  4. WHERE "user".name LIKE :name_1
  5.  LIMIT :param_1) AS anon_1
  6. JOIN address ON anon_1.user_id = address.user_id
  7. WHERE address.email LIKE :email_1

Looking out for Inner / Outer Columns

Keep in mind that when referring to columns that originate frominside the subquery, we need to ensure they are present in thecolumns clause of the subquery itself; this is an ordinary aspect ofSQL. For example, if we wanted to load from a joined entity insidethe subquery using contains_eager(), we need to add thosecolumns. Below illustrates a join of Address to User,then a subquery, and then we’d like contains_eager() to accessthe User columns:

  1. q = session.query(Address).join(Address.user).\
  2.     filter(User.name.like('e%'))
  3.  
  4. q = q.add_entity(User).from_self().\
  5.     options(contains_eager(Address.user))

We use Query.add_entity() above before we callQuery.from_self() so that the User columns are presentin the inner subquery, so that they are available to thecontains_eager() modifier we are using on the outside,producing:

  1. SELECT anon_1.address_id AS anon_1_address_id,
  2.        anon_1.address_email AS anon_1_address_email,
  3.        anon_1.address_user_id AS anon_1_address_user_id,
  4.        anon_1.user_id AS anon_1_user_id,
  5.        anon_1.user_name AS anon_1_user_name
  6. FROM (
  7.     SELECT address.id AS address_id,
  8.     address.email AS address_email,
  9.     address.user_id AS address_user_id,
  10.     "user".id AS user_id,
  11.     "user".name AS user_name
  12. FROM address JOIN "user" ON "user".id = address.user_id
  13. WHERE "user".name LIKE :name_1) AS anon_1

If we didn’t call add_entity(User), but still askedcontains_eager() to load the User entity, it would beforced to add the table on the outside without the correctjoin criteria - note the anon1, "user" phrase atthe end:

  1. -- incorrect query
  2. SELECT anon_1.address_id AS anon_1_address_id,
  3.        anon_1.address_email AS anon_1_address_email,
  4.        anon_1.address_user_id AS anon_1_address_user_id,
  5.        "user".id AS user_id,
  6.        "user".name AS user_name
  7. FROM (
  8.     SELECT address.id AS address_id,
  9.     address.email AS address_email,
  10.     address.user_id AS address_user_id
  11. FROM address JOIN "user" ON "user".id = address.user_id
  12. WHERE "user".name LIKE :name_1) AS anon_1, "user"
  1. - Parameters
  2. -

*entities – optional list of entities which will replacethose being selected.

  • fromstatement(_statement)
  • Execute the given SELECT statement and return results.

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

The statement is typically either a text()or select() construct, and should return the setof columnsappropriate to the entity class represented by this Query.

See also

Using Textual SQL - usage examples in theORM tutorial

  • get(ident)
  • Return an instance based on the given primary key identifier,or None if not found.

E.g.:

  1. my_user = session.query(User).get(5)
  2.  
  3. some_object = session.query(VersionedFoo).get((5, 10))
  4.  
  5. some_object = session.query(VersionedFoo).get(
  6.     {"id": 5, "version_id": 10})

get() is special in that it provides directaccess to the identity map of the owning Session.If the given primary key identifier is presentin the local identity map, the object is returneddirectly from this collection and no SQL is emitted,unless the object has been marked fully expired.If not present,a SELECT is performed in order to locate the object.

get() also will perform a check ifthe object is present in the identity map andmarked as expired - a SELECTis emitted to refresh the object as well as toensure that the row is still present.If not, ObjectDeletedError is raised.

get() is only used to return a singlemapped instance, not multiple instances orindividual column constructs, and strictlyon a single primary key value. The originatingQuery must be constructed in this way,i.e. against a single mapped entity,with no additional filtering criterion. Loadingoptions via options() may be appliedhowever, and will be used if the object is notyet locally present.

A lazy-loading, many-to-one attribute configuredby relationship(), using a simpleforeign-key-to-primary-key criterion, will also use anoperation equivalent to get() in order to retrievethe target value from the local identity mapbefore querying the database. See Relationship Loading Techniquesfor further details on relationship loading.

  1. - Parameters
  2. -

ident –

A scalar, tuple, or dictionary representing theprimary key. For a composite (e.g. multiple column) primary key,a tuple or dictionary should be passed.

For a single-column primary key, the scalar calling form is typicallythe most expedient. If the primary key of a row is the value “5”,the call looks like:

  1. my_object = query.get(5)

The tuple form contains primary key values typically inthe order in which they correspond to the mapped Tableobject’s primary key columns, or if theMapper.primary_key configuration parameter were used, inthe order used for that parameter. For example, if the primary keyof a row is represented by the integerdigits “5, 10” the call would look like:

  1. my_object = query.get((5, 10))

The dictionary form should include as keys the mapped attribute namescorresponding to each element of the primary key. If the mapped classhas the attributes id, version_id as the attributes whichstore the object’s primary key value, the call would look like:

  1. my_object = query.get({"id": 5, "version_id": 10})

New in version 1.3: the Query.get() method now optionallyaccepts a dictionary of attribute names to values in order toindicate a primary key identifier.

  1. - Returns
  2. -

The object instance, or None.

  • get_execution_options()
  • Get the non-SQL options which will take effect during execution.

New in version 1.3.

See also

Query.execution_options()

  • groupby(*criterion_)
  • apply one or more GROUP BY criterion to the query and returnthe newly resulting Query

All existing GROUP BY settings can be suppressed bypassing None - this will suppress any GROUP BY configuredon mappers as well.

New in version 1.1: GROUP BY can be cancelled by passing None,in the same way as ORDER BY.

  • having(criterion)
  • apply a HAVING criterion to the query and return thenewly resulting Query.

having() is used in conjunction withgroup_by().

HAVING criterion makes it possible to use filters on aggregatefunctions like COUNT, SUM, AVG, MAX, and MIN, eg.:

  1. q = session.query(User.id).\
  2.             join(User.addresses).\
  3.             group_by(User.id).\
  4.             having(func.count(Address.id) > 2)
  • instances(cursor, Querycontext=None_)
  • Given a ResultProxy cursor as returned by connection.execute(),return an ORM result as an iterator.

e.g.:

  1. result = engine.execute("select * from users")
  2. for u in session.query(User).instances(result):
  3.     print u
  • intersect(*q)
  • Produce an INTERSECT of this Query against one or more queries.

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

  • intersectall(*q_)
  • Produce an INTERSECT ALL of this Query against one or more queries.

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

  • join(*props, **kwargs)
  • Create a SQL JOIN against this Query object’s criterionand apply generatively, returning the newly resulting Query.

Simple Relationship Joins

Consider a mapping between two classes User and Address,with a relationship User.addresses representing a collectionof Address objects associated with each User. The mostcommon usage of join() is to create a JOIN along thisrelationship, using the User.addresses attribute as an indicatorfor how this should occur:

  1. q = session.query(User).join(User.addresses)

Where above, the call to join() along User.addresseswill result in SQL equivalent to:

  1. SELECT user.* FROM user JOIN address ON user.id = address.user_id

In the above example we refer to User.addresses as passed tojoin() as the on clause, that is, it indicateshow the “ON” portion of the JOIN should be constructed. For asingle-entity query such as the one above (i.e. we start by selectingonly from User and nothing else), the relationship can also bespecified by its string name:

  1. q = session.query(User).join("addresses")

join() can also accommodate multiple“on clause” arguments to produce a chain of joins, such as belowwhere a join across four related entities is constructed:

  1. q = session.query(User).join("orders", "items", "keywords")

The above would be shorthand for three separate calls tojoin(), each using an explicit attribute to indicatethe source entity:

  1. q = session.query(User).\
  2.         join(User.orders).\
  3.         join(Order.items).\
  4.         join(Item.keywords)

Joins to a Target Entity or Selectable

A second form of join() allows any mapped entityor core selectable construct as a target. In this usage,join() will attemptto create a JOIN along the natural foreign key relationship betweentwo entities:

  1. q = session.query(User).join(Address)

The above calling form of join() will raise an error ifeither there are no foreign keys between the two entities, or ifthere are multiple foreign key linkages between them. In theabove calling form, join() is called upon tocreate the “on clause” automatically for us. The target canbe any mapped entity or selectable, such as a Table:

  1. q = session.query(User).join(addresses_table)

Joins to a Target with an ON Clause

The third calling form allows both the target entity as wellas the ON clause to be passed explicitly. Suppose forexample we wanted to join to Address twice, usingan alias the second time. We use aliased()to create a distinct alias of Address, and jointo it using the target, onclause form, so that thealias can be specified explicitly as the target along withthe relationship to instruct how the ON clause should proceed:

  1. a_alias = aliased(Address)
  2.  
  3. q = session.query(User).\
  4.         join(User.addresses).\
  5.         join(a_alias, User.addresses).\
  6.         filter(Address.email_address=='ed@foo.com').\
  7.         filter(a_alias.email_address=='ed@bar.com')

Where above, the generated SQL would be similar to:

  1. SELECT user.* FROM user
  2.     JOIN address ON user.id = address.user_id
  3.     JOIN address AS address_1 ON user.id=address_1.user_id
  4.     WHERE address.email_address = :email_address_1
  5.     AND address_1.email_address = :email_address_2

The two-argument calling form of join()also allows us to construct arbitrary joins with SQL-oriented“on clause” expressions, not relying upon configured relationshipsat all. Any SQL expression can be passed as the ON clausewhen using the two-argument form, which should refer to the targetentity in some way as well as an applicable source entity:

  1. q = session.query(User).join(Address, User.id==Address.user_id)

Advanced Join Targeting and Adaption

There is a lot of flexibility in what the “target” can be when usingjoin(). As noted previously, it also acceptsTable constructs and other selectables such asalias() and select() constructs, with either the oneor two-argument forms:

  1. addresses_q = select([Address.user_id]).\
  2.             where(Address.email_address.endswith("@bar.com")).\
  3.             alias()
  4.  
  5. q = session.query(User).\
  6.             join(addresses_q, addresses_q.c.user_id==User.id)

join() also features the ability to adapt arelationship() -driven ON clause to the targetselectable. Below we construct a JOIN from User to a subqueryagainst Address, allowing the relationship denoted byUser.addresses to adapt itself to the altered target:

  1. address_subq = session.query(Address).\
  2.                 filter(Address.email_address == 'ed@foo.com').\
  3.                 subquery()
  4.  
  5. q = session.query(User).join(address_subq, User.addresses)

Producing SQL similar to:

  1. SELECT user.* FROM user
  2.     JOIN (
  3.         SELECT address.id AS id,
  4.                 address.user_id AS user_id,
  5.                 address.email_address AS email_address
  6.         FROM address
  7.         WHERE address.email_address = :email_address_1
  8.     ) AS anon_1 ON user.id = anon_1.user_id

The above form allows one to fall back onto an explicit ONclause at any time:

  1. q = session.query(User).\
  2.         join(address_subq, User.id==address_subq.c.user_id)

Controlling what to Join From

While join() exclusively deals with the “right”side of the JOIN, we can also control the “left” side, in thosecases where it’s needed, using select_from().Below we construct a query against Address but can stillmake usage of User.addresses as our ON clause by instructingthe Query to select first from the Userentity:

  1. q = session.query(Address).select_from(User).\
  2.                 join(User.addresses).\
  3.                 filter(User.name == 'ed')

Which will produce SQL similar to:

  1. SELECT address.* FROM user
  2.     JOIN address ON user.id=address.user_id
  3.     WHERE user.name = :name_1

Constructing Aliases Anonymously

join() can construct anonymous aliasesusing the aliased=True flag. This feature is usefulwhen a query is being joined algorithmically, such aswhen querying self-referentially to an arbitrary depth:

  1. q = session.query(Node).\
  2.         join("children", "children", aliased=True)

When aliased=True is used, the actual “alias” constructis not explicitly available. To work with it, methods such asQuery.filter() will adapt the incoming entity tothe last join point:

  1. q = session.query(Node).\
  2.         join("children", "children", aliased=True).\
  3.         filter(Node.name == 'grandchild 1')

When using automatic aliasing, the from_joinpoint=Trueargument can allow a multi-node join to be broken intomultiple calls to join(), so thateach path along the way can be further filtered:

  1. q = session.query(Node).\
  2.         join("children", aliased=True).\
  3.         filter(Node.name='child 1').\
  4.         join("children", aliased=True, from_joinpoint=True).\
  5.         filter(Node.name == 'grandchild 1')

The filtering aliases above can then be reset back to theoriginal Node entity using reset_joinpoint():

  1. q = session.query(Node).\
  2.         join("children", "children", aliased=True).\
  3.         filter(Node.name == 'grandchild 1').\
  4.         reset_joinpoint().\
  5.         filter(Node.name == 'parent 1)

For an example of aliased=True, see the distributionexample XML Persistence which illustratesan XPath-like query system using algorithmic joins.

  1. - Parameters
  2. - 
  3.   -

*props – A collection of one or more join conditions,each consisting of a relationship-bound attribute or stringrelationship name representing an “on clause”, or a singletarget entity, or a tuple in the form of (target, onclause).A special two-argument calling form of the form target, onclauseis also accepted.

  1.   -

aliased=False – If True, indicate that the JOIN target should beanonymously aliased. Subsequent calls to filter()and similar will adapt the incoming criterion to the targetalias, until reset_joinpoint() is called.

  1.   -

isouter=False –

If True, the join used will be a left outer join,just as if the Query.outerjoin() method were called. Thisflag is here to maintain consistency with the same flag as acceptedby FromClause.join() and other Core constructs.

New in version 1.0.0.

  1.   -

full=False –

render FULL OUTER JOIN; implies isouter.

New in version 1.1.

  1.   -

from_joinpoint=False – When using aliased=True, a settingof True here will cause the join to be from the most recentjoined target, rather than starting back from the originalFROM clauses of the query.

See also

Querying with Joins in the ORM tutorial.

Mapping Class Inheritance Hierarchies for details on howjoin() is used for inheritance relationships.

orm.join() - a standalone ORM-level join function,used internally by Query.join(), which in previousSQLAlchemy versions was the primary ORM-level joining interface.

  • label(name)
  • Return the full SELECT statement represented by thisQuery, convertedto a scalar subquery with a label of the given name.

Analogous to sqlalchemy.sql.expression.SelectBase.label().

  • lazyloaded_from = None_
  • An InstanceState that is using this Query for alazy load operation.

This can be used for extensions like the horizontal sharding extensionas well as event handlers and custom mapper options to determinewhen a query is being used to lazy load a relationship on an object.

New in version 1.2.9.

  • limit(limit)

  • Apply a LIMIT to the query and return the newly resultingQuery.

  • mergeresult(_iterator, load=True)

  • Merge a result into this Query object’s Session.

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

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

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

For an example of how merge_result() is used, seethe source code for the example Dogpile Caching, wheremerge_result() is used to efficiently restore statefrom a cache back into a target Session.

  • offset(offset)

  • Apply an OFFSET to the query and return the newly resultingQuery.

  • one()

  • Return exactly one result or raise an exception.

Raises sqlalchemy.orm.exc.NoResultFound if the query selectsno rows. Raises sqlalchemy.orm.exc.MultipleResultsFoundif multiple object identities are returned, or if multiplerows are returned for a query that returns only scalar valuesas opposed to full identity-mapped entities.

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

See also

Query.first()

Query.one_or_none()

  • one_or_none()
  • Return at most one result or raise an exception.

Returns None if the query selectsno rows. Raises sqlalchemy.orm.exc.MultipleResultsFoundif multiple object identities are returned, or if multiplerows are returned for a query that returns only scalar valuesas opposed to full identity-mapped entities.

Calling Query.one_or_none() results in an execution of theunderlying query.

New in version 1.0.9: Added Query.one_or_none()

See also

Query.first()

Query.one()

  • onlyreturn_tuples(_value)
    • When set to True, the query results will always be a tuple,
    • specifically for single element queries. The default is False.

. .. versionadded:: 1.2.5

  • options(*args)
  • Return a new Query object, applying the given list ofmapper options.

Most supplied options regard changing how column- andrelationship-mapped attributes are loaded.

See also

Deferred Column Loader Query Options

Relationship Loading with Loader Options

  • orderby(*criterion_)
  • apply one or more ORDER BY criterion to the query and returnthe newly resulting Query

All existing ORDER BY settings can be suppressed bypassing None - this will suppress any ordering configuredon the mapper() object using the deprecatedmapper.order_by parameter.

  • outerjoin(*props, **kwargs)
  • Create a left outer join against this Query object’s criterionand apply generatively, returning the newly resulting Query.

Usage is the same as the join() method.

  • params(*args, **kwargs)
  • add values for bind parameters which may have beenspecified in filter().

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

  • populate_existing()
  • Return a Query that will expire and refresh all instancesas they are loaded, or reused from the current Session.

populate_existing() does not improve behavior whenthe ORM is used normally - the Session object’s usualbehavior of maintaining a transaction and expiring all attributesafter rollback or commit handles object state automatically.This method is not intended for general use.

  • prefixwith(*prefixes_)

  • Apply the prefixes to the query and return the newly resultingQuery.

    • Parameters
    • *prefixes – optional prefixes, typically strings,not using any commas. In particular is useful for MySQL keywordsand optimizer hints:

e.g.:

  1. query = sess.query(User.name).\
  2.     prefix_with('HIGH_PRIORITY').\
  3.     prefix_with('SQL_SMALL_RESULT', 'ALL').\
  4.     prefix_with('/*+ BKA(user) */')

Would render:

  1. SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL /*+ BKA(user) */
  2. users.name AS users_name FROM users

See also

HasPrefixes.prefix_with()

  • reset_joinpoint()
  • Return a new Query, where the “join point” hasbeen reset back to the base FROM entities of the query.

This method is usually used in conjunction with thealiased=True feature of the join()method. See the example in join() for howthis is used.

  • scalar()
  • Return the first element of the first result or Noneif no rows present. If multiple rows are returned,raises MultipleResultsFound.
  1. >>> session.query(Item).scalar()
  2. <Item>
  3. >>> session.query(Item.id).scalar()
  4. 1
  5. >>> session.query(Item.id).filter(Item.id < 0).scalar()
  6. None
  7. >>> session.query(Item.id, Item.name).scalar()
  8. 1
  9. >>> session.query(func.count(Parent.id)).scalar()
  10. 20

This results in an execution of the underlying query.

  • selectentity_from(_from_obj)
  • Set the FROM clause of this Query to acore selectable, applying it as a replacement FROM clausefor corresponding mapped entities.

The Query.select_entity_from() method supplies an alternativeapproach to the use case of applying an aliased() constructexplicitly throughout a query. Instead of referring to thealiased() construct explicitly,Query.select_entity_from() automatically adapts alloccurrences of the entity to the target selectable.

Given a case for aliased() such as selecting Userobjects from a SELECT statement:

  1. select_stmt = select([User]).where(User.id == 7)
  2. user_alias = aliased(User, select_stmt)
  3.  
  4. q = session.query(user_alias).\
  5.     filter(user_alias.name == 'ed')

Above, we apply the user_alias object explicitly throughout thequery. When it’s not feasible for user_alias to be referencedexplicitly in many places, Query.select_entity_from() may beused at the start of the query to adapt the existing User entity:

  1. q = session.query(User).\
  2.     select_entity_from(select_stmt).\
  3.     filter(User.name == 'ed')

Above, the generated SQL will show that the User entity isadapted to our statement, even in the case of the WHERE clause:

  1. SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
  2. FROM (SELECT "user".id AS id, "user".name AS name
  3. FROM "user"
  4. WHERE "user".id = :id_1) AS anon_1
  5. WHERE anon_1.name = :name_1

The Query.select_entity_from() method is similar to theQuery.select_from() method, in that it sets the FROM clauseof the query. The difference is that it additionally appliesadaptation to the other parts of the query that refer to theprimary entity. If above we had used Query.select_from()instead, the SQL generated would have been:

  1. -- uses plain select_from(), not select_entity_from()
  2. SELECT "user".id AS user_id, "user".name AS user_name
  3. FROM "user", (SELECT "user".id AS id, "user".name AS name
  4. FROM "user"
  5. WHERE "user".id = :id_1) AS anon_1
  6. WHERE "user".name = :name_1

To supply textual SQL to the Query.select_entity_from() method,we can make use of the text() construct. However, thetext() construct needs to be aligned with the columns of ourentity, which is achieved by making use of theTextClause.columns() method:

  1. text_stmt = text("select id, name from user").columns(
  2.     User.id, User.name)
  3. q = session.query(User).select_entity_from(text_stmt)

Query.select_entity_from() itself accepts an aliased()object, so that the special options of aliased() such asaliased.adapt_on_names may be used within thescope of the Query.select_entity_from() method’s adaptationservices. Supposea view user_view also returns rows from user. Ifwe reflect this view into a Table, this view has norelationship to the Table to which we are mapped, howeverwe can use name matching to select from it:

  1. user_view = Table('user_view', metadata,
  2.                   autoload_with=engine)
  3. user_view_alias = aliased(
  4.     User, user_view, adapt_on_names=True)
  5. q = session.query(User).\
  6.     select_entity_from(user_view_alias).\
  7.     order_by(User.name)

Changed in version 1.1.7: The Query.select_entity_from()method now accepts an aliased() object as an alternativeto a FromClause object.

  1. - Parameters
  2. -

from_obj – a FromClause object that will replacethe FROM clause of this Query. It also may be an instanceof aliased().

See also

Query.select_from()

  • selectfrom(*fromobj)
  • Set the FROM clause of this Query explicitly.

Query.select_from() is often used in conjunction withQuery.join() in order to control which entity is selectedfrom on the “left” side of the join.

The entity or selectable object here effectively replaces the“left edge” of any calls to join(), when nojoinpoint is otherwise established - usually, the default “joinpoint” is the leftmost entity in the Query object’slist of entities to be selected.

A typical example:

  1. q = session.query(Address).select_from(User).\
  2.     join(User.addresses).\
  3.     filter(User.name == 'ed')

Which produces SQL equivalent to:

  1. SELECT address.* FROM user
  2. JOIN address ON user.id=address.user_id
  3. WHERE user.name = :name_1
  1. - Parameters
  2. -

*from_obj – collection of one or more entities to applyto the FROM clause. Entities can be mapped classes,AliasedClass objects, Mapper objectsas well as core FromClause elements like subqueries.

Changed in version 0.9: This method no longer applies the given FROM objectto be the selectable from which matching entitiesselect from; the select_entity_from() methodnow accomplishes this. See that method for a descriptionof this behavior.

See also

join()

Query.select_entity_from()

  • property selectable
  • Return the Select object emitted by this Query.

Used for inspect() compatibility, this is equivalent to:

  1. query.enable_eagerloads(False).with_labels().statement
  • slice(start, stop)
  • Computes the “slice” of the Query represented bythe given indices and returns the resulting Query.

The start and stop indices behave like the argument to Python’sbuilt-in range() function. This method provides analternative to using LIMIT/OFFSET to get a slice of thequery.

For example,

  1. session.query(User).order_by(User.id).slice(1, 3)

renders as

  1. SELECT users.id AS users_id,
  2.        users.name AS users_name
  3. FROM users ORDER BY users.id
  4. LIMIT ? OFFSET ?
  5. (2, 1)

See also

Query.limit()

Query.offset()

  • property statement
  • The full SELECT statement represented by this Query.

The statement by default will not have disambiguating labelsapplied to the construct unless with_labels(True) is calledfirst.

  • subquery(name=None, with_labels=False, reduce_columns=False)
  • return the full SELECT statement represented bythis Query, embedded within an Alias.

Eager JOIN generation within the query is disabled.

  1. - Parameters
  2. - 
  3.   -

name – string name to be assigned as the alias;this is passed through to FromClause.alias().If None, a name will be deterministically generatedat compile time.

  1.   -

with_labels – if True, with_labels() will be calledon the Query first to apply table-qualified labelsto all columns.

  1.   -

reduce_columns – if True, Select.reduce_columns() willbe called on the resulting select() construct,to remove same-named columns where one also refers to the othervia foreign key or WHERE clause equivalence.

  • suffixwith(*suffixes_)

  • Apply the suffix to the query and return the newly resultingQuery.

    • Parameters
    • *suffixes – optional suffixes, typically strings,not using any commas.

New in version 1.0.0.

See also

Query.prefix_with()

HasSuffixes.suffix_with()

  • union(*q)
  • Produce a UNION of this Query against one or more queries.

e.g.:

  1. q1 = sess.query(SomeClass).filter(SomeClass.foo=='bar')
  2. q2 = sess.query(SomeClass).filter(SomeClass.bar=='foo')
  3.  
  4. q3 = q1.union(q2)

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

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

will nest on each union(), and produces:

  1. SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION
  2.                 SELECT * FROM y) UNION SELECT * FROM Z)

Whereas:

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

produces:

  1. SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION
  2.                 SELECT * FROM Z)

Note that many database backends do not allow ORDER BY tobe rendered on a query called within UNION, EXCEPT, etc.To disable all ORDER BY clauses including those configuredon mappers, issue query.order_by(None) - the resultingQuery object will not render ORDER BY withinits SELECT statement.

  • unionall(*q_)
  • Produce a UNION ALL of this Query against one or more queries.

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

  • update(values, synchronize_session='evaluate', update_args=None)
  • Perform a bulk update query.

Updates rows matched by this query in the database.

E.g.:

  1. sess.query(User).filter(User.age == 25).\
  2.     update({User.age: User.age - 10}, synchronize_session=False)
  3.  
  4. sess.query(User).filter(User.age == 25).\
  5.     update({"age": User.age - 10}, synchronize_session='evaluate')

Warning

The Query.update() method is a “bulk” operation,which bypasses ORM unit-of-work automation in favor of greaterperformance. Please read all caveats and warnings below.

  1. - Parameters
  2. - 
  3.   -

values –

a dictionary with attributes names, or alternativelymapped attributes or SQL expressions, as keys, and literalvalues or sql expressions as values. If parameter-orderedmode is desired, the values can bepassed as a list of 2-tuples;this requires that thepreserve_parameter_orderflag is passed to the Query.update.update_args dictionaryas well.

Changed in version 1.0.0: - string names in the values dictionaryare now resolved against the mapped entity; previously, thesestrings were passed as literal column names with no mapper-leveltranslation.

  1.   -

synchronize_session –

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

False - don’t synchronize the session. This option is the mostefficient and is reliable once the session is expired, whichtypically occurs after a commit(), or explicitly usingexpire_all(). Before the expiration, updated objects may stillremain in the session with stale values on their attributes, whichcan lead to confusing results.

'fetch' - performs a select query before the update to findobjects that are matched by the update query. The updatedattributes are expired on matched objects.

'evaluate' - Evaluate the Query’s criteria in Python straighton the objects in the session. If evaluation of the criteria isn’timplemented, an exception is raised.

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

  1.   -

update_args –

Optional dictionary, if present will be passedto the underlying update() construct as the **kw forthe object. May be used to pass dialect-specific arguments suchas mysql_limit, as well as other special arguments such aspreserve_parameter_order.

New in version 1.0.0.

  1. - Returns
  2. -

the count of rows matched as returned by the database’s“row count” feature.

Warning

Additional Caveats for bulk query updates

  1. -

The method does not offer in-Python cascading ofrelationships - it is assumed that ON UPDATE CASCADE isconfigured for any foreign key references which requireit, otherwise the database may emit an integrityviolation if foreign key references are being enforced.

After the UPDATE, dependent objects in theSession which were impacted by an ON UPDATECASCADE may not contain the current state; this issue isresolved once the Session is expired, whichnormally occurs upon Session.commit() or can beforced by using Session.expire_all().

  1. -

The 'fetch' strategy results in an additionalSELECT statement emitted and will significantly reduceperformance.

  1. -

The 'evaluate' strategy performs a scan ofall matching objects within the Session; if thecontents of the Session are expired, such asvia a proceeding Session.commit() call, this willresult in SELECT queries emitted for every matching object.

  1. -

The method supports multiple table updates, as detailedin Multiple Table Updates, and this behavior doesextend to support updates of joined-inheritance andother multiple table mappings. However, the joincondition of an inheritance mapper is notautomatically rendered. Care must be taken in anymultiple-table update to explicitly include the joiningcondition between those tables, even in mappings wherethis is normally automatic. E.g. if a class Engineersubclasses Employee, an UPDATE of the Engineerlocal table using criteria against the Employeelocal table might look like:

  1. session.query(Engineer).\
  2.     filter(Engineer.id == Employee.id).\
  3.     filter(Employee.name == 'dilbert').\
  4.     update({"engineer_type": "programmer"})
  1. -

The polymorphic identity WHERE criteria is not includedfor single- orjoined- table updates - this must be added manually, evenfor single table inheritance.

  1. -

The MapperEvents.before_update() andMapperEvents.after_update()events are not invoked from this method. Instead, theSessionEvents.after_bulk_update() method is provided toact upon a mass UPDATE of entity rows.

See also

Query.delete()

Inserts, Updates and Deletes - Core SQL tutorial

  • value(column)

  • Return a scalar result corresponding to the givencolumn expression.

  • values(*columns)

  • Return an iterator yielding result tuples correspondingto the given list of columns

  • property whereclause

  • A readonly attribute which returns the current WHERE criterion forthis Query.

This returned value is a SQL expression construct, or None if nocriterion has been established.

  • withentities(*entities_)
  • Return a new Query replacing the SELECT list with thegiven entities.

e.g.:

  1. # Users, filtered on some arbitrary criterion
  2. # and then ordered by related email address
  3. q = session.query(User).\
  4.             join(User.address).\
  5.             filter(User.name.like('%ed%')).\
  6.             order_by(Address.email)
  7.  
  8. # given *only* User.id==5, Address.email, and 'q', what
  9. # would the *next* User in the result be ?
  10. subq = q.with_entities(Address.email).\
  11.             order_by(None).\
  12.             filter(User.id==5).\
  13.             subquery()
  14. q = q.join((subq, subq.c.email < Address.email)).\
  15.             limit(1)
  • withfor_update(_read=False, nowait=False, of=None, skip_locked=False, key_share=False)
  • return a new Query with the specified options for theFOR UPDATE clause.

The behavior of this method is identical to that ofSelectBase.with_for_update(). When called with no arguments,the resulting SELECT statement will have a FOR UPDATE clauseappended. When additional arguments are specified, backend-specificoptions such as FOR UPDATE NOWAIT or LOCK IN SHARE MODEcan take effect.

E.g.:

  1. q = sess.query(User).with_for_update(nowait=True, of=User)

The above query on a PostgreSQL backend will render like:

  1. SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT

New in version 0.9.0: Query.with_for_update() supersedesthe Query.with_lockmode() method.

See also

GenerativeSelect.with_for_update() - Core level method withfull argument and behavioral description.

  • withhint(_selectable, text, dialect_name='*')
  • Add an indexing or other executional contexthint for the given entity or selectable tothis Query.

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

See also

Query.with_statement_hint()

:meth:..Query.prefix_with - generic SELECT prefixing which alsocan suit some database-specific HINT syntaxes such as MySQLoptimizer hints

  • with_labels()
  • Apply column labels to the return value of Query.statement.

Indicates that this Query’s statement accessor should returna SELECT statement that applies labels to all columns in theform _; this is commonly used todisambiguate columns from multiple tables which have the samename.

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

Note

The Query.with_labels() method only appliesthe output of Query.statement, and not to any ofthe result-row invoking systems of Query itself, e.g.Query.first(), Query.all(), etc. To executea query using Query.with_labels(), invoke theQuery.statement using Session.execute():

  1. result = session.execute(query.with_labels().statement)
  • withlockmode(_mode)
  • Return a new Query object with the specified “locking mode”,which essentially refers to the FOR UPDATE clause.

Deprecated since version 0.9: The Query.with_lockmode() method is deprecated and will be removed in a future release. Please refer to Query.with_for_update(). 

  1. - Parameters
  2. -

mode –

a string representing the desired locking mode.Valid values are:

  1.   -

None - translates to no lockmode

  1.   -

'update' - translates to FOR UPDATE(standard SQL, supported by most dialects)

  1.   -

'update_nowait' - translates to FOR UPDATE NOWAIT(supported by Oracle, PostgreSQL 8.1 upwards)

  1.   -

'read' - translates to LOCK IN SHARE MODE (for MySQL),and FOR SHARE (for PostgreSQL)

See also

Query.with_for_update() - improved API forspecifying the FOR UPDATE clause.

  • withparent(_instance, property=None, from_entity=None)
  • Add filtering criterion that relates the given instanceto a child object or collection, using its attribute stateas well as an established relationship()configuration.

The method uses the with_parent() function to generatethe clause, the result of which is passed to Query.filter().

Parameters are the same as with_parent(), with the exceptionthat the given property can be None, in which case a search isperformed against this Query object’s target mapper.

  1. - Parameters
  2. - 
  3.   -

instance – An instance which has some relationship().

  1.   -

property – String property name, or class-bound attribute, which indicateswhat relationship from the instance should be used to reconcile theparent/child relationship.

  1.   -

from_entity – Entity in which to consider as the left side. This defaults to the“zero” entity of the Query itself.

  • withpolymorphic(_cls_or_mappers, selectable=None, polymorphic_on=None)
  • Load columns for inheriting classes.

Query.with_polymorphic() applies transformationsto the “main” mapped class represented by this Query.The “main” mapped class here means the Queryobject’s first argument is a full class, i.e.session.query(SomeClass). These transformations allow additionaltables to be present in the FROM clause so that columns for ajoined-inheritance subclass are available in the query, both for thepurposes of load-time efficiency as well as the ability to usethese columns at query time.

See the documentation section Using with_polymorphic fordetails on how this method is used.

  • withsession(_session)
  • Return a Query that will use the given Session.

While the Query object is normally instantiated using theSession.query() method, it is legal to build the Querydirectly without necessarily using a Session. Such aQuery object, or any Query already associatedwith a different Session, can produce a new Queryobject associated with a target session using this method:

  1. from sqlalchemy.orm import Query
  2.  
  3. query = Query([MyClass]).filter(MyClass.id == 5)
  4.  
  5. result = query.with_session(my_session).one()
  • withstatement_hint(_text, dialect_name='*')
  • add a statement hint to this Select.

This method is similar to Select.with_hint() except thatit does not require an individual table, and instead applies to thestatement as a whole.

This feature calls down into Select.with_statement_hint().

New in version 1.0.0.

See also

Query.with_hint()

  • withtransformation(_fn)
  • Return a new Query object transformed bythe given function.

E.g.:

  1. def filter_something(criterion):
  2.     def transform(q):
  3.         return q.filter(criterion)
  4.     return transform
  5.  
  6. q = q.with_transformation(filter_something(x==5))

This allows ad-hoc recipes to be created for Queryobjects. See the example at Building Transformers.

  • yieldper(_count)
  • Yield only count rows at a time.

The purpose of this method is when fetching very large result sets(> 10K rows), to batch results in sub-collections and yield themout partially, so that the Python interpreter doesn’t need to declarevery large areas of memory which is both time consuming and leadsto excessive memory use. The performance from fetching hundreds ofthousands of rows can often double when a suitable yield-per setting(e.g. approximately 1000) is used, even with DBAPIs that bufferrows (which are most).

The Query.yield_per() method is not compatiblesubqueryload eager loading or joinedload eager loading whenusing collections. It is potentially compatible with “select in”eager loading, provided the database driver supports multiple,independent cursors (pysqlite and psycopg2 are known to work,MySQL and SQL Server ODBC drivers do not).

Therefore in some cases, it may be helpful to disableeager loads, either unconditionally withQuery.enable_eagerloads():

  1. q = sess.query(Object).yield_per(100).enable_eagerloads(False)

Or more selectively using lazyload(); such as withan asterisk to specify the default loader scheme:

  1. q = sess.query(Object).yield_per(100).\
  2.     options(lazyload('*'), joinedload(Object.some_related))

Warning

Use this method with caution; if the same instance ispresent in more than one batch of rows, end-user changesto attributes will be overwritten.

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

Also note that whileyield_per() will set thestream_results execution option to True, currentlythis is only understood bypsycopg2,mysqldb andpymysql dialectswhich will stream results using server side cursorsinstead of pre-buffer all rows for this query. OtherDBAPIs pre-buffer all rows before making themavailable. The memory use of raw database rows is much lessthan that of an ORM-mapped object, but should still be taken intoconsideration when benchmarking.

See also

Query.enable_eagerloads()

ORM-Specific Query Constructs

  • sqlalchemy.orm.aliased(element, alias=None, name=None, flat=False, adapt_on_names=False)
  • Produce an alias of the given element, usually an AliasedClassinstance.

E.g.:

  1. my_alias = aliased(MyClass)
  2.  
  3. session.query(MyClass, my_alias).filter(MyClass.id > my_alias.id)

The aliased() function is used to create an ad-hoc mappingof a mapped class to a new selectable. By default, a selectableis generated from the normally mapped selectable (typically aTable) using the FromClause.alias() method.However, aliased() can also be used to link the class toa new select() statement. Also, the with_polymorphic()function is a variant of aliased() that is intended to specifya so-called “polymorphic selectable”, that corresponds to the unionof several joined-inheritance subclasses at once.

For convenience, the aliased() function also accepts plainFromClause constructs, such as a Table orselect() construct. In those cases, the FromClause.alias()method is called on the object and the new Alias objectreturned. The returned Alias is not ORM-mapped in this case.

  • Parameters

    • element – element to be aliased. Is normally a mapped class,but for convenience can also be a FromClause element.

    • alias – Optional selectable unit to map the element to. This shouldnormally be a Alias object corresponding to the Tableto which the class is mapped, or to a select() construct thatis compatible with the mapping. By default, a simple anonymousalias of the mapped table is generated.

    • name – optional string name to use for the alias, if not specifiedby the alias parameter. The name, among other things, forms theattribute name that will be accessible via tuples returned by aQuery object.

    • flat –

Boolean, will be passed through to theFromClause.alias() call so that aliases of Join objectsdon’t include an enclosing SELECT. This can lead to more efficientqueries in many circumstances. A JOIN against a nested JOIN will berewritten as a JOIN against an aliased SELECT subquery on backends thatdon’t support this syntax.

New in version 0.9.0.

See also

Join.alias()

  1. -

adapt_on_names –

if True, more liberal “matching” will be used whenmapping the mapped columns of the ORM entity to those of thegiven selectable - a name-based match will be performed if thegiven selectable doesn’t otherwise have a column that correspondsto one on the entity. The use case for this is when associatingan entity with some derived selectable such as one that usesaggregate functions:

  1. class UnitPrice(Base):
  2.     __tablename__ = 'unit_price'
  3.     ...
  4.     unit_id = Column(Integer)
  5.     price = Column(Numeric)
  6.  
  7. aggregated_unit_price = Session.query(
  8.                             func.sum(UnitPrice.price).label('price')
  9.                         ).group_by(UnitPrice.unit_id).subquery()
  10.  
  11. aggregated_unit_price = aliased(UnitPrice,
  12.             alias=aggregated_unit_price, adapt_on_names=True)

Above, functions on aggregated_unit_price which refer to.price will return thefunc.sum(UnitPrice.price).label('price') column, as it ismatched on the name “price”. Ordinarily, the “price” functionwouldn’t have any “column correspondence” to the actualUnitPrice.price column as it is not a proxy of the original.

  • class sqlalchemy.orm.util.AliasedClass(cls, alias=None, name=None, flat=False, adapt_on_names=False, with_polymorphic_mappers=(), with_polymorphic_discriminator=None, base_alias=None, use_mapper_path=False, represents_outer_join=False)
  • Represents an “aliased” form of a mapped class for usage with Query.

The ORM equivalent of a alias()construct, this object mimics the mapped class using agetattr scheme and maintains a reference to areal Alias object.

A primary purpose of AliasedClass is to serve as an alternatewithin a SQL statement generated by the ORM, such that an existingmapped entity can be used in multiple contexts. A simple example:

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

AliasedClass is also capable of mapping an existing mappedclass to an entirely new selectable, provided this selectable is column-compatible with the existing mapped selectable, and it can also beconfigured in a mapping as the target of a relationship().See the links below for examples.

The AliasedClass object is constructed typically using theorm.aliased() function. It also is produced with additionalconfiguration when using the orm.with_polymorphic() function.

The resulting object is an instance of AliasedClass.This object implements an attribute scheme which produces thesame attribute and method interface as the original mappedclass, allowing AliasedClass to be compatiblewith any attribute technique which works on the original class,including hybrid attributes (see Hybrid Attributes).

The AliasedClass can be inspected for its underlyingMapper, aliased selectable, and other informationusing inspect():

  1. from sqlalchemy import inspect
  2. my_alias = aliased(MyClass)
  3. insp = inspect(my_alias)

The resulting inspection object is an instance of AliasedInsp.

See also

aliased()

with_polymorphic()

Relationship to Aliased Class

Row-Limited Relationships with Window Functions

  • class sqlalchemy.orm.util.AliasedInsp(entity, mapper, selectable, name, with_polymorphic_mappers, polymorphic_on, basealias, usemapper_path, adapt_on_names, represents_outer_join)
  • Bases: sqlalchemy.orm.base.InspectionAttr

Provide an inspection interface for anAliasedClass object.

The AliasedInsp object is returnedgiven an AliasedClass using theinspect() function:

  1. from sqlalchemy import inspect
  2. from sqlalchemy.orm import aliased
  3.  
  4. my_alias = aliased(MyMappedClass)
  5. insp = inspect(my_alias)

Attributes on AliasedInspinclude:

  • entity - the AliasedClass represented.

  • mapper - the Mapper mapping the underlying class.

  • selectable - the Alias construct which ultimatelyrepresents an aliased Table or Selectconstruct.

  • name - the name of the alias. Also is used as the attributename when returned in a result tuple from Query.

  • with_polymorphic_mappers - collection of Mapper objectsindicating all those mappers expressed in the select constructfor the AliasedClass.

  • polymorphic_on - an alternate column or SQL expression whichwill be used as the “discriminator” for a polymorphic load.

See also

Runtime Inspection API

A grouping of SQL expressions that are returned by a Queryunder one namespace.

The Bundle essentially allows nesting of the tuple-basedresults returned by a column-oriented Query object. It alsois extensible via simple subclassing, where the primary capabilityto override is that of how the set of expressions should be returned,allowing post-processing as well as custom return types, withoutinvolving ORM identity-mapped classes.

New in version 0.9.0.

See also

Column Bundles

  • init(name, *exprs, **kw)
  • Construct a new Bundle.

e.g.:

  1. bn = Bundle("mybundle", MyClass.x, MyClass.y)
  2.  
  3. for row in session.query(bn).filter(
  4.         bn.c.x == 5).filter(bn.c.y == 4):
  5.     print(row.mybundle.x, row.mybundle.y)
  1. - Parameters
  2. - 
  3.   -

name – name of the bundle.

  1.   -

*exprs – columns or SQL expressions comprising the bundle.

  1.   -

single_entity=False – if True, rows for this Bundlecan be returned as a “single entity” outside of any enclosing tuplein the same manner as a mapped entity.

  • c = None

  • An alias for Bundle.columns.

  • columns = None

  • A namespace of SQL expressions referred to by this Bundle.

e.g.:

  1. bn = Bundle("mybundle", MyClass.x, MyClass.y)q = sess.query(bn).filter(bn.c.x == 5)

Nesting of bundles is also supported:

  1. b1 = Bundle("b1",        Bundle('b2', MyClass.a, MyClass.b),        Bundle('b3', MyClass.x, MyClass.y)    )q = sess.query(b1).filter(    b1.c.b2.c.a == 5).filter(b1.c.b3.c.y == 9)

See also

Bundle.c

  • createrow_processor(_query, procs, labels)
  • Produce the “row processing” function for this Bundle.

May be overridden by subclasses.

See also

Column Bundles - includes an example of subclassing.

  • label(name)

  • Provide a copy of this Bundle passing a new label.

  • singleentity = False_

  • If True, queries for a single Bundle will be returned as a singleentity, rather than an element within a keyed tuple.
  • class sqlalchemy.util.KeyedTuple
  • Bases: sqlalchemy.util._collections.AbstractKeyedTuple

tuple subclass that adds labeled names.

E.g.:

  1. >>> k = KeyedTuple([1, 2, 3], labels=["one", "two", "three"])
  2. >>> k.one
  3. 1
  4. >>> k.two
  5. 2

Result rows returned by Query that contain multipleORM entities and/or column expressions make use of thisclass to return rows.

The KeyedTuple exhibits similar behavior to thecollections.namedtuple() construct provided in the Pythonstandard library, however is architected very differently.Unlike collections.namedtuple(), KeyedTuple isdoes not rely on creation of custom subtypes in order to representa new series of keys, instead each KeyedTuple instancereceives its list of keys in place. The subtype approachof collections.namedtuple() introduces significant complexityand performance overhead, which is not necessary for theQuery object’s use case.

See also

Querying

  • _asdict()
  • Return the contents of this KeyedTuple as a dictionary.

This method provides compatibility with collections.namedtuple(),with the exception that the dictionary returned is not ordered.

  • property _fields
  • Return a tuple of string key names for this KeyedTuple.

This method provides compatibility with collections.namedtuple().

See also

KeyedTuple.keys()

  • keys()

inherited from the keys() method of AbstractKeyedTuple

Return a list of string key names for this KeyedTuple.

See also

KeyedTuple._fields

  • class sqlalchemy.orm.strategyoptions.Load(_entity)
  • Bases: sqlalchemy.sql.expression.Generative, sqlalchemy.orm.interfaces.MapperOption

Represents loader options which modify the state of aQuery in order to affect how various mapped attributes areloaded.

The Load object is in most cases used implicitly behind thescenes when one makes use of a query option like joinedload(),defer(), or similar. However, the Load objectcan also be used directly, and in some cases can be useful.

To use Load directly, instantiate it with the target mappedclass as the argument. This style of usage isuseful when dealing with a Query that has multiple entities:

  1. myopt = Load(MyClass).joinedload("widgets")

The above myopt can now be used with Query.options(), where itwill only take effect for the MyClass entity:

  1. session.query(MyClass, MyOtherClass).options(myopt)

One case where Load is useful as public API is when specifying“wildcard” options that only take effect for a certain class:

  1. session.query(Order).options(Load(Order).lazyload('*'))

Above, all relationships on Order will be lazy-loaded, but otherattributes on those descendant objects will load using their normalloader strategy.

See also

Deferred Column Loader Query Options

Deferred Loading across Multiple Entities

Relationship Loading with Loader Options

  • bakedlazyload(_attr)
  • Produce a new Load object with theorm.baked_lazyload() option applied.

See orm.baked_lazyload() for usage examples.

See orm.contains_eager() for usage examples.

See orm.defaultload() for usage examples.

See orm.defer() for usage examples.

See orm.immediateload() for usage examples.

See orm.joinedload() for usage examples.

See orm.lazyload() for usage examples.

See orm.load_only() for usage examples.

See orm.noload() for usage examples.

  • options(*opts)
  • Apply a series of options as sub-options to this Loadobject.

E.g.:

  1. query = session.query(Author)
  2. query = query.options(
  3.             joinedload(Author.book).options(
  4.                 load_only("summary", "excerpt"),
  5.                 joinedload(Book.citations).options(
  6.                     joinedload(Citation.author)
  7.                 )
  8.             )
  9.         )
  1. - Parameters
  2. -

*opts – A series of loader option objects (ultimatelyLoad objects) which should be applied to the pathspecified by this Load object.

New in version 1.3.6.

See also

defaultload()

Relationship Loading with Loader Options

Deferred Loading across Multiple Entities

  • processquery(_query)

  • Apply a modification to the given Query.

  • processquery_conditionally(_query)

  • same as process_query(), except that this option may notapply to the given query.

This is typically used during a lazy load or scalar refreshoperation to propagate options stated in the original Query to thenew Query being used for the load. It occurs for those options thatspecify propagate_to_loaders=True.

  • raiseload(attr, sql_only=False)
  • Produce a new Load object with theorm.raiseload() option applied.

See orm.raiseload() for usage examples.

See orm.selectin_polymorphic() for usage examples.

See orm.selectinload() for usage examples.

See orm.subqueryload() for usage examples.

See orm.undefer() for usage examples.

See orm.undefer_group() for usage examples.

See orm.with_expression() for usage examples.

  • sqlalchemy.orm.join(left, right, onclause=None, isouter=False, full=False, join_to_left=None)
  • Produce an inner join between left and right clauses.

orm.join() is an extension to the core join interfaceprovided by sql.expression.join(), where theleft and right selectables may be not only core selectableobjects such as Table, but also mapped classes orAliasedClass instances. The “on” clause canbe a SQL expression, or an attribute or string namereferencing a configured relationship().

orm.join() is not commonly needed in modern usage,as its functionality is encapsulated within that of theQuery.join() method, which features asignificant amount of automation beyond orm.join()by itself. Explicit usage of orm.join()with Query involves usage of theQuery.select_from() method, as in:

  1. from sqlalchemy.orm import join
  2. session.query(User).\
  3.     select_from(join(User, Address, User.addresses)).\
  4.     filter(Address.email_address=='foo@bar.com')

In modern SQLAlchemy the above join can be written moresuccinctly as:

  1. session.query(User).\
  2.         join(User.addresses).\
  3.         filter(Address.email_address=='foo@bar.com')

See Query.join() for information on modern usageof ORM level joins.

Deprecated since version 0.8: the join_to_left parameter is deprecated, and will be removedin a future release. The parameter has no effect.

  • sqlalchemy.orm.outerjoin(left, right, onclause=None, full=False, join_to_left=None)
  • Produce a left outer join between left and right clauses.

This is the “outer join” version of the orm.join() function,featuring the same behavior except that an OUTER JOIN is generated.See that function’s documentation for other usage details.

  • sqlalchemy.orm.withparent(_instance, prop, from_entity=None)
  • Create filtering criterion that relates this query’s primary entityto the given related instance, using established relationship()configuration.

The SQL rendered is the same as that rendered when a lazy loaderwould fire off from the given parent on that attribute, meaningthat the appropriate state is taken from the parent object inPython without the need to render joins to the parent tablein the rendered statement.

  • Parameters

    • instance – An instance which has some relationship().

    • property – String property name, or class-bound attribute, which indicateswhat relationship from the instance should be used to reconcile theparent/child relationship.

    • from_entity –

Entity in which to consider as the left side. This defaults to the“zero” entity of the Query itself.

New in version 1.2.