How to rename column on MySQL without deleting existing data?

[Mike]

Setup:

mysql> SELECT version();

* 5.7.27-0ubuntu0.18.04.1

* Python 3.6.8

* SQLAlchemy 1.3.6

* Alembic 1.0.11

models.py:

class Experiments(db.Model):

    id = db.Column(db.Integer, primary_key=True)

    # country = db.Column(db.String(100))

    countryland = db.Column(db.String(100))

    insert_date = db.Column(db.DateTime, index=True,

        default=datetime.utcnow)

SQL data:

INSERT INTO experiments (country)

VALUES ('US'),

       ('Canada'),

       ('Mexico'),

       ('Brazil'),

       ('Argentina'),

       ('Spain'),

       ('Portugal'),

       ('France'),

       ('Germany'),

       ('UK');

migration file:

def upgrade():

    # ### commands auto generated by Alembic - please adjust! ###

    # op.add_column('experiments', sa.Column('countryland', sa.String(length=100), nullable=True))

    # op.drop_column('experiments', 'country')

    op.alter_column('experiments', column_name='country', new_column_name='countryland')

    # ### end Alembic commands ###

Problem:

I want to rename the column name from "country" to "countryland". But I'm getting this error in my terminal can anybody help me please?

$ flask db upgrade

[2019-08-27 11:43:26,155] INFO in __init__: microblog startup

INFO  [alembic.runtime.migration] Context impl MySQLImpl.

INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

INFO  [alembic.runtime.migration] Running upgrade 1f866a4fa5a0 -> 69e7cfcf87f1, Testing column rename 02.

ERROR [root] Error: All MySQL CHANGE/MODIFY COLUMN operations require the existing type.

Electronically yours,

Mike

[Mike Bayer]

hi there -

for MySQL the alter_column() operation requires that you pass the existing information about the column as well:

op.alter_column('table", column_name='a', new_column_name='b', existing_type=String, existing_nullable=False, existing_server_default=...)

see https://alembic.sqlalchemy.org/en/latest/ops.html?highlight=alter_column#alembic.operations.Operations.alter_column for the full list of parameters.

    # ### end Alembic commands ###

Problem:

I want to rename the column name from "country" to "countryland". But I'm getting this error in my terminal can anybody help me please?

$ flask db upgrade

[2019-08-27 11:43:26,155] INFO in __init__: microblog startup

INFO  [alembic.runtime.migration] Context impl MySQLImpl.

INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

INFO  [alembic.runtime.migration] Running upgrade 1f866a4fa5a0 -> 69e7cfcf87f1, Testing column rename 02.

ERROR [root] Error: All MySQL CHANGE/MODIFY COLUMN operations require the existing type.

Electronically yours,

Mike

--

You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/0e9b482c-0f31-4492-b1b8-df97bc84adbd%40googlegroups.com.

[Mike]

Thanks it worked!

models.py

class Experiments(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    kountry = db.Column(db.String(100))
    #countryland = db.Column(db.String(100))

migration file

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    # op.add_column('experiments', sa.Column('kountry', sa.String(length=100), nullable=True))
    # op.drop_column('experiments', 'countryland')

    op.alter_column('experiments',
                     column_name = 'countryland',
                     new_column_name = 'kountry',
                     existing_type = sa.String(100),
                     existing_nullable = False,
                     existing_server_default = 'Greenland')

    # ### end Alembic commands ###

Electronically yours,

Mike