Switching to SQLAlchemy

If you have a simple model, SQLObject may be enough. If you want more flexibility, check out SQLAlchemy.

SQLObject was so far enough for me, but I stumbled upon a weird issue when doing updates. Once in a while my selects returned old data that had been overwritten by a previous update.

SteveA had the same problem and showed me a workaround for this, but it sounded like too much work, and it was scary that this could happen.

I decided to try out SQLAlchemy. After some hair pulling I got it working (you can see the reason for the pulling at the end of the post).

You can get the latest version by typing

easy_install sqlalchemy

Make sure you have the latest 0.9x version of TurboGears.

The model

Using the model is almost as simple as before. The model definition is more complicated, but that’s something you won’t change very often.

model.py (compare to SQLObject version. Note that I’ve added some columns not shown in the previous model):

from sqlalchemy import *
import cherrypy

db = create_engine(cherrypy.config.get('sqlalchemy.dburi'),
session = create_session(bind_to=db)
meta = BoundMetaData(db)

projects = Table('project', meta,
    Column('id', Integer, primary_key=True),
    Column('title', String(1000), nullable = False),
contexts = Table('context', meta,
    Column('id', Integer, primary_key=True),
    Column('title', String(1000), nullable = False),
actions = Table('action', meta,
    Column('id', Integer, primary_key=True),
    Column('title', String(1000), nullable = False),
    Column('project_id', Integer, ForeignKey('project.id')),
    Column('context_id', Integer, ForeignKey('context.id')),
    Column('notes', String(1000)),
    Column('priority', Integer, default=0),
    Column('closed', Boolean, default=0),
tags = Table('tag', meta,
    Column('id', Integer, primary_key=True),
    Column('title', String(1000), nullable = False),
tagaction = Table('tag_action', meta,
    Column('action_id', Integer, ForeignKey('action.id')),
    Column('tag_id', Integer, ForeignKey('tag.id'))
class Project(object):
    def __init__(self, title, notes):
        self.title = title
        self.notes = notes
    def __repr__(self):
        return self.title
class Action(object):
    def __init__(self, title, notes, project, context, 
           priority, closed):
        self.title = title
        self.notes = notes
        self.context = context
        self.project = project
        self.priority = priority
        self.closed = closed
    def __repr__(self):
        return self.title
class Tag(object):
    def __init__(self, title):
        self.title = title
    def __repr__(self):
        return self.title

class Context(object):
    def __init__(self, title):
        self.title = title
    def __repr__(self):
        return self.title
mapper(Tag, tags)
actionmapper = mapper(Action, actions, properties = {
    'tags' : relation(Tag, secondary=tagaction)
projectmapper = mapper(Project, projects, properties={
    'actions' : relation(Action, backref='project'),
contextmapper = mapper(Context, contexts, properties={
    'actions' : relation(Action, backref='context'),

Using the model:

>>> p = Project(title='p', notes='n')
>>> c = Context(title='c')
>>> a = Action(title='a', notes='n', project=p, context=c, priority=0, closed=False)
>>> t = Tag(title='t')
>>> t2 = Tag(title='t2')
>>> a.tags.append(t)
>>> a.tags.append(t2)

Saving the action object and flushing the session persists all objects:

[engine]: BEGIN
[engine]: INSERT INTO tag (title) VALUES (?)
[engine]: ['t']
[engine]: INSERT INTO tag (title) VALUES (?)
[engine]: ['t2']
[engine]: INSERT INTO context (title, notes) VALUES (?, ?)
[engine]: ['c', None]
[engine]: INSERT INTO project (title) VALUES (?)
[engine]: ['t']
[engine]: INSERT INTO action (title, project_id, context_id, notes, priority, closed) VALUES (?, ?, ?, ?, ?, ?)
[engine]: ['t', 1, 1, 'n', 0, False]
[engine]: INSERT INTO tag_action (action_id, tag_id) VALUES (?, ?)
[engine]: [[1, 1], [1, 2]]
[engine]: COMMIT

Examples of differences in CRUD-calls (SQLObject -> SQLAlchemy):

select all projects:
Project.select() ->

select one:
Project.get(id) -> session.get(Project, id)

context.set(title=title) ->

automatic -> session.save(action)


Remember to call session.flush() after each C/U/D operation to commit your work. You can also enable autoflush, but I didn’t find any documentation about it.

Remember also to remove deleted objects from relations. Deleting an action means you have to either reload the parent project or call project.actions.remove(action).

Great SQLAlchemy tutorial

Hair pulling details: I lost some hours finding out that my Actions table definition couldn’t have a column called ‘project’. I would get the cryptic error “AttributeError: ‘LazyLoader’ object has no attribute ‘lazybinds’” when calling p.actions.append(a) . I renamed it to project_id and the problem was gone.


10 Responses to Switching to SQLAlchemy

  1. Robin Munn says:

    The reason you were having trouble with your Actions table definition is because you also had a backref called “project”, defined in your projectmapper definition. Backref *creates* the name it’s using, and in this case it was hiding the “project” attribute from your Actions table. Or the project attribute was hiding the backref; I don’t really know which one is which.

    You might want to report this as a “The error message was totally unclear and should be improved” bug on the SQLAlchemy mailing list, since you can’t be the only one who had this kind of problem.

  2. I’m a huge fan of SQLAlchemy.

    It’s worth noting that, with a reasonably straightforward model, ActiveMapper gives you almost as concise a syntax as SQLObject, but the power of SQLAlchemy when you need it. There was also some recent discussion on the list about assign_mapper which simplifies some things.

  3. mike bayer says:

    it seems like the “project” mismatch is because your mapper has a relationship called “project” which conflicts with the column named “project” (each column in the mapped table becomes a property of the mapper in the same way each relation() does). i sort of thought SA would detect that condition and raise a clear message, but from your experience it seems like it trips over it much later; ill see if i can look into that.

    there is a way to have a column named whatever you want, and to override the name used by the mapper for it, described in http://www.sqlalchemy.org/docs/adv_datamapping.myt#advdatamapping_properties_colname
    . that way you could leave your db columns named however you want, independently of the property names in the mapper.

  4. Sanjay says:

    I like using assign_mapper than mapper or ActiveMapper. Best of both worlds.

  5. atwork says:

    I found too little information to get assign_mapper working. I would be glad if someone had some documentation about it.

  6. mike bayer says:

    doc for assign_mapper, using it completely by itself, is here:


    the trick is that it needs a SessionContext with which to get the current contextual session. all other arguments to assign_mapper are the same as those to mapper().

  7. […] Thanks to all comments to my previous post, I managed to further simplify the model. […]

  8. […] intuitive and pleasing paradigm, and I was perfectly content until I heard some clever people say nice things about you. I enjoy working in new environments, so I took a look at you. Much to my suprise, […]

  9. […] Switching to SQLAlchemy « From Java to Python If you have a simple model, SQLObject may be enough. If you want more flexibility, check out SQLAlchemy. (tags: python sqlalchemy sample code tutorial cherryPy) […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: