API Documentation

sqlalchemy_unchained

init_sqlalchemy_unchained	Main entry point for connecting to the database.
scoped_session_factory	Creates a scoped session using sessionmaker.
declarative_base	Construct a base class for declarative class definitions.
foreign_key	Helper method to add a foreign key column to a model.

sqlalchemy_unchained.base_model

BaseModel

Base model class for SQLAlchemy declarative base model.

sqlalchemy_unchained.base_model_metaclass

DeclarativeMeta

Base metaclass for models in SQLAlchemy Unchained.

sqlalchemy_unchained.base_query

BaseQuery

Base class to use for the query attribute on model managers.

sqlalchemy_unchained.session_manager

SessionManager

The session manager for SQLAlchemy Unchained.

sqlalchemy_unchained.model_manager

ModelManager

Base class for model managers.

sqlalchemy_unchained.model_meta_options

ColumnMetaOption	A MetaOption subclass that simplifies adding columns to models. For example::.
ModelMetaOptionsFactory	The default MetaOptionsFactory subclass used by SQLAlchemy Unchained.

sqlalchemy_unchained.model_registry

ModelRegistry

The SQLAlchemy Unchained model registry.

sqlalchemy_unchained.validation

BaseValidator	Base class for column validators in SQLAlchemy Unchained.
ValidationError	Holds a validation error for a single column of a model.
ValidationErrors	Holds validation errors for an entire model.
Required	A validator to require data.

init_sqlalchemy_unchained

sqlalchemy_unchained.init_sqlalchemy_unchained(database_uri, session_scopefunc=None, query_cls=<class 'sqlalchemy_unchained.base_query.BaseQuery'>, isolation_level=None, **kwargs)

Main entry point for connecting to the database.

Parameters

• database_uri – The SQLAlchemy database URI to connect to.

• session_scopefunc – The function to use for automatically scoping the session. Defaults to None, which means you have full control over the session lifecycle.

• query_cls – Class which should be used to create new Query objects, as returned by query.

• isolation_level – The isolation level to use for the engine connection.

• kwargs – Any extra keyword arguments to pass to declarative_base().

Returns

Tuple[engine, Session, Model, relationship]

scoped_session_factory

sqlalchemy_unchained.scoped_session_factory(bind=None, scopefunc=None, query_cls=<class 'sqlalchemy_unchained.base_query.BaseQuery'>, **kwargs)

Creates a scoped session using sessionmaker. See the SQLAlchemy documentation on Contextual Sessions to learn more.

Parameters

• bind – An Engine or other Connectable with which newly created Session objects will be associated.

• scopefunc – An optional function which defines the current scope. If not passed, the scoped_session object assumes “thread-local” scope, and will use a Python threading.local() in order to maintain the current Session. If passed, the function should return a hashable token; this token will be used as the key in a dictionary in order to store and retrieve the current Session.

• query_cls – Class which should be used to create new Query objects, as returned by query.

• kwargs – Any extra kwargs to pass along to sessionmaker.

declarative_base

sqlalchemy_unchained.declarative_base(model=<class 'sqlalchemy_unchained.base_model.BaseModel'>, name='Model', bind=None, metadata=None, mapper=None, class_registry=None, metaclass=<class 'sqlalchemy_unchained.base_model_metaclass.DeclarativeMeta'>)

Construct a base class for declarative class definitions.

The new base class will be given a metaclass that produces appropriate Table objects and makes the appropriate mapper() calls based on the information provided declaratively in the class and any subclasses of the class.

Parameters

• bind – An optional Connectable, will be assigned the bind attribute on the MetaData instance.

• metadata – An optional MetaData instance. All Table objects implicitly declared by subclasses of the base will share this MetaData. A MetaData instance will be created if none is provided. The MetaData instance will be available via the metadata attribute of the generated declarative base class.

• mapper – An optional callable, defaults to mapper(). Will be used to map subclasses to their Tables.

• model – Defaults to BaseModel. A type to use as the base for the generated declarative base class. May be a class or a tuple of classes.

• name – Defaults to Model. The display name for the generated class. Customizing this is not required, but can improve clarity in tracebacks and debugging.

• class_registry – An optional dictionary that will serve as the registry of class names-> mapped classes when string names are used to identify classes inside of relationship() and others. Allows two or more declarative base classes to share the same registry of class names for simplified inter-base relationships.

• metaclass – Defaults to DeclarativeMeta. The metaclass to use as the meta type of the generated declarative base class. If you want to customize this, your metaclass must extend DeclarativeMeta.

foreign_key

sqlalchemy_unchained.foreign_key(*args, fk_col: Optional[str] = None, primary_key: bool = False, nullable: bool = False, ondelete: Optional[str] = None, onupdate: Optional[str] = None, **kwargs) → sqlalchemy.sql.schema.Column

Helper method to add a foreign key column to a model.

For example:

class Post(db.Model):
    category_id = db.foreign_key('Category')
    category = db.relationship('Category', back_populates='posts')

Is equivalent to:

class Post(db.Model):
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'),
                            nullable=False)
    category = db.relationship('Category', back_populates='posts')

Customizing all the things:

class Post(db.Model):
    _category_id = db.foreign_key('category_id',  # db column name
                                  db.String,      # db column type
                                  'categories',   # foreign table name
                                  fk_col='pk')    # foreign key col name

Would be equivalent to:

class Post(db.Model):
    _category_id = db.Column('category_id', db.String,
                             db.ForeignKey('categories.pk'))

