Another Step-by-Step SqlAlchemy Tutorial (part 2 of 2)

In the first part of this series, we went over what some might call the “SQL Expression” method of using SqlAlchemy to interact with your database. The theory behind this is that we should learn the less abstract way of doing things before we get to the higher level (and more abstract) methods. This is true in many math classes, like Calculus where you learn the long way to find the standard deviation of some calulation before you learn about the shortcut.

For the second half, we will be doing what some might say is the easy way to use SqlAlchemy. It is known as the “Object Relational” method and the official documentation actually starts with it. This methodology takes a little longer to set up initially, but in many ways, it is also much easier to follow.

Getting Used to Data Mapping

Robin Munn’s old school SqlAlchemy tutorial called this section “data mapping” because we’ll be mapping the data in a database to Python classes. Let’s get started!

from sqlalchemy import create_engine
from sqlalchemy import Column, MetaData, Table
from sqlalchemy import Integer, String, ForeignKey
from sqlalchemy.orm import mapper, sessionmaker

####################################################
class User(object):
    """"""

    #----------------------------------------------------------------------
    def __init__(self, name, fullname, password):
        """Constructor"""
        self.name = name
        self.fullname = fullname
        self.password = password
        
    def __repr__(self):
        return "" % (self.name, self.fullname, self.password)
        
# create a connection to a sqlite database
# turn echo on to see the auto-generated SQL
engine = create_engine("sqlite:///tutorial.db", echo=True)

# this is used to keep track of tables and their attributes
metadata = MetaData()
users_table = Table('users', metadata,
                    Column('user_id', Integer, primary_key=True),
                    Column('name', String),
                    Column('fullname', String),
                    Column('password', String)
                    )
email_table = Table('email', metadata,
                    Column('email_id', Integer, primary_key=True),
                    Column('email_address', String),
                    Column('user_id', Integer, ForeignKey('users.user_id'))
                    )

# create the table and tell it to create it in the 
# database engine that is passed
metadata.create_all(engine)

# create a mapping between the users_table and the User class
mapper(User, users_table)

The first difference to take note of compared to our previous examples is the User class. We have changed our original example (see part one) a little to match what’s on the official documentation, namely the parameters are now name, full name and password. The rest should look the same until we get to the mapper statement. This handy method allows SqlAlchemy to map the User class to the users_table. This might not seem like a big deal, but this method makes adding users to the database much more straightforward.

However, before we get to that, we need to discuss the Declarative configurational style. While the style above gives us granular control over the table, mapper and class, for the most part we don’t need it to be that complicated. That is where the Declarative style comes in. It makes configuring everything even easier. The first declarative style that I knew of was an add-on for SqlAlchemy called Elixir. This built-in Declarative style isn’t as full-featured as Elixir, but it’s handier because you don’t have the extra dependency. Let’s see how Declarative is different:

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, mapper, relation, sessionmaker

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):
        """Constructor"""
        self.name = name
        self.fullname = fullname
        self.password = password
        
    def __repr__(self):
        return "" % (self.name, self.fullname, self.password)
        
########################################################################
class Address(Base):
    """
    Address Class
    
    Create some class properties before initilization
    """
    __tablename__ = "addresses"
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    
    # creates a bidirectional relationship
    # from Address to User it's Many-to-One
    # from User to Address it's One-to-Many
    user = relation(User, backref=backref('addresses', order_by=id))

    #----------------------------------------------------------------------
    def __init__(self, email_address):
        """Constructor"""
        self.email_address = email_address
        
    def __repr__(self):
        return "" % self.email_address
    
    
# create a connection to a sqlite database
# turn echo on to see the auto-generated SQL
engine = create_engine("sqlite:///tutorial.db", echo=True)

# get a handle on the table object
users_table = User.__table__
# get a handle on the metadata
metadata = Base.metadata
metadata.create_all(engine)

As you can see, almost everything is now created in classes. We create class attributes (which are like global variables for the class) that identify the columns of the table. Then we create the same __init__ that we had in the original class example above. Also, we sub-class declarative_base rather than the basic object. If we need a table object, we have to call the following magic method User.__table__; and to get the metadata, we need to call Base.metadata. That covers the differences that we care about. Now we can look at how to add data to our database.

Class is now in Session

The beauty of using the Object Relational method for interacting with our database can be shown in a few quick code snippets. Let’s see how we can create a row:

mike_user = User("mike", "Mike Driscoll", "password")
print "User name: %s, fullname: %s, password: %s" % (mike_user.name,
                                                     mike_user.fullname,
                                                     mike_user.password)

