Release: 0.8.6 | Release Date: March 28, 2014

SQLAlchemy 0.8 Documentation

Object Relational Tutorial

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

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

While there is overlap among the usage patterns of the ORM and the Expression Language, the similarities are more superficial than they may at first appear. One approaches the structure and content of data from the perspective of a user-defined domain model which is transparently persisted and refreshed from its underlying storage model. The other approaches it from the perspective of literal schema and SQL expression representations which are explicitly composed into messages consumed individually by the database.

A successful application may be constructed using the Object Relational Mapper exclusively. In advanced situations, an application constructed with the ORM may make occasional usage of the Expression Language directly in certain areas where specific database interactions are required.

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

Version Check

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

>>> import sqlalchemy
>>> sqlalchemy.__version__ 
0.8.0

Connecting

For this tutorial we will use an in-memory-only SQLite database. To connect we use 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 is accomplished via Python’s standard logging module. With it enabled, we’ll see all the generated SQL produced. If you are working through this tutorial and want less output generated, set it to False. This tutorial will format the 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 of Engine, and it represents the core interface to the database, adapted through a dialect that handles the details of the database and DBAPI in use. In this case the SQLite dialect will interpret instructions to the Python built-in sqlite3 module.

The Engine has not actually tried to connect to the database yet; that happens only the first time it is asked to perform a task against the database. We can illustrate this by asking it to perform a simple SELECT statement:

sql>>> engine.execute("select 1").scalar()
1

As the Engine.execute() method is called, the Engine establishes a connection to the SQLite database, which is then used to emit the SQL. The connection is then returned to an internal connection pool where it will be reused on subsequent statement executions. While we illustrate direct usage of the Engine here, this isn’t typically necessary when using the ORM, where the Engine, once created, is used behind 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 database tables we’ll be dealing with, and then by defining our own classes which will be mapped to those tables. In modern SQLAlchemy, these two tasks are usually performed together, using a system known as Declarative, which allows us to create classes that include directives to describe the actual database table they will be mapped to.

Classes mapped using the Declarative system are defined in terms of a base class which maintains a catalog of classes and tables relative to that base - this is known as the declarative base class. Our application will usually have just one instance of this base in a commonly imported 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 terms of it. We will start with just a single table called users, which will store records for the end-users using our application. A new class called User will be the class to which we map this table. The imports we’ll need to accomplish this include objects that represent the components of our table, including the Column class which represents a database column, as well as the Integer and String classes that represent basic datatypes used in columns:

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

The above User class establishes details about the table being mapped, including the name of the table denoted by the __tablename__ attribute, a set of columns id, name, fullname and password, where the id column will also be the primary key of the table. While its certainly possible that some database tables don’t have primary key columns (as is also the case with views, which can also be mapped), the ORM in order to actually map to a particular table needs there to be at least one column denoted as a primary key column; multiple-column, i.e. composite, primary keys are of course entirely feasible as well.

We define a constructor via __init__() and also a __repr__() method - both are optional. The class of course can have any number of other methods and attributes as required by the application, as it’s basically just a plain Python class. Inheriting from Base is also only a requirement of the declarative configurational system, which itself is optional and relatively open ended; at its core, the SQLAlchemy ORM only requires that a class be a so-called “new style class”, that is, it inherits from object in Python 2, in order to be mapped. All classes in Python 3 are “new style” classes.

The Non Opinionated Philosophy

In our User mapping example, it was required that we identify the name of the table in use, as well as the names and characteristics of all columns which we care about, including which column or columns represent the primary key, as well as some basic information about the types in use. SQLAlchemy never makes assumptions about these decisions - the developer must always be explicit about specific conventions in use. However, that doesn’t mean the task can’t be automated. While this tutorial will keep things explicit, developers are encouraged to make use of helper functions as well as “Declarative Mixins” to automate their tasks in large scale applications. The section Mixin and Custom Base Classes introduces many of these techniques.

With our User class constructed via the Declarative system, we have defined information about our table, known as table metadata, as well as a user-defined class which is linked to this table, known as a mapped class. Declarative has provided for us a shorthand system for what in SQLAlchemy is called a “Classical Mapping”, which specifies these two units separately and is discussed in Classical Mappings. The table is actually represented by a datastructure known as Table, and the mapping represented by a Mapper object generated by a function called mapper(). Declarative performs both of these steps for us, making available the Table it has created via the __table__ attribute:

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

and while rarely needed, making available the Mapper object via the __mapper__ attribute:

>>> User.__mapper__ 
<Mapper at 0x...; User>

The Declarative base class also contains a catalog of all the Table objects that have been defined called MetaData, available via the .metadata attribute. In this example, we are defining new tables that have yet to be created in our SQLite database, so one helpful feature the MetaData object offers is the ability to issue CREATE TABLE statements to the database for all tables that don’t yet exist. We illustrate this by calling the MetaData.create_all() method, passing in our Engine as a source of database connectivity. We will see that special commands are first emitted to check for the presence of the users table, and following that the actual CREATE TABLE statement:

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

Minimal Table Descriptions vs. Full Descriptions

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

Column(String(50))

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

Additionally, Firebird and Oracle require sequences to generate new primary key identifiers, and SQLAlchemy doesn’t generate or assume these without 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 declarative mapping 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))
    password = Column(String(12))

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

We include this more verbose table definition separately to highlight the difference between a minimal construct geared primarily towards in-Python usage only, versus one that will be used to emit CREATE TABLE statements on a particular set of backends with more stringent requirements.

Create an Instance of the Mapped Class

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

>>> ed_user = User('ed', 'Ed Jones', 'edspassword')
>>> ed_user.name
'ed'
>>> ed_user.password
'edspassword'
>>> str(ed_user.id)
'None'

The id attribute, which while not defined by our __init__() method, exists with a value of None on our User instance due to the id column we declared in our mapping. By default, the ORM creates class attributes for all columns present in the table being mapped. These class attributes exist as descriptors, and define instrumentation for the mapped class. The functionality of this instrumentation includes the ability to fire on change events, track modifications, and to automatically load new data from the database when needed.

Since we have not yet told SQLAlchemy to persist Ed Jones within the database, its id is None. When we persist the object later, this attribute will be populated with a newly generated value.

The default __init__() method

Note that in our User example we supplied an __init__() method, which receives name, fullname and password as positional arguments. The Declarative system supplies for us a default constructor if one is not already present, which accepts keyword arguments of the same name as that of the mapped attributes. Below we define User without specifying a constructor:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

Our User class above will make usage of the default constructor, and provide id, name, fullname, and password as keyword arguments:

u1 = User(name='ed', fullname='Ed Jones', password='foobar')

Creating a Session

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

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

In the case where your application does not yet have an Engine when you define your module-level objects, just set it up like this:

>>> Session = sessionmaker()

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

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

This custom-made Session class will create new Session objects which are bound to our database. Other transactional characteristics may be defined when calling sessionmaker() as well; these are described in a later chapter. Then, whenever you need to have a conversation with the database, you instantiate a Session:

>>> session = Session()

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

Session Creational Patterns

The business of acquiring a Session has a good deal of variety based on the variety of types of applications and frameworks out there. Keep in mind the Session is just a workspace for your objects, local to a particular database connection - if you think of an application thread as a guest at a dinner party, the Session is the guest’s plate and the objects it holds are the food (and the database...the kitchen?)! Hints on how Session is integrated into an application are at Session Frequently Asked Questions.

Adding New Objects

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

>>> ed_user = User('ed', 'Ed Jones', 'edspassword')
>>> session.add(ed_user)

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

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

sql>>> our_user = session.query(User).filter_by(name='ed').first() 
>>> our_user
<User('ed','Ed Jones', 'edspassword')>

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

>>> ed_user is our_user
True

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

We can add more User objects at once using add_all():

>>> session.add_all([
...     User('wendy', 'Wendy Williams', 'foobar'),
...     User('mary', 'Mary Contrary', 'xxg527'),
...     User('fred', 'Fred Flinstone', 'blah')])

Also, we’ve decided the password for Ed isn’t too secure, so lets change it:

>>> ed_user.password = 'f8s7ccs'

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

>>> session.dirty
IdentitySet([<User('ed','Ed Jones', 'f8s7ccs')>])

and that three new User objects are pending:

>>> session.new  
IdentitySet([<User('wendy','Wendy Williams', 'foobar')>,
<User('mary','Mary Contrary', 'xxg527')>,
<User('fred','Fred Flinstone', 'blah')>])

We tell the Session that we’d like to issue all remaining changes to the database and commit the transaction, which has been in progress throughout. We do this via commit():

sql>>> session.commit()

commit() flushes whatever remaining changes remain to the database, and commits the transaction. The connection resources referenced by the session are now returned to the connection pool. Subsequent operations with this session will occur in a new transaction, which will again re-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 
1

After the Session inserts new rows in the database, all newly generated identifiers and database-generated defaults become available on the instance, either immediately or via load-on-first-access. In this case, the entire row was re-loaded on access because a new transaction was begun after we issued commit(). SQLAlchemy by default refreshes data from a previous transaction the first time it’s accessed 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, to inside the Session without a primary key, to actually being inserted, it moved between three out of four available “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('fakeuser', 'Invalid', '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() 
[<User('Edwardo','Ed Jones', 'f8s7ccs')>, <User('fakeuser','Invalid', '12345')>]

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

sql>>> session.rollback()

sql>>> ed_user.name 
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() 
[<User('ed','Ed Jones', 'f8s7ccs')>]

Querying

A Query object is created using the query() method on Session. This function takes a variable number of arguments, which can be any combination of classes and class-instrumented descriptors. Below, we indicate a Query which loads User instances. When evaluated in an iterative context, the list of User objects present is returned:

sql>>> for instance in session.query(User).order_by(User.id): 
...     print instance.name, instance.fullname
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flinstone

The Query also accepts ORM-instrumented descriptors as arguments. Any time multiple class entities or column-based entities are expressed as arguments to the query() function, the return result is expressed as tuples:

sql>>> for name, fullname in session.query(User.name, User.fullname): 
...     print name, fullname
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flinstone

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

sql>>> for row in session.query(User, User.name).all(): 
...    print row.User, row.name
<User('ed','Ed Jones', 'f8s7ccs')> ed
<User('wendy','Wendy Williams', 'foobar')> wendy
<User('mary','Mary Contrary', 'xxg527')> mary
<User('fred','Fred Flinstone', 'blah')> fred

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

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

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

>>> 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
<User('ed','Ed Jones', 'f8s7ccs')>
<User('wendy','Wendy Williams', 'foobar')>
<User('mary','Mary Contrary', 'xxg527')>
<User('fred','Fred Flinstone', 'blah')>

Basic operations with Query include issuing LIMIT and OFFSET, most conveniently using Python array slices and typically in conjunction with ORDER BY:

sql>>> for u in session.query(User).order_by(User.id)[1:3]: 
...    print u
<User('wendy','Wendy Williams', 'foobar')>
<User('mary','Mary Contrary', 'xxg527')>

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

sql>>> for name, in session.query(User.name).\
...             filter_by(fullname='Ed Jones'): 
...    print name
ed

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

sql>>> for name, in session.query(User.name).\
...             filter(User.fullname=='Ed Jones'): 
...    print name
ed

The Query object is fully generative, meaning that most method calls return a new Query object upon which further criteria may be added. For example, to query for users named “ed” with a full name of “Ed Jones”, you can call filter() twice, which joins criteria using AND:

sql>>> for user in session.query(User).\
...          filter(User.name=='ed').\
...          filter(User.fullname=='Ed Jones'): 
...    print user
<User('ed','Ed Jones', 'f8s7ccs')>

Common Filter Operators

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

  • equals:

    query.filter(User.name == 'ed')
  • not equals:

    query.filter(User.name != 'ed')
  • LIKE:

    query.filter(User.name.like('%ed%'))
  • 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%'))))
  • NOT IN:

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

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

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

    from sqlalchemy import and_
    filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
    
    # or call filter()/filter_by() multiple times
    filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
  • OR:

    from sqlalchemy import or_
    filter(or_(User.name == 'ed', User.name == 'wendy'))
  • MATCH:

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

Note

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

Returning Lists and Scalars

The all(), one(), and first() methods of Query immediately issue SQL and return a non-iterator value. all() returns a list:

>>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)
sql>>> query.all() 
[<User('ed','Ed Jones', 'f8s7ccs')>, <User('fred','Fred Flinstone', 'blah')>]

first() applies a limit of one and returns the first result as a scalar:

sql>>> query.first() 
<User('ed','Ed Jones', 'f8s7ccs')>

one(), fully fetches all rows, and if not exactly one object identity or composite row is present in the result, raises an error:

sql>>> from sqlalchemy.orm.exc import MultipleResultsFound
>>> try: 
...     user = query.one()
... except MultipleResultsFound, e:
...     print e
Multiple rows were found for one()
sql>>> from sqlalchemy.orm.exc import NoResultFound
>>> try: 
...     user = query.filter(User.id == 99).one()
... except NoResultFound, e:
...     print e
No row was found for one()

Using Literal SQL

Literal strings can be used flexibly with Query. Most methods accept strings in addition to SQLAlchemy clause constructs. For example, filter() and order_by():

sql>>> for user in session.query(User).\
...             filter("id<224").\
...             order_by("id").all(): 
...     print user.name
ed
wendy
mary
fred

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

sql>>> session.query(User).filter("id<:value and name=:name").\
...     params(value=224, name='fred').order_by(User.id).one() 

To use an entirely string-based statement, using from_statement(); just ensure that the columns clause of the statement contains the column names normally used by the mapper (below illustrated using an asterisk):

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

You can use from_statement() to go completely “raw”, using string names to identify desired columns:

sql>>> session.query("id", "name", "thenumber12").\
...         from_statement("SELECT id, name, 12 as "
...                 "thenumber12 FROM users where name=:name").\
...                 params(name='ed').all()
[(1, u'ed', 12)]

Pros and Cons of Literal SQL

Query is constructed like the rest of SQLAlchemy, in that it tries to always allow “falling back” to a less automated, lower level approach to things. Accepting strings for all SQL fragments is a big part of that, so that you can bypass the need to organize SQL constructs if you know specifically what string output you’d like. But when using literal strings, the Query no longer knows anything about that part of the SQL construct being emitted, and has no ability to transform it to adapt to new contexts.

For example, suppose we selected User objects and ordered by the name column, using a string to indicate name:

>>> q = session.query(User.id, User.name)
sql>>> q.order_by("name").all()
[(1, u'ed'), (4, u'fred'), (3, u'mary'), (2, u'wendy')]

Perfectly fine. But suppose, before we got a hold of the Query, some sophisticated transformations were applied to it, such as below where we use from_self(), a particularly advanced method, to retrieve pairs of user names with different numbers of characters:

>>> from sqlalchemy import func
>>> ua = aliased(User)
>>> q = q.from_self(User.id, User.name, ua.name).\
...     filter(User.name < ua.name).\
...     filter(func.length(ua.name) != func.length(User.name))

The Query now represents a select from a subquery, where User is represented twice both inside and outside of the subquery. Telling the Query to order by “name” doesn’t really give us much guarantee which “name” it’s going to order on. In this case it assumes “name” is against the outer “aliased” User construct:

sql>>> q.order_by("name").all() 
[(1, u'ed', u'fred'), (1, u'ed', u'mary'), (1, u'ed', u'wendy'), (3, u'mary', u'wendy'), (4, u'fred', u'wendy')]

Only if we use the SQL element directly, in this case User.name or ua.name, do we give Query enough information to know for sure which “name” we’d like to order on, where we can see we get different results for each:

sql>>> q.order_by(ua.name).all() 
[(1, u'ed', u'fred'), (1, u'ed', u'mary'), (1, u'ed', u'wendy'), (3, u'mary', u'wendy'), (4, u'fred', u'wendy')]

sql>>> q.order_by(User.name).all() 
[(1, u'ed', u'wendy'), (1, u'ed', u'mary'), (1, u'ed', u'fred'), (4, u'fred', u'wendy'), (3, u'mary', u'wendy')]

Counting

Query includes a convenience method for counting called count():

sql>>> session.query(User).filter(User.name.like('%ed')).count() 
2

The count() method is used to determine how many rows the SQL statement would return. Looking at the generated SQL above, SQLAlchemy always places whatever it is we are querying into a subquery, then counts the rows from that. In some cases this can be reduced to a simpler SELECT count(*) FROM table, however modern 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” needs to be indicated specifically, we can specify the “count” function directly using the expression func.count(), available from the func construct. Below we use 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()  
[(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()
4

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

sql>>> session.query(func.count(User.id)).scalar() 
4

Building a Relationship

Let’s consider how a second table, related to User, can be mapped and queried. Users in our system can store any number of email addresses associated with their username. This implies a basic one to many association from the users to a new table which stores email addresses, which we will call addresses. Using declarative, we define this table along with its mapped class, Address:

>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship, backref

>>> 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", backref=backref('addresses', order_by=id))
...
...     def __init__(self, email_address):
...         self.email_address = email_address
...
...     def __repr__(self):
...         return "<Address('%s')>" % self.email_address

The above class introduces the ForeignKey construct, which is a directive applied to Column that indicates that values in this column should be constrained to be values present in the named remote column. This is a core feature of relational databases, and is the “glue” that transforms an otherwise unconnected collection of tables to have rich overlapping relationships. The ForeignKey above expresses that values in the addresses.user_id column should be constrained to those 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 linked to the User class, using the attribute Address.user. relationship() uses the foreign key relationships between the two tables to determine the nature of this linkage, determining that Address.user will be many-to-one. A subdirective of relationship() called backref() is placed inside of relationship(), providing details about the relationship as expressed in reverse, that of a collection of Address objects on User referenced by User.addresses. The reverse side of a many-to-one relationship is always one-to-many. A full catalog of available relationship() configurations is at Basic Relational Patterns.

The two complementing relationships Address.user and User.addresses are referred to as a bidirectional relationship, and is a key feature of the SQLAlchemy ORM. The section Linking Relationships with Backref discusses the “backref” feature in detail.

Arguments to relationship() which concern the remote class can be specified using strings, assuming the Declarative system is in use. Once all mappings are complete, these strings are evaluated as Python expressions in order to produce the actual argument, in the above case the User class. The names which are allowed during this evaluation include, among other things, the names of all classes which have been created in terms of the declared base. Below we illustrate creation of the same “addresses/user” bidirectional relationship in terms of User instead of Address:

class User(Base):
    # ....
    addresses = relationship("Address", order_by="Address.id", backref="user")

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 can only 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 itself has multiple columns, is known as a “composite foreign key”. It can also reference a subset of those columns.
  • FOREIGN KEY columns can automatically update themselves, in response to a change in 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 issue another CREATE from our metadata, which will skip over tables which have already been created:

sql>>> Base.metadata.create_all(engine) 

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 to join 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, a
<User('jack','Jack Bean', 'gjffdd')> <Address('jack@google.com')>

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

sql>>> session.query(User).join(Address).\
...         filter(Address.email_address=='jack@google.com').\
...         all() 
[<User('jack','Jack Bean', 'gjffdd')>]

Query.join() knows how to join between User and Address because there’s only one foreign key between them. If there were 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 the outerjoin() function:

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

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

Using Aliases

When querying across multiple tables, if the same table needs to be referenced more than once, SQL typically requires that the table be aliased with another name, so that it can be distinguished against other occurrences of that table. The Query supports this most explicitly using the aliased construct. Below we join to the Address entity twice, to locate a user who has two distinct email addresses at the same 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      
jack jack@google.com j25@yahoo.com

Using Subqueries

The Query is suitable for generating statements which can be used as subqueries. Suppose we wanted to load User objects along with a count of how many Address records each user has. The best way to generate SQL like this is to get the count of addresses grouped by user ids, and JOIN to the parent. In this case we use a LEFT OUTER JOIN so that we get 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 this from the inside out. The statement accessor returns a SQL expression representing the statement generated by a particular Query - 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 on Query produces a SQL expression construct representing a SELECT statement embedded within an alias (it’s actually shorthand for query.statement.alias()).

Once we have our statement, it behaves like a Table construct, such as the one we created for users at the start of this tutorial. The columns on the statement are accessible 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
<User('ed','Ed Jones', 'f8s7ccs')> None
<User('wendy','Wendy Williams', 'foobar')> None
<User('mary','Mary Contrary', 'xxg527')> None
<User('fred','Fred Flinstone', 'blah')> None
<User('jack','Jack Bean', 'gjffdd')> 2

Selecting Entities from Subqueries

Above, we just selected a result that included a column from a subquery. What if 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, address
<User('jack','Jack Bean', 'gjffdd')> <Address('jack@google.com')>

Using EXISTS

The EXISTS keyword in SQL is a boolean operator which returns True if the given expression contains any rows. It may be used in many scenarios in place of joins, and is also useful for locating rows which do not have a corresponding 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
jack

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

sql>>> for name, in session.query(User.name).\
...         filter(User.addresses.any()):   
...     print name
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
jack

has() is the same operator as any() 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() 
[]

Common Relationship Operators

Here’s all the operators which build on relationships - each one is linked to its API documentation which includes full details on usage and 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, when we accessed the User.addresses collection of a User and SQL was emitted. If you want to reduce the number of queries (dramatically, in many cases), we can apply an eager load to the query operation. SQLAlchemy offers three types of eager loading, two of which are automatic, and a third which involves custom criterion. All three are usually invoked via functions known as query options which give additional instructions to the Query on how we would like various attributes to be loaded, via the Query.options() method.

Subquery 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 collections is the orm.subqueryload() option, which emits a second SELECT statement that fully loads the collections associated with the results just loaded. The name “subquery” originates from the fact that the SELECT statement constructed directly via the Query is re-used, embedded as a subquery into a SELECT against the related table. This is a little elaborate but very easy to use:

>>> from sqlalchemy.orm import subqueryload
sql>>> jack = session.query(User).\
...                 options(subqueryload(User.addresses)).\
...                 filter_by(name='jack').one() 
>>> jack
<User('jack','Jack Bean', 'gjffdd')>

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

Joined Load

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

>>> from sqlalchemy.orm import joinedload

sql>>> jack = session.query(User).\
...                        options(joinedload(User.addresses)).\
...                        filter_by(name='jack').one() 
>>> jack
<User('jack','Jack Bean', 'gjffdd')>

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

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

While joinedload() has been around for a long time, subqueryload() is a newer form of eager loading. subqueryload() tends to be more appropriate for loading related collections while joinedload() tends to be better suited for many-to-one relationships, due to the fact that only one row is loaded for both the lead and the related object.

joinedload() is not a replacement for join()

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

Explicit Join + Eagerload

A third style of eager loading is when we are constructing a JOIN explicitly in order to locate the primary rows, and would like to additionally apply the extra table to a related object or collection on the primary object. This feature is supplied via the orm.contains_eager() function, and is most typically useful for pre-loading the many-to-one object on a query that needs to filter on that same object. Below we illustrate loading an Address row 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.user attribute:

>>> 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() 
>>> jacks_addresses
[<Address('jack@google.com')>, <Address('j25@yahoo.com')>]

>>> jacks_addresses[0].user
<User('jack','Jack Bean', 'gjffdd')>

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

Deleting

Let’s try to delete jack and see how that goes. We’ll mark as deleted in 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() 
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() 
2

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

Configuring delete/delete-orphan Cascade

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

>>> session.close()

and use a new declarative_base():

>>> Base = declarative_base()

Next we’ll declare the User class, adding in the addresses relationship including 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)
...     password = Column(String)
...
...     addresses = relationship("Address", backref='user', cascade="all, delete, delete-orphan")
...
...     def __repr__(self):
...        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

Then we recreate Address, noting that in this case we’ve created the 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'))
...
...     def __repr__(self):
...         return "<Address('%s')>" % self.email_address

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

# load Jack by primary key
sql>>> jack = session.query(User).get(5)    

# remove one Address (lazy load fires off)
sql>>> del jack.addresses[1] 

# only one address remains
sql>>> session.query(Address).filter(
...     Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
... ).count() 
1

Deleting Jack will delete both Jack and the remaining Address associated with the user:

>>> session.delete(jack)

sql>>> session.query(User).filter_by(name='jack').count() 
0

sql>>> session.query(Address).filter(
...    Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
... ).count() 
0

More on Cascades

Further detail on configuration of cascades is at Cascades. The cascade functionality can also integrate smoothly with the 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-many relationship. We’ll sneak in some other features too, just to take a tour. We’ll make our application a blog application, where users can write BlogPost items, which have Keyword items associated with them.

For a plain many-to-many, we need to create an un-mapped Table construct to 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', Integer, ForeignKey('posts.id')),
...     Column('keyword_id', Integer, ForeignKey('keywords.id'))
... )

Above, we can see declaring a Table directly is a little different than declaring a mapped class. Table is a constructor function, so each individual Column argument is separated by a comma. The Column object is also given its name explicitly, rather than it being taken from an assigned attribute name.

Next we define BlogPost and Keyword, with a relationship() linked via the post_keywords 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, backref='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)
...
...     def __init__(self, keyword):
...         self.keyword = keyword

Above, the many-to-many relationship is BlogPost.keywords. The defining feature of a many-to-many relationship is the secondary keyword argument which references a Table object representing the association table. This table only contains columns which reference the two sides of the relationship; if it has any other columns, such as its own primary key, or foreign keys to other tables, SQLAlchemy requires a different usage pattern called the “association object”, described at Association Object.

We would also like our BlogPost class to have an author field. We will add this as another bidirectional relationship, except one issue we’ll have is that a single user might have lots of blog posts. When we access User.posts, we’d like to be able to filter results further so as not to load the entire collection. For this we use a setting accepted by relationship() called lazy='dynamic', which configures an alternate loader strategy on the attribute. To use it on the “reverse” side of a relationship(), we use the backref() function:

>>> from sqlalchemy.orm import backref
>>> # "dynamic" loading relationship to User
>>> BlogPost.author = relationship(User, backref=backref('posts', lazy='dynamic'))

Create new tables:

sql>>> Base.metadata.create_all(engine) 

Usage is not too different from what we’ve been doing. Let’s give Wendy some blog posts:

sql>>> wendy = session.query(User).\
...                 filter_by(name='wendy').\
...                 one() 
>>> post = BlogPost("Wendy's Blog Post", "This is a test", wendy)
>>> session.add(post)

We’re storing keywords uniquely in the database, but we know that we don’t have any yet, so we can just create them:

>>> post.keywords.append(Keyword('wendy'))
>>> post.keywords.append(Keyword('firstpost'))

We can now look up all blog posts with the keyword ‘firstpost’. We’ll use the any operator to locate “blog posts where any of its keywords has the keyword string ‘firstpost’”:

sql>>> session.query(BlogPost).\
...             filter(BlogPost.keywords.any(keyword='firstpost')).\
...             all() 
[BlogPost("Wendy's Blog Post", 'This is a test', <User('wendy','Wendy Williams', 'foobar')>)]

If we want to look up posts owned by the user wendy, we can tell the query to narrow down to that User object as a parent:

sql>>> session.query(BlogPost).\
...             filter(BlogPost.author==wendy).\
...             filter(BlogPost.keywords.any(keyword='firstpost')).\
...             all() 
[BlogPost("Wendy's Blog Post", 'This is a test', <User('wendy','Wendy Williams', 'foobar')>)]

Or we can use Wendy’s own posts relationship, which is a “dynamic” relationship, to query straight from there:

sql>>> wendy.posts.\
...         filter(BlogPost.keywords.any(keyword='firstpost')).\
...         all() 
[BlogPost("Wendy's Blog Post", 'This is a test', <User('wendy','Wendy Williams', 'foobar')>)]

Further Reference

Query Reference: Querying

Mapper Reference: Mapper Configuration

Relationship Reference: Relationship Configuration

Session Reference: Using the Session