Object Relational Tutorial

The SQLAlchemy Object Relational Mapper presents a method of associatinguser-defined Python classes with database tables, and instances of thoseclasses (objects) with rows in their corresponding tables. It includes asystem that transparently synchronizes all changes in state between objectsand their related rows, called a unit of work, as well as a systemfor expressing database queries in terms of the user defined classes and theirdefined relationships between each other.

The ORM is in contrast to the SQLAlchemy Expression Language, upon which theORM is constructed. Whereas the SQL Expression Language, introduced inSQL Expression Language Tutorial, presents a system of representing the primitiveconstructs of the relational database directly without opinion, the ORMpresents a high level and abstracted pattern of usage, which itself is anexample of applied usage of the Expression Language.

While there is overlap among the usage patterns of the ORM and the ExpressionLanguage, the similarities are more superficial than they may at first appear.One approaches the structure and content of data from the perspective of auser-defined domain model which is transparentlypersisted and refreshed from its underlying storage model. The otherapproaches it from the perspective of literal schema and SQL expressionrepresentations which are explicitly composed into messages consumedindividually by the database.

A successful application may be constructed using the Object Relational Mapperexclusively. In advanced situations, an application constructed with the ORMmay make occasional usage of the Expression Language directly in certain areaswhere specific database interactions are required.

The following tutorial is in doctest format, meaning each >>> linerepresents something you can type at a Python command prompt, and thefollowing text represents the expected return value.

Version Check

A quick check to verify that we are on at least version 1.3 of SQLAlchemy:

>>> import sqlalchemy
>>> sqlalchemy.__version__ 
1.3.0

Connecting

For this tutorial we will use an in-memory-only SQLite database. To connect weuse create_engine():

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)

The echo flag is a shortcut to setting up SQLAlchemy logging, which isaccomplished via Python’s standard logging module. With it enabled, we’llsee all the generated SQL produced. If you are working through this tutorialand want less output generated, set it to False. This tutorial will formatthe SQL behind a popup window so it doesn’t get in our way; just click the“SQL” links to see what’s being generated.

The return value of create_engine() is an instance ofEngine, and it represents the core interface to thedatabase, adapted through a dialect that handles the detailsof the database and DBAPI in use. In this case the SQLitedialect will interpret instructions to the Python built-in sqlite3module.

Lazy Connecting

The Engine, when first returned by create_engine(),has not actually tried to connect to the database yet; that happensonly the first time it is asked to perform a task against the database.

The first time a method like Engine.execute() or Engine.connect()is called, the Engine establishes a real DBAPI connection to thedatabase, which is then used to emit the SQL. When using the ORM, we typicallydon’t use the Engine directly once created; instead, it’s usedbehind the scenes by the ORM as we’ll see shortly.

See also

Database Urls - includes examples of create_engine()connecting to several kinds of databases with links to more information.

Declare a Mapping

When using the ORM, the configurational process starts by describing the databasetables we’ll be dealing with, and then by defining our own classes which willbe mapped to those tables. In modern SQLAlchemy,these two tasks are usually performed together,using a system known as Declarative, which allows us to createclasses that include directives to describe the actual database table they willbe mapped to.

Classes mapped using the Declarative system are defined in terms of a base class whichmaintains a catalog of classes andtables relative to that base - this is known as the declarative base class. Ourapplication will usually have just one instance of this base in a commonlyimported module. We create the base class using the declarative_base()function, as follows:

>>> from sqlalchemy.ext.declarative import declarative_base
 
>>> Base = declarative_base()

Now that we have a “base”, we can define any number of mapped classes in termsof it. We will start with just a single table called users, which will storerecords for the end-users using our application.A new class called User will be the class to which we map this table. Withinthe class, we define details about the table to which we’ll be mapping, primarilythe table name, and names and datatypes of columns:

>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     nickname = Column(String)
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', nickname='%s')>" % (
...                             self.name, self.fullname, self.nickname)

Tip

The User class defines a repr() method,but note that is optional; we only implement it inthis tutorial so that our examples show nicelyformatted User objects.

A class using Declarative at a minimumneeds a tablename attribute, and at least oneColumn which is part of a primary key 1. SQLAlchemy never makes anyassumptions by itself about the table to whicha class refers, including that it has no built-in conventions for names,datatypes, or constraints. But this doesn’t meanboilerplate is required; instead, you’re encouraged to create yourown automated conventions using helper functions and mixin classes, whichis described in detail at Mixin and Custom Base Classes.

When our class is constructed, Declarative replaces all the Columnobjects with special Python accessors known as descriptors; this is aprocess known as instrumentation. The “instrumented” mapped classwill provide us with the means to refer to our table in a SQL context as wellas to persist and load the values of columns from the database.

Outside of what the mapping process does to our class, the class remainsotherwise mostly a normal Python class, to which we can define anynumber of ordinary attributes and methods needed by our application.

• 1
• For information on why a primary key is required, seeHow do I map a table that has no primary key?.

Create a Schema

With our User class constructed via the Declarative system, we have defined information aboutour table, known as table metadata. The object used by SQLAlchemy to representthis information for a specific table is called the Table object, and here Declarative has madeone for us. We can see this object by inspecting the table attribute:

>>> User.__table__ 
Table('users', MetaData(bind=None),
            Column('id', Integer(), table=<users>, primary_key=True, nullable=False),
            Column('name', String(), table=<users>),
            Column('fullname', String(), table=<users>),
            Column('nickname', String(), table=<users>), schema=None)

Classical Mappings

The Declarative system, though highly recommended,is not required in order to use SQLAlchemy’s ORM.Outside of Declarative, anyplain Python class can be mapped to any Tableusing the mapper() function directly; thisless common usage is described at Classical Mappings.

When we declared our class, Declarative used a Python metaclass in order toperform additional activities once the class declaration was complete; withinthis phase, it then created a Table object according to ourspecifications, and associated it with the class by constructinga Mapper object. This object is a behind-the-scenes object we normallydon’t need to deal with directly (though it can provide plenty of informationabout our mapping when we need it).

The Table object is a member of a larger collectionknown as MetaData. When using Declarative,this object is available using the .metadataattribute of our declarative base class.

The MetaDatais a registry which includes the ability to emit a limited setof schema generation commands to the database. As our SQLite databasedoes not actually have a users table present, we can use MetaDatato issue CREATE TABLE statements to the database for all tables that don’t yet exist.Below, we call the MetaData.create_all() method, passing in our Engineas a source of database connectivity. We will see that special commands arefirst emitted to check for the presence of the users table, and following thatthe actual CREATE TABLE statement:

