Flask中SQLite插入语句重复执行的原因排查咨询

This is a super common gotcha with Flask's debug mode—let me break it down for you:

The Root Cause: Flask Debug Mode's Auto-Reloader #

When you start your Flask app with debug=True, Werkzeug (the WSGI server under Flask) spins up two processes:

• The main process that runs your app
• A secondary "reloader" process that watches for file changes to auto-restart your app whenever you edit code

The problem is that all code outside the if __name__ == '__main__': block gets executed by both processes. Your SQLite insert code is sitting directly in the global scope of your script, so it runs once when the main process starts, then again when the reloader process fires up.

This Explains Your Exact Symptoms #

Let's tie this to what you saw:

1. With UNIQUE constraints: The main process inserts the record successfully. Then the reloader process tries to run the same insert, hits the UNIQUE constraint, throws sqlite3.IntegrityError, but the first insert was already committed—so you end up with 1 record in the DB, even though the app crashed.
2. Without UNIQUE constraints: Both processes run the insert without issues, so you get 2 identical records in the DB.

How to Fix It #

The quick fix is to move your database insert logic inside the if __name__ == '__main__': block. This block only runs in the main process, not the reloader. Here's how your code should look:

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)
migrate = Migrate(app, db)

@app.route('/')
def index():
    return render_template('index.html')

if __name__ == '__main__':
    # Move your insert here so it only runs once
    conn = sqlite3.connect('WO1.db')
    with conn:
        cur1 = conn.cursor()
        cur1.execute("insert into workorder (Title, Link, Status) values (?,?,?)", ('en24433', 'www.reddit.com', 'Not Completed'))
    if conn:
        conn.close()
    # Now start the app
    app.run(host='localhost', port=8080, debug=True)

Bonus: Alternative for Initialization #

If this insert is meant to be one-time setup data, consider using Flask-SQLAlchemy's migration tools (like Alembic, which comes with Flask-Migrate) instead of running inserts directly in your startup script. Migrations are designed for managing database schema and initial data in a controlled way.

Or, if you don't need auto-reloading during development, you can just set debug=False—but that's usually not ideal when you're actively coding.

内容的提问来源于stack exchange，提问作者will mannix