In my previous post, Learning SQL Alchemy, I focused on the SQL-like aspect of SQL Alchemy. You'll need to review the sample code in that post before understanding the code below. On with the code.
class User(object): pass
mapper(User, user_table)
Believe it or not, we just created a class with an ID, Name and Password. Simply create an empty class and pass it to the the mapper along with the user_table we had created. How do we use it? First off we have to create a Session object. Note that when we call the sessionmaker, it returns a Session class which you use to create the session.
Session = sqlalchemy.orm.sessionmaker()
session = Session()
All have we to do now is create a new User object, set the appropriate fields and pass it to the "save" method.
user = User()
user.name = "Fred Flintstone"
user.password = "yaba daba doo"
session.save(user)
But how do we get our user back from the database?
query = session.query(User)
We can now iterate through the query like any list.
for user in query:
print user.id, user.name, user.password
You can see the new record we inserted along with the other records in the table.
2 angie password2
3 nate awesomeness
4 Fred Flintstone yaba daba doo
The important thing to note is that the query object is actually a list of the very same User type I defined at the beginning of this post. Of course we probably don't want everything in the table, so here's an example of getting back a single object.
for user in query.filter_by(name = "angie"):
print user.id, user.name, user.password
The result is just what we would expect.
2 angie password2
But let's say I want to change Angie's password.
user.password = "a much more secure passphrase"
for user in query.filter_by(name = "angie"):
print user.id, user.name, user.password
The result?
2 angie a much more secure passphrase
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.
That's the subject line of an email I received yesterday. No one is more surprised than me. Considering who the other MVPs are, I'm honored to be among them. I'm not entirely sure what I did to deserve this, but I'm grateful all the same. Thank you, Microsoft, for noticing me and here's to a productive 2009. Cheers!