how to set unique value for each row on alembic migration

Question

I added a unique attribute uid for MyModel model:

class MyModel(db.Model):
...
    uid = db.Column(db.String(50), nullable=False)
...
    __table_args__ = (UniqueConstraint('uid', name='unique_uid'),)

I have a migration:

def upgrade():
    op.add_column('mymodel', sa.Column('uid', sa.String(length=50), nullable=True))

    mymodel = table('mymodel', column('uid'))
    op.execute(mymodel.update().values(uid=generate_uid()))
    op.create_unique_constraint('unique_uid', 'mymodel', ['uid'])

    op.alter_column(
        table_name='mymodel',
        column_name='uid',
        nullable=False
    )

On run db upgrade i've got an error:

...
psycopg2.IntegrityError: could not create unique index "unique_uid"
DETAIL:  Key (uid)=(c92U6txA2) is duplicated.

How to set unique value for each row on op.execute(mymodel.update().values(uid=generate_uid()))?

$ pip freeze
alembic==0.8.6
Flask==0.10.1
Flask-Fixtures==0.3.3
Flask-Login==0.3.2
Flask-Migrate==1.8.0
Flask-Script==2.0.5
Flask-SQLAlchemy==2.1
itsdangerous==0.24
Jinja2==2.8
Mako==1.0.4
MarkupSafe==0.23
psycopg2==2.6.1
python-editor==1.0
requests==2.10.0
SQLAlchemy==1.0.13
Werkzeug==0.11.9

I think that is not important. But nonetheless: generate_uid() returned unique value like a 1Er45gh78k; DB is PostgreSQL — Alexey Egorov, Jul 22 '16 at 04:30

score 4 · Accepted Answer · answered Jul 23 '16 at 10:20

The possible solution:

from sqlalchemy.orm import Session
from alembic import op
import sqlalchemy as sa

def upgrade():
    conn = op.get_bind()
    session = Session(bind=conn)

    op.add_column('mymodel', sa.Column('uid', sa.String(length=50), nullable=True))

    for item in session.query(MyModel).filter_by(uid=None):
        item.uid = generate_uid()
    session.commit()

    op.create_unique_constraint('unique_uid', 'mymodel', ['uid'])

    op.alter_column(
        table_name='mymodel',
        column_name='uid',
        nullable=False
    )

I get this error if i do update on all columns ERROR [root] Error: All MySQL CHANGE/MODIFY COLUMN operations require the existing type. — Srikanth Jeeva, Apr 30 '20 at 08:36

score 0 · Answer 2 · answered Jul 21 '16 at 23:48

0

The migration script that you wrote puts the same uid on all the rows, the generate_uid() function is called once, and its result is then added into all the rows. So then when the index is created you get a duplicated key error.

Depending on what your uids are and the database you maybe able to write a single SQL statement that creates unique ids for all your rows, but the safe bet would be to do a loop and update each row separately.

answered Jul 21 '16 at 23:48

Miguel Grinberg

65,299
14
133
152

I would like to get a solution only within the migration code (if it possible). Without writing SQL code. – Alexey Egorov Jul 22 '16 at 04:27
@Meatbot you may not realize it, but the `op.execute` line you have in your migration script is actually running SQL. You don't have to write SQL if you don't want to, use SQLAlchemy if you prefer, that does not change the answer. – Miguel Grinberg Jul 22 '16 at 06:17
I have already a function that returns unique value for uid column - generate_uid(). And I don't want clone this function into the DB. I want to apply this function in my migration. – Alexey Egorov Jul 22 '16 at 07:46
Write a Python loop that calls this function once per row. – Miguel Grinberg Jul 22 '16 at 16:38

how to set unique value for each row on alembic migration

2 Answers2