Most of this is pretty self-explanatory. The relation() function is where all the magic happens. As you can see from the above example, it creates a property on the data class which will act like a list or a single object, as appropriate. When you're in the User class, reading the emails property, you're looking from the "one" side to the "many" side of the one-to-many relationship, so the property acts like a list. When you're in the Email class and reading the user property, you're looking from the "many" side to the "one" side of the relationship, so the property acts like a reference to a single object. The property has both getter and setter methods, so you can run code like "em1.user = harry" and the correct things will happen.

A quick note on the get_by() and select_by() functions we're using above. These are shorthand functions that take keyword arguments referring to the columns in the table we're selecting from. This lets you write "get_by(name='Mary')" instead of "selectfirst(users.c.name=='Mary')". If you specify multiple keyword arguments, they'll be joined together with AND. However, keep in mind a pretty severe limitation of the get_by() and select_by() functions, that comes directly from their use of Python keyword arguments. Keyword arguments are always in the form "name=value", which means that if you want to do anything other than an equality comparison, you need to use the full column name. E.g., "get_by(age<=39)" won't work; you need to do "selectfirst(users.c.name <= 39)" instead.

One final note: notice that in the penultimate example, when we created a new user Harry with two email addresses, we didn't call em1.save() or em2.save(). And yet both Email instances were also saved. This demonstrates how SQLAlchemy tracks object dependencies for you. The newly-created User instance held two references to Email instances in its emails property; therefore, the Emailinstances were dependent on their "parent" object (the User instance) and needed to be written out to the database at the same time. Notice also that the User object was created first, then the two dependent Email objects. That's because they needed to know the userid for Harry's newly-created database row, in order to set their own userid values properly. SQLAlchemy is very smart about dependency tracking, and will generally figure out the right order in which to do things. And if you have a very complicated example that SQLAlchemy can't figure out correctly, the author of SQLAlchemy considers that a bug and asks that you report it on the SQLAlchemy mailing list.

New in 0.2: As in the selectdemo.py example, we call select() functions on the Session object instead of on the mapper, via session.query(DataClass). That means that we often don't need to keep a reference to the mapper around, and makes it possible to simply throw away the results of the mapper() function call. It also means that the assign_mapper() function is less useful, soassign_mapper() isn't covered in this version of the tutorial.

Data mapping, part three: many-to-many relationships

There's one more item to cover. We've looked at one-to-many relationships, but we also need to look at many-to-many relationships. As you probably know already, many-to-many relationships in databases are handled by a third table that holds information about the relation. E.g., if you have an "articles" table and a "keywords" table, and you want to be able to associate keywords with articles, you'd need a many-to-many relationship. One-to-many wouldn't work, because one article might need to be tagged with several different keywords, and the same keyword might be used to tag several articles; so this is a classic many-to-many relationship. Thus, you'd use a third table with just two columns, "article_id" and "keyword_id", to keep track of the associations. By convention, such a table is usually named with the names of the two tables it references, separated by an underscore. Thus, the table structure in the following example (call it "manytomany.py"):

from sqlalchemy import *
db = create_engine('sqlite:///keywords.db')
db.echo = True
metadata = BoundMetaData(db)
session = create_session()

articles = Table('articles', metadata,
    Column('article_id', Integer, primary_key = True),
    Column('headline', String(150)),
    Column('body', String),
)

keywords = Table('keywords', metadata,
    Column('keyword_id', Integer, primary_key = True),
    Column('keyword_name', String(50)),
)

association = Table('articles_keywords', metadata,
    Column('keyword_id', Integer, ForeignKey('articles.article_id')),
    Column('article_id', Integer, ForeignKey('keywords.keyword_id')),
)

# Handy feature: create all the tables with one function call
metadata.create_all()

class Article(object):
    def __init__(self, headline=None, body=None):
        self.headline = headline
        self.body = body
    def __repr__(self):
        return 'Article %d: "%s"' % (self.article_id, self.headline)

class Keyword(object):
    def __init__(self, name=None):
        self.keyword_name = name
    def __repr__(self):
        return self.keyword_name

# To create a many-to-many relation, specify the association table as
# the "secondary" keyword parameter to mapper()
mapper(Article, articles)
mapper(Keyword, keywords, properties = {
    'articles': relation(Article, secondary=association, backref='keywords'),
})

a1 = Article(headline="Python is cool!", body="(to be written)")
a2 = Article(headline="SQLAlchemy Tutorial", body="You're reading it")
session.save(a1)
session.save(a2)

k_tutorial = Keyword('tutorial')
k_cool = Keyword('cool')
k_unfinished = Keyword('unfinished')

a1.keywords.append(k_unfinished)
k_cool.articles.append(a1)
k_cool.articles.append(a2)
# Or:
k_cool.articles = [a1, a2]  # This works as well!
a2.keywords.append(k_tutorial)

# Now we write all this out to the database in one single step, and
# SQLAlchemy automatically figures out the correct order for the SQL
# statements. Notice also how we didn't need to save the Keyword
# instances, because a dependency relationship was set up when we
# associated them with their articles just now.
session.flush()

print a1, a1.keywords
print a2, a2.keywords
print k_tutorial, k_tutorial.articles
print k_cool, k_cool.articles
print k_unfinished, k_unfinished.articles

Again, the code pretty much speaks for itself. If you pass the association table as the second parameter to mapping(), and SQLAlchemy sees that it's in the correct format (it has only two columns, each of which is a foreign key to one of the tables involved in the relation), it will automatically set up a many-to-many relationship for you.

One thing you may be interested in is the handy metadata.create_all() method. It will automatically figure out the dependency relationships between your tables, and create them in the proper order. It will also detect if the tables have already been created, and not try to re-create them a second time.

If you want to hold more data in the association object (for example, maybe you want to record the exact date and time when the article was tagged with any given keyword), it's slightly more complicated. The SQLAlchemy documentation has a good explanation of the process at the bottom of the Data Mapping page. That whole page, in fact, is well worth reading, since there are several other features of data mapping that I glossed over or left out entirely, in order to keep the size of this tutorial manageable. And once you've grasped those concepts, you can move on to the Advanced Data Mapping section, which covers subjects like mapping a table onto itself (useful for tracking manager/subordinate relationships in an Employees table, for example).

New in 0.2: The create_all() function is new. Notice also that you need to explicitly save() the newly-created Article objects so that session.flush() will flush them to the database.

Conclusion

Hopefully this has given you a taste of what using SQLAlchemy feels like. The examples in this tutorial should be enough to give you a head start in writing your own applications. When you need more advanced features beyond what we've covered here, check out the extensive SQLAlchemy documentation. Just about everything you'll need is there; and if you still don't find what you're looking for, join the SQLAlchemy mailing list and ask!