A long time ago (circa 2007 if Google serves me right), there was a Python programmer named Robin Munn who wrote a really nice tutorial on SqlAlchemy. It was originally based on the 0.1 release, but updated for the newer 0.2. Then, Mr. Munn just disappeared and the tutorial was never updated. I have been kicking around the idea of releasing my own version of this tutorial for quite some time and finally decided to just do it. I hope you will find this article helpful as I found the original to be.

Getting Started

SqlAlchemy is usually referred to as an Object Relational Mapper (ORM), although it is much more full featured than any of the other Python ORMs that I’ve used, such as SqlObject or the one that’s built into Django. SqlAlchemy was founded by a fellow named Michael Bayer. I usually see Jonathan Ellis’ name floating around the project a lot too, especially at PyCon.

This tutorial will be based on the latest released version of SqlAlchemy: 0.5.8. You can check your version by doing the following:

import sqlalchemy
print sqlalchemy.__version__

Note: I’ll also be using Python 2.5 on Windows for testing. However, this code should work equally well on Mac and Linux. If you need SqlAlchemy to work on Python 3, then you’ll want the SVN version of 0.6. The website gives instructions on how to do acquire the code.

If you don’t happen to have SqlAlchemy, you can download it from their website or use easy_install if you have setuptools installed. Let’s see how:

In the case of downloading the source, you’ll need to extract it and then open a console window (on Windows, go to Start, Run and type “cmd”, without the quotes). Then change directories until you are in the unzipped folder.

To install SQLAlchemy, you can us pip:


pip install sqlalchemy


This also assumes that you have pip on your path. If you do not, then use the full path to use it (i.e. c:\python38\scripts\pip.exe or whatever).

Creating the First Script

Now we get to create our first example using SqlAlchemy. We will create a simple table to store a user’s name, their age and their password.

from sqlalchemy import create_engine
from sqlalchemy import MetaData, Column, Table, ForeignKey
from sqlalchemy import Integer, String

engine = create_engine('sqlite:///tutorial.db',
                       echo=True)

metadata = MetaData(bind=engine)

users_table = Table('users', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('name', String(40)),
                    Column('age', Integer),
                    Column('password', String),
                    )

addresses_table = Table('addresses', metadata,
                        Column('id', Integer, primary_key=True),
                        Column('user_id', None, ForeignKey('users.id')),
                        Column('email_address', String, nullable=False)                            
                        )

# create tables in database
metadata.create_all()

Looking Deeper

As you can see, we needed to import various bits and pieces from the sqlalchemy package, namely create_engine, MetaData, Column, Table, Integer, and String. Then we create an “engine” which is basically an object that knows how to communicate with the provided database using the credentials you supply. In this case, we are using a Sqlite database that doesn’t need credentials. There are in-depth docs on this subject alone where you can read up on for your favorite database flavor. Also notice that we set echo to True. This means that SqlAlchemy will output all the SQL command it is executing to stdout. This is handy for debugging, but should be set to False when you’re ready to put the code into production.

Next, we create a MetaData object. This cool creation from the SqlAlchemy team holds all the database metadata. It consists of Python objects that hold descriptions of the tables and other schema-level objects of the database. We can bind the metadata object to our database here or in the create_all statement near the end of the code.

The last section is how we create the tables programmatically. This is accomplished by using SqlAlchemy’s Table and Column objects. Notice that we have various field types available to us, like String and Integer. There are many others too. For this example, we create a database and name it “users”, then pass in our metadata object. Next, we put it in the Columns. The “id” column is set as our primary key. SqlAlchemy will magically increment this for us as we add users to the database. The “name” column is a String type and capped at 40 characters long. The “age” column is just a simple Integer and the “password” column is just set to String. We didn’t set its length, but we probably should. The only major difference in the addresses_table is how we set up the Foreign key attribute that connects the two tables. Basically, we point it at the other table by passing the correct field name in a string to the ForeignKey object.

The final line of this snippet actually creates the database and the table. You can call it as often as you like as it will always check for the existence of the specified table before trying to create it. That means you can create additional tables and call create_all and SqlAlchemy will only create the new table.

SqlAlchemy also provides a way to load tables that were previously created:

someTable = Table("users", metadata, autoload=True, schema="schemaName")

I’ve noticed that with this release, SqlAlchemy has gotten pretty picky about having the database schema specified when you autoload a database. Should you have that issue, you’ll need to add the following to your Table definition: schema=”some schema”. For more information, see the documentation.

Inserting

