Adding a table column to an existing table

If we use an object-relational mapper (ORM), such as SQLAlchemy, we map classes to tables and class attributes to table columns. Often, due to new business requirements, we need to add a table column and corresponding class attribute. We will probably need to populate the column immediately after adding it.

If we deal with a production database, then probably you do not have direct access. Luckily, we can generate SQL with Alembic, which a database administrator can review.

Getting ready

Refer to the Setting up database migration scripts recipe.

How to do it…

Alembic has its own versioning system, which requires extra tables. It also creates a versions directory under the alembic directory with generated Python code files. We need to specify the types of change necessary for migration in these files:

1. Create a new revision, as follows:

   $ alembic revision -m "Add a column"
   

2. Open the generated Python file (for instance, 27218d73000_add_a_column...