Using SQLAlchemy session from Flask raises "SQLite objects created in a thread can only be used in that same thread"

Question

I have a Flask view which uses SQLAlchemy to query and display some blog posts. I am running my app using mod_wsgi. This view works the first time I go to the page, but returns a 500 error next time. The traceback shows the error ProgrammingError: SQLite objects created in a thread can only be used in that same thread. Why am I getting this error and how do I fix it?

views.py

engine = create_engine('sqlite:////var/www/homepage/blog.db')
Base.metadata.bind = engine
DBSession = sessionmaker(bind = engine)
session = DBSession()

@app.route('/blog')
@app.route('/blog.html')
def blog():
    entrys = session.query(Entry).order_by(desc(Entry.timestamp)).all()
    return render_template('blog.html', blog_entrys = entrys)

models.py:

class Entry(Base):
    __tablename__ = 'entry'

    id = Column(Integer, primary_key = True)

    title = Column(String(100), nullable = False)
    body = Column(String, nullable = False)
    timestamp = Column(DateTime, nullable = False)
    featured = Column(Boolean, nullable = False)

    comments = relationship('Comment')

    def is_featured(self):
        return self.featured


class Comment(Base):
    __tablename__ = 'comment'

    id = Column(Integer, primary_key = True)
    entry_id = Column(Integer, ForeignKey('entry.id'))

    text = Column(String(500), nullable = False)
    name = Column(String(80))


engine = create_engine('sqlite:////var/www/homepage/blog.db')
Base.metadata.create_all(engine)

Exception on /blog.html [GET]
Traceback (most recent call last):
  File "/usr/lib/python2.6/dist-packages/flask/app.py", line 861, in wsgi_app
    rv = self.dispatch_request()
  File "/usr/lib/python2.6/dist-packages/flask/app.py", line 696, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/var/www/homepage/webserver.py", line 38, in blog
    entrys = session.query(Entry).order_by(desc(Entry.timestamp)).all()
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 1453, in all
    return list(self)
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 1565, in __iter__
    return self._execute_and_instances(context)
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 1570, in _execute_and_instances
    mapper=self._mapper_zero_or_none())
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/orm/session.py", line 735, in execute
    clause, params or {})
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1157, in execute
    params)
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1235, in _execute_clauseelement
    parameters=params
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1348, in __create_execution_context
    None, None)
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", line 1343, in __create_execution_context
    connection=self, **kwargs)
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/engine/default.py", line 381, in __init__
    self.cursor = self.create_cursor()
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/engine/default.py", line 523, in create_cursor
    return self._connection.connection.cursor()
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/pool.py", line 383, in cursor
    c = self.connection.cursor(*args, **kwargs)
ProgrammingError: (ProgrammingError) SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140244498364160 and this is thread id 140244523542272 None [{}]

Solution 1

SQLAlchemy (and in this case SQLite also) doesn't work if you share a session across threads. You may not be using threads explicitly, but mod_wsgi is, and you've defined a global session object. Either use scoped_session to handle creating a unique session for each thread.

session = scoped_session(sessionmaker(bind=engine))

@app.teardown_request
def remove_session(ex=None):
    session.remove()

@app.route('/')
def example():
    item = session.query(MyModel).filter(...).all()
    ...

Preferably, use Flask-SQLAlchemy which handles this and other things for you. The SQLAlchemy docs recommend you use the integration library rather than doing this yourself.

db = SQLAlchemy(app)

@app.route('/')
def example():
    item = db.session.query(MyModel).filter(...).all()
    ...

Also note that you should only be defining the engine, session, etc. once and importing it elsewhere, rather than redefining it in each file like your current code does.

Solution 2

Taking a hint from this SO answer I searched SA docs and found out you can do this:

engine = create_engine('sqlite:////var/www/homepage/blog.db?check_same_thread=False')

scoped_session wasn't really suitable in my case since Flask-SQLAlchemy only takes a connection string argument:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy


class Config(object):
    SQLALCHEMY_DATABASE_URI = 'sqlite:///app.db?check_same_thread=False'


db = SQLAlchemy()


def create_app():
    app.config.from_object(Config)
    app = Flask(__name__)
    db.init_app(app)
    ...

According to sqlite3.connect:

By default, check_same_thread is True and only the creating thread may use the connection. If set False, the returned connection may be shared across multiple threads. When using multiple threads with the same connection writing operations should be serialized by the user to avoid data corruption.