There are several ways to add and extract information from the database. We’ll look at the low-level ways first and then in one of the other parts of this series, we’ll get into Sessions and the Declarative styles which tend to be slightly more abstract. Let’s take a look at the different ways to Insert data into our database:

# create an Insert object
ins = users_table.insert()
# add values to the Insert object
new_user = ins.values(name="Joe", age=20, password="pass")

# create a database connection
conn = engine.connect()
# add user to database by executing SQL
conn.execute(new_user)

The code above shows how to use a Connection object to do the insert. First, you need to create the Insert object by calling the table’s insert method. Then you can use the Insert’s values method to add the required values for the row. Next, we create the Connection object via the engine’s connect method. Finally, we call the Connection object’s execute method on the Insert object. It sounds kind of complicated, but it’s actually pretty easy.

This next snippet shows a couple ways to do an insert without the Connection object:

# a connectionless way to Insert a user
ins = users_table.insert()
result = engine.execute(ins, name="Shinji", age=15, password="nihongo")

# another connectionless Insert
result = users_table.insert().execute(name="Martha", age=45, password="dingbat")

In both cases, you will need to call the table object insert method. Basically, you just take the engine out of the picture in the second instance. The last insert method we’ll look at is how to insert multiple rows:

conn.execute(users_table.insert(), [
    {"name": "Ted", "age":10, "password":"dink"},
    {"name": "Asahina", "age":25, "password":"nippon"},
    {"name": "Evan", "age":40, "password":"macaca"}
])

This is pretty self-explanatory, but the gist is that you need to use the Connection object from earlier and pass it two arguments: the table’s Insert object and a list of dictionaries which contain column name and value pairs. Please note that by using the execute method, the data is committed to the database in these examples.

Now let’s move on to doing selects.

Selecting

SqlAlchemy provides a robust set of methods to accomplish Selects. We will focus on the simple methods here. For the advanced stuff, I recommend their official documentation and mailing list. One of the most common examples is doing a select all, so let’s start with that:

from sqlalchemy.sql import select

s = select([users_table])
result = s.execute()

for row in result:
    print row

First we have to import the select method from sqlalchemy.sql. Then we pass it the table as a one element list. Finally we call the select object’s execute method and store the returned data in the result variable. Now that we have all the results, we should probably see if we got what we expected. Thus, we create a for loop to iterate over the result.

If you need all the results in a list of tuples rather than Row object, you can do the following:

# get all the results in a list of tuples
conn = engine.connect()
res = conn.execute(s)
rows = res.fetchall()

And if you just need the first result back, then you use fetchone() instead of fetchall():

res = conn.execute(s)
row = res.fetchone()

Now let’s pretend that we need to get a little bit more granular in our results. In the next example, we just want to return the user’s name and their age while omitting their password.

s = select([users_table.c.name, users_table.c.age])
result = conn.execute(s)
for row in result:
    print row

Well, that was pretty easy. All we had to do was specify the column names in our select statement. The little “c” basically means “column”, so we do a select on column name and column age. If you had multiple tables, then the select statement would be something like this:

select([tableOne, tableTwo])

Of course, this will probably return duplicate results, so you’ll want to do something like this to mitigate the issue:

s = select([tableOne, tableTwo], tableOne.c.id==tableTwo.c.user_id)

The SqlAlchemy documentation called the first result a Cartesian product since it caused each row from the first table to be produced against each row of the second table. The second statement above eliminates that annoyance. How? Well, this is the way you do a WHERE clause using this form of a select. In the next part of the series, I’ll show a different way to do a select and where with sessions.

Here are a couple more examples though with explanations in the comments:

from sqlalchemy.sql import and_

# The following is the equivalent to 
# SELECT * FROM users WHERE id > 3
s = select([users_table], users_table.c.id > 3)

# You can use the "and_" module to AND multiple fields together
s = select(and_(users_table.c.name=="Martha", users_table.c.age < 25))

The code above illustrates that SqlAlchemy can use operators and conjunctions in their queries as well. I recommend reading their documentation for full details here.

Wrapping Up

I think this is a good place for us to stop. We have now learned how to create a database, add rows and select data from our database. In the next part of our series, we will learn the more popular way of doing this using the Object Relation methods. We will also learn about a few other key topics, such as the SqlAlchemy session. We will also look at how joins work in SqlAlchemy. See you then!

Further Reading

• SQL Expression Tutorial
• Robin Munn's SqlAlchemy Tutorial

Downloads

• SqlAlchemy Demo One.zip
• SqlAlchemy Demo One.tar