CODEWITHSUNDEEP
pythonormpylonspyramidstorm-orm
dave
dave

Reputation: 7867

Trying to get the Storm ORM to work with Pyramid but I'm having troubles with threads

Here's the relevant code from my websites __init__.py

from site.models import initialise_sql

def main(global_config, **settings):
    """ This function returns a Pyramid WSGI application """

    initialise_sql(settings['database.url'])

    return config.make_wsgi_app()

Here's my models/__init__.py

from storm.locals import *

from datetime import datetime

DBSession = None

class Tables:
    """ A simple namespace for all the table objects in our database """

    class Users(object):

        __storm_table__ = 'users'

        user_id      = Int(primary=True)
        username     = RawStr()
        password     = RawStr()
        date_created = DateTime(default=datetime.now())

def initialise_sql(db_uri):
    """ Initialise the database """

    global DBSession

    database  = create_database(db_uri)
    DBSession = Store(database)

And here's my users model:

def create_account(username, password):

    user = Tables.Users()
    user.username = username
    user.password = password

    DBSession.add(user)
    DBSession.flush()

From what I've read of the Storm documentation, this is all correct. The problem is, when the create_account function is called from my view callable, I get the following exception thrown:

ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id -1220417856 and this is thread id -1239418000

I wasn't even aware Pyramid was threading the application :/

How can I fix this?

Upvotes: 0

Views: 729

Answers (2)

Michael Merickel
Michael Merickel

Reputation: 23331

The web server you're using is multithreaded, not Pyramid itself. It's safer to think about your application in terms of handling requests. Basically you'll have one request per thread at any given point in time, so each active request should be using a different connection to the database. This is handled trivially using the scoped_session from SQLAlchemy, but you can also do this in a manner similar to the way this Pyramid tutorial does it with raw SQLite connections.

The basic idea here is that it establishes a new connection the database in a NewRequest subscriber, so you're guaranteed there won't be any threading issues from sharing connections across requests. You can also use this paradigm with a connection pool which I'm sure any decent ORM is supplying you.

Update After looking at Storm more carefully, I don't see a ton of support for connection pooling, but there is the ZStorm package which will integrate Storm with the transaction manager that Pyramid uses. This does implement some pooling mechanisms that would make your life easier.

Upvotes: 1

sdupton
sdupton

Reputation: 1879

global DBSession

is your problem. The Storm manual is very explicit about this.

You need to use a thread-local storage. threading.local is the answer:

import threading
tlocal = threading.local()

def initialise_sql(db_uri):
    global tlocal
    db = create_database(db_uri)
    tlocal.dbsession = Store(db)

#etc

Upvotes: 0

Related Questions