>>> Base.metadata.create_all(engine)
SELECT ...
PRAGMA main.table_info("users")
()
PRAGMA temp.table_info("users")
()
CREATE TABLE users (
    id INTEGER NOT NULL, name VARCHAR,
    fullname VARCHAR,
    nickname VARCHAR,
    PRIMARY KEY (id)
)
()
COMMIT

Minimal Table Descriptions vs. Full Descriptions

Users familiar with the syntax of CREATE TABLE may notice that theVARCHAR columns were generated without a length; on SQLite and PostgreSQL,this is a valid datatype, but on others, it’s not allowed. So if runningthis tutorial on one of those databases, and you wish to use SQLAlchemy toissue CREATE TABLE, a “length” may be provided to the String type asbelow:

Column(String(50))

The length field on String, as well as similar precision/scale fieldsavailable on Integer, Numeric, etc. are not referenced bySQLAlchemy other than when creating tables.

Additionally, Firebird and Oracle require sequences to generate newprimary key identifiers, and SQLAlchemy doesn’t generate or assume thesewithout being instructed. For that, you use the Sequence construct:

from sqlalchemy import Sequence
Column(Integer, Sequence('user_id_seq'), primary_key=True)

A full, foolproof Table generated via our declarativemapping is therefore:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    nickname = Column(String(50))
 
    def __repr__(self):
        return "<User(name='%s', fullname='%s', nickname='%s')>" % (
                                self.name, self.fullname, self.nickname)

We include this more verbose table definition separatelyto highlight the difference between a minimal construct geared primarilytowards in-Python usage only, versus one that will be used to emit CREATETABLE statements on a particular set of backends with more stringentrequirements.

Create an Instance of the Mapped Class

With mappings complete, let’s now create and inspect a User object:

>>> ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
>>> ed_user.name
'ed'
>>> ed_user.nickname
'edsnickname'
>>> str(ed_user.id)
'None'

the init() method

Our User class, as defined using the Declarative system, hasbeen provided with a constructor (e.g. init() method) which automaticallyaccepts keyword names that match the columns we’ve mapped. We are freeto define any explicit init() method we prefer on our class, whichwill override the default method provided by Declarative.

Even though we didn’t specify it in the constructor, the id attributestill produces a value of None when we access it (as opposed to Python’susual behavior of raising AttributeError for an undefined attribute).SQLAlchemy’s instrumentation normally produces this default value forcolumn-mapped attributes when first accessed. For those attributes wherewe’ve actually assigned a value, the instrumentation system is trackingthose assignments for use within an eventual INSERT statement to be emitted to thedatabase.

Creating a Session

We’re now ready to start talking to the database. The ORM’s “handle” to thedatabase is the Session. When we first set upthe application, at the same level as our create_engine()statement, we define a Session class whichwill serve as a factory for new Sessionobjects:

>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)

In the case where your application does not yet have anEngine when you define your module-levelobjects, just set it up like this:

>>> Session = sessionmaker()

Later, when you create your engine with create_engine(),connect it to the Session usingconfigure():

>>> Session.configure(bind=engine)  # once engine is available

Session Lifecycle Patterns

The question of when to make a Session depends a lot on whatkind of application is being built. Keep in mind,the Session is just a workspace for your objects,local to a particular database connection - if you think ofan application thread as a guest at a dinner party, the Sessionis the guest’s plate and the objects it holds are the food(and the database…the kitchen?)! More on this topicavailable at When do I construct a Session, when do I commit it, and when do I close it?.

This custom-made Session class will createnew Session objects which are bound to ourdatabase. Other transactional characteristics may be defined when callingsessionmaker as well; these are described in a laterchapter. Then, whenever you need to have a conversation with the database, youinstantiate a Session:

>>> session = Session()

The above Session is associated with ourSQLite-enabled Engine, but it hasn’t opened any connections yet. When it’s firstused, it retrieves a connection from a pool of connections maintained by theEngine, and holds onto it until we commit all changes and/or close thesession object.

Adding and Updating Objects

To persist our User object, we add() it to our Session:

>>> ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
>>> session.add(ed_user)

At this point, we say that the instance is pending; no SQL has yet been issuedand the object is not yet represented by a row in the database. TheSession will issue the SQL to persist EdJones as soon as is needed, using a process known as a flush. If wequery the database for Ed Jones, all pending information will first beflushed, and the query is issued immediately thereafter.

For example, below we create a new Query objectwhich loads instances of User. We “filter by” the name attribute ofed, and indicate that we’d like only the first result in the full list ofrows. A User instance is returned which is equivalent to that which we’veadded:

sql>>> our_user = session.query(User).filter_by(name='ed').first() 
BEGIN (implicit)
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
('ed', 'Ed Jones', 'edsnickname')
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name = ?
 LIMIT ? OFFSET ?
('ed', 1, 0)
>>> our_user
<User(name='ed', fullname='Ed Jones', nickname='edsnickname')>

In fact, the Session has identified that therow returned is the same row as one already represented within itsinternal map of objects, so we actually got back the identical instance asthat which we just added:

>>> ed_user is our_user
True

The ORM concept at work here is known as an identity mapand ensures thatall operations upon a particular row within aSession operate upon the same set of data.Once an object with a particular primary key is present in theSession, all SQL queries on thatSession will always return the same Pythonobject for that particular primary key; it also will raise an error if anattempt is made to place a second, already-persisted object with the sameprimary key within the session.

We can add more User objects at once usingadd_all():

>>> session.add_all([
...     User(name='wendy', fullname='Wendy Williams', nickname='windy'),
...     User(name='mary', fullname='Mary Contrary', nickname='mary'),
...     User(name='fred', fullname='Fred Flintstone', nickname='freddy')])

Also, we’ve decided Ed’s nickname isn’t that great, so lets change it:

>>> ed_user.nickname = 'eddie'

The Session is paying attention. It knows,for example, that Ed Jones has been modified:

>>> session.dirty
IdentitySet([<User(name='ed', fullname='Ed Jones', nickname='eddie')>])

and that three new User objects are pending:

>>> session.new  
IdentitySet([<User(name='wendy', fullname='Wendy Williams', nickname='windy')>,
<User(name='mary', fullname='Mary Contrary', nickname='mary')>,
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>])

We tell the Session that we’d like to issueall remaining changes to the database and commit the transaction, which hasbeen in progress throughout. We do this via commit(). TheSession emits the UPDATE statementfor the nickname change on “ed”, as well as INSERT statements for thethree new User objects we’ve added:

