Sqlalchemy data migration notes, sqlalchemy data migration
Sqlalchemy notes:
Content: Use Alembic to migrate a database
Alembic introduction:
SQLAlchemy is a very good ORM framework, but it does not have the database version control function. This is inconvenient. during development, it is inevitable to modify the data model, add a table, and modify a field, if you need to manually modify all of them, it will be a lot of trouble. It is better not to use SQLAlchemy. Here we will introduce Alembic, a database version control tool written by SQLAlchemy. Another tool is SQLAlchemy-Migrate, which makes Alembic more flexible during use.
1. alembic Installation
pip install alembic
2. alembic Initialization
Before use, initialize the project in the project root directory. After initialization, an alembic. ini configuration file and an alembic directory are generated. $ Alembic init alembic $ alembic init YOUR_ALEMBIC_DIR
3. Create the alembic_database directory under the module package directory,
The directory structure is as follows:
Myproject/| -- index. wsgi | --... | -- myapp/| -- _ init __. py | --... | -- alembic. ini # configuration file | -- alembic_database/| -- env. py # Running alembic will load this module | -- script. py. mako # template generated by migration script | --... | -- versions/# store the migration script, similar to the historical database
4. Create a model class
Create a module that uses SQLAlchemy to define a database.
5. modify the configuration file
(1) set the database connection in alembic. ini.
(2). In order to update the database using the model class, you must set in env. py to assign the target_metadata value to the metadata of the database (metadata ). The original configuration is as follows:
# Modify: target_metadata = None # change to: # Method 1: import osimport sysroot = OS. path. dirname (_ file _) + '/.. /.. /'# locate the project root directory print rootsys. path. append (root) from myapp import dbtarget_metadata = db. metadata # Method 2: import sys from OS. path import abspath, dirname sys. path. append (dirname (abspath (_ file _) from modules. models import Base target_metadata = Base. metadata
6. Create a data migration version
Use alembic revision-m + to create a database version. Because I provide model classes, you can use the-autogenerate parameter to automatically generate a migration script. Run (preparation: configure the alembic environment variable, and run the following command in the project directory ).
alembic revision –autogenerate -m”create_user_table”
Two functions are used for Database Upgrade and downgrade.
7. update the database
Upgrade the database to use alembic upgrade, and downgrade to use alembic downgrade. Update to the latest version.
$ alembic upgrade head
Alembic update operation command
Update Database
$ Alembic upgrade version
Update to the latest version.
alembic upgrade head
Downgrade Database
$ Alembic downgrade version
Offline Update (SQL generation)
Alembic upgrade version -- SQL> migration. SQL
Generate an SQL statement from a specific start version
Alembic upgrade version 1: Version 2 -- SQL> migration. SQL