Learning SQL Alchemy

I spent a portion of my holidays studying the basics of SQL Alchemy. My first lesson is that SQL Alchemy is NOT an ORM for Python but instead a SQL abstraction tool with an ORM built-into it. At first I was having trouble understanding what this really meant, but after seeing an example it sunk in. Let's start by building a table.

from sqlalchemy import  Table, Column, Integer, String

meta = sqlalchemy.MetaData("sqlite:///")

user_table = Table('userTable', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String, unique=True, nullable=False),
    Column('password', String, nullable=False))

meta.create_all()

First thing we need is a MetaData object which holds all of our database schema information as well as our engine for executing queries. By passing "sqlite:///" in the MetData constructor we create an engine pointing at an in-memory SQLite database. To create a table, we just declare an instance of the Table object and pass in the necessary parameters: table name, our MetData instance and whatever columns we need in the table. The constructor for the Column class not only takes the column name and type, but constraints as well. Then all we have to do is execute the statement by using the create_all() method of the MetaData class.

Did you note the user_table variable we created? That comes in really handy when we want to start manipulating our new table.

insert = user_table.insert()
insert.execute(name='darrell', password='password1')
insert.execute(name='angie', password='password2')
insert.execute(name='nate', password='password3')
insert.execute(name='drew', password='password4')

See how easy inserting data is? There are methods just like the insert method above for select, insert and delete. Let's take a look at the select method.

select = user_table.select()
results = select.execute()

for row in results:
    print row.name, row.password

The result are just as you would expect.

darrell password1
angie password2
nate password3
drew password4

That's great, but how do we change Nate's password and then return only his record?

update = user_table.update(user_table.c.name=='nate')
update.execute(password='awesomeness')

select = user_table.select(user_table.c.name=='nate')
results = select.execute()

for row in results:
    print row.name, row.password

It's very simple and readable code with one caveat: what is "user_table.c.name"? It's a convenience attribute containing a collection of all the columns in userTable. So what our update statement is saying in terms of SQL is "UPDATE userTable SET ????? WHERE name = 'nate'". The only thing we don't know at this point is what to change. That happens when we execute the statement on the next line. If you understand the update statement above, the select statement works exactly the same way. Our result is below.

nate awesomeness

We've seen select, update and insert, but what about delete?

delete = user_table.delete(user_table.c.name.like("d%"))
delete.execute()
select = user_table.select()
results = select.execute()

I couldn't help myself - I had to confuse the issue by using "LIKE". In my defense, now you know how to introduce a "LIKE" comparison into your code AND that the "c" attribute is more than just a collection of strings. Beyond that, the delete works exactly the way you would expect it to work: call the delete method by passing the constraining expression as a parameter and then execute the result.

Now I understand why SQL Alchemy is an SQL abstration tool. All I did in the examples above were execute very basic SQL statements; the ORM was never used. Why is this so powerful? Imagine being able to write the same code for a SQL Server database as you did for a MySQL server and having the confidence that it's going to work. That's the power of SQL Alchemy.