sql>>> session.commit()
UPDATE users SET nickname=? WHERE users.id = ?
('eddie', 1)
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
('wendy', 'Wendy Williams', 'windy')
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
('mary', 'Mary Contrary', 'mary')
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
('fred', 'Fred Flintstone', 'freddy')
COMMIT

commit() flushes the remaining changes to thedatabase, and commits the transaction. The connection resources referenced bythe session are now returned to the connection pool. Subsequent operationswith this session will occur in a new transaction, which will againre-acquire connection resources when first needed.

If we look at Ed’s id attribute, which earlier was None, it now has a value:

sql>>> ed_user.id 
BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.id = ?
(1,)
1

After the Session inserts new rows in thedatabase, all newly generated identifiers and database-generated defaultsbecome available on the instance, either immediately or viaload-on-first-access. In this case, the entire row was re-loaded on accessbecause a new transaction was begun after we issued commit(). SQLAlchemyby default refreshes data from a previous transaction the first time it’saccessed within a new transaction, so that the most recent state is available.The level of reloading is configurable as is described in Using the Session.

Session Object States

As our User object moved from being outside the Session, toinside the Session without a primary key, to actually beinginserted, it moved between three out of fouravailable “object states” - transient, pending, and persistent.Being aware of these states and what they mean is always a good idea -be sure to read Quickie Intro to Object States for a quick overview.

Rolling Back

Since the Session works within a transaction,we can roll back changes made too. Let’s make two changes that we’ll revert;ed_user’s user name gets set to Edwardo:

>>> ed_user.name = 'Edwardo'

and we’ll add another erroneous user, fake_user:

>>> fake_user = User(name='fakeuser', fullname='Invalid', nickname='12345')
>>> session.add(fake_user)

Querying the session, we can see that they’re flushed into the current transaction:

sql>>> session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()
UPDATE users SET name=? WHERE users.id = ?
('Edwardo', 1)
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
('fakeuser', 'Invalid', '12345')
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name IN (?, ?)
('Edwardo', 'fakeuser')
[<User(name='Edwardo', fullname='Ed Jones', nickname='eddie')>, <User(name='fakeuser', fullname='Invalid', nickname='12345')>]

Rolling back, we can see that ed_user’s name is back to ed, andfake_user has been kicked out of the session:

sql>>> session.rollback()
ROLLBACK
 
sql>>> ed_user.name
BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.id = ?
(1,)
u'ed'
>>> fake_user in session
False

issuing a SELECT illustrates the changes made to the database:

sql>>> session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name IN (?, ?)
('ed', 'fakeuser')
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>]

Querying

A Query object is created using thequery() method onSession. This function takes a variablenumber of arguments, which can be any combination of classes andclass-instrumented descriptors. Below, we indicate aQuery which loads User instances. Whenevaluated in an iterative context, the list of User objects present isreturned:

sql>>> for instance in session.query(User).order_by(User.id):
...     print(instance.name, instance.fullname)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users ORDER BY users.id
()
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flintstone

The Query also accepts ORM-instrumenteddescriptors as arguments. Any time multiple class entities or column-basedentities are expressed as arguments to thequery() function, the return resultis expressed as tuples:

sql>>> for name, fullname in session.query(User.name, User.fullname):
...     print(name, fullname)
SELECT users.name AS users_name,
        users.fullname AS users_fullname
FROM users
()
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flintstone

The tuples returned by Query are _named_tuples, supplied by the KeyedTuple class, and can be treated much like anordinary Python object. The names arethe same as the attribute’s name for an attribute, and the class name for aclass:

sql>>> for row in session.query(User, User.name).all():
...    print(row.User, row.name)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
()
<User(name='ed', fullname='Ed Jones', nickname='eddie')> ed
<User(name='wendy', fullname='Wendy Williams', nickname='windy')> wendy
<User(name='mary', fullname='Mary Contrary', nickname='mary')> mary
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')> fred

You can control the names of individual column expressions using thelabel() construct, which is available fromany ColumnElement-derived object, as well as any class attribute whichis mapped to one (such as User.name):

sql>>> for row in session.query(User.name.label('name_label')).all():
...    print(row.name_label)
SELECT users.name AS name_label
FROM users
()
ed
wendy
mary
fred

The name given to a full entity such as User, assuming that multipleentities are present in the call to query(), can be controlled usingaliased() :

>>> from sqlalchemy.orm import aliased
>>> user_alias = aliased(User, name='user_alias')
 
sql>>> for row in session.query(user_alias, user_alias.name).all():
...    print(row.user_alias)
SELECT user_alias.id AS user_alias_id,
        user_alias.name AS user_alias_name,
        user_alias.fullname AS user_alias_fullname,
        user_alias.nickname AS user_alias_nickname
FROM users AS user_alias
()
<User(name='ed', fullname='Ed Jones', nickname='eddie')>
<User(name='wendy', fullname='Wendy Williams', nickname='windy')>
<User(name='mary', fullname='Mary Contrary', nickname='mary')>
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>

Basic operations with Query include issuingLIMIT and OFFSET, most conveniently using Python array slices and typically inconjunction with ORDER BY:

sql>>> for u in session.query(User).order_by(User.id)[1:3]:
...    print(u)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users ORDER BY users.id
LIMIT ? OFFSET ?
(2, 1)
<User(name='wendy', fullname='Wendy Williams', nickname='windy')>
<User(name='mary', fullname='Mary Contrary', nickname='mary')>

and filtering results, which is accomplished either withfilter_by(), which uses keyword arguments:

sql>>> for name, in session.query(User.name).\
...             filter_by(fullname='Ed Jones'):
...    print(name)
SELECT users.name AS users_name FROM users
WHERE users.fullname = ?
('Ed Jones',)
ed

…or filter(), which uses more flexible SQLexpression language constructs. These allow you to use regular Pythonoperators with the class-level attributes on your mapped class:

sql>>> for name, in session.query(User.name).\
...             filter(User.fullname=='Ed Jones'):
...    print(name)
SELECT users.name AS users_name FROM users
WHERE users.fullname = ?
('Ed Jones',)
ed

The Query object is fully generative, meaningthat most method calls return a new Queryobject upon which further criteria may be added. For example, to query forusers named “ed” with a full name of “Ed Jones”, you can callfilter() twice, which joins criteria usingAND:

sql>>> for user in session.query(User).\
...          filter(User.name=='ed').\
...          filter(User.fullname=='Ed Jones'):
...    print(user)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name = ? AND users.fullname = ?
('ed', 'Ed Jones')
<User(name='ed', fullname='Ed Jones', nickname='eddie')>

Common Filter Operators

