SQLAlchemy in multithread scoped_session

Question

I am trying to use SQLAlchemy in a multithreaded environment but am getting odd errors. Here is the situation:

Thread 1 initiates all SQLAlchemy objects (Engine, Models, scoped_session, etc.)

Thread 2 then attempts to use the scoped_session object to query on the database. Unfortunately, from Thread 2 errors are thrown.

I created a simple test case to illustrate what I am trying to accomplish:

import sqlalchemy
from sqlalchemy import Column, Integer, String, Boolean, desc, asc, func

from sqlalchemy import create_engine
sqlEngine = create_engine('sqlite:///:memory:',echo=False)
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class SimpleTable(Base):
    __tablename__ = 'SimpleTable'

    orderID = Column(Integer, primary_key=True)
    field = Column(Integer)

    def __repr__(self):
    return "<Simple Table: %s>" % self.field

session_factory = sessionmaker(bind=sqlEngine)
Session = scoped_session(session_factory)
Base.metadata.create_all(sqlEngine)

for i in xrange(10):
    var = SimpleTable(field=10)
    Session.add(var)

Session.query(SimpleTable).all()
Session.commit()

from threading import Thread

class TestThread(Thread):

    def run(self):
    print "Running test thread..."
    try:
        print 'Grabbing data from thread:'+str(Session.query(SimpleTable).all())
    except BaseException as e:
        print e
    print "Done running test thread..."

TestThread().start()

print 'Grabbing data outside thread:'+str(Session.query(SimpleTable).all())

Here is the output with the error message:

Running test thread...
(OperationalError) no such table: SimpleTable u'SELECT "SimpleTable"."orderID" AS "SimpleTable_orderID", "SimpleTable".field AS "SimpleTable_field" \nFROM "SimpleTable"' ()
Done running test thread...
Grabbing data outside thread:[<Simple Table: 10>, <Simple Table: 10>, <Simple Table: 10>, <Simple Table: 10>, <Simple Table: 10>, <Simple Table: 10>, <Simple Table: 10>, <Simple Table: 10>, <Simple Table: 10>, <Simple Table: 10>]

The table doesn't exist for some reason. I have read and reread the documentation as well as multiple posts around the internet, and it seems scoped_session exists for the exact case described above. Could someone enlighten me?

Answer 1

I have faced the same situation. I suspect "in memory" db does not support scoped_session. When I switched to a normal file based db, the problem went away.