# SQLAlchemy 0.4 Documentation

Multiple Pages | One Page
Version: 0.4.4 Last Updated: 03/11/08 20:04:34

### module sqlalchemy.orm.query

The Query class and support.

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

The `Query` class should not be confused with the 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(object)

Encapsulates the object-fetching operations provided by Mappers.

def __init__(self, class_or_mapper, session=None, entity_name=None)

Construct a new `Query`.

def add_column(self, column, id=None)

Add a SQL ColumnElement to the list of result columns to be returned.

This will have the effect of all result-returning methods returning a tuple of results, the first element being an instance of the primary class for this Query, and subsequent elements matching columns or entities which were specified via add_column or add_entity.

When adding columns to the result, its generally desirable to add limiting criterion to the query which can associate the primary entity of this Query along with the additional columns, if the column is based on a table or selectable that is not the primary mapped selectable. The Query selects from all tables with no joining criterion by default.

column
  a string column name or sql.ColumnElement to be added to the results.

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

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

This will have the effect of all result-returning methods returning a tuple of results, the first element being an instance of the primary class for this Query, and subsequent elements matching columns or entities which were specified via add_column or add_entity.

When adding entities to the result, its generally desirable to add limiting criterion to the query which can associate the primary entity of this Query along with the additional entities. The Query selects from all tables with no joining criterion by default.

> entity
>   a class or mapper which will be added to the results.
> alias
>   a sqlalchemy.sql.Alias object which will be used to select rows. this will match the usage of the given Alias in filter(), order_by(), etc. expressions
> id
>   a string ID matching that given to query.join() or query.outerjoin(); rows will be selected from the aliased join created via those methods.

def all(self)

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

This results in an execution of the underlying query.

def apply_avg(*args, **kwargs)

apply the SQL `avg()` function against the given column to the query and return the newly resulting `Query`.

DEPRECATED.

def apply_max(*args, **kwargs)

apply the SQL `max()` function against the given column to the query and return the newly resulting `Query`.

DEPRECATED.

def apply_min(*args, **kwargs)

apply the SQL `min()` function against the given column to the query and return the newly resulting `Query`.

DEPRECATED.

def apply_sum(*args, **kwargs)

apply the SQL `sum()` function against the given column to the query and return the newly resulting `Query`.

DEPRECATED.

def autoflush(self, setting)
def avg(self, col)

Execute the SQL `avg()` function against the given column.

def compile(self)

compiles and returns a SQL statement based on the criterion and conditions within this Query.

def count(self, whereclause=None, params=None, **kwargs)

Apply this query's criterion to a SELECT COUNT statement.

the whereclause, params and **kwargs arguments are deprecated. use filter() and other generative methods to establish modifiers.

def count_by(*args, **kwargs)

DEPRECATED. use query.filter_by(**params).count()

def distinct(self)

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

def execute(*args, **kwargs)

DEPRECATED. use query.from_statement().all()

def filter(self, criterion)

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

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

def filter_by(self, **kwargs)

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

def first(self)

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

This results in an execution of the underlying query.

def from_statement(self, statement)

Execute the given SELECT statement and return results.

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

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

Also see the `instances()` method.

def get(self, ident, **kwargs)

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

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

def get_by(*args, **kwargs)

DEPRECATED. use query.filter_by(**params).first()

def group_by(self, criterion)

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

def having(self, criterion)

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

def instances(self, cursor, *mappers_or_columns, **kwargs)
def iterate_instances(self, cursor, *mappers_or_columns, **kwargs)
def join(self, prop, id=None, aliased=False, from_joinpoint=False)

Create a join against this `Query` object's criterion and apply generatively, retunring the newly resulting `Query`.

'prop' may be one of:
  - a string property name, i.e. "rooms"
  - a class-mapped attribute, i.e. Houses.rooms
  - a 2-tuple containing one of the above, combined with a selectable which derives from the properties' mapped table
  - a list (not a tuple) containing a combination of any of the above.

e.g.:

    session.query(Company).join('employees')
    session.query(Company).join(['employees', 'tasks'])
    session.query(Houses).join([Colonials.rooms, Room.closets])
    session.query(Company).join([('employees', people.join(engineers)), Engineer.computers])

def join_by(*args, **kwargs)

DEPRECATED. use join() to construct joins based on attribute names.

def join_to(*args, **kwargs)

DEPRECATED. use join() to create joins based on property names.

def join_via(*args, **kwargs)

DEPRECATED. use join() to create joins based on property names.