Here’s a rundown of some of the most common operators used infilter():

• equals:

query.filter(User.name == 'ed')

• not equals:

query.filter(User.name != 'ed')

• LIKE:

query.filter(User.name.like('%ed%'))

Note

ColumnOperators.like() renders the LIKE operator, whichis case insensitive on some backends, and case sensitiveon others. For guaranteed case-insensitive comparisons, useColumnOperators.ilike().

• ILIKE (case-insensitive LIKE):

query.filter(User.name.ilike('%ed%'))

Note

most backends don’t support ILIKE directly. For those,the ColumnOperators.ilike() operator renders an expressioncombining LIKE with the LOWER SQL function applied to each operand.

• IN:

query.filter(User.name.in_(['ed', 'wendy', 'jack']))
 
# works with query objects too:
query.filter(User.name.in_(
    session.query(User.name).filter(User.name.like('%ed%'))
))
 
# use tuple_() for composite (multi-column) queries
from sqlalchemy import tuple_
query.filter(
    tuple_(User.name, User.nickname).\
    in_([('ed', 'edsnickname'), ('wendy', 'windy')])
)

• NOT IN:

query.filter(~User.name.in_(['ed', 'wendy', 'jack']))

• IS NULL:

query.filter(User.name == None)
 
# alternatively, if pep8/linters are a concern
query.filter(User.name.is_(None))

• IS NOT NULL:

query.filter(User.name != None)
 
# alternatively, if pep8/linters are a concern
query.filter(User.name.isnot(None))

• AND:

# use and_()
from sqlalchemy import and_
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
 
# or send multiple expressions to .filter()
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
 
# or chain multiple filter()/filter_by() calls
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

Note

Make sure you use and_() and not thePython and operator!

• OR:

from sqlalchemy import or_
query.filter(or_(User.name == 'ed', User.name == 'wendy'))

Note

Make sure you use or_() and not thePython or operator!

• MATCH:

query.filter(User.name.match('wendy'))

Note

match() uses a database-specific MATCHor CONTAINS function; its behavior will vary by backend and is notavailable on some backends such as SQLite.

Returning Lists and Scalars

A number of methods on Queryimmediately issue SQL and return a value containing loadeddatabase results. Here’s a brief tour:

• all() returns a list:

>>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)
sql>>> query.all()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name LIKE ? ORDER BY users.id
('%ed',)
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>,
      <User(name='fred', fullname='Fred Flintstone', nickname='freddy')>]

Warning

When the Query object returns lists of ORM-mapped objectssuch as the User object above, the entries are deduplicatedbased on primary key, as the results are interpreted from the SQLresult set. That is, if SQL query returns a row with id=7 twice,you would only get a single User(id=7) object back in the resultlist. This does not apply to the case when individual columns arequeried.

See also

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

• first() applies a limit of one and returnsthe first result as a scalar:

sql>>> query.first()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name LIKE ? ORDER BY users.id
 LIMIT ? OFFSET ?
('%ed', 1, 0)
<User(name='ed', fullname='Ed Jones', nickname='eddie')>

• one() fully fetches all rows, and if notexactly one object identity or composite row is present in the result, raisesan error. With multiple rows found:

>>> user = query.one()
Traceback (most recent call last):
...
MultipleResultsFound: Multiple rows were found for one()

With no rows found:

>>> user = query.filter(User.id == 99).one()
Traceback (most recent call last):
...
NoResultFound: No row was found for one()

The one() method is great for systems that expect to handle“no items found” versus “multiple items found” differently; such as a RESTfulweb service, which may want to raise a “404 not found” when no results are found,but raise an application error when multiple results are found.

• one_or_none() is like one(), except that if noresults are found, it doesn’t raise an error; it just returns None. Likeone(), however, it does raise an error if multiple results arefound.

• scalar() invokes the one() method, and uponsuccess returns the first column of the row:

>>> query = session.query(User.id).filter(User.name == 'ed').\
...    order_by(User.id)
sql>>> query.scalar()
SELECT users.id AS users_id
FROM users
WHERE users.name = ? ORDER BY users.id
('ed',)
1

Using Textual SQL

Literal strings can be used flexibly withQuery, by specifying their usewith the text() construct, which is acceptedby most applicable methods. For example,filter() andorder_by():

>>> from sqlalchemy import text
sql>>> for user in session.query(User).\
...             filter(text("id<224")).\
...             order_by(text("id")).all():
...     print(user.name)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE id<224 ORDER BY id
()
ed
wendy
mary
fred

Bind parameters can be specified with string-based SQL, using a colon. Tospecify the values, use the params()method:

sql>>> session.query(User).filter(text("id<:value and name=:name")).\
...     params(value=224, name='fred').order_by(User.id).one()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE id<? and name=? ORDER BY users.id
(224, 'fred')
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>

To use an entirely string-based statement, a text() constructrepresenting a complete statement can be passed tofrom_statement(). Without additionalspecifiers, the columns in the string SQL are matched to the model columnsbased on name, such as below where we use just an asterisk to representloading all columns:

sql>>> session.query(User).from_statement(
...                     text("SELECT * FROM users where name=:name")).\
...                     params(name='ed').all()
SELECT * FROM users where name=?
('ed',)
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>]

Matching columns on name works for simple cases but can become unwieldy whendealing with complex statements that contain duplicate column names or whenusing anonymized ORM constructs that don’t easily match to specific names.Additionally, there is typing behavior present in our mapped columns thatwe might find necessary when handling result rows. For these cases,the text() construct allows us to link its textual SQLto Core or ORM-mapped column expressions positionally; we can achieve thisby passing column expressions as positional arguments to theTextClause.columns() method:

>>> stmt = text("SELECT name, id, fullname, nickname "
...             "FROM users where name=:name")
>>> stmt = stmt.columns(User.name, User.id, User.fullname, User.nickname)
sql>>> session.query(User).from_statement(stmt).params(name='ed').all()
SELECT name, id, fullname, nickname FROM users where name=?
('ed',)
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>]

New in version 1.1: The TextClause.columns() method now accepts column expressionswhich will be matched positionally to a plain text SQL result set,eliminating the need for column names to match or even be unique in theSQL statement.

When selecting from a text() construct, the Querymay still specify what columns and entities are to be returned; instead ofquery(User) we can also ask for the columns individually, as inany other case:

>>> stmt = text("SELECT name, id FROM users where name=:name")
>>> stmt = stmt.columns(User.name, User.id)
sql>>> session.query(User.id, User.name).\
...          from_statement(stmt).params(name='ed').all()
SELECT name, id FROM users where name=?
('ed',)
[(1, u'ed')]

