使用 Flask 中的 SQLAlchemy 会话会引发“在一个线程中创建的 SQLite 对象只能在同一个线程中使用"问题. [英] Using SQLAlchemy session from Flask raises "SQLite objects created in a thread can only be used in that same thread"

问题描述

我有一个 Flask 视图，它使用 SQLAlchemy 来查询和显示一些博客文章.我正在使用 mod_wsgi 运行我的应用程序.这个视图在我第一次进入页面时有效，但下次返回 500 错误.回溯显示错误 ProgrammingError: SQLite objects created in a thread can only be used in the same thread. 为什么我会收到这个错误，我该如何解决?

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

推荐答案

如果您跨线程共享会话，SQLAlchemy(在本例中也是 SQLite)将无法工作.您可能没有明确使用线程，但是 mod_wsgi 是，并且您已经定义了一个全局 session 对象.要么使用 scoped_session 来处理创建唯一每个线程的会话.

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()
    ...

最好使用 Flask-SQLAlchemy 为您处理这些和其他事情.SQLAlchemy 文档建议您使用集成库，而不是自己执行此操作.

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.

这篇关于使用 Flask 中的 SQLAlchemy 会话会引发“在一个线程中创建的 SQLite 对象只能在同一个线程中使用"问题.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！