Integrity Error - Flask & SQLAlchemy

Question

This is my first time playing around with SQLAlchemy and I'm trying to just insert an item into a table and have flask print out that item. However, I keep on getting an integrity error like this when I don't catch the exception:

sqlalchemy.exc.IntegrityError: (IntegrityError) column email is not unique u'INSERT INTO user (username, email) VALUES (?, ?)' ('test', '[email protected]')

I have the following code:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.exc import IntegrityError

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///test.db"

db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return "<User %r>" % self.username

@app.route("/")
def index():
    return User.query.all() # I don't think this will work

if __name__ == "__main__":
    db.create_all()
    try:
        x = User("test", "[email protected]")
        db.session.add(x)
        db.session.commit()
        print "worked"
    except IntegrityError:
        print "Not Working"

    app.run(debug=True)

Would anyone mind giving a hand? I've done some research on IntegrityError but most of the documentation is on handling the error and not preventing it.

Answer 1

Okay, the reason for this is a bit tricky :)

The error message does indeed mean that this email address is already in the DB. But why the heck is this? Because the debug flag tells the built-in server to also use the reloader. And this reloader basically makes the app run twice on the first start. You can see that in the output:

$ python2 server.py
worked
 * Running on http://127.0.0.1:5000/
 * Restarting with reloader
Not Working

There are multiple ways to get around that. Quick'n'dirty would be to just disable the reloader:

app.run(debug=True, use_reloader=False)

A better way would be to not populate the test DB that way. Instead you could create a separate custom command using Flask's CLI.