ORM Querying Guide

    Readers of this section should be familiar with the SQLAlchemy overview at SQLAlchemy 1.4 / 2.0 Tutorial, and in particular most of the content here expands upon the content at .

    SELECT statements are produced by the function which returns a object:

    To invoke a Select with the ORM, it is passed to :

    1. sql>>> result = session.execute(stmt)
    2. SELECT user_account.id, user_account.name, user_account.fullname
    3. FROM user_account
    4. WHERE user_account.name = ?
    5. [...] ('spongebob',)
    6. >>> for user_obj in result.scalars():
    7. ...     print(f"{user_obj.name} {user_obj.fullname}")
    8. spongebob Spongebob Squarepants

    The select() construct accepts ORM entities, including mapped classes as well as class-level attributes representing mapped columns, which are converted into ORM-annotated and ColumnElement elements at construction time.

    A object that contains ORM-annotated entities is normally executed using a Session object, and not a object, so that ORM-related features may take effect, including that instances of ORM-mapped objects may be returned. When using the Connection directly, result rows will only contain column-level data.

    Below we select from the User entity, producing a that selects from the mapped Table to which User is mapped:

    1. sql>>> result = session.execute(select(User).order_by(User.id))
    2. SELECT user_account.id, user_account.name, user_account.fullname
    3. FROM user_account ORDER BY user_account.id
    4. [...] ()

    When selecting from ORM entities, the entity itself is returned in the result as a row with a single element, as opposed to a series of individual columns; for example above, the returns Row objects that have just a single element per row, that element holding onto a User object:

    1. >>> result.fetchone()
    2. (User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)

    When selecting a list of single-element rows containing ORM entities, it is typical to skip the generation of objects and instead receive ORM entities directly, which is achieved using the Result.scalars() method:

    1. >>> result.scalars().all()
    2. [User(id=2, name='sandy', fullname='Sandy Cheeks'),
    3.  User(id=3, name='patrick', fullname='Patrick Star'),
    4.  User(id=4, name='squidward', fullname='Squidward Tentacles'),
    5.  User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')]

    ORM Entities are named in the result row based on their class name, such as below where we SELECT from both User and Address at the same time:

    1. >>> stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)
    3. sql>>> for row in session.execute(stmt):
    4. ...    print(f"{row.User.name} {row.Address.email_address}")
    5. SELECT user_account.id, user_account.name, user_account.fullname,
    6. address.id AS id_1, address.user_id, address.email_address
    7. FROM user_account JOIN address ON user_account.id = address.user_id
    8. ORDER BY user_account.id, address.id
    9. [...] ()
    10. spongebob spongebob@sqlalchemy.org
    11. sandy sandy@sqlalchemy.org
    12. sandy squirrel@squirrelpower.org
    13. patrick pat999@aol.com
    14. squidward stentcl@sqlalchemy.org

    Selecting Individual Attributes

    The attributes on a mapped class, such as User.name and Address.email_address, have a similar behavior as that of the entity class itself such as User in that they are automatically converted into ORM-annotated Core objects when passed to select(). They may be used in the same way as table columns are used:

    1. sql>>> result = session.execute(
    2. ...     select(User.name, Address.email_address).
    3. ...     join(User.addresses).
    4. ...     order_by(User.id, Address.id)
    5. ... )
    6. SELECT user_account.name, address.email_address
    7. FROM user_account JOIN address ON user_account.id = address.user_id
    8. ORDER BY user_account.id, address.id
    9. [...] ()

    ORM attributes, themselves known as objects, can be used in the same way as any ColumnElement, and are delivered in result rows just the same way, such as below where we refer to their values by column name within each row:

    1. >>> for row in result:
    2. ...     print(f"{row.name}  {row.email_address}")
    3. spongebob  spongebob@sqlalchemy.org
    4. sandy  sandy@sqlalchemy.org
    5. sandy  squirrel@squirrelpower.org
    6. patrick  pat999@aol.com
    7. squidward  stentcl@sqlalchemy.org

    Grouping Selected Attributes with Bundles

    The Bundle construct is an extensible ORM-only construct that allows sets of column expressions to be grouped in result rows:

    1. >>> from sqlalchemy.orm import Bundle
    2. >>> stmt = select(
    3. ...     Bundle("user", User.name, User.fullname),
    4. ...     Bundle("email", Address.email_address)
    5. ... ).join_from(User, Address)
    6. sql>>> for row in session.execute(stmt):
    7. ...     print(f"{row.user.name} {row.email.email_address}")
    8. SELECT user_account.name, user_account.fullname, address.email_address
    9. FROM user_account JOIN address ON user_account.id = address.user_id
    10. [...] ()
    11. spongebob spongebob@sqlalchemy.org
    12. sandy sandy@sqlalchemy.org
    13. sandy squirrel@squirrelpower.org
    14. patrick pat999@aol.com
    15. squidward stentcl@sqlalchemy.org

    The is potentially useful for creating lightweight views as well as custom column groupings such as mappings.

    See also

    Column Bundles - in the ORM loading documentation.

    Selecting ORM Aliases

    As discussed in the tutorial at Using Aliases, to create a SQL alias of an ORM entity is achieved using the construct against a mapped class:

    1. >>> from sqlalchemy.orm import aliased
    2. >>> u1 = aliased(User)
    3. SELECT user_account_1.id, user_account_1.name, user_account_1.fullname
    4. FROM user_account AS user_account_1 ORDER BY user_account_1.id

    As is the case when using Table.alias(), the SQL alias is anonymously named. For the case of selecting the entity from a row with an explicit name, the parameter may be passed as well:

    1. >>> from sqlalchemy.orm import aliased
    2. >>> u1 = aliased(User, name="u1")
    3. >>> stmt = select(u1).order_by(u1.id)
    4. sql>>> row = session.execute(stmt).first()
    5. SELECT u1.id, u1.name, u1.fullname
    6. FROM user_account AS u1 ORDER BY u1.id
    7. [...] ()
    8. >>> print(f"{row.u1.name}")
    9. spongebob

    The aliased construct is also central to making use of subqueries with the ORM; the section discusses this further.

    Getting ORM Results from Textual and Core Statements

    The ORM supports loading of entities from SELECT statements that come from other sources. The typical use case is that of a textual SELECT statement, which in SQLAlchemy is represented using the construct. The text() construct, once constructed, can be augmented with information about the ORM-mapped columns that the statement would load; this can then be associated with the ORM entity itself so that ORM objects can be loaded based on this statement.

    Given a textual SQL statement we’d like to load from:

    We can add column information to the statement by using the method; when this method is invoked, the TextClause object is converted into a object, which takes on a role that is comparable to the Select construct. The method is typically passed Column objects or equivalent, and in this case we can make use of the ORM-mapped attributes on the User class directly:

    1. >>> textual_sql = textual_sql.columns(User.id, User.name, User.fullname)

    We now have an ORM-configured SQL construct that as given, can load the “id”, “name” and “fullname” columns separately. To use this SELECT statement as a source of complete User entities instead, we can link these columns to a regular ORM-enabled construct using the Select.from_statement() method:

    1. >>> # using from_statement()
    2. >>> orm_sql = select(User).from_statement(textual_sql)
    3. >>> for user_obj in session.execute(orm_sql).scalars():
    4. ...     print(user_obj)
    5. SELECT id, name, fullname FROM user_account ORDER BY id
    6. [...] ()
    7. User(id=1, name='spongebob', fullname='Spongebob Squarepants')
    8. User(id=2, name='sandy', fullname='Sandy Cheeks')
    9. User(id=3, name='patrick', fullname='Patrick Star')
    10. User(id=4, name='squidward', fullname='Squidward Tentacles')
    11. User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

    The same object can also be converted into a subquery using the TextualSelect.subquery() method, and linked to the entity to it using the construct, in a similar manner as discussed below in Joining to Subqueries:

    1. >>> # using aliased() to select from a subquery
    2. >>> orm_subquery = aliased(User, textual_sql.subquery())
    3. >>> stmt = select(orm_subquery)
    4. >>> for user_obj in session.execute(stmt).scalars():
    5. ...     print(user_obj)
    6. SELECT anon_1.id, anon_1.name, anon_1.fullname
    7. FROM (SELECT id, name, fullname FROM user_account ORDER BY id) AS anon_1
    8. [...] ()
    9. User(id=1, name='spongebob', fullname='Spongebob Squarepants')
    10. User(id=2, name='sandy', fullname='Sandy Cheeks')
    11. User(id=3, name='patrick', fullname='Patrick Star')
    12. User(id=4, name='squidward', fullname='Squidward Tentacles')
    13. User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

    The difference between using the directly with Select.from_statement() versus making use of aliased() is that in the former case, no subquery is produced in the resulting SQL. This can in some scenarios be advantageous from a performance or complexity perspective.

    See also

    - The Select.from_statement() method also works with statements that support RETURNING.

    The Select.join() and methods are used to construct SQL JOINs against a SELECT statement.

    This section will detail ORM use cases for these methods. For a general overview of their use from a Core perspective, see Explicit FROM clauses and JOINs in the .

    The usage of Select.join() in an ORM context for queries is mostly equivalent, minus legacy use cases, to the usage of the Query.join() method in queries.

    Simple Relationship Joins

    1. >>> stmt = select(User).join(User.addresses)

    Where above, the call to along User.addresses will result in SQL approximately equivalent to:

    1. >>> print(stmt)
    2. SELECT user_account.id, user_account.name, user_account.fullname
    3. FROM user_account JOIN address ON user_account.id = address.user_id

    In the above example we refer to User.addresses as passed to Select.join() as the “on clause”, that is, it indicates how the “ON” portion of the JOIN should be constructed.

    Chaining Multiple Joins

    To construct a chain of joins, multiple Select.join() calls may be used. The relationship-bound attribute implies both the left and right side of the join at once. Consider additional entities Order and Item, where the User.orders relationship refers to the Order entity, and the Order.items relationship refers to the Item entity, via an association table order_items. Two calls will result in a JOIN first from User to Order, and a second from Order to Item. However, since Order.items is a many to many relationship, it results in two separate JOIN elements, for a total of three JOIN elements in the resulting SQL:

    1. >>> stmt = (
    2. ...     select(User).
    3. ...     join(User.orders).
    4. ...     join(Order.items)
    5. ... )
    6. >>> print(stmt)
    7. SELECT user_account.id, user_account.name, user_account.fullname
    8. FROM user_account
    9. JOIN user_order ON user_account.id = user_order.user_id
    10. JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
    11. JOIN item ON item.id = order_items_1.item_id

    The order in which each call to the method is significant only to the degree that the “left” side of what we would like to join from needs to be present in the list of FROMs before we indicate a new target. Select.join() would not, for example, know how to join correctly if we were to specify select(User).join(Order.items).join(User.orders), and would raise an error. In correct practice, the method is invoked in such a way that lines up with how we would want the JOIN clauses in SQL to be rendered, and each call should represent a clear link from what precedes it.

    All of the elements that we target in the FROM clause remain available as potential points to continue joining FROM. We can continue to add other elements to join FROM the User entity above, for example adding on the User.addresses relationship to our chain of joins:

    1. >>> stmt = (
    2. ...     select(User).
    3. ...     join(User.orders).
    4. ...     join(Order.items).
    5. ...     join(User.addresses)
    6. ... )
    7. >>> print(stmt)
    8. SELECT user_account.id, user_account.name, user_account.fullname
    9. FROM user_account
    10. JOIN user_order ON user_account.id = user_order.user_id
    11. JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
    12. JOIN item ON item.id = order_items_1.item_id
    13. JOIN address ON user_account.id = address.user_id

    Joins to a Target Entity or Selectable

    A second form of allows any mapped entity or core selectable construct as a target. In this usage, Select.join() will attempt to infer the ON clause for the JOIN, using the natural foreign key relationship between two entities:

    1. >>> stmt = select(User).join(Address)
    2. >>> print(stmt)
    3. SELECT user_account.id, user_account.name, user_account.fullname
    4. FROM user_account JOIN address ON user_account.id = address.user_id

    In the above calling form, is called upon to infer the “on clause” automatically. This calling form will ultimately raise an error if either there are no ForeignKeyConstraint setup between the two mapped constructs, or if there are multiple ForeignKeyConstraint linakges between them such that the appropriate constraint to use is ambiguous.

    Note

    When making use of or Select.join_from() without indicating an ON clause, ORM configured constructs are not taken into account. Only the configured ForeignKeyConstraint relationships between the entities at the level of the mapped objects are consulted when an attempt is made to infer an ON clause for the JOIN.

    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:

    1. >>> stmt = select(User).join(Address, User.id==Address.user_id)
    2. >>> print(stmt)
    3. SELECT user_account.id, user_account.name, user_account.fullname
    4. FROM user_account JOIN address ON user_account.id = address.user_id

    The expression-based ON clause may also be the relationship-bound attribute; this form in fact states the target of Address twice, however this is accepted:

    1. >>> stmt = select(User).join(Address, User.addresses)
    2. >>> print(stmt)
    3. SELECT user_account.id, user_account.name, user_account.fullname
    4. FROM user_account JOIN address ON user_account.id = address.user_id

    The above syntax has more functionality if we use it in terms of aliased entities. The default target for User.addresses is the Address class, however if we pass aliased forms using , the aliased() form will be used as the target, as in the example below:

    When using relationship-bound attributes, the target entity can also be substituted with an aliased entity by using the method. The same example using this method would be:

    1. >>> stmt = (
    2. ...     select(User).
    3. ...     join(User.addresses.of_type(a1)).
    4. ...     join(User.addresses.of_type(a2)).
    5. ...     where(a1.email_address == 'ed@foo.com').
    6. ...     where(a2.email_address == 'ed@bar.com')
    7. ... )
    8. >>> print(stmt)
    9. SELECT user_account.id, user_account.name, user_account.fullname
    10. FROM user_account
    11. JOIN address AS address_1 ON user_account.id = address_1.user_id
    12. WHERE address_1.email_address = :email_address_1
    13. AND address_2.email_address = :email_address_2

    Augmenting Built-in ON Clauses

    As a substitute for providing a full custom ON condition for an existing relationship, the function may be applied to a relationship attribute to augment additional criteria into the ON clause; the additional criteria will be combined with the default criteria using AND. Below, the ON criteria between user_account and address contains two separate elements joined by AND, the first one being the natural join along the foreign key, and the second being a custom limiting criteria:

    1. >>> stmt = (
    2. ...     select(User).
    3. ...     join(User.addresses.and_(Address.email_address != 'foo@bar.com'))
    4. ... )
    5. >>> print(stmt)
    6. SELECT user_account.id, user_account.name, user_account.fullname
    7. FROM user_account
    8. JOIN address ON user_account.id = address.user_id
    9. AND address.email_address != :email_address_1

    See also

    The method also works with loader strategies. See the section for an example.

    Joining to Subqueries

    The target of a join may be any “selectable” entity which usefully includes subuqeries. When using the ORM, it is typical that these targets are stated in terms of an construct, but this is not strictly required particularly if the joined entity is not being returned in the results. For example, to join from the User entity to the Address entity, where the Address entity is represented as a row limited subquery, we first construct a Subquery object using , which may then be used as the target of the Select.join() method:

    1. >>> subq = (
    2. ...     select(Address).
    3. ...     where(Address.email_address == 'pat999@aol.com').
    4. ...     subquery()
    5. ... )
    6. >>> stmt = select(User).join(subq, User.id == subq.c.user_id)
    7. >>> print(stmt)
    8. SELECT user_account.id, user_account.name, user_account.fullname
    9. FROM user_account
    10. JOIN (SELECT address.id AS id,
    11. address.user_id AS user_id, address.email_address AS email_address
    12. FROM address
    13. WHERE address.email_address = :email_address_1) AS anon_1
    14. ON user_account.id = anon_1.user_id

    The above SELECT statement when invoked via will return rows that contain User entities, but not Address entities. In order to add Address entities to the set of entities that would be returned in result sets, we construct an aliased() object against the Address entity and the custom subquery. Note we also apply a name "address" to the construct so that we may refer to it by name in the result row:

    1. >>> address_subq = aliased(Address, subq, name="address")
    2. >>> stmt = select(User, address_subq).join(address_subq)
    3. >>> for row in session.execute(stmt):
    4. ...     print(f"{row.User} {row.address}")
    5. SELECT user_account.id, user_account.name, user_account.fullname,
    6. anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
    7. FROM user_account
    8. JOIN (SELECT address.id AS id,
    9. address.user_id AS user_id, address.email_address AS email_address
    10. FROM address
    11. WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
    12. [...] ('pat999@aol.com',)
    13. User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')

    The same subquery may be referred towards by multiple entities as well, for a subquery that represents more than one entity. The subquery itself will remain unique within the statement, while the entities that are linked to it using aliased refer to distinct sets of columns:

    1. >>> user_address_subq = (
    2. ...        select(User.id, User.name, Address.id, Address.email_address).
    3. ...        join_from(User, Address).
    4. ...        where(Address.email_address.in_(['pat999@aol.com', 'squirrel@squirrelpower.org'])).
    5. ...        subquery()
    6. ... )
    7. >>> user_alias = aliased(User, user_address_subq, name="user")
    8. >>> address_alias = aliased(Address, user_address_subq, name="address")
    9. >>> stmt = select(user_alias, address_alias).where(user_alias.name == 'sandy')
    10. >>> for row in session.execute(stmt):
    11. ...     print(f"{row.user} {row.address}")
    12. SELECT anon_1.id, anon_1.name, anon_1.id_1, anon_1.email_address
    13. FROM (SELECT user_account.id AS id, user_account.name AS name, address.id AS id_1, address.email_address AS email_address
    14. FROM user_account JOIN address ON user_account.id = address.user_id
    15. WHERE address.email_address IN (?, ?)) AS anon_1
    16. WHERE anon_1.name = ?
    17. [...] ('pat999@aol.com', 'squirrel@squirrelpower.org', 'sandy')
    18. User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='squirrel@squirrelpower.org')

    Controlling what to Join From

    In cases where the left side of the current state of Select is not in line with what we want to join from, the method may be used:

    1. >>> stmt = select(Address).join_from(User, User.addresses).where(User.name == 'sandy')
    2. >>> print(stmt)
    3. SELECT address.id, address.user_id, address.email_address
    4. FROM user_account JOIN address ON user_account.id = address.user_id
    5. WHERE user_account.name = :name_1

    The Select.join_from() method accepts two or three arguments, either in the form <join from>, <onclause>, or <join from>, <join to>, [<onclause>]:

    1. >>> stmt = select(Address).join_from(User, Address).where(User.name == 'sandy')
    2. >>> print(stmt)
    3. SELECT address.id, address.user_id, address.email_address
    4. FROM user_account JOIN address ON user_account.id = address.user_id
    5. WHERE user_account.name = :name_1

    To set up the initial FROM clause for a SELECT such that can be used subsequent, the Select.select_from() method may also be used:

    1. >>> stmt = select(Address).select_from(User).join(User.addresses).where(User.name == 'sandy')
    2. >>> print(stmt)
    3. SELECT address.id, address.user_id, address.email_address
    4. FROM user_account JOIN address ON user_account.id = address.user_id
    5. WHERE user_account.name = :name_1

    As detailed in the at Using Relationships in Queries, ORM attributes mapped by may be used in a variety of ways as SQL construction helpers. In addition to the above documentation on Joins, relationships may produce criteria to be used in the WHERE clause as well. See the linked sections below.

    See also

    Sections in the section of the SQLAlchemy 1.4 / 2.0 Tutorial:

    • - helpers to generate EXISTS clauses using relationship()

    • - helpers to create comparisons in terms of a relationship() in reference to a specific object instance

    Loader options are objects that are passed to the method which affect the loading of both column and relationship-oriented attributes. The majority of loader options descend from the Load hierarchy. For a complete overview of using loader options, see the linked sections below.

    See also

    • - details relationship and loading options that affect how relationship() mapped attributes are loaded

    Execution options are keyword arguments that are passed to an “execution_options” method, which take place at the level of statement execution. The primary “execution option” method is in Core at . In the ORM, execution options may also be passed to Session.execute() using the parameter. Perhaps more succinctly, most execution options, including those specific to the ORM, can be assigned to a statement directly, using the Executable.execution_options() method, so that the options may be associated directly with the statement instead of being configured separately. The examples below will use this form.

    Populate Existing

    The populate_existing execution option ensures that for all rows loaded, the corresponding instances in the Session will be fully refreshed, erasing any existing data within the objects (including pending changes) and replacing with the data loaded from the result.

    Example use looks like:

    1. >>> stmt = select(User).execution_options(populate_existing=True)
    2. sql>>> result = session.execute(stmt)
    3. SELECT user_account.id, user_account.name, user_account.fullname
    4. FROM user_account
    5. ...

    Normally, ORM objects are only loaded once, and if they are matched up to the primary key in a subsequent result row, the row is not applied to the object. This is both to preserve pending, unflushed changes on the object as well as to avoid the overhead and complexity of refreshing data which is already there. The assumes a default working model of a highly isolated transaction, and to the degree that data is expected to change within the transaction outside of the local changes being made, those use cases would be handled using explicit steps such as this method.

    Another use case for populate_existing is in support of various attribute loading features that can change how an attribute is loaded on a per-query basis. Options for which this apply include:

    The populate_existing execution option is equvialent to the Query.populate_existing() method in ORM queries.

    See also

    I’m re-loading data with my Session but it isn’t seeing changes that I committed elsewhere - in

    Refreshing / Expiring - in the ORM documentation

    Autoflush

    This option when passed as False will cause the to not invoke the “autoflush” step. It’s equivalent to using the Session.no_autoflush context manager to disable autoflush:

    1. >>> stmt = select(User).execution_options(autoflush=False)
    2. sql>>> session.execute(stmt)
    3. SELECT user_account.id, user_account.name, user_account.fullname
    4. ...

    This option will also work on ORM-enabled and Delete queries.

    The autoflush execution option is equvialent to the method in 1.x style ORM queries.

    See also

    Yield Per

    The yield_per execution option is an integer value which will cause the to yield only a fixed count of rows at a time. It is often useful to use with a result partitioning method such as Result.partitions(), e.g.:

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

    When yield_per is used, the option is also set for the Core execution, so that a streaming / server side cursor will be used if the backend supports it 1

    The yield_per execution option is not compatible with subqueryload eager loading or joinedload eager loading when using collections. It is potentially compatible with selectinload eager loading, provided the database driver supports multiple, independent cursors .

    The yield_per execution option is equvialent to the Query.yield_per() method in ORM queries.

    1

    currently known are , mysqldb and . Other backends will pre buffer all rows. 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.

    2

    the and pysqlite drivers are known to work, drivers for MySQL and SQL Server ODBC drivers do not.

    See also

    ORM Update / Delete with Arbitrary WHERE clause

    The Session.execute() method, in addition to handling ORM-enabled objects, can also accommodate ORM-enabled Update and objects, which UPDATE or DELETE any number of database rows while also being able to synchronize the state of matching objects locally present in the Session. See the section for background on this feature.