Improving MediaLocker: wxPython, SQLAlchemy, and MVC

This blog ran an article about wxPython, SQLAlchemy, CRUD and MVC earlier this month. The program that we created in that post was dubbed “MediaLocker”, whether or not it was explicitly stated as such. Anyway, since then, I have received a couple comments about improving the program. One came from Michael Bayer, one of the creative minds behind SQLAlchemy itself and the other comments came from Werner Bruhin, a nice guy who haunts the wxPython mailing list, helping new users. So I went about creating an improved version of the code following their advice. Werner then improved it a bit more. So in this article, we will be looking at improving the code, first with my example and then with his. Enough talk though; let’s get to the meat of story!

Making MediaLocker Better

Michael Bayer and Werner Bruhin both thought that I should only connect to the database once as that’s a fairly “expensive” operation. This could be an issue if there were multiple sessions existing at the same time too, but even in my original code, I made sure to close the session so that wouldn’t happen. When I wrote my original version, I thought about separating out the session creation, but ended up going with what I thought was more straightforward. To fix this niggling issue, I changed the code so that I passed the session object around instead of constantly calling my controller’s connectToDatabase function. You can read more about Sessions here. See the code snippet from

class BookPanel(wx.Panel):
    def __init__(self, parent):
        wx.Panel.__init__(self, parent)

        if not os.path.exists("devdata.db"):

        self.session = controller.connectToDatabase()
            self.bookResults = controller.getAllRecords(self.session)
            self.bookResults = []

Note that we have a little conditional right up front that will create the database if it doesn’t already exist. Next I create the session object in the main GUI as a property of the panel sub-class. Then I pass it where ever I need to. One example can be seen above where I pass the session object to the controller’s getAllRecords method.

Another big change was to remove the ObjectListView model from and just use the SQLAlchemy table class instead:

class Book(DeclarativeBase):
    __tablename__ = "book"
    id = Column(Integer, primary_key=True)
    author_id = Column(Integer, ForeignKey(""))
    title = Column(Unicode)
    isbn = Column(Unicode)
    publisher = Column(Unicode)
    person = relation("Person", backref="books", cascade_backrefs=False)
    def author(self):
        return "%s %s" % (self.person.first_name, self.person.last_name)

This is actually mostly the same as the original class except that it uses SQLAlchemy constructs. I also needed to add a special property to return the author’s full name for display in our widget, so we used Python’s built-in function: property which returns a property attribute. It’s easier to understand if you just look at the code. As you can see, we applied property as a decorator to the author method.

Werner’s Additions

Werner’s additions are mostly adding more explicit imports in the model. The biggest change in the model is as follows:

import sys
if not hasattr(sys, 'frozen'):
    # needed when having multiple versions of SA installed
    import pkg_resources
    pkg_resources.require("sqlalchemy") # get latest version
import sqlalchemy as sa
import sqlalchemy.orm as sao
import sqlalchemy.ext.declarative as sad
from sqlalchemy.ext.hybrid import hybrid_property

maker = sao.sessionmaker(autoflush=True, autocommit=False)
DBSession = sao.scoped_session(maker)

class Base(object):
    """Extend the base class

    - Provides a nicer representation when a class instance is printed.
        Found on the SA wiki, not included with TG
    def __repr__(self):
        return "%s(%s)" % (
                 ', '.join(["%s=%r" % (key, getattr(self, key))
                            for key in sorted(self.__dict__.keys())
                            if not key.startswith('_')]))

DeclarativeBase = sad.declarative_base(cls=Base)
metadata = DeclarativeBase.metadata

def init_model(engine):
    """Call me before using any of the tables or classes in the model."""

The first few lines are for people with SetupTools / easy_install on their machine. If the user has multiple versions of SQLALchemy installed, it will force it to use the latest. Most of the other imports are shortened to make it very obvious where various classes and attributes come from. I am honestly not familiar with the hybrid_property, so here’s what its docstring had to say:

A decorator which allows definition of a Python descriptor with both instance-level and class-level behavior.

You can read more here:

Werner also added a little __repr__ method to the Base class to make it return a better representation of the class instance when it’s printed, which is handy for debugging. Finally, he added a function called init_model to initialize the model.

Wrapping Up

Now you should know that Werner and I have decided to make MediaLocker into an example of a wxPython database-enabled application. He’s been doing a bunch of work on it since the simple edits I mentioned above. We’ll be making an official announcement about that soon. In the mean time, I hope that this has helped open your eyes to some fun ways to enhance a project and clean it up a bit. It is my plan to add lots of new features to this program and chronicle those on this blog in addition to all my other articles.

Source Code