def limit(self, limit)

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

def list(*args, **kwargs)

DEPRECATED. use all()

def load(self, ident, raiseerr=True, **kwargs)

Return an instance of the object based on the given identifier.

If not found, raises an exception. The method will remove all pending changes to the object already existing in the Session. The ident argument is a scalar or tuple of primary key column values in the order of the table def's primary key columns.

def max(self, col)

Execute the SQL `max()` function against the given column.

def min(self, col)

Execute the SQL `min()` function against the given column.

def offset(self, offset)

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

def one(self)

Return the first result of this `Query`, raising an exception if more than one row exists.

This results in an execution of the underlying query.

def options(self, *args)

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

def order_by(self, criterion)

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

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

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

'prop' may be one of:
  - a string property name, i.e. "rooms"
  - a class-mapped attribute, i.e. Houses.rooms
  - a 2-tuple containing one of the above, combined with a selectable which derives from the properties' mapped table
  - a list (not a tuple) containing a combination of any of the above.

e.g.:

    session.query(Company).outerjoin('employees')
    session.query(Company).outerjoin(['employees', 'tasks'])
    session.query(Houses).outerjoin([Colonials.rooms, Room.closets])
    session.query(Company).join([('employees', people.join(engineers)), Engineer.computers])

def params(self, *args, **kwargs)

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

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

def populate_existing(self)

Return a Query that will refresh all instances loaded.

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

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

This is essentially the en-masse version of load().

primary_key_columns = property()
def query_from_parent(cls, instance, property, **kwargs)

Return a new Query with criterion corresponding to a parent instance.

Return a newly constructed Query object, with criterion corresponding to a relationship to the given parent instance.

instance

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

  property
    string name of the property which relates this query's class to the instance.
  **kwargs
    all extra keyword arguments are propagated to the constructor of Query.

def reset_joinpoint(self)

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

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

def scalar(*args, **kwargs)

DEPRECATED. use first()

def select(*args, **kwargs)

DEPRECATED. use query.filter(whereclause).all(), or query.from_statement(statement).all()

def select_by(*args, **kwargs)

DEPRECATED. use use query.filter_by(**params).all().

def select_from(self, from_obj)

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

from_obj is a single table or selectable.

def select_statement(*args, **kwargs)

DEPRECATED. Use query.from_statement(statement)

def select_text(*args, **kwargs)

DEPRECATED. Use query.from_statement(statement)

def select_whereclause(*args, **kwargs)

DEPRECATED. use query.filter(whereclause).all()

def selectfirst(*args, **kwargs)

DEPRECATED. use query.filter(whereclause).first()

def selectfirst_by(*args, **kwargs)

DEPRECATED. Use query.filter_by(**kwargs).first()

def selectone(*args, **kwargs)

DEPRECATED. use query.filter(whereclause).one()

def selectone_by(*args, **kwargs)

DEPRECATED. Use query.filter_by(**kwargs).one()

session = property()
def sum(self, col)

Execute the SQL `sum()` function against the given column.

def with_lockmode(self, mode)

Return a new Query object with the specified locking mode.

def with_parent(self, instance, property=None)

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

> instance
>   a persistent or detached instance which is related to class represented by this query.
> property
>   string name of the property which relates this query's class to the instance. if None, the method will attempt to find a suitable property.

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

def with_polymorphic(self, cls_or_mappers, selectable=None)

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

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

If this Query's mapper has a `select_table` argument, with_polymorphic() overrides it; the FROM clause will be against the local table of the base mapper outer joined with the local tables of each specified descendant mapper (unless `selectable` is specified).

`cls_or_mappers` is a single class or mapper, or list of class/mappers, which inherit from this Query's mapper. Alternatively, it may also be the string `'*'`, in which case all descending mappers will be added to the FROM clause.

`selectable` is a table or select() statement that will be used in place of the generated FROM clause. This argument is required if any of the desired mappers use concrete table inheritance, since SQLAlchemy currently cannot generate UNIONs among tables automatically. If used, the `selectable` argument must represent the full set of tables and columns mapped by every desired mapper. Otherwise, the unaccounted mapped columns will result in their table being appended directly to the FROM clause which will usually lead to incorrect results.

def yield_per(self, count)

Yield only `count` rows at a time.

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

In particular, it's usually impossible to use this setting with eagerly loaded collections (i.e. any lazy=False) since those collections will be cleared for a new load when encountered in a subsequent result batch.

def __getitem__(self, item)
def __iter__(self)
back to section top