CODEWITHSUNDEEP
pythonpython-3.xflaskflask-sqlalchemyflask-login
alex3025
alex3025

Reputation: 139

Flask SQLAlchemy IntegrityError "UNIQUE constraint failed" when trying to update values

I have a flask website with a user authentication system. I'm getting the error when I try to commit the changes to the database.

class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
surname = db.Column(db.String)
email = db.Column(db.String, unique=True)
password = db.Column(db.String)
registered_on = db.Column(db.DateTime)
admin = db.Column(db.Boolean)
avatar = db.Column(db.String)
confirmed = db.Column(db.Boolean)
confirmed_on = db.Column(db.DateTime)

cloud_storage_actived = db.Column(db.String)

def __init__(self, name=None, surname=None, email=None, password=None, user_id=None, confirmed=None, confirmed_on=None, admin=False, avatar='/static/avatars/default.png', cloud_storage_actived=False):
    self.id = user_id
    self.name = name
    self.surname = surname
    self.email = email
    self.password = password
    self.admin = admin
    self.avatar = avatar
    self.confirmed = confirmed
    self.confirmed_on = confirmed_on
    self.registered_on = datetime.datetime.now()

    self.cloud_storage_actived = cloud_storage_actived

def is_authenticated(self):
    return True

user = User.query.filter_by(email=current_user.email).first_or_404()
user.query.update(dict(name=request.form['name'], 
surname=request.form['surname'], email=request.form['email'], avatar=url_for('static', filename='avatars/') + filename if filename else current_user.avatar))
db.session.commit()

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.email
[SQL: UPDATE user SET name=?, surname=?, email=?, avatar=?]
[parameters: ('MyName', 'MySurname', 'MyEmail', '/static/avatars/1.gif')]
(Background on this error at: http://sqlalche.me/e/gkpj)

I searched on internet how to do this, but I didn't found the answer.

Upvotes: 1

Views: 5786

Answers (1)

Ron Nabuurs
Ron Nabuurs

Reputation: 1556

It's because you update all the users records to the same values. Your query should have a where statement.

The query you are running is 

UPDATE user SET name='MyName', surname='MySurname', email='MyEmail', avatar='/static/avatars/1.gif'

but it should actually be something like:

UPDATE user SET name='MyName', surname='MySurname', email='MyEmail', avatar='/static/avatars/1.gif' WHERE id = someid

You should only update one user by using a where statement:

user = User.query.filter_by(email=current_user.email).first_or_404()
user.query.update(dict(name=request.form['name'], surname=request.form['surname'], email=request.form['email'], avatar=url_for('static', filename='avatars/') + filename if filename else current_user.avatar)).where(User.email == current_user.email)
db.session.commit()

but because you are using a model you can also update the model

user = User.query.filter_by(email=current_user.email).first_or_404()
user.name = request.form['name']
user.surname = request.form['surname']
user.email = request.form['email']
user.avatar = url_for('static', filename='avatars/') + filename if filename else current_user.avatar
db.session.flush()
db.session.commit()

Upvotes: 1

Related Questions