Flask-SQLAlchemy:sqlite3 IntegrityError [英] Flask-SQLAlchemy: sqlite3 IntegrityError

问题描述

我正在创建一个应用程序来替换浏览器中的当前标签管理器.我已经在组之间创建了一个简单的一对多关系-表主题和选项卡-表选项卡.我希望能够自动删除主题的孩子.这是我目前拥有的:

I'm creating an application to replace current tab managers in the browser. I've created a simple one-to-many relationship between groups - table Topic, and tabs - table Tab. I want to be able to automatically delete topic's children if I delete it. This is what I currently have:

from flask import request, redirect, url_for, render_template, Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import exc
from flask_whooshee import Whooshee
from datetime import datetime

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///database.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)
whooshee = Whooshee(app)

@whooshee.register_model('topic_name')
class Topic(db.Model):
    __tablename__ = 'topic'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    topic_name = db.Column(db.String, unique=True, nullable=False)
    topic_created = db.Column(db.DateTime, default=datetime.utcnow)
    topic_changed = db.Column(db.DateTime, default=datetime.utcnow)
    topic_tabs = db.relationship('Tab', backref='topic', cascade='all, delete-orphan', lazy='dynamic')

@whooshee.register_model('tab_name', 'tab_link', 'tab_description')
class Tab(db.Model):
    __tablename__ = 'tab'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    tab_name = db.Column(db.String, nullable=False)
    tab_link = db.Column(db.String, unique=True, nullable=False)
    tab_favicon = db.Column(db.String)
    tab_description = db.Column(db.String)
    tab_created = db.Column(db.DateTime, default=datetime.utcnow)
    tab_changed = db.Column(db.DateTime, default=datetime.utcnow)
    topic_id = db.Column(db.Integer, db.ForeignKey('topic.id'))

@app.route('/', methods=['GET'])
def index():
    return "Test"

if __name__ == '__main__':
    db.create_all()
    try:
        db.session.commit()
    except exc.IntegrityError as e:
        print(str(e))
        db.session.rollback()

    top = Topic(topic_name='Test')
    db.session.add(top)
    try:
        db.session.commit()
    except exc.IntegrityError as e:
        print(str(e))
        db.session.rollback()

    top2 = Topic(topic_name='Test2')
    db.session.add(top2)
    try:
        db.session.commit()
    except exc.IntegrityError as e:
        print(str(e))
        db.session.rollback()

    t1 = Tab(tab_name='t1', tab_link='t1l', tab_favicon='t1f', tab_description='t1d', topic_id=top.id)
    t2 = Tab(tab_name='t2', tab_link='t2l', tab_favicon='t2f', tab_description='t2d', topic_id=top.id)
    t3 = Tab(tab_name='t3', tab_link='t3l', tab_favicon='t3f', tab_description='t3d', topic_id=top.id)

    db.session.add(t1)
    db.session.add(t2)
    db.session.add(t3)
    try:
        db.session.commit()
    except exc.IntegrityError as e:
        print(str(e))
        db.session.rollback()
    import uuid
    app.secret_key = str(uuid.uuid4())
    app.run(debug=True)

问题是，当我尝试在数据库中添加某些内容时却出现完整性错误，但我不确定会出什么问题.如果停止服务器，我还想将以前的记录保存在数据库中，该如何实现呢?

The problem is that I get integrity errors, when I'm trying to add something in the database and I'm not sure what can go wrong. I also want to save previous records in the database if I stop the server, how do I implement that as well?

UPD :即使我完全删除数据库，也会发生以下行为.我只是在空文件夹中运行脚本python script_name.py后收到此错误.

UPD: the behaviour below happens even if I delete my database entirely. I get this error after simply running the script python script_name.py in an empty folder.

UPD2 :如果有人需要检查数据库是否存在，则有一个特殊的帮助器功能

UPD2: if anyone needs to check whether a database exists, there is a special helper function https://sqlalchemy-utils.readthedocs.io/en/latest/database_helpers.html#database-exists

