Jivan Jivan - 5 months ago 18
Python Question

SQLAlchemy pushes the same default id on creation

I have this method to create a random user id:

import random
import string

def generate_id():
size = 9
chars = string.ascii_uppercase + string.ascii_lowercase + string.digits
return ''.join(random.SystemRandom().choice(chars) for _ in range(size))

And this SQLAlchemy object for a user:

class User(db.Model, UserMixin):
__tablename__ = 'user'
id = db.Column(db.String, primary_key=True, default=generate_id())
name = db.Column(db.String(255))
# ...

My problem is: sometimes it works well (some people manage to sign up and create a new user without any trouble) and sometimes the generated
has already been used, triggering this error:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.id

This error happens at least half of the time.


The problem is that you are calling the generate_id function in the model definition. This means that only the first time your application starts a value will be generated (once for each thread/server).

Since SQLAlchemy accepts callable defaults you can simply remove the () so it automatically gets called internally:

class User(db.Model, UserMixin):
    __tablename__ = 'user'
    id = db.Column(db.String, primary_key=True, default=generate_id)
    name = db.Column(db.String(255))
    # ...