Flask-SQLAlchemy and Alembic: Best practices for migrating databases in Python web applications (Part 2)

Flask-SQLAlchemy and Alembic: Best practices for migrating databases in Python web applications (Part 2)

In the previous article, we discussed how Flask-SQLAlchemy and Alembic work together . This article will mainly introduce how to add and remove columns in some basic data models, and modify the types or constraints of some columns. These changes are very common during actual project development.

Adding and deleting columns

When using Flask-SQLAlchemy and Alembic for database migration, adding and removing table columns is very common. To demonstrate this process, we will add some new columns to the following example Person model.

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    age = db.Column(db.Integer)

    def __repr__(self):
        return '<Person %r>' % self.name

Suppose we want to add two new columns, one is the date of birth (birthdate) and a Boolean column indicating whether the person is married (is_married). We can use the following command to generate a migration script:

$ alembic revision -m "add birthdate, is_married columns to person"

Next, we need to modify the generated .py migration script file to add new columns. We can use add_column() in upgrade() function.

from alembic import op
import sqlalchemy as sa

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('person', sa.Column('birthdate', sa.Date(), nullable=True))
    op.add_column('person', sa.Column('is_married', sa.Boolean(), nullable=True))
    # ### end Alembic commands ###

For deleting columns, we can use the drop_column() function in the corresponding destructor degrade() to delete the column from the database model.

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('person', 'birthdate')
    op.drop_column('person', 'is_married')
    # ### end Alembic commands ###

The complete sample code for this migration script can be found below.

"""add birthdate, is_married columns to person"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'eab2c4f1c9fb'
down_revision = '7cfae59c2402'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('person', sa.Column('birthdate', sa.Date(), nullable=True))
    op.add_column('person', sa.Column('is_married', sa.Boolean(), nullable=True))
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('person', 'birthdate')
    op.drop_column('person', 'is_married')
    # ### end Alembic commands ###

Column type change and constraint modification

In many cases, we need to modify the column type and constraints. Suppose we want to change the age column type of the Person model from INTEGER to SMALLINT. We can use the alter_column() function in the generated .py migration script file to achieve this.

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('person', 'age', existing_type=sa.Integer(),
               type_=sa.SmallInteger(), nullable=True)
    # ### end Alembic commands ###

We can also modify the constraints on the column. Examining the Person model, we notice that there are no unique value constraints in the model. We can add unique value constraints for name and birthdate columns using the following code.

from alembic import op
import sqlalchemy as sa

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_unique_constraint(op.f('uq_person_name'), 'person', ['name'])
    op.create_unique_constraint(op.f('uq_person_birthdate'), 'person', ['birthdate'])
    # ### end Alembic commands ###

If we need to cancel the unique value constraint later, we can use the drop_constraint() function. For example:

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(op.f('uq_person_name'), 'person', type_='unique')
    op.drop_constraint(op.f('uq_person_birthdate'), 'person', type_='unique')
    # ### end Alembic commands ###

This article introduces some common types of database schema changes, such as adding and removing columns, changing column constraints, and changing column data types. It demonstrates best practices for database migration using Flask-SQLAlchemy and Alembic. This will help you manage your database schema more efficiently and share migration files more easily in a team environment.

Reference link:

• Flask-SQLAlchemy - https://flask-sqlalchemy.palletsprojects.com/
• Alembic - https://alembic.sqlalchemy. org/en/latest/

The above is the detailed content of Flask-SQLAlchemy and Alembic: Best practices for migrating databases in Python web applications (Part 2). For more information, please follow other related articles on the PHP Chinese website!