SQLAlchemy 0.5 Documentation

Version Check

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

>>> import sqlalchemy
>>> sqlalchemy.__version__ 
0.5.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 whats being generated.

Define and Create a Table

Next we want to tell SQLAlchemy about our tables. We will start with just a single table called users, which will store records for the end-users using our application (lets assume it's a website). We define our tables within a catalog called MetaData, using the Table construct, which is used in a manner similar to SQL's CREATE TABLE syntax:

>>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey    
>>> metadata = MetaData()
>>> users_table = Table('users', metadata,
...     Column('id', Integer, primary_key=True),
...     Column('name', String),
...     Column('fullname', String),
...     Column('password', String)
... )

All about how to define Table objects, as well as how to load their definition from an existing database (known as reflection), is described in Database Meta Data.

Next, we can issue CREATE TABLE statements derived from our table metadata, by calling create_all() and passing it the engine instance which points to our database. This will check for the presence of a table first before creating, so it's safe to call multiple times:

sql>>> metadata.create_all(engine) 

Users familiar with the syntax of CREATE TABLE may notice that the VARCHAR columns were generated without a length; on SQLite, this is a valid datatype, but on most databases it's not allowed. So if running this tutorial on a database such as Postgres or MySQL, and you wish to use SQLAlchemy to generate the tables, a "length" may be provided to the String type as below:

Column('name', 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.

Define a Python Class to be Mapped

While the Table object defines information about our database, it does not say anything about the definition or behavior of the business objects used by our application; SQLAlchemy views this as a separate concern. To correspond to our users table, let's create a rudimentary User class. It only need subclass Python's built-in object class (i.e. it's a new style class):

>>> class User(object):
...     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 class has an __init__() and a __repr__() method for convenience. These methods are both entirely optional, and can be of any form. SQLAlchemy never calls __init__() directly.

Setting up the Mapping

With our users_table and User class, we now want to map the two together. That's where the SQLAlchemy ORM package comes in. We'll use the mapper function to create a mapping between users_table and User:

>>> from sqlalchemy.orm import mapper
>>> mapper(User, users_table) 
<Mapper at 0x...; User>

The mapper() function creates a new Mapper object and stores it away for future reference, associated with our class. 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 due to the id column present within the users_table object. By default, the mapper creates class attributes for all columns present within the Table. These class attributes exist as Python descriptors, and define instrumentation for the mapped class. The functionality of this instrumentation is very rich and includes the ability to track modifications and 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.

Creating Table, Class and Mapper All at Once Declaratively

The preceding approach to configuration involving a Table, user-defined class, and mapper() call illustrate classical SQLAlchemy usage, which values the highest separation of concerns possible. A large number of applications don't require this degree of separation, and for those SQLAlchemy offers an alternate "shorthand" configurational style called declarative. For many applications, this is the only style of configuration needed. Our above example using this style is as follows:

>>> from sqlalchemy.ext.declarative import declarative_base

>>> Base = declarative_base()
>>> 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)

Above, the declarative_base() function defines a new class which we name Base, from which all of our ORM-enabled classes will derive. Note that we define Column objects with no "name" field, since it's inferred from the given attribute name.

The underlying Table object created by our declarative_base() version of User is accessible via the __table__ attribute:

>>> users_table = User.__table__

and the owning MetaData object is available as well:

>>> metadata = Base.metadata

Yet another "declarative" method is available for SQLAlchemy as a third party library called Elixir. This is a full-featured configurational product which also includes many higher level mapping configurations built in. Like declarative, once classes and mappings are defined, ORM usage is the same as with a classical SQLAlchemy configuration.

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

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, the instance is pending; no SQL has yet been issued. 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 afterwards.

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, Ed has already decided his password 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 the chapter on Sessions.

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 is created using the query() function 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, 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 using the label() construct for scalar attributes and aliased() for class constructs:

>>> from sqlalchemy.orm import aliased
>>> user_alias = aliased(User, name='user_alias')
sql>>> for row in session.query(user_alias, user_alias.name.label('name_label')).all():
...    print row.user_alias, row.name_label

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

>>> 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')>]

sql>>> query.first()

<User('ed','Ed Jones', 'f8s7ccs')>

sql>>> try:  
...     user = query.one() 
... except Exception, e: 
...     print e

Multiple rows were found for one()

sql>>> try:
...     user = query.filter(User.id == 99).one() 
... except Exception, e: 
...     print e

No row was found for one()

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

ed
wendy
mary
fred

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

<User('fred','Fred Flinstone', 'blah')>

sql>>> session.query(User).from_statement("SELECT * FROM users where name=:name").params(name='ed').all()

[<User('ed','Ed Jones', 'f8s7ccs')>]

Building a Relation

Now let's consider a second table to be dealt with. Users in our system also can store any number of email addresses associated with their username. This implies a basic one to many association from the users_table 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 relation, 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 = relation(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 a foreign key constraint which references the users table. This defines for SQLAlchemy the relationship between the two tables at the database level. The relationship between the User and Address classes is defined separately using the relation() function, which defines an attribute user to be placed on the Address class, as well as an addresses collection to be placed on the User class. Such a relation is known as a bidirectional relationship. Because of the placement of the foreign key, from Address to User it is many to one, and from User to Address it is one to many. SQLAlchemy is automatically aware of many-to-one/one-to-many based on foreign keys.

The relation() function is extremely flexible, and could just have easily been defined on the User class:

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

We are also free to not define a backref, and to define the relation() only on one class and not the other. It is also possible to define two separate relation()s for either direction, which is generally safe for many-to-one and one-to-many relations, but not for many-to-many relations.

When using the declarative extension, relation() gives us the option to use strings for most arguments that concern the target class, in the case that the target class has not yet been defined. This only works in conjunction with declarative:

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

When declarative is not in use, you typically define your mapper() well after the target classes and Table objects have been defined, so string expressions are not needed.

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>>> metadata.create_all(engine) 

Working with Related Objects

Now when we create a User, a blank addresses collection will be present. By default, the collection is a Python list. Other collection types, such as sets and dictionaries, are available as well:

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

We are free to add Address objects on our User object. In this case we just 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 direction automatically become visible in the other direction. This is the basic behavior of the backref keyword, which maintains the relationship purely in memory, without using any SQL:

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

>>> jack.addresses[1].user
<User('jack','Jack Bean', 'gjffdd')>

Let's add and commit Jack Bean to the database. jack as well as the two Address members in his addresses collection are both added to the session at once, using a process known as cascading:

>>> session.add(jack)
sql>>> session.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()

>>> jack
<User('jack','Jack Bean', 'gjffdd')>

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

sql>>> jack.addresses

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

When we accessed the addresses collection, SQL was suddenly issued. This is an example of a lazy loading relation. The addresses collection is now loaded and behaves just like an ordinary list.

If you want to reduce the number of queries (dramatically, in many cases), we can apply an eager load to the query operation. With the same query, we may apply an option to the query, indicating that we'd like addresses to load "eagerly". SQLAlchemy then constructs an outer join between the users and addresses tables, and loads them at once, populating the addresses collection on each User object if it's not already populated:

>>> from sqlalchemy.orm import eagerload

sql>>> jack = session.query(User).options(eagerload('addresses')).filter_by(name='jack').one() 

>>> jack
<User('jack','Jack Bean', 'gjffdd')>

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

SQLAlchemy has the ability to control exactly which attributes and how many levels deep should be joined together in a single SQL query. More information on this feature is available in Relation Configuration.

Querying with Joins

While the eager load created a JOIN specifically to populate a collection, we can also work explicitly with joins in many ways. For example, to construct a simple inner join between User and Address, we can just filter() 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')>

Or we can make a real JOIN construct; one way to do so is to use the ORM join() function, and tell Query to "select from" this join:

>>> from sqlalchemy.orm import join
sql>>> session.query(User).select_from(join(User, Address)).\
...         filter(Address.email_address=='jack@google.com').all()

[<User('jack','Jack Bean', 'gjffdd')>]

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, join() would require a third argument indicating the ON clause of the join, in one of the following forms:

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

The functionality of join() is also available generatively from Query itself using Query.join. This is most easily used with just the "ON" clause portion of the join, such as:

sql>>> session.query(User).join(User.addresses).\
...     filter(Address.email_address=='jack@google.com').all()

[<User('jack','Jack Bean', 'gjffdd')>]

To explicitly specify the target of the join, use tuples to form an argument list similar to the standalone join. This becomes more important when using aliases and similar constructs:

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

Multiple joins can be created by passing a list of arguments:

session.query(Foo).join(Foo.bars, Bar.bats, (Bat, 'widgets'))

The above would produce SQL something like foo JOIN bars ON <onclause> JOIN bats ON <onclause> JOIN widgets ON <onclause>.

>>> 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), (adalias2, User.addresses)).\
...     filter(adalias1