See also

Using Textual SQL - The text() construct explainedfrom the perspective of Core-only queries.

Counting

Query includes a convenience method forcounting called count():

sql>>> session.query(User).filter(User.name.like('%ed')).count()
SELECT count(*) AS count_1
FROM (SELECT users.id AS users_id,
                users.name AS users_name,
                users.fullname AS users_fullname,
                users.nickname AS users_nickname
FROM users
WHERE users.name LIKE ?) AS anon_1
('%ed',)
2

Counting on count()

Query.count() used to be a very complicated methodwhen it would try to guess whether or not a subquery was neededaround theexisting query, and in some exotic cases it wouldn’t do the right thing.Now that it uses a simple subquery every time, it’s only two lines longand always returns the right answer. Use func.count() if aparticular statement absolutely cannot tolerate the subquery being present.

The count() method is used to determinehow many rows the SQL statement would return. Lookingat the generated SQL above, SQLAlchemy always places whatever it is we arequerying into a subquery, then counts the rows from that. In some casesthis can be reduced to a simpler SELECT count(*) FROM table, howevermodern versions of SQLAlchemy don’t try to guess when this is appropriate,as the exact SQL can be emitted using more explicit means.

For situations where the “thing to be counted” needsto be indicated specifically, we can specify the “count” functiondirectly using the expression func.count(), available from thefunc construct. Below weuse it to return the count of each distinct user name:

>>> from sqlalchemy import func
sql>>> session.query(func.count(User.name), User.name).group_by(User.name).all()
SELECT count(users.name) AS count_1, users.name AS users_name
FROM users GROUP BY users.name
()
[(1, u'ed'), (1, u'fred'), (1, u'mary'), (1, u'wendy')]

To achieve our simple SELECT count(*) FROM table, we can apply it as:

sql>>> session.query(func.count('*')).select_from(User).scalar()
SELECT count(?) AS count_1
FROM users
('*',)
4

The usage of select_from() can be removed if we express the count in termsof the User primary key directly:

sql>>> session.query(func.count(User.id)).scalar()
SELECT count(users.id) AS count_1
FROM users
()
4

Building a Relationship

Let’s consider how a second table, related to User, can be mapped andqueried. Users in our systemcan store any number of email addresses associated with their username. Thisimplies a basic one to many association from the users to a newtable which stores email addresses, which we will call addresses. Usingdeclarative, we define this table along with its mapped class, Address:

>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship
 
>>> class Address(Base):
...     __tablename__ = 'addresses'
...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey('users.id'))
...
...     user = relationship("User", back_populates="addresses")
...
...     def __repr__(self):
...         return "<Address(email_address='%s')>" % self.email_address
 
>>> User.addresses = relationship(
...     "Address", order_by=Address.id, back_populates="user")

The above class introduces the ForeignKey construct, which is adirective applied to Column that indicates that values in thiscolumn should be constrained to be values present in the named remotecolumn. This is a core feature of relational databases, and is the “glue” thattransforms an otherwise unconnected collection of tables to have richoverlapping relationships. The ForeignKey above expresses thatvalues in the addresses.user_id column should be constrained tothose values in the users.id column, i.e. its primary key.

A second directive, known as relationship(),tells the ORM that the Address class itself should be linkedto the User class, using the attribute Address.user.relationship() uses the foreign keyrelationships between the two tables to determine the nature ofthis linkage, determining that Address.user will be many to one.An additional relationship() directive is placed on theUser mapped class under the attribute User.addresses. In bothrelationship() directives, the parameterrelationship.back_populates is assigned to refer to thecomplementary attribute names; by doing so, each relationship()can make intelligent decision about the same relationship as expressedin reverse; on one side, Address.user refers to a User instance,and on the other side, User.addresses refers to a list ofAddress instances.

Note

The relationship.back_populates parameter is a newerversion of a very common SQLAlchemy feature calledrelationship.backref. The relationship.backrefparameter hasn’t gone anywhere and will always remain available!The relationship.back_populates is the same thing, excepta little more verbose and easier to manipulate. For an overviewof the entire topic, see the section Linking Relationships with Backref.

The reverse side of a many-to-one relationship is always one to many.A full catalog of available relationship() configurationsis at Basic Relationship Patterns.

The two complementing relationships Address.user and User.addressesare referred to as a bidirectional relationship, and is a keyfeature of the SQLAlchemy ORM. The section Linking Relationships with Backrefdiscusses the “backref” feature in detail.

Arguments to relationship() which concern the remote classcan be specified using strings, assuming the Declarative system is inuse. Once all mappings are complete, these strings are evaluatedas Python expressions in order to produce the actual argument, in theabove case the User class. The names which are allowed duringthis evaluation include, among other things, the names of all classeswhich have been created in terms of the declared base.

See the docstring for relationship() for more detail on argument style.

Did you know ?

• a FOREIGN KEY constraint in most (though not all) relational databases canonly link to a primary key column, or a column that has a UNIQUE constraint.

• a FOREIGN KEY constraint that refers to a multiple column primary key, and itselfhas multiple columns, is known as a “composite foreign key”. It can alsoreference a subset of those columns.

• FOREIGN KEY columns can automatically update themselves, in response to a changein the referenced column or row. This is known as the CASCADE referential action,and is a built in function of the relational database.

• FOREIGN KEY can refer to its own table. This is referred to as a “self-referential”foreign key.

• Read more about foreign keys at Foreign Key - Wikipedia.

We’ll need to create the addresses table in the database, so we will issueanother CREATE from our metadata, which will skip over tables which havealready been created:

sql>>> Base.metadata.create_all(engine)
PRAGMA...
CREATE TABLE addresses (
    id INTEGER NOT NULL,
    email_address VARCHAR NOT NULL,
    user_id INTEGER,
    PRIMARY KEY (id),
     FOREIGN KEY(user_id) REFERENCES users (id)
)
()
COMMIT

Working with Related Objects

Now when we create a User, a blank addresses collection will bepresent. Various collection types, such as sets and dictionaries, are possiblehere (see Customizing Collection Access for details), but bydefault, the collection is a Python list.

>>> jack = User(name='jack', fullname='Jack Bean', nickname='gjffdd')
>>> jack.addresses
[]

We are free to add Address objects on our User object. In this case wejust assign a full list directly:

>>> jack.addresses = [
...                 Address(email_address='jack@google.com'),
...                 Address(email_address='j25@yahoo.com')]

When using a bidirectional relationship, elements added in one directionautomatically become visible in the other direction. This behavior occursbased on attribute on-change events and is evaluated in Python, withoutusing any SQL:

