SQLAlchemy-Migrate or Alembic deletes data when renaming model

user_671150 0
Viewed 5502

I am using SQLAlchemy-Migrate to manage migrations for my PostgreSQL database. I changed the __tablename__ for a model, and running the migration changed the name in the database, but all the rows in the table were deleted. How can I rename a model without deleting data?

class Contract(db.Model):
    __tablename__ = 'contract'
    id = db.Column(db.Integer, primary_key=True)
    is_valid = db.Column(db.Boolean, default=IS_VALID)

I rename it from contract to contracts and get this migration:

def upgrade(migrate_engine):
    pre_meta.bind = migrate_engine
    post_meta.bind = migrate_engine
    pre_meta.tables['contract'].drop()
    post_meta.tables['contracts'].create()

It drops the old table and creates a new one. I never examined other migration scripts because they always ran without dropping the data.

sqlalchemy-migrate alembic sqlalchemy python
2 Answers

I had a similar problem, for some readon, when using

op.rename_table("old_name", "new_name")

I kept getting

psycopg2.errors.DuplicateTable: relation "new_name" already exists

In the end, I used

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # op.drop_table('uploaded_resource')
    op.drop_table('sessions')
    
    op.execute('DROP TABLE IF EXISTS new_name')
    op.execute('ALTER TABLE old_name RENAME TO new_name')
    op.execute('ALTER SEQUENCE old_name_id_seq RENAME TO new_name_id_seq')
    op.execute('ALTER INDEX old_name_pkey RENAME TO new_name_pkey')
    ....

and

def downgrade():
    ...
    op.execute('ALTER TABLE new_name RENAME TO old_name')
    op.execute('ALTER SEQUENCE new_name_id_seq RENAME TO old_name_id_seq')
    op.execute('ALTER INDEX new_name_pkey RENAME TO old_name_pkey')

Which is less then ideal.

This also helped a fair bit https://petegraham.co.uk/rename-postgres-table-with-alembic/

user_68553340
Related