(sqlite3.IntegrityError)唯一约束失败:topic.topic_name [SQL:'INSERT INTO topic(topic_name，topic_created，topic_changed)VALUES(?，?，?)'] [参数:('Test'，'2018- 09-03 05:21:08.728564'，'2018-09-03 05:21:08.728564')](此错误的背景位于: http://sqlalche.me/e/gkpj )

(sqlite3.IntegrityError)唯一约束失败:topic.topic_name [SQL:'INSERT INTO topic(topic_name，topic_created，topic_changed)VALUES(?，?，?)'] [参数:('Test2'，'2018- 09-03 05:21:08.730562'，'2018-09-03 05:21:08.730562')](此错误的背景位于: http://sqlalche.me/e/gkpj )

(sqlite3.IntegrityError) UNIQUE constraint failed: topic.topic_name [SQL: 'INSERT INTO topic (topic_name, topic_created, topic_changed) VALUES (?, ?, ?)'] [parameters: ('Test2', '2018-09-03 05:21:08.730562', '2018-09-03 05:21:08.730562')] (Background on this error at: http://sqlalche.me/e/gkpj)

(sqlite3.IntegrityError)唯一约束失败:tab.tab_link [SQL:'INSERT INTO选项卡(tab_name，tab_link，tab_favicon，tab_description，tab_created，tab_changed，topic_id)值(?，?，?，?，?，?，? ，?)'] [参数:('t1'，'t1l'，'t1f'，'t1d'，'2018-09-03 05:21:08.733561'，'2018-09-03 05:21:08.733561' ，无)](此错误的背景位于: http://sqlalche.me/e/gkpj )

(sqlite3.IntegrityError) UNIQUE constraint failed: tab.tab_link [SQL: 'INSERT INTO tab (tab_name, tab_link, tab_favicon, tab_description, tab_created, tab_changed, topic_id) VALUES (?, ?, ?, ?, ?, ?, ?)'] [parameters: ('t1', 't1l', 't1f', 't1d', '2018-09-03 05:21:08.733561', '2018-09-03 05:21:08.733561', None)] (Background on this error at: http://sqlalche.me/e/gkpj)

推荐答案

您的脚本是WSGI应用程序，因此以__main__身份运行.如果将debug=True用作app.run()的参数，则服务器将启动，设置调试模式:on并重新启动with stat，再次执行__name__ == '__main__'块(以及每次对脚本进行更改并在保存时将其保存一次)服务器正在运行).

Your script is the WSGI application, hence runs as __main__. If you use debug=True as argument to app.run() the server will start, set debug mode: on and restart with stat, executing the __name__ == '__main__' block again (and again every time you make changes to your script and save it while the server is running).

在空/已删除的数据库上启动应用程序时，第一次执行__name__ == '__main__'块时，它将创建该DB，并在该块中插入您创建的两个Topic和三个Tab对象.然后，它设置调试模式并重新启动，再次执行该块，并尝试第二次插入这五个对象.

When you start your app on an empty/deleted DB, the first time the __name__ == '__main__' block is executed, it creates the DB and inserts the two Topic and three Tab objects you create in that block. Then it sets debug mode and restarts, again executing that block, attempting to insert these five objects a second time.

from flask import Flask

app = Flask(__name__)

@app.route('/')
def hello_world():
    return "Hello, World!"

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

输出:

__main__ # <-- fist time around it creates DB and objects
 * Serving Flask app "main" (lazy loading)
 * Environment: production
   WARNING: Do not use the development server in a production environment.
   Use a production WSGI server instead.
 * Debug mode: on
 * Restarting with stat # <-- re-executing your script
__main__ # <-- trying to create the same objects again, violating the UNIQUE constraint
 * Debugger is active!
 * Debugger PIN: 302-544-855
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

这篇关于Flask-SQLAlchemy:sqlite3 IntegrityError的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！