CODEWITHSUNDEEP
pythonsqlalchemy
Saif ali Karedia
Saif ali Karedia

Reputation: 912

Sql-alchemy Integrity error

I have User model which is described as below:-

class User(db.Model, object):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(15), unique=True)
    email = db.Column(db.String(50), unique=True)
    password = db.Column(db.String(80))

    def is_authenticated(self):
        return True

    def is_active(self):
        return True

    def get_id(self):
        return unicode(self.id)

Now I have a registration form which is described as below:-

class RegisterForm(Form):
    email = StringField('email', validators=[InputRequired(), Email(message='Invalid email'), Length(max=50)])
    username = StringField('username', validators=[InputRequired(), Length(min=4, max=15)])
    password = PasswordField('password', validators=[InputRequired(), Length(min=8, max=80)])

NOW THE PROBLEM,

When I register/signup through web-app, id is automatically incremented and a new user is created. So I created user1 with id1 getting automatically assigned by sql-alchemy through registration form.

Now I use insert statement in database to insert a new user with a valid id. So I created user2 with id2 through insert statement in database.

If I try to create user3 through web-app, it shows sqlalchemy.exc.IntegrityError IntegrityError: (psycopg2.IntegrityError) duplicate key value violates unique constraint "users_pkey" DETAIL: Key (id)=(2) already exists.

Now again if I try to signup with user3, it creates a new user.

I tried using autoload to make flask know about recent id but it is not working.

Upvotes: 1

Views: 2711

Answers (2)

ScR4tCh
ScR4tCh

Reputation: 21

Just use a Sequence to auto-increment your id.

from sqlalchemy import Integer, Sequence

id = Column(Integer, Sequence('id_seq'), primary_key=True)

Upvotes: 0

Saif ali Karedia
Saif ali Karedia

Reputation: 912

A hack would be to query the maximum id from id column and increment it by 1 and assign it to the new user to prevent primary key collision.

max_id = session.query(User).order_by(User.id.desc()).first()
new_user = User(id=max_id+1, email=form.email.data, username=form.username.data, password=hashed_password)
db.session.add(new_user)
db.session.commit()

Upvotes: 1

Related Questions