Sunday, January 19, 2014

Preserve query_property() behaviour when using Session instead of ScopedSession

I feel so much hungry about posting something :)

Here is another tip I've come up yesterday night.

What I like about the sqlalchemy.orm.scoping.ScopedSession is its query_property method. Which helps you to setup your classes so it is easier to write queries.

Here is a code showing a normal Sqlalchemy query without the query_propety trick:
from stalker import db, User
db.session.query(User).filter_by(name='Erkan Ozgur Yilmsz').first()
Now with using the query_property, this query can be simplified to:
from stalker import User
User.query.filter_by(name='Erkan Ozgur Yilmsz').first()
As you see I don't need to import the session object and also it is easier to understand and read.

So I was doing that with Stalker.

But last night I've recognized that I'm doing something wrong by using a ScopedSession in our PyQt4/PySide UI scripts, we were getting a lot of errors from our Postgresql database, stating that there are already max connections reached.

Basically, what a ScopedSession does is to have a pool of connections to the database and use them over and over again until the application is closed. It is very good for a Web server (like Stalker Pyramid, more on that later) but it is not correct or at least not correct to use in the same way that I was using it in Stalker.

So I've decided to switch back to the regular Session class which is created by the session_maker() utility. But the down side was that by using a Session object I will loose what query_property was doing for me.

The solution is; to copy/paste and customize the ScopedSession.query_property method for the Session class and Stalker. Here is the customized version that uses the regular Session instance from Stalker:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import class_mapper
from sqlalchemy.orm.exc import UnmappedClassError

from stalker.models import make_plural

def query_property():
    """A ripped off version of the sqlalchemy.orm.ScopedSession.query_property
    class query(object):
        def __get__(s, instance, owner):
                mapper = class_mapper(owner)
                if mapper:
                    # session's configured query class
                    from stalker import db
                    return db.session.query(mapper)
            except UnmappedClassError:
                return None
    return query()

class ORMClass(object):
    """The base of the Base class

    query = query_property()

    def plural_class_name(self):
        """the plural name of this class
        return make_plural(self.__class__.__name__)

Base = declarative_base(cls=ORMClass)

No comments: