ORM Querying Guide

This section provides an overview of emitting queries with the SQLAlchemy ORM using 2.0 style usage.

Readers of this section should be familiar with the SQLAlchemy overview at SQLAlchemy Unified Tutorial, and in particular most of the content here expands upon the content at Using SELECT Statements.

For users of SQLAlchemy 1.x

In the SQLAlchemy 2.x series, SQL SELECT statements for the ORM are constructed using the same select() construct as is used in Core, which is then invoked in terms of a Session using the Session.execute() method (as are the update() and delete() constructs now used for the ORM-Enabled INSERT, UPDATE, and DELETE statements feature). However, the legacy Query object, which performs these same steps as more of an “all-in-one” object, continues to remain available as a thin facade over this new system, to support applications that were built on the 1.x series without the need for wholesale replacement of all queries. For reference on this object, see the section Legacy Query API.

• Writing SELECT statements for ORM Mapped Classes
  • Selecting ORM Entities and Attributes
    • Selecting ORM Entities
    • Selecting Multiple ORM Entities Simultaneously
    • Selecting Individual Attributes
    • Grouping Selected Attributes with Bundles
    • Selecting ORM Aliases
    • Getting ORM Results from Textual Statements
    • Selecting Entities from Subqueries
    • Selecting Entities from UNIONs and other set operations
  • Joins
    • Simple Relationship Joins
    • Chaining Multiple Joins
    • Joins to a Target Entity
    • Joins to a Target with an ON Clause
    • Combining Relationship with Custom ON Criteria
    • Using Relationship to join between aliased targets
    • Joining to Subqueries
    • Joining to Subqueries along Relationship paths
    • Subqueries that Refer to Multiple Entities
    • Setting the leftmost FROM clause in a join
  • Relationship WHERE Operators
    • EXISTS forms: has() / any()
    • Relationship Instance Comparison Operators
• Writing SELECT statements for Inheritance Mappings
  • SELECTing from the base class vs. specific sub-classes
  • Using selectin_polymorphic()
    • Combining additional loader options with selectin_polymorphic() subclass loads
    • Applying selectin_polymorphic() to an existing eager load
    • Configuring selectin_polymorphic() on mappers
  • Using with_polymorphic()
    • Filtering Subclass Attributes with with_polymorphic()
    • Using aliasing with with_polymorphic
    • Configuring with_polymorphic() on mappers
  • Joining to specific sub-types or with_polymorphic() entities
    • Eager Loading of Polymorphic Subtypes
  • SELECT Statements for Single Inheritance Mappings
    • Optimizing Attribute Loads for Single Inheritance
  • Inheritance Loading API
    • with_polymorphic()
    • selectin_polymorphic()
• ORM-Enabled INSERT, UPDATE, and DELETE statements
  • ORM Bulk INSERT Statements
    • Getting new objects with RETURNING
    • Using Heterogenous Parameter Dictionaries
    • Bulk INSERT for Joined Table Inheritance
    • ORM Bulk Insert with SQL Expressions
    • Legacy Session Bulk INSERT Methods
    • ORM “upsert” Statements
  • ORM Bulk UPDATE by Primary Key
    • Bulk UPDATE by Primary Key for Joined Table Inheritance
    • Legacy Session Bulk UPDATE Methods
  • ORM UPDATE and DELETE with Custom WHERE Criteria
    • Selecting a Synchronization Strategy
    • Using RETURNING with UPDATE/DELETE and Custom WHERE Criteria
    • UPDATE/DELETE with Custom WHERE Criteria for Joined Table Inheritance
    • Legacy Query Methods
• Column Loading Options
  • Limiting which Columns Load with Column Deferral
    • Using load_only() to reduce loaded columns
    • Using defer() to omit specific columns
    • Using raiseload to prevent deferred column loads
  • Configuring Column Deferral on Mappings
    • Using deferred() for imperative mappers, mapped SQL expressions
    • Using undefer() to “eagerly” load deferred columns
    • Loading deferred columns in groups
    • Undeferring by group with undefer_group()
    • Undeferring on wildcards
    • Configuring mapper-level “raiseload” behavior
  • Loading Arbitrary SQL Expressions onto Objects
    • Using with_expression() with UNIONs, other subqueries
  • Column Loading API
    • defer()
    • deferred()
    • query_expression()
    • load_only()
    • undefer()
    • undefer_group()
    • with_expression()
• Relationship Loading Techniques
  • Summary of Relationship Loading Styles
  • Configuring Loader Strategies at Mapping Time
  • Relationship Loading with Loader Options
    • Adding Criteria to loader options
    • Specifying Sub-Options with Load.options()
  • Lazy Loading
    • Preventing unwanted lazy loads using raiseload
  • Joined Eager Loading
    • The Zen of Joined Eager Loading
  • Select IN loading
  • Subquery Eager Loading
  • What Kind of Loading to Use ?
  • Polymorphic Eager Loading
  • Wildcard Loading Strategies
    • Per-Entity Wildcard Loading Strategies
  • Routing Explicit Joins/Statements into Eagerly Loaded Collections
    • Using contains_eager() to load a custom-filtered collection result
  • Relationship Loader API
    • contains_eager()
    • defaultload()
    • immediateload()
    • joinedload()
    • lazyload()
    • Load
    • noload()
    • raiseload()
    • selectinload()
    • subqueryload()
• ORM API Features for Querying
  • ORM Loader Options
  • ORM Execution Options
    • Populate Existing
    • Autoflush
    • Fetching Large Result Sets with Yield Per
    • Identity Token
• Legacy Query API
  • The Query Object
    • Query
  • ORM-Specific Query Constructs