>>> jack.addresses[1]
<Address(email_address='j25@yahoo.com')>
 
>>> jack.addresses[1].user
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>

Let’s add and commit Jack Bean to the database. jack as wellas the two Address members in the corresponding addressescollection are both added to the session at once, using a processknown as cascading:

>>> session.add(jack)
sql>>> session.commit()
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
('jack', 'Jack Bean', 'gjffdd')
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('jack@google.com', 5)
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('j25@yahoo.com', 5)
COMMIT

Querying for Jack, we get just Jack back. No SQL is yet issued for Jack’s addresses:

sql>>> jack = session.query(User).\
... filter_by(name='jack').one()
BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name = ?
('jack',)
>>> jack
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>

Let’s look at the addresses collection. Watch the SQL:

sql>>> jack.addresses
SELECT addresses.id AS addresses_id,
        addresses.email_address AS
        addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id ORDER BY addresses.id
(5,)
[<Address(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]

When we accessed the addresses collection, SQL was suddenly issued. Thisis an example of a lazy loading relationship. The addresses collectionis now loaded and behaves just like an ordinary list. We’ll cover waysto optimize the loading of this collection in a bit.

Querying with Joins

Now that we have two tables, we can show some more features of Query,specifically how to create queries that deal with both tables at the same time.The Wikipedia page on SQL JOIN offers a good introduction tojoin techniques, several of which we’ll illustrate here.

To construct a simple implicit join between User and Address,we can use Query.filter() to equate their related columns together.Below we load the User and Address entities at once using this method:

sql>>> for u, a in session.query(User, Address).\
...                     filter(User.id==Address.user_id).\
...                     filter(Address.email_address=='jack@google.com').\
...                     all():
...     print(u)
...     print(a)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname,
        addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM users, addresses
WHERE users.id = addresses.user_id
        AND addresses.email_address = ?
('jack@google.com',)
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>
<Address(email_address='jack@google.com')>

The actual SQL JOIN syntax, on the other hand, is most easily achievedusing the Query.join() method:

sql>>> session.query(User).join(Address).\
...         filter(Address.email_address=='jack@google.com').\
...         all()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = ?
('jack@google.com',)
[<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>]

Query.join() knows how to join between Userand Address because there’s only one foreign key between them. If therewere no foreign keys, or several, Query.join()works better when one of the following forms are used:

query.join(Address, User.id==Address.user_id)    # explicit condition
query.join(User.addresses)                       # specify relationship from left to right
query.join(Address, User.addresses)              # same, with explicit target
query.join('addresses')                          # same, using a string

As you would expect, the same idea is used for “outer” joins, using theouterjoin() function:

query.outerjoin(User.addresses)   # LEFT OUTER JOIN

The reference documentation for join() contains detailed informationand examples of the calling styles accepted by this method; join()is an important method at the center of usage for any SQL-fluent application.

What does Query select from if there’s multiple entities?

The Query.join() method will typically join from the leftmostitem in the list of entities, when the ON clause is omitted, or if theON clause is a plain SQL expression. To control the first entity in the listof JOINs, use the Query.select_from() method:

query = session.query(User, Address).select_from(Address).join(User)

Using Aliases

When querying across multiple tables, if the same table needs to be referencedmore than once, SQL typically requires that the table be aliased withanother name, so that it can be distinguished against other occurrences ofthat table. The Query supports this mostexplicitly using the aliased construct. Below we join to the Addressentity twice, to locate a user who has two distinct email addresses at thesame time:

>>> from sqlalchemy.orm import aliased
>>> adalias1 = aliased(Address)
>>> adalias2 = aliased(Address)
sql>>> for username, email1, email2 in \
...     session.query(User.name, adalias1.email_address, adalias2.email_address).\
...     join(adalias1, User.addresses).\
...     join(adalias2, User.addresses).\
...     filter(adalias1.email_address=='jack@google.com').\
...     filter(adalias2.email_address=='j25@yahoo.com'):
...     print(username, email1, email2)
SELECT users.name AS users_name,
        addresses_1.email_address AS addresses_1_email_address,
        addresses_2.email_address AS addresses_2_email_address
FROM users JOIN addresses AS addresses_1
        ON users.id = addresses_1.user_id
JOIN addresses AS addresses_2
        ON users.id = addresses_2.user_id
WHERE addresses_1.email_address = ?
        AND addresses_2.email_address = ?
('jack@google.com', 'j25@yahoo.com')
jack jack@google.com j25@yahoo.com

Using Subqueries

The Query is suitable for generating statementswhich can be used as subqueries. Suppose we wanted to load User objectsalong with a count of how many Address records each user has. The best wayto generate SQL like this is to get the count of addresses grouped by userids, and JOIN to the parent. In this case we use a LEFT OUTER JOIN so that weget rows back for those users who don’t have any addresses, e.g.:

SELECT users.*, adr_count.address_count FROM users LEFT OUTER JOIN
    (SELECT user_id, count(*) AS address_count
        FROM addresses GROUP BY user_id) AS adr_count
    ON users.id=adr_count.user_id

Using the Query, we build a statement like thisfrom the inside out. The statement accessor returns a SQL expressionrepresenting the statement generated by a particularQuery - this is an instance of a select()construct, which are described in SQL Expression Language Tutorial:

>>> from sqlalchemy.sql import func
>>> stmt = session.query(Address.user_id, func.count('*').\
...         label('address_count')).\
...         group_by(Address.user_id).subquery()

The func keyword generates SQL functions, and the subquery() method onQuery produces a SQL expression constructrepresenting a SELECT statement embedded within an alias (it’s actuallyshorthand for query.statement.alias()).

Once we have our statement, it behaves like aTable construct, such as the one we created forusers at the start of this tutorial. The columns on the statement areaccessible through an attribute called c:

sql>>> for u, count in session.query(User, stmt.c.address_count).\
...     outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id):
...     print(u, count)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname,
        anon_1.address_count AS anon_1_address_count
FROM users LEFT OUTER JOIN
    (SELECT addresses.user_id AS user_id, count(?) AS address_count
    FROM addresses GROUP BY addresses.user_id) AS anon_1
    ON users.id = anon_1.user_id
ORDER BY users.id
('*',)
<User(name='ed', fullname='Ed Jones', nickname='eddie')> None
<User(name='wendy', fullname='Wendy Williams', nickname='windy')> None
<User(name='mary', fullname='Mary Contrary', nickname='mary')> None
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')> None
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')> 2

Selecting Entities from Subqueries

