flask web 5.8节，使用Role.query.all(),报错Traceback (most recent call last)

最新推荐文章于 2022-07-18 18:00:51 发布

最新推荐文章于 2022-07-18 18:00:51 发布 · 1.8k 阅读

文章标签：

#python #开发工具 #数据库

python3 专栏收录该内容

14 篇文章

订阅专栏

本文记录了在使用Flask框架结合SQLite数据库进行Web应用开发时遇到的多线程访问错误，并提供了解决方案。通过在提交数据前加入try...except...finally...结构确保即使发生异常也能关闭数据库会话。

这几天一直在学习<<flask web开发>>，在学习到5.8节，操作数据库时，一直报错，连续三天，一直在找原因，各种百度，最后发现解决方法有二：

一、更换成mysql数据库，这种解决方案不太好，因为我们没有根据实例做出结果来；

第二种方法：hello.py

import os
from flask import Flask, render_template, session, redirect, url_for, flash
from flask_bootstrap import Bootstrap
from flask_moment import Moment
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired
from flask_sqlalchemy import SQLAlchemy

basedir = os.path.abspath(os.path.dirname(__file__))
app = Flask(__name__)
app.config['SECRET_KEY'] = 'hard to guess string'
# app.config['SQLALCHEMY_DATABASE_URI'] = "sqlite:///D:/PycharmProjects/hello/data.sqlite"
# app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
#
# app.config['SQLALCHEMY_DATABASE_URI'] =\
#     'sqlite:///' + os.path.join(basedir, 'data.sqlite')
# app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
# app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:root@localhost:3306/test?charset=utf8mb4'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'data.sqlite')
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
bootstrap = Bootstrap(app)
moment = Moment(app)
db = SQLAlchemy(app)


class Role(db.Model):
    __tablename__ = 'roles'
id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    users = db.relationship('User', backref='role')

    def __repr__(self):
        return '<Role %r>' % self.name

class User(db.Model):
    __tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True, index=True)
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))

    def __repr__(self):
        return '<User %r>' % self.username
    # def __repr__(self):
    #     return '%s (%r, %r,%r)' % (self.__class__.__name__, self.id, self.username, self.role_id)
class NameForm(FlaskForm):
    name = StringField('What is your name?', validators=[DataRequired()])
    submit = SubmitField('Submit')


@app.errorhandler(404)
def page_not_found(e):
    return render_template('404.html'), 404
@app.errorhandler(500)
def internal_server_error(e):
    return render_template('500.html'), 500
@app.route('/', methods=['GET', 'POST'])
def index():
    form = NameForm()
    if form.validate_on_submit():
        old_name = session.get('name')
        if old_name is not None and old_name != form.name.data:
            flash('Looks like you have changed your name!')
        session['name'] = form.name.data
        return redirect(url_for('index'))
    return render_template('index.html', form=form, name=session.get('name'))

if __name__ == '__main__':
    app.run(debug=True)

然后，在PyCharm开发工具中的Python Console中开始输入命令

PyDev console: starting.
Python 3.6.2 (v3.6.2:5fd33b5, Jul 8 2017, 04:57:36) [MSC v.1900 64 bit (AMD64)] on win32
from hello import db
db.drop_all()
db.create_all()
from hello import Role, User
admin_role = Role(name='Admin')
mod_role = Role(name='Moderator')
user_role= Role(name='User')
user_john = User(username='john',role=admin_role)
user_susan = User(username='susan',role=user_role)
user_david = User(username='david',role=user_role)
db.session.add_all([admin_role, mod_role, user_role,user_john, user_susan, user_david])
db.session.commit()
print(admin_role.id)
1
Role.query.all()
Traceback (most recent call last):
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1127, in _execute_context
    context = constructor(dialect, self, conn, *args)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\engine\default.py", line 637, in _init_compiled
    self.cursor = self.create_cursor()
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\engine\default.py", line 952, in create_cursor
    return self._dbapi_connection.cursor()
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\pool.py", line 970, in cursor
    return self.connection.cursor(*args, **kwargs)
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 5668 and this is thread id 9904
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<input>", line 1, in <module>
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\orm\query.py", line 2726, in all
    return list(self)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\orm\query.py", line 2878, in __iter__
    return self._execute_and_instances(context)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\orm\query.py", line 2901, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1132, in _execute_context
    None, None)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1127, in _execute_context
    context = constructor(dialect, self, conn, *args)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\engine\default.py", line 637, in _init_compiled
    self.cursor = self.create_cursor()
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\engine\default.py", line 952, in create_cursor
    return self._dbapi_connection.cursor()
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\pool.py", line 970, in cursor
    return self.connection.cursor(*args, **kwargs)
sqlalchemy.exc.ProgrammingError: (sqlite3.ProgrammingError) SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 5668 and this is thread id 9904 [SQL: 'SELECT roles.id AS roles_id, roles.name AS roles_name \nFROM roles'] [parameters: [{}]] (Background on this error at: http://sqlalche.me/e/f405)

看到这些是不是疯了，没办法重新写，

~~~~~

db.session.commit()
Traceback (most recent call last):
File "<input>", line 1, in <module>
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\orm\scoping.py", line 153, in do
    return getattr(self.registry(), name)(*args, **kwargs)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\orm\session.py", line 937, in commit
    self.transaction.commit()
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\orm\session.py", line 459, in commit
    self._assert_active(prepared_ok=True)
File "D:\PycharmProjects\hello\venv\lib\site-packages\sqlalchemy\orm\session.py", line 276, in _assert_active
    % self._rollback_exception
sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.ProgrammingError) SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 5700 and this is thread id 556 [SQL: 'INSERT INTO roles (name) VALUES (?)'] [parameters: [{'name': 'Admin'}]] (Background on this error at: http://sqlalche.me/e/f405)

谁能告诉我，这又是怎么回事？

from hello import db
db.drop_all()
db.create_all()
from hello import Role, User
admin_role = Role(name='Admin')
mod_role = Role(name='Moderator')
user_role= Role(name='User')
user_john = User(username='john',role=admin_role)
user_susan = User(username='susan',role=user_role)
user_david = User(username='david',role=user_role)
db.session.add_all([admin_role, mod_role, user_role,user_john, user_susan, user_david])
try:
    db.session.commit()
except:
    db.session.rollback()
    raise
finally:
    db.session.close()
    #注意此处有空格
Role.query.all()
[<Role 'Admin'>, <Role 'Moderator'>, <Role 'User'>]

最后居然好了，