This page is part of the .

Legacy Query API

About the Legacy Query API

This page contains the Python generated documentation for the Query construct, which for many years was the sole SQL interface when working with the SQLAlchemy ORM. As of version 2.0, an all new way of working is now the standard approach, where the same construct that works for Core works just as well for the ORM, providing a consistent interface for building queries.

For any application that is built on the SQLAlchemy ORM prior to the 2.0 API, the Query API will usually represents the vast majority of database access code within an application, and as such the majority of the API is not being removed from SQLAlchemy. The Query object behind the scenes now translates itself into a 2.0 style object when the Query object is executed, so it now is just a very thin adapter API.

For a guide to migrating an application based on to 2.0 style, see 2.0 Migration - ORM Usage.

For an introduction to writing SQL for ORM objects in the 2.0 style, start with the . Additional reference for 2.0 style querying is at ORM Querying Guide.

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

Following is the full interface for the object.

class sqlalchemy.orm.Query

ORM-level SQL construction object.

Legacy Feature

The ORM Query object is a legacy construct as of SQLAlchemy 2.0. See the notes at the top of for an overview, including links to migration documentation.

Query objects are normally initially generated using the method of Session, and in less common cases by instantiating the directly and associating with a Session using the method.

Members

__init__(), , add_columns(), , all(), , as_scalar(), , column_descriptions, , count(), , delete(), , enable_assertions(), , except_(), , execution_options(), , filter(), , first(), , get(), , get_execution_options(), , group_by(), , instances(), , intersect_all(), , join(), , lazy_loaded_from, , merge_result(), , one(), , only_return_tuples(), , order_by(), , params(), , prefix_with(), , scalar(), , select_from(), , set_label_style(), , statement, , suffix_with(), , union(), , update(), , values(), , whereclause, , with_for_update(), , with_labels(), , with_session(), , with_transformation(),

Class signature

class sqlalchemy.orm.Query (, sqlalchemy.sql.annotation.SupportsCloneAnnotations, , sqlalchemy.sql.expression.HasSuffixes, sqlalchemy.sql.expression.HasHints, sqlalchemy.event.registry.EventTarget, , sqlalchemy.sql.expression.Generative, sqlalchemy.sql.expression.Executable, typing.Generic)

method __init__(entities: Sequence[_ColumnsClauseArgument[Any]], session: Optional[] = None)

Construct a Query directly.

E.g.:
```
q = Query([User, Address], session=some_session)
```
The above is equivalent to:
```
q = some_session.query(User, Address)
```
- Parameters:
  - entities – a sequence of entities and/or SQL expressions.
  - session – a with which the Query will be associated. Optional; a can be associated with a Session generatively via the method as well.

See also
[Session.query()]($694f628462946390.md#sqlalchemy.orm.Session.query "sqlalchemy.orm.Session.query")
[Query.with\_session()](#sqlalchemy.orm.Query.with_session "sqlalchemy.orm.Query.with_session")

method sqlalchemy.orm.Query.add_column(column: _ColumnExpressionArgument[Any]) → [Any]

Add a column expression to the list of result columns to be returned.

Deprecated since version 1.4: Query.add_column() is deprecated and will be removed in a future release. Please use
method sqlalchemy.orm.Query.add_columns(*column: _ColumnExpressionArgument[Any]) → [Any]

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

See also

Select.add_columns() - v2 comparable method.
method add_entity(entity: _EntityType[Any], alias: Optional[Union[Alias, ]] = None) → Query[Any]

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

See also

- v2 comparable method.
method sqlalchemy.orm.Query.all() → List[_T]

Return the results represented by this as a list.

This results in an execution of the underlying SQL statement.

Warning

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

See also

- v2 comparable method.

Result.scalars() - v2 comparable method.
method apply_labels() → SelfQuery

Deprecated since version 2.0: The Query.with_labels() and method is considered legacy as of the 1.x series of SQLAlchemy and becomes a legacy construct in 2.0. Use set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) instead. (Background on SQLAlchemy 2.0 at: SQLAlchemy 2.0 - Major Migration Guide)
method as_scalar() → ScalarSelect[Any]

Return the full SELECT statement represented by this , converted to a scalar subquery.

Deprecated since version 1.4: The Query.as_scalar() method is deprecated and will be removed in a future release. Please refer to .
method sqlalchemy.orm.Query.autoflush(setting: bool) → SelfQuery

Return a Query with a specific ‘autoflush’ setting.

As of SQLAlchemy 1.4, the method is equivalent to using the autoflush execution option at the ORM level. See the section Autoflush for further background on this option.

attribute column_descriptions

Return metadata about the columns which would be returned by this Query.

Format is a list of dictionaries:

user_alias = aliased(User, name='user2')
q = sess.query(User, User.id, user_alias)
# this expression:
q.column_descriptions
# would return:
[
    {
        'name':'User',
        'type':User,
        'aliased':False,
        'expr':User,
        'entity': User
    },
    {
        'name':'id',
        'type':Integer(),
        'aliased':False,
        'expr':User.id,
        'entity': User
    },
    {
        'name':'user2',
        'type':User,
        'aliased':True,
        'expr':user_alias,
        'entity': user_alias
    }
]

See also

Select.cte() - v2 equivalent method.

method delete(synchronize_session: SynchronizeSessionArgument = ‘auto’) → int

Perform a DELETE with an arbitrary WHERE clause.

Deletes rows matched by this query from the database.

E.g.:
```
sess.query(User).filter(User.age == 25).\
    delete(synchronize_session=False)
sess.query(User).filter(User.age == 25).\
    delete(synchronize_session='evaluate')
```
Warning

See the section ORM-Enabled INSERT, UPDATE, and DELETE statements for important caveats and warnings, including limitations when using bulk UPDATE and DELETE with mapper inheritance configurations.
- Parameters:
  
  synchronize_session – chooses the strategy to update the attributes on objects in the session. See the section for a discussion of these strategies.
  
  Returns:
  
  the count of rows matched as returned by the database’s “row count” feature.
See also

ORM-Enabled INSERT, UPDATE, and DELETE statements
method distinct(*expr: _ColumnExpressionArgument[Any]) → SelfQuery

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

Note

The ORM-level distinct() call includes logic that will automatically add columns from the ORDER BY of the query to the columns clause of the SELECT statement, to satisfy the common need of the database backend that ORDER BY columns be part of the SELECT list when DISTINCT is used. These columns are not added to the list of columns actually fetched by the , however, so would not affect results. The columns are passed through when using the Query.statement accessor, however.

Deprecated since version 2.0: This logic is deprecated and will be removed in SQLAlchemy 2.0. See for a description of this use case in 2.0.

See also

Select.distinct() - v2 equivalent method.
- Parameters:
  
  *expr –
  
  optional column expressions. When present, the PostgreSQL dialect will render a DISTINCT ON (<expressions>) construct.
  
  Deprecated since version 1.4: Using *expr in other dialects is deprecated and will raise in a future version.
method sqlalchemy.orm.Query.enable_assertions(value: bool) → SelfQuery

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.
method enable_eagerloads(value: bool) → SelfQuery

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(), 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, or when using Query.yield_per().
method except_(*q: Query) → SelfQuery

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

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

See also

- v2 equivalent method.
method sqlalchemy.orm.Query.except_all(*q: ) → SelfQuery

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

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

See also

Select.except_all() - v2 equivalent method.
method execution_options(**kwargs: Any) → SelfQuery

Set non-SQL options which take effect during execution.

Options allowed here include all of those accepted by Connection.execution_options(), as well as a series of ORM specific options:

populate_existing=True - equivalent to using

autoflush=True|False - equivalent to using Query.autoflush()

yield_per=<value> - equivalent to using

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

New in version 1.4: - added ORM options to Query.execution_options()

The execution options may also be specified on a per execution basis when using queries via the Session.execution_options parameter.

Warning

The parameter should not be used at the level of individual ORM statement executions, as the Session will not track objects from different schema translate maps within a single session. For multiple schema translate maps within the scope of a single , see Horizontal Sharding.

See also

Query.get_execution_options()

- v2 equivalent method.
method sqlalchemy.orm.Query.exists() →

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

e.g.:
```
q = session.query(User).filter(User.name == 'fred')
session.query(q.exists())
```
Producing SQL similar to:
```
SELECT EXISTS (
    SELECT 1 FROM users WHERE users.name = :name_1
) AS anon_1
```
The EXISTS construct is usually used in the WHERE clause:
```
session.query(User.id).filter(q.exists()).scalar()
```
Note that some databases such as SQL Server don’t allow an EXISTS expression to be present in the columns clause of a SELECT. To select a simple boolean value based on the exists as a WHERE, use literal():
```
from sqlalchemy import literal
session.query(literal(True)).filter(q.exists()).scalar()
```
See also

- v2 comparable method.
method sqlalchemy.orm.Query.filter(*criterion: _ColumnExpressionArgument[bool]) → SelfQuery

Apply the given filtering criterion to a copy of this , using SQL expressions.

e.g.:
Multiple criteria may be specified as comma separated; the effect is that they will be joined together using the and_() function:
```
session.query(MyClass).\
    filter(MyClass.name == 'some name', MyClass.id > 5)
```
The criterion is any SQL expression object applicable to the WHERE clause of a select. String expressions are coerced into SQL expression constructs via the construct.

See also

Query.filter_by() - filter on keyword expressions.

- v2 equivalent method.
method sqlalchemy.orm.Query.filter_by(**kwargs: Any) → SelfQuery

Apply the given filtering criterion to a copy of this , using keyword expressions.

e.g.:
```
session.query(MyClass).filter_by(name = 'some name')
```
Multiple criteria may be specified as comma separated; the effect is that they will be joined together using the and_() function:
```
session.query(MyClass).\
    filter_by(name = 'some name', id = 5)
```
The keyword expressions are extracted from the primary entity of the query, or the last entity that was the target of a call to .

See also

Query.filter() - filter on SQL expressions.

- v2 comparable method.
method sqlalchemy.orm.Query.first() → Optional[_T]

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 results in an execution of the underlying query.

See also

Query.one()

Result.first() - v2 comparable method.

- v2 comparable method.
method sqlalchemy.orm.Query.from_statement(statement: ExecutableReturnsRows) → SelfQuery

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

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

See also

Select.from_statement() - v2 comparable method.
method get(ident: _PKIdentityArgument) → Optional[Any]

Return an instance based on the given primary key identifier, or None if not found.

Deprecated since version 2.0: The Query.get() method is considered legacy as of the 1.x series of SQLAlchemy and becomes a legacy construct in 2.0. The method is now available as (Background on SQLAlchemy 2.0 at: SQLAlchemy 2.0 - Major Migration Guide)

E.g.:
```
my_user = session.query(User).get(5)
some_object = session.query(VersionedFoo).get((5, 10))
some_object = session.query(VersionedFoo).get(
    {"id": 5, "version_id": 10})
```
is special in that it provides direct access to the identity map of the owning Session. If the given primary key identifier is present in the local identity map, the object is returned directly 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.

also will perform a check if the object is present in the identity map and marked as expired - a SELECT is emitted to refresh the object as well as to ensure that the row is still present. If not, ObjectDeletedError is raised.

is only used to return a single mapped instance, not multiple instances or individual column constructs, and strictly on a single primary key value. The originating Query must be constructed in this way, i.e. against a single mapped entity, with no additional filtering criterion. Loading options via may be applied however, and will be used if the object is not yet locally present.
- Parameters:
  
  ident –
  
  A scalar, tuple, or dictionary representing the primary 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 typically the most expedient. If the primary key of a row is the value “5”, the call looks like:
```
my_object = query.get(5)
```
  The tuple form contains primary key values typically in the order in which they correspond to the mapped Table object’s primary key columns, or if the configuration parameter were used, in the order used for that parameter. For example, if the primary key of a row is represented by the integer digits “5, 10” the call would look like:
  
  The dictionary form should include as keys the mapped attribute names corresponding to each element of the primary key. If the mapped class has the attributes id, version_id as the attributes which store the object’s primary key value, the call would look like:
```
my_object = query.get({"id": 5, "version_id": 10})
```
  New in version 1.3: the Query.get() method now optionally accepts a dictionary of attribute names to values in order to indicate a primary key identifier.
  
  Returns:
  
  The object instance, or None.
method get_children(*, omit_attrs: Tuple[str, …] = (), **kw: Any) → Iterable[HasTraverseInternals]

inherited from the HasTraverseInternals.get_children() method of HasTraverseInternals

Return immediate child HasTraverseInternals elements of this HasTraverseInternals.

This is used for visit traversal.

**kw may contain flags that change the collection that is returned, for example to return a subset of items in order to cut down on larger traversals, or to return child items from a different context (such as schema-level collections instead of clause-level).
method sqlalchemy.orm.Query.get_execution_options() → _ImmutableExecuteOptions

Get the non-SQL options which will take effect during execution.

New in version 1.3.

See also

Select.get_execution_options() - v2 comparable method.
attribute get_label_style

Retrieve the current label style.

New in version 1.4.

See also

Select.get_label_style() - v2 equivalent method.
method group_by(_Query\_first: Union[Literal[None, False, _NoArg.NO_ARG], _ColumnExpressionOrStrLabelArgument[Any]] = _NoArg.NO_ARG, *clauses: _ColumnExpressionOrStrLabelArgument[Any]_) → SelfQuery

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

All existing GROUP BY settings can be suppressed by passing None - this will suppress any GROUP BY configured on mappers as well.

See also

These sections describe GROUP BY in terms of invocation but apply to Query as well:

- in the SQLAlchemy Unified Tutorial

- in the SQLAlchemy Unified Tutorial

- v2 equivalent method.
method sqlalchemy.orm.Query.having(*having: _ColumnExpressionArgument[bool]) → SelfQuery

Apply a HAVING criterion to the query and return the newly resulting .

Query.having() is used in conjunction with .

HAVING criterion makes it possible to use filters on aggregate functions like COUNT, SUM, AVG, MAX, and MIN, eg.:
```
q = session.query(User.id).\
            join(User.addresses).\
            group_by(User.id).\
            having(func.count(Address.id) > 2)
```
See also

Select.having() - v2 equivalent method.
method instances(result_proxy: CursorResult[Any], context: Optional[] = None) → Any

Return an ORM result given a CursorResult and .

Deprecated since version 2.0: The Query.instances() method is deprecated and will be removed in a future release. Use the Select.from_statement() method or aliased() construct in conjunction with Session.execute() instead.
method intersect(*q: Query) → SelfQuery

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

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

See also

- v2 equivalent method.
method sqlalchemy.orm.Query.intersect_all(*q: ) → SelfQuery

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

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

See also

Select.intersect_all() - v2 equivalent method.
attribute is_single_entity

Indicates if this Query returns tuples or single entities.

Returns True if this query returns a single entity for each instance in its result list, and False if this query returns a tuple of entities for each result.

New in version 1.3.11.

See also
method sqlalchemy.orm.Query.join(target: _JoinTargetArgument, onclause: Optional[_OnClauseArgument] = None, *, isouter: bool = False, full: bool = False) → SelfQuery

Create a SQL JOIN against this object’s criterion and 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 collection of Address objects associated with each User. The most common usage of is to create a JOIN along this relationship, using the User.addresses attribute as an indicator for how this should occur:
```
q = session.query(User).join(User.addresses)
```
Where above, the call to Query.join() along User.addresses will result in SQL approximately equivalent to:
```
SELECT user.id, user.name
FROM user JOIN address ON user.id = address.user_id
```
In the above example we refer to User.addresses as passed to as the “on clause”, that is, it indicates how the “ON” portion of the JOIN should be constructed.

To construct a chain of joins, multiple Query.join() calls may be used. The relationship-bound attribute implies both the left and right side of the join at once:
```
q = session.query(User).\
        join(User.orders).\
        join(Order.items).\
        join(Item.keywords)
```
Note

as seen in the above example, the order in which each call to the join() method occurs is important. Query would not, for example, know how to join correctly if we were to specify User, then Item, then Order, in our chain of joins; in such a case, depending on the arguments passed, it may raise an error that it doesn’t know how to join, or it may produce invalid SQL in which case the database will raise an error. In correct practice, the method is invoked in such a way that lines up with how we would want the JOIN clauses in SQL to be rendered, and each call should represent a clear link from what precedes it.

Joins to a Target Entity or Selectable

A second form of Query.join() allows any mapped entity or core selectable construct as a target. In this usage, will attempt to create a JOIN along the natural foreign key relationship between two entities:
```
q = session.query(User).join(Address)
```
In the above calling form, Query.join() is called upon to create the “on clause” automatically for us. This calling form will ultimately raise an error if either there are no foreign keys between the two entities, or if there are multiple foreign key linkages between the target entity and the entity or entities already present on the left side such that creating a join requires more information. Note that when indicating a join to a target without any ON clause, ORM configured relationships are not taken into account.

Joins to a Target with an ON Clause

The third calling form allows both the target entity as well as the ON clause to be passed explicitly. A example that includes a SQL expression as the ON clause is as follows:
```
q = session.query(User).join(Address, User.id==Address.user_id)
```
The above form may also use a relationship-bound attribute as the ON clause as well:
```
q = session.query(User).join(Address, User.addresses)
```
The above syntax can be useful for the case where we wish to join to an alias of a particular target entity. If we wanted to join to Address twice, it could be achieved using two aliases set up using the function:
```
a1 = aliased(Address)
a2 = aliased(Address)
q = session.query(User).\
        join(a1, User.addresses).\
        join(a2, User.addresses).\
        filter(a1.email_address=='ed@foo.com').\
        filter(a2.email_address=='ed@bar.com')
```
The relationship-bound calling form can also specify a target entity using the PropComparator.of_type() method; a query equivalent to the one above would be:
```
a1 = aliased(Address)
a2 = aliased(Address)
q = session.query(User).\
        join(User.addresses.of_type(a1)).\
        join(User.addresses.of_type(a2)).\
        filter(a1.email_address == 'ed@foo.com').\
        filter(a2.email_address == 'ed@bar.com')
```
Augmenting Built-in ON Clauses

As a substitute for providing a full custom ON condition for an existing relationship, the function may be applied to a relationship attribute to augment additional criteria into the ON clause; the additional criteria will be combined with the default criteria using AND:
```
q = session.query(User).join(
    User.addresses.and_(Address.email_address != 'foo@bar.com')
)
```
New in version 1.4.

Joining to Tables and Subqueries

The target of a join may also be any table or SELECT statement, which may be related to a target entity or not. Use the appropriate .subquery() method in order to make a subquery out of a query:

``` subq = session.query(Address).\
```
filter(Address.email_address == 'ed@foo.com').\
subquery()
```

q = session.query(User).join(
    subq, User.id == subq.c.user_id
)
```
Joining to a subquery in terms of a specific relationship and/or target entity may be achieved by linking the subquery to the entity using [aliased()]($661bd2ffd6937693.md#sqlalchemy.orm.aliased "sqlalchemy.orm.aliased"):
```
subq = session.query(Address).\
    filter(Address.email_address == 'ed@foo.com').\
    subquery()
address_subq = aliased(Address, subq)
q = session.query(User).join(
    User.addresses.of_type(address_subq)
)
```
**Controlling what to Join From**
In cases where the left side of the current state of [Query](#sqlalchemy.orm.Query "sqlalchemy.orm.Query") is not in line with what we want to join from, the [Query.select\_from()](#sqlalchemy.orm.Query.select_from "sqlalchemy.orm.Query.select_from") method may be used:
```
q = session.query(Address).select_from(User).\
                join(User.addresses).\
                filter(User.name == 'ed')
```
Which will produce SQL similar to:
```
SELECT address.* FROM user
    JOIN address ON user.id=address.user_id
    WHERE user.name = :name_1
```
See also
[Select.join()]($75ae4d183452a412.md#sqlalchemy.sql.expression.Select.join "sqlalchemy.sql.expression.Select.join") - v2 equivalent method.
-   Parameters:
    -   **\*props** – Incoming arguments for [Query.join()](#sqlalchemy.orm.Query.join "sqlalchemy.orm.Query.join"), the props collection in modern use should be considered to be a one or two argument form, either as a single “target” entity or ORM attribute-bound relationship, or as a target entity plus an “on clause” which may be a SQL expression or ORM attribute-bound relationship.
    -   **isouter=False** – If True, the join used will be a left outer join, just as if the [Query.outerjoin()](#sqlalchemy.orm.Query.outerjoin "sqlalchemy.orm.Query.outerjoin") method were called.
    -   **full=False** –
        render FULL OUTER JOIN; implies `isouter`.

method sqlalchemy.orm.Query.label(name: Optional[str]) → [Any]

Return the full SELECT statement represented by this Query, converted to a scalar subquery with a label of the given name.

See also

- v2 comparable method.
attribute sqlalchemy.orm.Query.lazy_loaded_from

An that is using this Query for a lazy load operation.

Deprecated since version 1.4: This attribute should be viewed via the attribute, within the context of the SessionEvents.do_orm_execute() event.

See also
method sqlalchemy.orm.Query.limit(limit: Union[int, _ColumnExpressionArgument[int]]) → SelfQuery

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

See also

- v2 equivalent method.
method sqlalchemy.orm.Query.merge_result(iterator: Union[[Any], Iterable[Sequence[Any]], Iterable[object]], load: bool = True) → Union[[Any], Iterable[Any]]

Merge a result into this Query object’s Session.

Deprecated since version 2.0: The method is considered legacy as of the 1.x series of SQLAlchemy and becomes a legacy construct in 2.0. The method is superseded by the merge_frozen_result() function. (Background on SQLAlchemy 2.0 at: )

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 . 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 - if these do not correspond, unchecked errors will occur.

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

For an example of how is used, see the source code for the example Dogpile Caching, where is used to efficiently restore state from a cache back into a target Session.
method offset(offset: Union[int, _ColumnExpressionArgument[int]]) → SelfQuery

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

See also

Select.offset() - v2 equivalent method.
method one() → _T

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 returns only scalar values as opposed to full identity-mapped entities.

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

See also

Query.one_or_none()

- v2 comparable method.

Result.scalar_one() - v2 comparable method.
method one_or_none() → Optional[_T]

Return at most one result or raise an exception.

Returns None 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 returns only scalar values as opposed to full identity-mapped entities.

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

New in version 1.0.9: Added

See also

Query.first()

Result.one_or_none() - v2 comparable method.

- v2 comparable method.
method sqlalchemy.orm.Query.only_return_tuples(value: bool) →

When set to True, the query results will always be a Row object.

This can change a query that normally returns a single entity as a scalar to return a result in all cases.

See also

Query.tuples() - returns tuples, but also at the typing level will type results as Tuple.

Result.tuples() - v2 comparable method.
method options(*args: ExecutableOption) → SelfQuery

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

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

See also

Relationship Loading with Loader Options
method outerjoin(target: _JoinTargetArgument, onclause: Optional[_OnClauseArgument] = None, *, full: bool = False) → SelfQuery

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

Usage is the same as the join() method.

See also

Select.outerjoin() - v2 equivalent method.
method params(_Query\_params: Optional[Dict[str, Any]] = None, **kw: Any_) → SelfQuery

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.
method sqlalchemy.orm.Query.populate_existing() → SelfQuery

Return a that will expire and refresh all instances as they are loaded, or reused from the current Session.

As of SQLAlchemy 1.4, the method is equivalent to using the populate_existing execution option at the ORM level. See the section Populate Existing for further background on this option.
method prefix_with(*prefixes: _TextCoercedExpressionArgument[Any], dialect: str = ‘*‘) → SelfHasPrefixes

inherited from the HasPrefixes.prefix_with() method of

Add one or more expressions following the statement keyword, i.e. SELECT, INSERT, UPDATE, or DELETE. Generative.

This is used to support backend-specific prefix keywords such as those provided by MySQL.

E.g.:
```
stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")
# MySQL 5.7 optimizer hints
stmt = select(table).prefix_with(
    "/*+ BKA(t1) */", dialect="mysql")
```
Multiple prefixes can be specified by multiple calls to HasPrefixes.prefix_with().
- Parameters:
  - *prefixes – textual or construct which will be rendered following the INSERT, UPDATE, or DELETE keyword.
  - dialect – optional string dialect name which will limit rendering of this prefix to only that dialect.

Return a new Query, where the “join point” has been reset back to the base FROM entities of the query.

This method is usually used in conjunction with the aliased=True feature of the method. See the example in Query.join() for how this is used.
method scalar() → Any

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.

See also

Result.scalar() - v2 comparable method.
method scalar_subquery() → ScalarSelect[Any]

Return the full SELECT statement represented by this , converted to a scalar subquery.

Analogous to SelectBase.scalar_subquery().

Changed in version 1.4: The method replaces the Query.as_scalar() method.

See also

- v2 comparable method.
method sqlalchemy.orm.Query.select_from(*from_obj: Union[FromClauseRole, Type[Any], Inspectable[_HasClauseElement], _HasClauseElement]) → SelfQuery

Set the FROM clause of this explicitly.

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

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

A typical example:
```
q = session.query(Address).select_from(User).\
    join(User.addresses).\
    filter(User.name == 'ed')
```
Which produces SQL equivalent to:
- Parameters:
  
  *from_obj – collection of one or more entities to apply to the FROM clause. Entities can be mapped classes, AliasedClass objects, objects as well as core FromClause elements like subqueries.
Changed in version 0.9: This method no longer applies the given FROM object to be the selectable from which matching entities select from; the select_entity_from() method now accomplishes this. See that method for a description of this behavior.

See also

Query.select_entity_from()

Select.select_from() - v2 equivalent method.
attribute selectable

Return the Select object emitted by this .

Used for inspect() compatibility, this is equivalent to:
```
query.enable_eagerloads(False).with_labels().statement
```
method set_label_style(style: SelectLabelStyle) → SelfQuery

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.

Note

The method only applies the output of Query.statement, and not to any of the result-row invoking systems of itself, e.g. Query.first(), , etc. To execute a query using Query.set_label_style(), invoke the using Session.execute():
```
result = session.execute(
    query
    .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
    .statement
)
```
New in version 1.4.

See also

- v2 equivalent method.
method sqlalchemy.orm.Query.slice(start: int, stop: int) → SelfQuery

Computes the “slice” of the represented by the given indices and returns the resulting Query.

The start and stop indices behave like the argument to Python’s built-in range() function. This method provides an alternative to using LIMIT/OFFSET to get a slice of the query.

For example,
```
session.query(User).order_by(User.id).slice(1, 3)
```
renders as
```
SELECT users.id AS users_id,
       users.name AS users_name
FROM users ORDER BY users.id
LIMIT ? OFFSET ?
(2, 1)
```
See also

Query.offset()

- v2 equivalent method.
attribute sqlalchemy.orm.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.
method subquery(name: Optional[str] = None, with_labels: bool = False, reduce_columns: bool = False) → Subquery

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

Eager JOIN generation within the query is disabled.

See also

- v2 comparable method.
- Parameters:
  - name – string name to be assigned as the alias; this is passed through to FromClause.alias(). If None, a name will be deterministically generated at compile time.
  - with_labels – if True, will be called on the Query first to apply table-qualified labels to all columns.
  - reduce_columns – if True, will be called on the resulting select() construct, to remove same-named columns where one also refers to the other via foreign key or WHERE clause equivalence.

method suffix_with(*suffixes: _TextCoercedExpressionArgument[Any], dialect: str = ‘*‘) → SelfHasSuffixes

inherited from the HasSuffixes.suffix_with() method of

Add one or more expressions following the statement as a whole.

This is used to support backend-specific suffix keywords on certain constructs.

E.g.:
```
stmt = select(col1, col2).cte().suffix_with(
    "cycle empno set y_cycle to 1 default 0", dialect="oracle")
```
Multiple suffixes can be specified by multiple calls to HasSuffixes.suffix_with().
- Parameters:
  - *suffixes – textual or construct which will be rendered following the target clause.
  - dialect – Optional string dialect name which will limit rendering of this suffix to only that dialect.

method sqlalchemy.orm.Query.tuples() →

return a tuple-typed form of this Query.

This method invokes the method with a value of True, which by itself ensures that this Query will always return objects, even if the query is made against a single entity. It then also at the typing level will return a “typed” query, if possible, that will type result rows as Tuple objects with typed elements.

This method can be compared to the Result.tuples() method, which returns “self”, but from a typing perspective returns an object that will yield typed Tuple objects for results. Typing takes effect only if this object is a typed query object already.

New in version 2.0.

See also

Result.tuples() - v2 equivalent method.
method union(*q: Query) → SelfQuery

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)
```
Note that many database backends do not allow ORDER BY to be rendered on a query called within UNION, EXCEPT, etc. To disable all ORDER BY clauses including those configured on mappers, issue query.order_by(None) - the resulting object will not render ORDER BY within its SELECT statement.

See also

Select.union() - v2 equivalent method.
method union_all(*q: Query) → SelfQuery

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

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

See also

- v2 equivalent method.
method sqlalchemy.orm.Query.update(values: Dict[_DMLColumnArgument, Any], synchronize_session: SynchronizeSessionArgument = ‘auto’, update_args: Optional[Dict[Any, Any]] = None) → int

Perform an UPDATE with an arbitrary WHERE clause.

Updates rows matched by this query in the database.

E.g.:
```
sess.query(User).filter(User.age == 25).\
    update({User.age: User.age - 10}, synchronize_session=False)
sess.query(User).filter(User.age == 25).\
    update({"age": User.age - 10}, synchronize_session='evaluate')
```
Warning

See the section for important caveats and warnings, including limitations when using arbitrary UPDATE and DELETE with mapper inheritance configurations.
- Parameters:
  - values – a dictionary with attributes names, or alternatively mapped attributes or SQL expressions, as keys, and literal values or sql expressions as values. If parameter-ordered mode is desired, the values can be passed as a list of 2-tuples; this requires that the flag is passed to the Query.update.update_args dictionary as well.
  - synchronize_session – chooses the strategy to update the attributes on objects in the session. See the section for a discussion of these strategies.
  - update_args – Optional dictionary, if present will be passed to the underlying update() construct as the **kw for the object. May be used to pass dialect-specific arguments such as mysql_limit, as well as other special arguments such as .
  Returns:
  
  the count of rows matched as returned by the database’s “row count” feature.
See also

ORM-Enabled INSERT, UPDATE, and DELETE statements
method value(column: _ColumnExpressionArgument[Any]) → Any

Return a scalar result corresponding to the given column expression.

Deprecated since version 1.4: Query.value() is deprecated and will be removed in a future release. Please use in combination with Query.scalar()
method values(*columns: _ColumnsClauseArgument[Any]) → Iterable[Any]

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

Deprecated since version 1.4: Query.values() is deprecated and will be removed in a future release. Please use
method sqlalchemy.orm.Query.where(*criterion: _ColumnExpressionArgument[bool]) → SelfQuery

A synonym for .

New in version 1.4.

See also

Select.where() - v2 equivalent method.
attribute whereclause

A readonly attribute which returns the current WHERE criterion for this Query.

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

See also

Select.whereclause - v2 equivalent property.

method with_entities(*entities: _ColumnsClauseArgument[Any], **_Query\_kw: Any_) → Query[Any]

Return a new replacing the SELECT list with the given entities.

e.g.:

# Users, filtered on some arbitrary criterion
# and then ordered by related email address
q = session.query(User).\
            join(User.address).\
            filter(User.name.like('%ed%')).\
            order_by(Address.email)
# given *only* User.id==5, Address.email, and 'q', what
# would the *next* User in the result be ?
subq = q.with_entities(Address.email).\
            order_by(None).\
            filter(User.id==5).\
            subquery()
q = q.join((subq, subq.c.email < Address.email)).\
            limit(1)

ORM-Specific Query Constructs

This section has moved to .