Jivan Jivan - 6 months ago 22
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
default
for
user.id
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.

Answer

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))
    # ...
Comments