Above, we just selected a result that included a column from a subquery. Whatif we wanted our subquery to map to an entity ? For this we use aliased()to associate an “alias” of a mapped class to a subquery:

sql>>> stmt = session.query(Address).\
...                 filter(Address.email_address != 'j25@yahoo.com').\
...                 subquery()
>>> adalias = aliased(Address, stmt)
>>> for user, address in session.query(User, adalias).\
...         join(adalias, User.addresses):
...     print(user)
...     print(address)
SELECT users.id AS users_id,
            users.name AS users_name,
            users.fullname AS users_fullname,
            users.nickname AS users_nickname,
            anon_1.id AS anon_1_id,
            anon_1.email_address AS anon_1_email_address,
            anon_1.user_id AS anon_1_user_id
FROM users JOIN
    (SELECT addresses.id AS id,
            addresses.email_address AS email_address,
            addresses.user_id AS user_id
    FROM addresses
    WHERE addresses.email_address != ?) AS anon_1
    ON users.id = anon_1.user_id
('j25@yahoo.com',)
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>
<Address(email_address='jack@google.com')>

Using EXISTS

The EXISTS keyword in SQL is a boolean operator which returns True if thegiven expression contains any rows. It may be used in many scenarios in placeof joins, and is also useful for locating rows which do not have acorresponding row in a related table.

There is an explicit EXISTS construct, which looks like this:

>>> from sqlalchemy.sql import exists
>>> stmt = exists().where(Address.user_id==User.id)
sql>>> for name, in session.query(User.name).filter(stmt):
...     print(name)
SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT *
FROM addresses
WHERE addresses.user_id = users.id)
()
jack

The Query features several operators which makeusage of EXISTS automatically. Above, the statement can be expressed along theUser.addresses relationship using any():

sql>>> for name, in session.query(User.name).\
...         filter(User.addresses.any()):
...     print(name)
SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT 1
FROM addresses
WHERE users.id = addresses.user_id)
()
jack

any() takes criterion as well, to limit the rows matched:

sql>>> for name, in session.query(User.name).\
...     filter(User.addresses.any(Address.email_address.like('%google%'))):
...     print(name)
SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT 1
FROM addresses
WHERE users.id = addresses.user_id AND addresses.email_address LIKE ?)
('%google%',)
jack

has() is the same operator asany() for many-to-one relationships(note the ~ operator here too, which means “NOT”):

sql>>> session.query(Address).\
...         filter(~Address.user.has(User.name=='jack')).all()
SELECT addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses
WHERE NOT (EXISTS (SELECT 1
FROM users
WHERE users.id = addresses.user_id AND users.name = ?))
('jack',)
[]

Common Relationship Operators

Here’s all the operators which build on relationships - each oneis linked to its API documentation which includes full details on usageand behavior:

• eq() (many-to-one “equals” comparison):

query.filter(Address.user == someuser)

• ne() (many-to-one “not equals” comparison):

query.filter(Address.user != someuser)

• IS NULL (many-to-one comparison, also uses eq()):

query.filter(Address.user == None)

• contains() (used for one-to-many collections):

query.filter(User.addresses.contains(someaddress))

• any() (used for collections):

query.filter(User.addresses.any(Address.email_address == 'bar'))
 
# also takes keyword arguments:
query.filter(User.addresses.any(email_address='bar'))

• has() (used for scalar references):

query.filter(Address.user.has(name='ed'))

• Query.with_parent() (used for any relationship):

session.query(Address).with_parent(someuser, 'addresses')

Eager Loading

Recall earlier that we illustrated a lazy loading operation, whenwe accessed the User.addresses collection of a User and SQLwas emitted. If you want to reduce the number of queries (dramatically, in many cases),we can apply an eager load to the query operation. SQLAlchemyoffers three types of eager loading, two of which are automatic, and a thirdwhich involves custom criterion. All three are usually invoked via functions knownas query options which give additional instructions to the Query on howwe would like various attributes to be loaded, via the Query.options() method.

Selectin Load

In this case we’d like to indicate that User.addresses should load eagerly.A good choice for loading a set of objects as well as their related collectionsis the orm.selectinload() option, which emits a second SELECT statementthat fully loads the collections associated with the results just loaded.The name “selectin” originates from the fact that the SELECT statementuses an IN clause in order to locate related rows for multiple objectsat once:

>>> from sqlalchemy.orm import selectinload
sql>>> jack = session.query(User).\
...                 options(selectinload(User.addresses)).\
...                 filter_by(name='jack').one()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name = ?
('jack',)
SELECT addresses.user_id AS addresses_user_id,
        addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address
FROM addresses
WHERE addresses.user_id IN (?)
ORDER BY addresses.user_id, addresses.id
(5,)
>>> jack
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>
 