Parameters

args – foreign_key() takes up to three positional arguments.

Most commonly, you will only pass one argument, which should be the table name you’re linking to (or indirectly, the model class/name works too). If you want to customize the column name the foreign key gets stored in the database under, then it must be the first string argument, and you must also supply the table name. You can customize the column type that gets used by passing it too, eg sa.Integer or sa.String(36).

Parameters

• fk_col (str) – The column name of the primary key on the opposite side of the relationship (defaults to sqlalchemy_unchained.ModelRegistry.default_primary_key_column).

• primary_key (bool) – Whether or not this Column is a primary key.

• nullable (bool) – Whether or not this Column should be nullable.

• ondelete (str) – The cascade operation for ON DELETE.

• onupdate (str) – The cascade operation for ON UPDATE.

• kwargs – Any other kwargs to pass the Column constructor.

BaseModel

class sqlalchemy_unchained.BaseModel(**kwargs)

Base model class for SQLAlchemy declarative base model.

update(partial_validation=True, **kwargs)

Update fields on the model.

validate(partial=False, **kwargs)

Validate kwargs before setting attributes on the model

DeclarativeMeta

class sqlalchemy_unchained.DeclarativeMeta(name, bases, clsdict)

Base metaclass for models in SQLAlchemy Unchained. Sets up support for using Meta options on models, automatically sets __tablename__ if necessary, and configures validation for concrete models.

BaseQuery

class sqlalchemy_unchained.BaseQuery(entities, session=None)

Base class to use for the query attribute on model managers.

add_column(column)

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

Pending deprecation: add_column() will be superseded by add_columns().

add_columns(*column)

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

add_entity(entity, alias=None)

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

all()

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

This results in an execution of the underlying SQL statement.

Warning

The _query.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

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

as_scalar()

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

Analogous to _expression.SelectBase.as_scalar().

autoflush(setting)

Return a Query with a specific ‘autoflush’ setting.

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

property column_descriptions

Return metadata about the columns which would be returned by this _query.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
    }
]

correlate(*args)

Return a Query construct which will correlate the given FROM clauses to that of an enclosing Query or select().

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

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

The correlation arguments take effect in such cases as when _query.Query.from_self() is used, or when a subquery as returned by _query.Query.subquery() is embedded in another _expression.select() construct.

count()

Return a count of rows this the SQL formed by this Query would return.

This generates the SQL for this Query as follows:

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

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

Warning

It is important to note that the value returned by count() is not the same as the number of ORM objects that this Query would return from a method such as the .all() method. The _query.Query object, when asked to return full entities, will deduplicate entries based on primary key, meaning if the same primary key value would appear in the results more than once, only one object of that primary key would be present. This does not 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 the usage of a subquery or otherwise control of the FROM clause, or to use other aggregate functions, use func expressions in conjunction with query(), i.e.:

from sqlalchemy import func

# count User records, without
# using a subquery.
session.query(func.count(User.id))

# return count of user "id" grouped
# by "name"
session.query(func.count(User.id)).\
        group_by(User.name)

from sqlalchemy import distinct

# count distinct "name" values
session.query(func.count(distinct(User.name)))

cte(name=None, recursive=False)

Return the full SELECT statement represented by this _query.Query represented as a common table expression (CTE).

Parameters and usage are the same as those of the _expression.SelectBase.cte() method; see that method for further details.

Here is the PostgreSQL WITH RECURSIVE example. Note that, in this example, the included_parts cte and the incl_alias alias of it are Core selectables, which means the columns are accessed via the .c. attribute. The parts_alias object is an _orm.aliased() instance of the Part entity, so column-mapped attributes are available directly:

from sqlalchemy.orm import aliased

class Part(Base):
    __tablename__ = 'part'
    part = Column(String, primary_key=True)
    sub_part = Column(String, primary_key=True)
    quantity = Column(Integer)

included_parts = session.query(
                Part.sub_part,
                Part.part,
                Part.quantity).\
                    filter(Part.part=="our part").\
                    cte(name="included_parts", recursive=True)

incl_alias = aliased(included_parts, name="pr")
parts_alias = aliased(Part, name="p")
included_parts = included_parts.union_all(
    session.query(
        parts_alias.sub_part,
        parts_alias.part,
        parts_alias.quantity).\
            filter(parts_alias.part==incl_alias.c.sub_part)
    )

q = session.query(
        included_parts.c.sub_part,
        func.sum(included_parts.c.quantity).
            label('total_quantity')
    ).\
    group_by(included_parts.c.sub_part)

See also

_expression.HasCTE.cte()

delete(synchronize_session='evaluate')

Perform a bulk delete query.

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

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

Parameters

synchronize_session –

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

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

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

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

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

Returns

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

Warning

Additional Caveats for bulk query deletes

• This method does not work for joined inheritance mappings, since the multiple table deletes are not supported by SQL as well as that the join condition of an inheritance mapper is not automatically rendered. Care must be taken in any multiple-table delete to first accommodate via some other means how the related table will be deleted, as well as to explicitly include the joining condition between those tables, even in mappings where this is normally automatic. E.g. if a class Engineer subclasses Employee, a DELETE against the Employee table would look like:

  session.query(Engineer).\
      filter(Engineer.id == Employee.id).\
      filter(Employee.name == 'dilbert').\
      delete()
  

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

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

• The polymorphic identity WHERE criteria is not included for single- or joined- table updates - this must be added manually even for single table inheritance.

