sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: users.login

Question

I am using sqlite database and I declare the model as this:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    login = db.Column(db.String(80), unique=True)
    password = db.Column(db.String(64))

    def is_authenticated(self):
        return True

    def is_active(self):
        return True

    def is_anonymous(self):
        return False

    def get_id(self):
        return self.id

    # Required for administrative interface
    def __unicode__(self):
        return self.username

And I have add a Model instance like this:

admin = admin.Admin(app, 'Example: Auth', index_view=MyAdminIndexView(), base_template='my_master.html')

admin.add_view(MyModelView(User, db.session))

db.drop_all()
db.create_all()

if db.session.query(User).filter_by(login='passport').count() <= 1:
    user = User(login = 'passport', password = 'password')
    db.session.add(user)
    db.session.commit()

However, if I comment the db.drop_all(), it will occur an error which is:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: users.login [SQL: 'INSERT INTO users (login, password) VALUES (?, ?)'] [parameters: ('passport', 'password')]

And if do not comment the db.drop_all(), everything is fun. Because there are other tables on this database, I do not want to drop all the tables when I run it. Are there any other solutions to fix that?

Thanks a lot.

sebastian · Accepted Answer · 2016-09-02 09:13:01Z

5

You're comparing the count to <= 1. So you'll attempt to create the new user even though it already exists. So it should probably simply be < 1:

if db.session.query(User).filter_by(login='passport').count() < 1:
    user = User(login = 'passport', password = 'password')
    db.session.add(user)
    db.session.commit()

answered Sep 2, 2016 at 9:13

sebastian

9,71628 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

turkus · Accepted Answer · 2016-09-02 09:11:41Z

0

You can just run query, let's say:

db.session.query(User).delete()
db.session.commit()

OR

User.query.delete()
db.session.commit()

in that case, you will just remove everything from User table.

answered Sep 2, 2016 at 9:11

turkus

4,9633 gold badges28 silver badges31 bronze badges

Comments

gsn · Accepted Answer · 2022-05-30 22:40:24Z

0

I was running into the same error.

if db.session.query(User).filter_by(login='passport').count() <= 1:
db.drop_all()
db.create_all()
user = User(login = 'passport', password = 'password')
db.session.add(user)
db.session.commit()

answered May 30, 2022 at 22:40

gsn

1

1 Comment

Sachith Muhandiram Over a year ago

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review

Collectives™ on Stack Overflow

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: users.login

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related