Using Flask's SQLAlchemy Session Enhances "SQLite Objects Created in a Stream Can Only Be Used in the Same Stream"

Question

I have a Flask view that uses SQLAlchemy to query and display some blog posts. I am running my application using mod_wsgi. This view works the first time I go to the page, but the next time it returns a 500 error. The error ProgrammingError: SQLite objects created in a thread can only be used in that same thread. displayed in the trace ProgrammingError: SQLite objects created in a thread can only be used in that same thread. Why am I getting this error and how to 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

SQLAlchemy (and in this case, SQLite also) does not work if you share the session by thread. You cannot use streams explicitly, but mod_wsgi is, and you have defined a global session object. Or 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. SQLAlchemy docs recommend that you use the integration library rather than doing it yourself.

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

Also note that you should specify only one engine, session, etc. and import it to another place, rather than overriding it in each file, as the current code does.

Answer 2

Having taken the hint this answer SO> I looked for SA documents and found out that you can do this:

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

scoped_session not suitable for my case, since Flask-SQLAlchemy only accepts a string connection 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) ... 

Answer 3

It worked great in my case. Thanks reubano