• The method does not offer in-Python cascading of relationships - it is assumed that ON DELETE CASCADE/SET NULL/etc. is configured for any foreign key references which require it, otherwise the database may emit an integrity violation if foreign key references are being enforced.

  After the DELETE, dependent objects in the Session which were impacted by an ON DELETE may not contain the current state, or may have been deleted. This issue is resolved once the Session is expired, which normally occurs upon Session.commit() or can be forced by using Session.expire_all(). Accessing an expired object whose row has been deleted will invoke a SELECT to locate the row; when the row is not found, an ObjectDeletedError is raised.

• The 'fetch' strategy results in an additional SELECT statement emitted and will significantly reduce performance.

• The 'evaluate' strategy performs a scan of all matching objects within the Session; if the contents of the Session are expired, such as via a proceeding Session.commit() call, this will result in SELECT queries emitted for every matching object.

• The MapperEvents.before_delete() and MapperEvents.after_delete() events are not invoked from this method. Instead, the SessionEvents.after_bulk_delete() method is provided to act upon a mass DELETE of entity rows.

See also

_query.Query.update()

Inserts, Updates and Deletes - Core SQL tutorial

distinct(*expr)

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

Note

The 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 _query.Query, however, so would not affect results. The columns are passed through when using the _query.Query.statement accessor, however.

Parameters

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

enable_assertions(value)

Control whether assertions are generated.

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

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

enable_eagerloads(value)

Control whether or not eager joins and subqueries are rendered.

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

This is used primarily when nesting the Query’s statement into a subquery or other selectable, or when using _query.Query.yield_per().

except_(*q)

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

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

except_all(*q)

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

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

execution_options(**kwargs)

Set non-SQL options which take effect during execution.

The options are the same as those accepted by _engine.Connection.execution_options().

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

See also

_query.Query.get_execution_options()

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()

filter(*criterion)

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

e.g.:

