It's been a long time since I last updated Pysawndz and one of the things that I wanted to do is to use an ORM with it. Right now it relies heavily on PyQt's QtSql package together with a Db class that I made that facilitates database queries. This type of approach just doesn't scale and hard to manage plus the only time it can handle complex queries was by passing the SQL statements itself. Back then this was the best way since I have limited time and spending some of it just to study sqlalchemy will just make me far from having something to present.

Anyway, sqlalchemy is an Object Relational Mapper (ORM) to make your database life in Python a breeze. But my attempt to explain it doesn't even scratch the surface of what sqlalchemy has to offer so lets just see some code.

To demonstrate, we're going to create 3 tables namely users, profiles and photos tables. Each user has one-to-one relationship with a profile while profile have one-to-many relationship with photos. The first thing that we need to do is to specify the database engine, you can specify it by calling the create_engine function. The first parameter is straightforward, the second one simply logs the output so you can see whats happening.

from sqlalchemy import create_engine engine = create_engine('sqlite:///site.db', echo=True)

Then we need a MetaData object to hold our table's schema. We also use its create_all method later which accepts the engine object we just instantiated to create all the tables we're going to define.

from sqlalchemy import MetaData meta_data = MetaData()

Here's our database tables field definitions:

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

users_table = Table('users', meta_data,
    Column('uid', Integer, primary_key=True),
    Column('username', String(30)),
    Column('password', String(40))
)
profiles_table = Table('profiles', meta_data,
    Column('pid', Integer, primary_key=True),
    Column('uid', Integer, ForeignKey('users.uid')),
    Column('first_name', String(30)),
    Column('last_name', String(30))
)
photos_table = Table('photos', meta_data,
    Column('tid', Integer, primary_key=True),
    Column('pid', Integer, ForeignKey('profiles.pid')),
    Column('file', String)
)

First we import a bunch of stuff. Table is used to create our database tables, Column for specifying a column, Integer and String are our only data-type and ForeignKey for specifying foreign keys. Then we create all the tables using the create-all method of MetaData object mentioned above:

meta_data.create_all(engine)

Next we're going to create classes to be mapped against our tables.

class User(object):
    def __init__(self, username, password):
        self.username = username
        self.password = password
    
    def __repr__(self):
        return "" % (self.username, self.password)

class Profile(object):
    def __init__(self, first_name, last_name):
        self.first_name = first_name
        self.last_name = last_name
    
    def __repr__(self):
        return "" % (self.first_name, self.last_name)

class Photo(object):
    def __init__(self, file):
        self.file = file
    
    def __repr__(self):
        return "" % (self.file)

Each of these classes represents a single row on each table, For sqlalchemy to know which table, we're going to map them. Now this is not the only way to map tables to classes, there's also a declarative way but I like this one so just stay with me. 

from sqlalchemy.orm import mapper, relation, backref 

mapper(User, users_table)
mapper(Profile, profiles_table,
    properties={'user': relation(User,
        backref=backref('profile', uselist=False))})
mapper(Photo, photos_table,
    properties={'profile': relation(Profile, backref='photos')})

Our first map is just simple, for each row on users_table theres a User object. The second maps Profile class to profiles_table plus we add a property named user which points to a User object so we can access the user who owns the profile. This is done using the backref function, the uselist=False simply tells to shift the relationship from many-to-one to one-to-one; without it, accessing a user's profile will return a list of profile since sqlalchemy sees and understand the foreign key we defined a while ago when we we're creating the tables. In the third map, we know that a profile can have multiple photos so we never alter the many-to-one relationship.

The way sqlalchemy works to talk to a database is it establishes a session, so we're going to create one.

from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind=engine) session = Session()

We bind the session to our engine object then Session acts as a session maker. Now we get to test things:

marc = User('marc', '1234')
marc.profile = Profile('Marconi', 'Moreto')
marc.profile.photos = [Photo('smile.jpg'), Photo('grin.jpg')]

hans = User('hans', 'sosecure')
hans.profile = Profile('Hans', 'Amorio')
hans.profile.photos = [Photo('bike.gif'), Photo('flirt.jpg')]

users_list = [marc, hans]

session.add_all(users_list)
session.commit()

Here we created two users, marc and hans. Notice how we take advantage of our class __init__ method and how we conveniently access .profile from a User instance and .photos from a Profile instance. These is possible because of the map we specified, The last three lines saves our objects to the database. Here's a snippet just to see the objects we've created:

for user in users_list:
    print "Account:\t", user.username , ', ', ''.join(['*' for c in user.password])
    print "Name:\t\t", user.profile.last_name, ', ', user.profile.first_name
    print "Photos:\t\t", ', '.join([photo.file for photo in user.profile.photos])
    print "------------------------------------------------"

... and the output:

Account: marc , ****
Name:    Moreto , Marconi
Photos:  smile.jpg, grin.jpg

------------------------------------------------

Account: hans , ********
Name:    Amorio , Hans
Photos:  bike.gif, flirt.jpg

------------------------------------------------

Note that I'm by no means an sqlalchemy expert. That said, hope this helped you understand its basic usage. With all the mess on Pysawndz, I think it'll take time before I can clean it all up but using sqlalchemy will definitely make things better.