As you can see, we can create users with the User class. We can use dot-notation to access the attributes, just as we would in any other Python class. We can even use them to update the rows. For example, if we need to change the user object above, we would do the following:

# this is how you would change the name field
mike_user.fullname = "Mike Dryskull"

Note that none of this will add the row to the database automatically like those insert methods that we looked at in our first article. Instead, we need a Session object to do that. Let’s run through some of the basics of using a Session:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

mike_user = User("mike", "Mike Driscoll", "password")
session.add(mike_user)

We’ll pause here to explain what’s going on. First we need to import the sessionmaker from sqlalchemy.orm and bind it to an engine (technically, you can create the session without binding, but to do anything useful, you’ll need to bind it eventually). Next we create a session instance. Then we instantiate a user object and add it to the session. At this point, no SQL code has been run and the transaction is just pending. To persist this row, we would need to call session.commit() or run a query.

If you need to add multiple users, you do this:

session.add_all([
     User('Mary', 'Mary Wonka', 'foobar'),
     User('Sue', 'Sue Lawhead', 'xxg527'),
     User('Fay', 'Fay Ray', 'blah')])

If you happen to change one of the user’s attributes after committing it to the database, you can use session.dirty to check which one was modified. If you just need to know what’s rows are pending, call session.new. Finally, we can use session.rollback() to rollback a transaction.

Now let’s take a look at some sample queries:

# do a Select all
all_users = session.query(User).all()

# Select just one user by the name of "mike"
our_user = session.query(User).filter_by(name='mike').first()
print our_user

# select users that match "Mary" or "Fay"
users = session.query(User).filter(User.name.in_(['Mary', 'Fay'])).all()
print users

# select all and print out all the results sorted by id
for instance in session.query(User).order_by(User.id): 
    print instance.name, instance.fullname

We don’t need to go over each of these as they all have explanations in the comments. Instead, we’ll move on to the topic of joins.

Join in the Fun

There are joins using the SQL Expression syntax that I won’t be covering here. Rather, we will use the Object Relational methodology. If you look back at either of the beginning examples for creating the tables, you will notice that we have already set up the join with the ForeignKey object. The Declarative format looked like this:

user_id = Column(Integer, ForeignKey('users.id'))

# creates a bidirectional relationship
# from Address to User it's Many-to-One
# from User to Address it's One-to-Many
user = relation(User, backref=backref('addresses', order_by=id))

Let’s see how this works by creating a new user:

prof = User("Prof", "Prof. Xavier", "fudge")
prof.addresses

Because of the ForeignKey and the backref command, the User object has an addresses attribute. If you run that code, you will see that it is empty. Let’s add some addresses! (Note: Be sure to add the prof user to the session: session.add(prof) )

prof.addresses = [Address(email_address='profx@dc.com'), 
                        Address(email_address='xavier@yahoo.com')]

See how easy that was? It’s even easy to get the information back out. For example, if you wanted to access just the first address, you would just call prof.addresses[0]. Now, say that you need to change one of the addresses (i.e. do an UPDATE). It’s as easy as pie:

# change the first address
prof.addresses[0].email_address = "profx@marvel.com"

Now let’s move on to doing queries on joins:

for u, a in session.query(User, Address).filter(User.id==Address.user_id).filter(Address.email_address=='xavier@yahoo.com').all():
    print u, a

That is one long query! I find those hard to follow myself, so I usually do the following to make it easier on my brain:

sql = session.query(User, Address)
sql = sql.filter(User.id==Address.user_id)
sql = sql.filter(Address.email_address=='xavier@yahoo.com')

for u, a in sql.all():
    print u, a

Now, for those of you that like the one-liners, there is nothing wrong with the first example. It will produce the exact same results. I just happen to find the longer version easier to debug. Finally, we can also use a real join:

from sqlalchemy.orm import join
session.query(User).select_from(join(User, Address)).filter(Address.email_address=='xavier@yahoo.com').all()

This also does the same as the previous two examples, but in a more explicit manner. For more information on joins using the Object Relational syntax, I recommend the official documentation.

Wrapping Up

At this point, you should be able to create your database with tables, populate the tables with data as well as select, update and commit transactions to your database using SqlAlchemy. I hope you found this tutorial helpful in understanding this amazing technology.

Note: This tutorial was tested on Windows with Python 2.5 and SqlAlchemy 0.5.8.

Further Reading

• Object Relational Tutorial
• Robin Munn’s SqlAlchemy Tutorial