>>> jack.addresses
[<Address(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]

Joined Load

The other automatic eager loading function is more well known and is calledorm.joinedload(). This style of loading emits a JOIN, by defaulta LEFT OUTER JOIN, so that the lead object as well as the related objector collection is loaded in one step. We illustrate loading the sameaddresses collection in this way - note that even though the User.addressescollection on jack is actually populated right now, the querywill emit the extra join regardless:

>>> from sqlalchemy.orm import joinedload
 
sql>>> jack = session.query(User).\
...                        options(joinedload(User.addresses)).\
...                        filter_by(name='jack').one()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname,
        addresses_1.id AS addresses_1_id,
        addresses_1.email_address AS addresses_1_email_address,
        addresses_1.user_id AS addresses_1_user_id
FROM users
    LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
WHERE users.name = ? ORDER BY addresses_1.id
('jack',)
>>> jack
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>
 
>>> jack.addresses
[<Address(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]

Note that even though the OUTER JOIN resulted in two rows, we still only gotone instance of User back. This is because Query applies a “uniquing”strategy, based on object identity, to the returned entities. This is specificallyso that joined eager loading can be applied without affecting the query results.

While joinedload() has been around for a long time, selectinload()is a newer form of eager loading. selectinload() tends to be more appropriatefor loading related collections while joinedload() tends to be better suitedfor many-to-one relationships, due to the fact that only one row is loadedfor both the lead and the related object. Another form of loading,subqueryload(), also exists, which can be used in place ofselectinload() when making use of composite primary keys on certainbackends.

joinedload() is not a replacement for join()

The join created by joinedload() is anonymously aliased such thatit does not affect the query results. An Query.order_by()or Query.filter() call cannot reference these aliasedtables - so-called “user space” joins are constructed usingQuery.join(). The rationale for this is that joinedload() is onlyapplied in order to affect how related objects or collections are loadedas an optimizing detail - it can be added or removed with no impacton actual results. See the section The Zen of Joined Eager Loading fora detailed description of how this is used.

Explicit Join + Eagerload

A third style of eager loading is when we are constructing a JOIN explicitly inorder to locate the primary rows, and would like to additionally apply the extratable to a related object or collection on the primary object. This featureis supplied via the orm.contains_eager() function, and is mosttypically useful for pre-loading the many-to-one object on a query that needsto filter on that same object. Below we illustrate loading an Addressrow as well as the related User object, filtering on the User named“jack” and using orm.contains_eager() to apply the “user” columns to the Address.userattribute:

>>> from sqlalchemy.orm import contains_eager
sql>>> jacks_addresses = session.query(Address).\
...                             join(Address.user).\
...                             filter(User.name=='jack').\
...                             options(contains_eager(Address.user)).\
...                             all()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname,
        addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses JOIN users ON users.id = addresses.user_id
WHERE users.name = ?
('jack',)
>>> jacks_addresses
[<Address(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]
 
>>> jacks_addresses[0].user
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>

For more information on eager loading, including how to configure various formsof loading by default, see the section Relationship Loading Techniques.

Deleting

Let’s try to delete jack and see how that goes. We’ll mark the object as deletedin the session, then we’ll issue a count query to see that no rows remain:

>>> session.delete(jack)
sql>>> session.query(User).filter_by(name='jack').count()
UPDATE addresses SET user_id=? WHERE addresses.id = ?
((None, 1), (None, 2))
DELETE FROM users WHERE users.id = ?
(5,)
SELECT count(*) AS count_1
FROM (SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name = ?) AS anon_1
('jack',)
0

So far, so good. How about Jack’s Address objects ?

sql>>> session.query(Address).filter(
...     Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
...  ).count()
SELECT count(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
                addresses.email_address AS addresses_email_address,
                addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')
2

Uh oh, they’re still there ! Analyzing the flush SQL, we can see that theuser_id column of each address was set to NULL, but the rows weren’tdeleted. SQLAlchemy doesn’t assume that deletes cascade, you have to tell itto do so.

Configuring delete/delete-orphan Cascade

We will configure cascade options on the User.addresses relationshipto change the behavior. While SQLAlchemy allows you to add new attributes andrelationships to mappings at any point in time, in this case the existingrelationship needs to be removed, so we need to tear down the mappingscompletely and start again - we’ll close the Session:

>>> session.close()
ROLLBACK

and use a new declarative_base():

>>> Base = declarative_base()

Next we’ll declare the User class, adding in the addresses relationshipincluding the cascade configuration (we’ll leave the constructor out too):

>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     nickname = Column(String)
...
...     addresses = relationship("Address", back_populates='user',
...                     cascade="all, delete, delete-orphan")
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', nickname='%s')>" % (
...                                self.name, self.fullname, self.nickname)

Then we recreate Address, noting that in this case we’ve createdthe Address.user relationship via the User class already:

>>> class Address(Base):
...     __tablename__ = 'addresses'
...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey('users.id'))
...     user = relationship("User", back_populates="addresses")
...
...     def __repr__(self):
...         return "<Address(email_address='%s')>" % self.email_address

Now when we load the user jack (below using get(),which loads by primary key), removing an address from thecorresponding addresses collection will result in that Addressbeing deleted:

# load Jack by primary key
sql>>> jack = session.query(User).get(5)
BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.id = ?
(5,)
 
# remove one Address (lazy load fires off)
sql>>> del jack.addresses[1]
SELECT addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id
(5,)
 
# only one address remains
sql>>> session.query(Address).filter(
...     Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
... ).count()
DELETE FROM addresses WHERE addresses.id = ?
(2,)
SELECT count(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
                addresses.email_address AS addresses_email_address,
                addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')
1

Deleting Jack will delete both Jack and the remaining Address associatedwith the user:

>>> session.delete(jack)
 
sql>>> session.query(User).filter_by(name='jack').count()
DELETE FROM addresses WHERE addresses.id = ?
(1,)
DELETE FROM users WHERE users.id = ?
(5,)
SELECT count(*) AS count_1
FROM (SELECT users.id AS users_id,
                users.name AS users_name,
                users.fullname AS users_fullname,
                users.nickname AS users_nickname
FROM users
WHERE users.name = ?) AS anon_1
('jack',)
0
 
sql>>> session.query(Address).filter(
...    Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
... ).count()
SELECT count(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
                addresses.email_address AS addresses_email_address,
                addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')
0

More on Cascades

Further detail on configuration of cascades is at Cascades.The cascade functionality can also integrate smoothly withthe ON DELETE CASCADE functionality of the relational database.See Using Passive Deletes for details.

Building a Many To Many Relationship

We’re moving into the bonus round here, but lets show off a many-to-manyrelationship. We’ll sneak in some other features too, just to take a tour.We’ll make our application a blog application, where users can writeBlogPost items, which have Keyword items associated with them.

For a plain many-to-many, we need to create an un-mapped Table constructto serve as the association table. This looks like the following:

>>> from sqlalchemy import Table, Text
>>> # association table
>>> post_keywords = Table('post_keywords', Base.metadata,
...     Column('post_id', ForeignKey('posts.id'), primary_key=True),
...     Column('keyword_id', ForeignKey('keywords.id'), primary_key=True)
... )

Above, we can see declaring a Table directly is a little differentthan declaring a mapped class. Table is a constructor function, soeach individual Column argument is separated by a comma. TheColumn object is also given its name explicitly, rather than it beingtaken from an assigned attribute name.

Next we define BlogPost and Keyword, using complementaryrelationship() constructs, each referring to the post_keywordstable as an association table:

>>> class BlogPost(Base):
...     __tablename__ = 'posts'
...
...     id = Column(Integer, primary_key=True)
...     user_id = Column(Integer, ForeignKey('users.id'))
...     headline = Column(String(255), nullable=False)
...     body = Column(Text)
...
...     # many to many BlogPost<->Keyword
...     keywords = relationship('Keyword',
...                             secondary=post_keywords,
...                             back_populates='posts')
...
...     def __init__(self, headline, body, author):
...         self.author = author
...         self.headline = headline
...         self.body = body
...
...     def __repr__(self):
...         return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)
 
 
>>> class Keyword(Base):
...     __tablename__ = 'keywords'
...
...     id = Column(Integer, primary_key=True)
...     keyword = Column(String(50), nullable=False, unique=True)
...     posts = relationship('BlogPost',
...                          secondary=post_keywords,
...                          back_populates='keywords')
...
...     def __init__(self, keyword):
...         self.keyword = keyword