SQLAlchemy: Adding an UUID column to a MySQL database-CodePudding

I need to add a new UUID column to an existing SQLAlchemy / MySQL table where.

For doing so I added in my database model:

class MyTable(db.Model):
    uid = db.Column(db.BINARY(16), nullable=False, unique=True, default=uuid.uuid4)

Doing so generates the following alembic upgrade code which of course does not work as the default value if the new column is null:

op.add_column('my_table', sa.Column('uid', sa.BINARY(length=16), nullable=True))
op.create_unique_constraint(None, 'my_table', ['uid'])

I tried to extend the db.Column(db.BINARY(16), nullable=False, unique=True, default=uuid.uuid4) definition with an appropriate server_default=... parameter but wasn't able to find a parameter that generates for each row a new random UUID.

How to add a new column and generate for all existing rows a new random and unique UUID value?

A solution that uses sa.String instead of sa.BINARY would also be acceptable.

CodePudding user response：

In the end I manually created the necessary UPDATE statements in the alembic update file so existing rows are assigned a unique UID.

For new entries default=uuid.uuid4 in the SQLAlchemy column definition is sufficient.

Note that the MySQL UUID() function generates timestamp based UUID values for the existing records, and new records created via Python/SQLAlchemy use uuid.uuid4 which generates a v4 (random) UUID. So both are UUIDs but you will see which UUIDs were generated by UUID() as they only differ in the first block when generating them using the UPDATE statement.

Using binary column type

class MyTable(db.Model):
    uid = db.Column(db.BINARY(16), nullable=False, unique=True, default=uuid.uuid4)

def upgrade():
    op.add_column('my_table', sa.Column('uid', sa.BINARY(length=16), nullable=False))
    op.execute("UPDATE my_table SET uid = (SELECT(UUID_TO_BIN(UUID())))")
    op.alter_column('my_table', 'uid', existing_type=sa.BINARY(length=16), nullable=False)
    op.create_unique_constraint(None, 'my_table', ['uid'])

Using String/varchar column type

class MyTable(db.Model):
    uid = db.Column(db.String(36), nullable=False, unique=True, default=uuid.uuid4)

def upgrade():
    op.add_column('my_table', sa.Column('uid', sa.String(length=36), nullable=False))
    op.execute("UPDATE my_table SET uid = (SELECT(UUID()))")
    op.alter_column('my_table', 'uid', existing_type=sa.String(length=36), nullable=False)
    op.create_unique_constraint(None, 'my_table', ['uid'])

CodePudding user response：

As per mysqlalchemy's documentation on server_default:

A text() expression will be rendered as-is, without quotes: Column('y', DateTime, server_default = text('NOW()'))

y DATETIME DEFAULT NOW()

Based on this, your server_default definition should look like this:

server_default=text('(UUID_TO_BIN(UUID())))')

However, if your mysql version is earlier than v8.0.12, then you cannot use the server side default like this, you need to use either the default with setting uuid from python or you need a trigger as specified in the following SO question: MySQL set default id UUID