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 [{}] 

Answer 1

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.