session.query(MyClass).filter(MyClass.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(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 _expression.text() construct.

See also

_query.Query.filter_by() - filter on keyword expressions.

filter_by(**kwargs)

Apply the given filtering criterion to a copy of this _query.Query, 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 _query.Query.join().

See also

_query.Query.filter() - filter on SQL expressions.

first()

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

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

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

See also

_query.Query.one()

_query.Query.one_or_none()

from_self(*entities)

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

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

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

Given the _query.Query.from_self() version:

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

This query renders as:

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

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

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

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

SELECT anon_1.user_id AS anon_1_user_id,
       anon_1.user_name AS anon_1_user_name
FROM (SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE "user".name LIKE :name_1
 LIMIT :param_1) AS anon_1
JOIN address ON anon_1.user_id = address.user_id
WHERE address.email LIKE :email_1

Automatic Aliasing

Another key behavior of _query.Query.from_self() is that it applies automatic aliasing to the entities inside the subquery, when they are referenced on the outside. Above, if we continue to refer to the User entity without any additional aliasing applied to it, those references wil be in terms of the subquery:

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

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

SELECT anon_1.user_id AS anon_1_user_id,
       anon_1.user_name AS anon_1_user_name
FROM (SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE "user".name LIKE :name_1
 LIMIT :param_1) AS anon_1
JOIN address ON anon_1.user_id = address.user_id
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 constructions such as referring to the entity in joins should prefer to use explicit subquery objects, typically making use of the _query.Query.subquery() method to produce an explicit subquery object. Always test the structure of queries by viewing the SQL to ensure a particular structure does what’s expected!

Changing the Entities

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

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

yielding:

SELECT address.email AS address_email
FROM (SELECT "user".id AS user_id, "user".name AS user_name
FROM "user"
WHERE "user".name LIKE :name_1
 LIMIT :param_1) AS anon_1
JOIN address ON anon_1.user_id = address.user_id
WHERE address.email LIKE :email_1

Looking out for Inner / Outer Columns

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

q = session.query(Address).join(Address.user).\
    filter(User.name.like('e%'))

q = q.add_entity(User).from_self().\
    options(contains_eager(Address.user))

We use _query.Query.add_entity() above before we call _query.Query.from_self() so that the User columns are present in the inner subquery, so that they are available to the contains_eager() modifier we are using on the outside, producing:

SELECT anon_1.address_id AS anon_1_address_id,
       anon_1.address_email AS anon_1_address_email,
       anon_1.address_user_id AS anon_1_address_user_id,
       anon_1.user_id AS anon_1_user_id,
       anon_1.user_name AS anon_1_user_name
FROM (
    SELECT address.id AS address_id,
    address.email AS address_email,
    address.user_id AS address_user_id,
    "user".id AS user_id,
    "user".name AS user_name
FROM address JOIN "user" ON "user".id = address.user_id
WHERE "user".name LIKE :name_1) AS anon_1

If we didn’t call add_entity(User), but still asked contains_eager() to load the User entity, it would be forced to add the table on the outside without the correct join criteria - note the anon1, "user" phrase at the end:

-- incorrect query
SELECT anon_1.address_id AS anon_1_address_id,
       anon_1.address_email AS anon_1_address_email,
       anon_1.address_user_id AS anon_1_address_user_id,
       "user".id AS user_id,
       "user".name AS user_name
FROM (
    SELECT address.id AS address_id,
    address.email AS address_email,
    address.user_id AS address_user_id
FROM address JOIN "user" ON "user".id = address.user_id
WHERE "user".name LIKE :name_1) AS anon_1, "user"

Parameters

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

from_statement(statement)

Execute the given SELECT statement and return results.

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

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

See also

Using Textual SQL - usage examples in the ORM tutorial

get(id: Union[int, str, Tuple[int, ...], Tuple[str, ...]])

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

For example:

my_user = session.query(User).get(5)

some_object = session.query(VersionedFoo).get((5, 10))

get() 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.

get() 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.

get() 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 options() may be applied however, and will be used if the object is not yet locally present.

A lazy-loading, many-to-one attribute configured by relationship(), using a simple foreign-key-to-primary-key criterion, will also use an operation equivalent to get() in order to retrieve the target value from the local identity map before querying the database.

Parameters

id – A scalar or tuple value representing the primary key. For a composite primary key, the order of identifiers corresponds in most cases to that of the mapped Table object’s primary key columns. For a mapper() that was given the primary key argument during construction, the order of identifiers corresponds to the elements present in this collection.

Returns

The object instance, or None.

get_by(**kwargs)

Returns a single model filtering by kwargs, only if a single model was found, otherwise it returns None.

Parameters

kwargs – column names and their values to filter by

Returns

An instance of the model

get_execution_options()

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

New in version 1.3.

See also

_query.Query.execution_options()

group_by(*criterion)

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

All existing GROUP BY settings can be suppressed by passing None - this will suppress any GROUP BY configured on 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 the newly resulting _query.Query.

_query.Query.having() is used in conjunction with _query.Query.group_by().

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)

instances(cursor, _Query__context=None)

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

e.g.:

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

intersect(*q)

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

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

intersect_all(*q)

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

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

property is_single_entity

Indicates if this _query.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

_query.Query.only_return_tuples()

join(*props, **kwargs)

Create a SQL JOIN against this _query.Query object’s criterion and apply generatively, returning the newly resulting _query.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 _query.Query.join() 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.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 _query.Query.join() 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.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 _query.Query.join() 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.Query.join() allows any mapped entity or core selectable construct as a target. In this usage, _query.Query.join() 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.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 aliased() 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 _orm.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')

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 _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.Query is not in line with what we want to join from, the _query.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

Legacy Features of Query.join()

The _query.Query.join() method currently supports several usage patterns and arguments that are considered to be legacy as of SQLAlchemy 1.3. A deprecation path will follow in the 1.4 series for the following features:

• Joining on relationship names rather than attributes:

  session.query(User).join("addresses")
  

  Why it’s legacy: the string name does not provide enough context for _query.Query.join() to always know what is desired, notably in that there is no indication of what the left side of the join should be. This gives rise to flags like from_joinpoint as well as the ability to place several join clauses in a single _query.Query.join() call which don’t solve the problem fully while also adding new calling styles that are unnecessary and expensive to accommodate internally.

  Modern calling pattern: Use the actual relationship, e.g. User.addresses in the above case:

  session.query(User).join(User.addresses)
  

• Automatic aliasing with the aliased=True flag:

  session.query(Node).join(Node.children, aliased=True).\
      filter(Node.name == 'some name')
  

  Why it’s legacy: the automatic aliasing feature of _query.Query is intensely complicated, both in its internal implementation as well as in its observed behavior, and is almost never used. It is difficult to know upon inspection where and when its aliasing of a target entity, Node in the above case, will be applied and when it won’t, and additionally the feature has to use very elaborate heuristics to achieve this implicit behavior.

  Modern calling pattern: Use the _orm.aliased() construct explicitly:

  from sqlalchemy.orm import aliased
  
  n1 = aliased(Node)
  
  session.query(Node).join(Node.children.of_type(n1)).\
      filter(n1.name == 'some name')
  

• Multiple joins in one call:

  session.query(User).join("orders", "items")
  
  session.query(User).join(User.orders, Order.items)
  
  session.query(User).join(
      (Order, User.orders),
      (Item, Item.order_id == Order.id)
  )
  
  # ... and several more forms actually
  

  Why it’s legacy: being able to chain multiple ON clauses in one call to _query.Query.join() is yet another attempt to solve the problem of being able to specify what entity to join from, and is the source of a large variety of potential calling patterns that are internally expensive and complicated to parse and accommodate.

  Modern calling pattern: Use relationship-bound attributes or SQL-oriented ON clauses within separate calls, so that each call to _query.Query.join() knows what the left side should be:

  session.query(User).join(User.orders).join(
      Item, Item.order_id == Order.id)
  

Parameters

• *props – Incoming arguments for _query.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.Query.outerjoin() method were called.

• full=False –

  render FULL OUTER JOIN; implies isouter.

  New in version 1.1.

• from_joinpoint=False –

  When using aliased=True, a setting of True here will cause the join to be from the most recent joined target, rather than starting back from the original FROM clauses of the query.

  Note

  This flag is considered legacy.

• aliased=False –

  If True, indicate that the JOIN target should be anonymously aliased. Subsequent calls to _query.Query.filter() and similar will adapt the incoming criterion to the target alias, until _query.Query.reset_joinpoint() is called.

  Note

  This flag is considered legacy.

See also

Querying with Joins in the ORM tutorial.

Mapping Class Inheritance Hierarchies for details on how _query.Query.join() is used for inheritance relationships.

_orm.join() - a standalone ORM-level join function, used internally by _query.Query.join(), which in previous SQLAlchemy versions was the primary ORM-level joining interface.

label(name)

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

Analogous to _expression.SelectBase.label().

limit(limit)

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

merge_result(iterator, load=True)

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

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

The structure of the results is determined based on the column list of this _query.Query - 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 _query.Query.merge_result() is used, see the source code for the example Dogpile Caching, where _query.Query.merge_result() is used to efficiently restore state from a cache back into a target Session.

offset(offset)

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

one()

Return exactly one result or raise an exception.

Raises sqlalchemy.orm.exc.NoResultFound if the query selects no rows. Raises sqlalchemy.orm.exc.MultipleResultsFound if multiple object identities are returned, or if multiple rows are returned for a query that returns only scalar values as opposed to full identity-mapped entities.

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

See also

_query.Query.first()

_query.Query.one_or_none()

one_or_none()

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

New in version 1.0.9: Added _query.Query.one_or_none()

See also

_query.Query.first()

_query.Query.one()

only_return_tuples(value)

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

This is specifically for single element queries. The default is False.

New in version 1.2.5.

See also

_query.Query.is_single_entity()

options(*args)

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

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

See also

Deferred Column Loader Query Options

Relationship Loading with Loader Options

order_by(*criterion)

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

All existing ORDER BY settings can be suppressed by passing None - this will suppress any ordering configured on the mapper() object using the deprecated :paramref:`.mapper.order_by` parameter.

outerjoin(*props, **kwargs)

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

Usage is the same as the _query.Query.join() method.

params(*args, **kwargs)

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

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

populate_existing()

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

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

See also

Refreshing / Expiring - in the ORM _orm.Session documentation

prefix_with(*prefixes)

Apply the prefixes to the query and return the newly resulting _query.Query.

Parameters

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

e.g.:

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

Would render:

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

See also

_expression.HasPrefixes.prefix_with()

reset_joinpoint()

Return a new _query.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 _query.Query.join() method. See the example in _query.Query.join() for how this is used.

scalar()

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

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

This results in an execution of the underlying query.

select_entity_from(from_obj)

Set the FROM clause of this _query.Query to a core selectable, applying it as a replacement FROM clause for corresponding mapped entities.

The _query.Query.select_entity_from() method supplies an alternative approach to the use case of applying an aliased() construct explicitly throughout a query. Instead of referring to the aliased() construct explicitly, _query.Query.select_entity_from() automatically adapts all occurrences of the entity to the target selectable.

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

select_stmt = select([User]).where(User.id == 7)
user_alias = aliased(User, select_stmt)

q = session.query(user_alias).\
    filter(user_alias.name == 'ed')

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

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

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

SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
FROM (SELECT "user".id AS id, "user".name AS name
FROM "user"
WHERE "user".id = :id_1) AS anon_1
WHERE anon_1.name = :name_1

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

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

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

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

_query.Query.select_entity_from() itself accepts an aliased() object, so that the special options of aliased() such as :paramref:`.aliased.adapt_on_names` may be used within the scope of the _query.Query.select_entity_from() method’s adaptation services. Suppose a view user_view also returns rows from user. If we reflect this view into a _schema.Table, this view has no relationship to the _schema.Table to which we are mapped, however we can use name matching to select from it:

user_view = Table('user_view', metadata,
                  autoload_with=engine)
user_view_alias = aliased(
    User, user_view, adapt_on_names=True)
q = session.query(User).\
    select_entity_from(user_view_alias).\
    order_by(User.name)

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

Parameters

from_obj – a _expression.FromClause object that will replace the FROM clause of this _query.Query. It also may be an instance of aliased().

See also

_query.Query.select_from()

select_from(*from_obj)

Set the FROM clause of this _query.Query explicitly.

_query.Query.select_from() is often used in conjunction with _query.Query.join() 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.Query.join(), when no joinpoint is otherwise established - usually, the default “join point” is the leftmost entity in the _query.Query 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:

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

Parameters

*from_obj – collection of one or more entities to apply to the FROM clause. Entities can be mapped classes, AliasedClass objects, _orm.Mapper objects as well as core _expression.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.Query.join()

_query.Query.select_entity_from()

property selectable

Return the _expression.Select object emitted by this _query.Query.

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

query.enable_eagerloads(False).with_labels().statement

slice(start, stop)

Computes the “slice” of the _query.Query represented by the given indices and returns the resulting _query.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.Query.limit()

_query.Query.offset()

property statement

The full SELECT statement represented by this Query.

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

subquery(name=None, with_labels=False, reduce_columns=False)

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

Eager JOIN generation within the query is disabled.

Parameters

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

• with_labels – if True, with_labels() will be called on the _query.Query first to apply table-qualified labels to all columns.

• reduce_columns – if True, _expression.Select.reduce_columns() will be called on the resulting _expression.select() construct, to remove same-named columns where one also refers to the other via foreign key or WHERE clause equivalence.

suffix_with(*suffixes)

Apply the suffix to the query and return the newly resulting _query.Query.

Parameters

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

New in version 1.0.0.

See also

_query.Query.prefix_with()

_expression.HasSuffixes.suffix_with()

union(*q)

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

e.g.:

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

q3 = q1.union(q2)

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

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

will nest on each union(), and produces:

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

Whereas:

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

produces:

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

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 _query.Query object will not render ORDER BY within its SELECT statement.

union_all(*q)

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

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

update(values, synchronize_session='evaluate', update_args=None)

Perform a bulk update query.

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

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

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 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order` flag is passed to the :paramref:`.Query.update.update_args` dictionary as well.

  Changed in version 1.0.0: - string names in the values dictionary are now resolved against the mapped entity; previously, these strings were passed as literal column names with no mapper-level translation.

• synchronize_session –

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

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

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

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

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

• update_args –

  Optional dictionary, if present will be passed to the underlying _expression.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 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`.

  New in version 1.0.0.

Returns

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

Warning

Additional Caveats for bulk query updates

• The method does not offer in-Python cascading of relationships - it is assumed that ON UPDATE CASCADE is configured for any foreign key references which require it, otherwise the database may emit an integrity violation if foreign key references are being enforced.

  After the UPDATE, dependent objects in the Session which were impacted by an ON UPDATE CASCADE may not contain the current state; this issue is resolved once the Session is expired, which normally occurs upon Session.commit() or can be forced by using Session.expire_all().

• The 'fetch' strategy results in an additional SELECT statement emitted and will significantly reduce performance.

• The 'evaluate' strategy performs a scan of all matching objects within the Session; if the contents of the Session are expired, such as via a proceeding Session.commit() call, this will result in SELECT queries emitted for every matching object.

• The method supports multiple table updates, as detailed in Multiple Table Updates, and this behavior does extend to support updates of joined-inheritance and other multiple table mappings. However, the join condition of an inheritance mapper is not automatically rendered. Care must be taken in any multiple-table update to explicitly include the joining condition between those tables, even in mappings where this is normally automatic. E.g. if a class Engineer subclasses Employee, an UPDATE of the Engineer local table using criteria against the Employee local table might look like:

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

• The polymorphic identity WHERE criteria is not included for single- or joined- table updates - this must be added manually, even for single table inheritance.

• The MapperEvents.before_update() and MapperEvents.after_update() events are not invoked from this method. Instead, the SessionEvents.after_bulk_update() method is provided to act upon a mass UPDATE of entity rows.

See also

_query.Query.delete()

Inserts, Updates and Deletes - Core SQL tutorial

value(column)

Return a scalar result corresponding to the given column expression.

values(*columns)

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

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

with_entities(*entities)

Return a new _query.Query 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)

with_for_update(read=False, nowait=False, of=None, skip_locked=False, key_share=False)

return a new _query.Query with the specified options for the FOR UPDATE clause.

The behavior of this method is identical to that of _expression.GenerativeSelect.with_for_update(). When called with no arguments, the resulting SELECT statement will have a FOR UPDATE clause appended. When additional arguments are specified, backend-specific options such as FOR UPDATE NOWAIT or LOCK IN SHARE MODE can take effect.

E.g.:

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

The above query on a PostgreSQL backend will render like:

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

New in version 0.9.0: _query.Query.with_for_update() supersedes the _query.Query.with_lockmode() method.

Note

It is generally a good idea to combine the use of the _orm.Query.populate_existing() method when using the _orm.Query.with_for_update() method. The purpose of _orm.Query.populate_existing() is to force all the data read from the SELECT to be populated into the ORM objects returned, even if these objects are already in the identity map.

See also

_expression.GenerativeSelect.with_for_update() - Core level method with full argument and behavioral description.

_orm.Query.populate_existing() - overwrites attributes of objects already loaded in the identity map.

with_hint(selectable, text, dialect_name='*')

Add an indexing or other executional context hint for the given entity or selectable to this _query.Query.

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

See also

_query.Query.with_statement_hint()

Query.prefix_with() - generic SELECT prefixing which also can suit some database-specific HINT syntaxes such as MySQL optimizer hints

with_labels()

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

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

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

Note

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

result = session.execute(query.with_labels().statement)

with_lockmode(mode)

Return a new _query.Query object with the specified “locking mode”, which essentially refers to the FOR UPDATE clause.

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

Parameters

mode –

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

• None - translates to no lockmode

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

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

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

See also

_query.Query.with_for_update() - improved API for specifying the FOR UPDATE clause.

with_parent(instance, property=None, from_entity=None)

Add filtering criterion that relates the given instance to a child object or collection, using its attribute state as well as an established _orm.relationship() configuration.

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

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

Parameters

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

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

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

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

Load columns for inheriting classes.

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

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

with_session(session)

Return a _query.Query that will use the given Session.

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

from sqlalchemy.orm import Query

query = Query([MyClass]).filter(MyClass.id == 5)

result = query.with_session(my_session).one()

with_statement_hint(text, dialect_name='*')

Add a statement hint to this _expression.Select.

This method is similar to _expression.Select.with_hint() except that it does not require an individual table, and instead applies to the statement as a whole.

This feature calls down into _expression.Select.with_statement_hint().

New in version 1.0.0.

See also

_query.Query.with_hint()

with_transformation(fn)

Return a new _query.Query object transformed by the given function.

E.g.:

def filter_something(criterion):
    def transform(q):
        return q.filter(criterion)
    return transform

q = q.with_transformation(filter_something(x==5))

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

yield_per(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 them out partially, so that the Python interpreter doesn’t need to declare very large areas of memory which is both time consuming and leads to excessive memory use. The performance from fetching hundreds of thousands of rows can often double when a suitable yield-per setting (e.g. approximately 1000) is used, even with DBAPIs that buffer rows (which are most).

The _query.Query.yield_per() method is not compatible subqueryload eager loading or joinedload eager loading when using 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 disable eager loads, either unconditionally with _query.Query.enable_eagerloads():

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

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

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

Warning

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

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

Also note that while yield_per() will set the stream_results execution option to True, currently this is only understood by psycopg2, mysqldb and pymysql dialects which will stream results using server side cursors instead of pre-buffer all rows for this query. Other DBAPIs pre-buffer all rows before making them available. The memory use of raw database rows is much less than that of an ORM-mapped object, but should still be taken into consideration when benchmarking.

See also

_query.Query.enable_eagerloads()

SessionManager

class sqlalchemy_unchained.SessionManager(*args, **kwargs)

The session manager for SQLAlchemy Unchained.

classmethod set_session_factory(session_factory)

Classmethod to set the session factory SessionManager should use.

Parameters

session_factory – The session factory callable.

save(instance: sqlalchemy_unchained.base_model.BaseModel, commit: bool = False) → sqlalchemy_unchained.base_model.BaseModel

Add a model instance to the session, optionally committing the current transaction immediately.

Parameters

• instance – The model instance to save.

• commit – Whether or not to immediately commit. WARNING: This will commit the entire session, including any other model instances that may have been added to the session but not yet committed.

Returns

The model instance.

save_all(instances: List[sqlalchemy_unchained.base_model.BaseModel], commit: bool = False) → List[sqlalchemy_unchained.base_model.BaseModel]

Adds a list of model instances to the session, optionally committing the current transaction immediately.

Parameters

• instances – The list of model instance to save.

• commit – Whether or not to immediately commit. WARNING: This will commit the entire session, including any other model instances that may have been added to the session but not yet committed.

Returns

The list of model instances.

commit() → None

Commits the current transaction.

property no_autoflush

Return a context manager that disables autoflush.

e.g.:

with session.no_autoflush:

    some_object = SomeClass()
    session.add(some_object)
    # won't autoflush
    some_object.related_thing = session.query(SomeRelated).first()

Operations that proceed within the with: block will not be subject to flushes occurring upon query access. This is useful when initializing a series of objects which involve existing database queries, where the uncompleted object should not yet be flushed.

ModelManager

class sqlalchemy_unchained.ModelManager(*args, **kwargs)

Base class for model managers. This is the strongly preferred pattern for managing all interactions with the database for models when using an ORM that implements the Data Mapper pattern (which SQLAlchemy does). You should create a subclass of ModelManager for every model in your app, customizing the create() method, and add any custom query methods you may need here, instead of on the model class as popularized by the Active Record pattern (which SQLAlchemy is not).

For example:

from your_package import db


class Foobar(db.Model):
    name = db.Column(db.String, nullable=False)
    optional = db.Column(db.String, nullable=True)


class FoobarManager(db.ModelManager):
    class Meta:
        model = Foobar

    def create(self, name, optional=None, commit=False):
        return super().create(name=name, optional=optional, commit=commit)

    def find_by_name(self, name):
        return self.get_by(name=name)

Subclasses of ModelManager are singletons, and as such, anytime you call YourModelManager(), you will get the same instance back.

Available ModelManager Meta options

Meta option name	type	Description
abstract	bool	Whether or not this class should be considered abstract.
model	Type[db.Model]	The model class this manager is for. Required, unless the class is marked abstract.

query: sqlalchemy_unchained.base_query.BaseQuery

The BaseQuery for this manager’s model.

q: sqlalchemy_unchained.base_query.BaseQuery

An alias for query.

create(commit: bool = False, **kwargs) → sqlalchemy_unchained.base_model.BaseModel

Creates an instance of self.Meta.model, optionally committing the current session transaction.

Parameters

• commit (bool) – Whether or not to commit the current session transaction.

• kwargs – The data to initialize the model with.

Returns

The created model instance.

get_or_create(defaults: Optional[dict] = None, commit: bool = False, **kwargs) → Tuple[sqlalchemy_unchained.base_model.BaseModel, bool]

Get or create an instance of self.Meta.model by kwargs and defaults, optionally committing te current session transaction.

Parameters

• defaults (dict) – Extra values to create the model with, if not found

• commit (bool) – Whether or not to commit the current session transaction.

• kwargs – The values to filter by and create the model with

Returns

Tuple[the_model_instance, did_create_bool]

update(instance: sqlalchemy_unchained.base_model.BaseModel, commit: bool = False, **kwargs) → sqlalchemy_unchained.base_model.BaseModel

Update kwargs on an instance, optionally committing the current session transaction.

Parameters

• instance – The model instance to update.

• commit (bool) – Whether or not to commit the current session transaction.

• kwargs – The data to update on the model.

Returns

The updated model instance.

update_or_create(defaults: Optional[dict] = None, commit: bool = False, **kwargs) → Tuple[sqlalchemy_unchained.base_model.BaseModel, bool]

Update or create an instance of self.Meta.model by kwargs and defaults, optionally committing te current session transaction.

Parameters

• defaults (dict) – Extra values to update on the model

• commit (bool) – Whether or not to commit the current session transaction.

• kwargs – The values to filter by and update on the model

Returns

Tuple[the_model_instance, did_create_bool]

all() → List[sqlalchemy_unchained.base_model.BaseModel]

Query the database for all records of self.Meta.model.

Returns

A list of all model instances (may be empty).

get(id: Union[int, str, Tuple[int, ...], Tuple[str, ...]]) → Optional[sqlalchemy_unchained.base_model.BaseModel]

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

E.g.:

my_user = UserManager().get(5)

some_object = SomeObjectManager().get((5, 10))

get() 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.

get() 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.

get() 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 options() may be applied however, and will be used if the object is not yet locally present.

A lazy-loading, many-to-one attribute configured by relationship(), using a simple foreign-key-to-primary-key criterion, will also use an operation equivalent to get() in order to retrieve the target value from the local identity map before querying the database.

Parameters

id – A scalar or tuple value representing the primary key. For a composite primary key, the order of identifiers corresponds in most cases to that of the mapped Table object’s primary key columns. For a mapper() that was given the primary key argument during construction, the order of identifiers corresponds to the elements present in this collection.

Returns

The object instance, or None.

get_by(**kwargs) → Optional[sqlalchemy_unchained.base_model.BaseModel]

Get one or none of self.Meta.model by kwargs.

Parameters

kwargs – The data to filter by.

Returns

The model instance, or None.

filter(*criterion) → sqlalchemy_unchained.base_query.BaseQuery

Get all instances of self.Meta.model matching criterion.

Parameters

criterion – The criterion to filter by.

Returns

A list of model instances (may be empty).

commit() → None

Commits the current transaction.

filter_by(**kwargs) → sqlalchemy_unchained.base_query.BaseQuery

Get all instances of self.Meta.model matching kwargs.

Parameters

kwargs – The data to filter by.

Returns

A list of model instances (may be empty).

property no_autoflush

Return a context manager that disables autoflush.

e.g.:

with session.no_autoflush:

    some_object = SomeClass()
    session.add(some_object)
    # won't autoflush
    some_object.related_thing = session.query(SomeRelated).first()

Operations that proceed within the with: block will not be subject to flushes occurring upon query access. This is useful when initializing a series of objects which involve existing database queries, where the uncompleted object should not yet be flushed.

save(instance: sqlalchemy_unchained.base_model.BaseModel, commit: bool = False) → sqlalchemy_unchained.base_model.BaseModel

Add a model instance to the session, optionally committing the current transaction immediately.

Parameters

• instance – The model instance to save.

• commit – Whether or not to immediately commit. WARNING: This will commit the entire session, including any other model instances that may have been added to the session but not yet committed.

Returns

The model instance.

save_all(instances: List[sqlalchemy_unchained.base_model.BaseModel], commit: bool = False) → List[sqlalchemy_unchained.base_model.BaseModel]

Adds a list of model instances to the session, optionally committing the current transaction immediately.

Parameters

• instances – The list of model instance to save.

• commit – Whether or not to immediately commit. WARNING: This will commit the entire session, including any other model instances that may have been added to the session but not yet committed.

Returns

The list of model instances.

classmethod set_session_factory(session_factory)

Classmethod to set the session factory SessionManager should use.

Parameters

session_factory – The session factory callable.

ColumnMetaOption

class sqlalchemy_unchained.ColumnMetaOption(name: str, default: Optional[Any] = None, inherit: bool = False)

A MetaOption subclass that simplifies adding columns to models. For example:

import sqlalchemy as sa

from sqlalchemy_unchained import ColumnMetaOption


class NameColumnMetaOption(ColumnMetaOption):
    def __init__():
        super().__init__(name='name', default='name', inherit=True)

    def get_column(mcs_args):
        return sa.Column(sa.String, nullable=False)

check_value(value, mcs_args: py_meta_utils.McsArgs)

Optional callback to verify the user provided a valid value.

Your implementation should raise ValueError with an error message if invalid.

contribute_to_class(mcs_args: py_meta_utils.McsArgs, col_name)

Optional callback to modify the McsArgs of the class-under-construction.

ModelMetaOptionsFactory

class sqlalchemy_unchained.ModelMetaOptionsFactory

The default MetaOptionsFactory subclass used by SQLAlchemy Unchained.

ModelRegistry

class sqlalchemy_unchained.ModelRegistry(*args, **kwargs)

The SQLAlchemy Unchained model registry.

finalize_mappings() → Dict[str, object]

Returns a dictionary of the model classes that were finalized.

Keyed by the names of the model classes, values are the classes themselves.

should_initialize(mcs_init_args: py_meta_utils.McsInitArgs) → bool

Whether or not the model represented by mcs_init_args should be initialized.

BaseValidator

class sqlalchemy_unchained.BaseValidator(msg=None)

Base class for column validators in SQLAlchemy Unchained.

You should supply the error message to the constructor, and implement your validation logic in __call__():

from sqlalchemy_unchained import BaseValidator, ValidationError


class NameRequired(BaseValidator):
    def __init__(msg='Name is required.'):
        super().__init__(msg=msg)

    def __call__(self, value):
        super().__call__(value)
        if not value:
            raise ValidationError(validator=self)


class YourModel(db.Model):
    name = db.Column(db.String, nullable=False, info=dict(
        validators=[NameRequired]))

msg

The message for this validator.

get_message(e: sqlalchemy_unchained.validation.ValidationError)

Returns the message for this validation error. By default we just return self.msg, but you can override this method if you need to customize the message.

ValidationError

class sqlalchemy_unchained.ValidationError(msg: Optional[str] = None, model=None, column=None, validator=None)

Holds a validation error for a single column of a model.

msg

The error message. If validator is provided and it implements get_message, that will take precedence over this value.

model

The model this validation error is for.

column

The Column this validation error is for.

validator

The validator instance that raised this validation error.

ValidationErrors

class sqlalchemy_unchained.ValidationErrors(errors: Dict[str, List[str]])

Holds validation errors for an entire model.

errors

A dictionary of errors, where the keys are column names, and the values are lists of error messages for each column.

Required

class sqlalchemy_unchained.Required(msg=None)

A validator to require data.