SQL Alchemy ORM Basics

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