sqlalchemy.orm.query

module documentation

The Query class and support.

Defines the _query.Query class, the central construct used by the ORM to construct database queries.

The _query.Query class should not be confused with the _expression.Select class, which defines database SELECT operations at the SQL (non-ORM) level. Query differs from Select in that it returns ORM-mapped objects and interacts with an ORM session, whereas the Select construct interacts directly with the database to return iterable result sets.

Class ​Query ORM-level SQL construction object.
Class ​Alias​Option No class docstring; 0/1 class variable, 1/2 method documented
Class ​Bulk​Delete BulkUD which handles DELETEs.
Class ​Bulk​UD State used for the orm.Query version of update() / delete().
Class ​Bulk​Update BulkUD which handles UPDATEs.
Class ​From​Statement Core construct that represents a load of ORM objects from a finished select or text construct.
Class ​Query​Context Undocumented
Function aliased Produce an alias of the given element, usually an .AliasedClass instance.
def aliased(element, alias=None, name=None, flat=False, adapt_on_names=False):

Produce an alias of the given element, usually an .AliasedClass instance.

E.g.:

my_alias = aliased(MyClass)

session.query(MyClass, my_alias).filter(MyClass.id > my_alias.id)

The .aliased function is used to create an ad-hoc mapping of a mapped class to a new selectable. By default, a selectable is generated from the normally mapped selectable (typically a _schema.Table ) using the _expression.FromClause.alias method. However, .aliased can also be used to link the class to a new _expression.select statement. Also, the .with_polymorphic function is a variant of .aliased that is intended to specify a so-called "polymorphic selectable", that corresponds to the union of several joined-inheritance subclasses at once.

For convenience, the .aliased function also accepts plain _expression.FromClause constructs, such as a _schema.Table or _expression.select construct. In those cases, the _expression.FromClause.alias method is called on the object and the new _expression.Alias object returned. The returned _expression.Alias is not ORM-mapped in this case.

See Also

:ref:`tutorial_orm_entity_aliases` - in the :ref:`unified_tutorial`

:ref:`orm_queryguide_orm_aliases` - in the :ref:`queryguide_toplevel`

:ref:`ormtutorial_aliases` - in the legacy :ref:`ormtutorial_toplevel`

Parameters
elementelement to be aliased. Is normally a mapped class, but for convenience can also be a _expression.FromClause element.
aliasOptional selectable unit to map the element to. This is usually used to link the object to a subquery, and should be an aliased select construct as one would produce from the _query.Query.subquery method or the _expression.Select.subquery or _expression.Select.alias methods of the _expression.select construct.
nameoptional string name to use for the alias, if not specified by the alias parameter. The name, among other things, forms the attribute name that will be accessible via tuples returned by a _query.Query object. Not supported when creating aliases of _sql.Join objects.
flatBoolean, will be passed through to the _expression.FromClause.alias call so that aliases of _expression.Join objects will alias the individual tables inside the join, rather than creating a subquery. This is generally supported by all modern databases with regards to right-nested joins and generally produces more efficient queries.
adapt​_on​_names

if True, more liberal "matching" will be used when mapping the mapped columns of the ORM entity to those of the given selectable - a name-based match will be performed if the given selectable doesn't otherwise have a column that corresponds to one on the entity. The use case for this is when associating an entity with some derived selectable such as one that uses aggregate functions:

class UnitPrice(Base):
    __tablename__ = 'unit_price'
    ...
    unit_id = Column(Integer)
    price = Column(Numeric)

aggregated_unit_price = Session.query(
                            func.sum(UnitPrice.price).label('price')
                        ).group_by(UnitPrice.unit_id).subquery()

aggregated_unit_price = aliased(UnitPrice,
            alias=aggregated_unit_price, adapt_on_names=True)

Above, functions on aggregated_unit_price which refer to .price will return the func.sum(UnitPrice.price).label('price') column, as it is matched on the name "price". Ordinarily, the "price" function wouldn't have any "column correspondence" to the actual UnitPrice.price column as it is not a proxy of the original.