That's enough for a taste. More information can be found in the SQL Construction section of the SQLAlchemy documentation. Now let's move on to the really interesting part: mapping your data objects to SQL database rows.

Mapping your objects to SQL rows

Now for the really interesting part: mapping your objects onto the database.

In other object-relational mappers such as SQLObject, the table definition also doubles as the class whose instances are rows of data from the table. SQLAlchemy, on the other hand, makes a strict distinction between the table definition and the data class. You first create the table definition, then create an (empty) class definition that will hold your data objects, and then create a mapper that will map that class onto the database. It's perhaps easier to show how this works than to explain it. Copy the following into "mapper1.py":

from sqlalchemy import *

db = create_engine('sqlite:///joindemo.db')

db.echo = True

metadata = BoundMetaData(db)

users = Table('users', metadata, autoload=True)
emails = Table('emails', metadata, autoload=True)

# These are the empty classes that will become our data classes
class User(object):
    pass
class Email(object):
    pass

usermapper = mapper(User, users)
emailmapper = mapper(Email, emails)

session = create_session()

mary = session.query(User).selectfirst(users.c.name=='Mary')
mary.age += 1

session.flush()

fred = User()
fred.name = 'Fred'
fred.age = 37

print "About to flush() without a save()..."
session.flush()  # Will *not* save Fred's data yet

session.save(fred)
print "Just called save(). Now flush() will actually do something."
session.flush()  # Now Fred's data will be saved

session.delete(fred)
session.flush()

Let's break this down piece-by-piece to see what's going on here.

from sqlalchemy import *

db = create_engine('sqlite:///joindemo.db')

db.echo = True

metadata = BoundMetaData(db)

Here we're using the same SQLite database that we created in the join demo, which contains users and email addresses. Unless you've deleted that file, the data (four users and four email addresses) should still be there as well. We set echo to True so that the SQL will be printed out at each step.

users = Table('users', metadata, autoload=True)
emails = Table('emails', metadata, autoload=True)

Because the users and emails tables are already in the database, we don't have to specify them again; we can just let SQLAlchemy fetch their definitions from the database.

# These are the empty classes that will become our data classes
class User(object):
    pass
class Email(object):
    pass

Note that your data classes must be new-style classes (e.g., derived from the base class object). If they aren't, SQLAlchemy will raise an ArgumentError exception when you try to create the mapper. If you don't know what the difference between new-style classes and old-style classes is, don't worry about it; just get in the habit of deriving all your classes either from object or from another base class that descends from object. Most of Python's object-oriented features work much better on classes that ultimately derive from object, and SQLAlchemy makes heavy use of those features.

Moving on:

usermapper = mapper(User, users)
emailmapper = mapper(Email, emails)