Python SQLAlchemy Alembic的使用

大家好，我是练习时长两年半的大数据练习生，喜欢数学，AI，大数据。
写博客是为了总结，分享，自娱自乐。希望写出的东西会对自己，对别人都有价值！
废话不多说，现在是个终身学习的时代，开始学习了！奥力给！干了兄弟们！
是时候展现真正的技术了：

本文是一篇翻译型学习笔记，原文如下链接。
https://www.pythoncentral.io/migrate-sqlalchemy-databases-alembic/

Alembic

Alembic is a lightweight database migration tool for SQLAlchemy.
Alembic是一个服务于SQLAlchemy的轻量级数据迁移工具。

It is created by the author of SQLAlchemy and it has become the de-facto standard tool to perform migrations on SQLAlchemy backed databases.
它是SQLAlchemy的作者创造的，并且已经成为迁移SQLAlchemy支持的数据库的标准方法。

Database Migration in SQLAlchemy
SQLAlchemy中的数据迁移

A database migration usually changes the schema of a database, such as adding a column or a constraint, adding a table or updating a table. It’s often performed using raw SQL wrapped in a transaction so that it can be rolled back if something went wrong during the migration. In this article, we are going to use a sample database to demonstrate how to write Alembic migration scripts for a SQLAlchemy database.
数据库的迁移经常改变数据库的架构，比如增加一列或者增加一个约束，添加表或者修改表。它经常使用包裹在事务中的原始SQL语句，所以当迁移过程中错误发生时它可以被回滚，在这篇文章里，我们将会使用一个简单的数据库去实验如何写出用于SQLAlchemy数据库的Alembic迁移脚本

To migrate a SQLAlchemy database, we add an Alembic migration script for the intended migration, perform the migration, update the model definition and then start using the database under the migrated schema. These steps sound like a lot, but they are quite straightforward to do, which is illustrated in the following section.
为了迁移一个SQLAlchemy的数据库，我们可以为了预期的迁移添加Alembic迁移脚本，执行迁移，更新模型定义然后开始使用迁移架构下的数据库，这些步骤听起来很多，但是它们做起来很简单，比如下面这些例子。

Sample Database Schema
样本数据库架构

Let’s create a SQLAlchemy database with a department and a employee table.
让我们用一个部门表和一个员工表创建一个SQLAlchemy数据库

import os
 
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
 
 
Base = declarative_base()
 
# 定义部门类
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
 
# 定义员工类
class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    hired_on = Column(DateTime, default=func.now())
 
 
db_name = 'alembic_sample.sqlite'
if os.path.exists(db_name):
    os.remove(db_name)
 
from sqlalchemy import create_engine
engine = create_engine('sqlite:///' + db_name)
 
from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

After the database alembic_sample.sqlite has been created, we realize we forgot to add a many-to-many relationship between Employee and Department.
数据库 alembic_sample.sqlite 创建完成后，我们意识到我们忘了在员工和部门之间添加一个多对多关系

Migration
迁移

Instead of changing the schema directly and then recreate the database from scratch, we choose to migrate the database using alembic. In order to do that, we install alembic, initialize an alembic environment, write a migration script to add the link table, perform the migration, and then use an updated model definition to access the database again.
相对于直接改变schema然后从头开始创建数据库，我们选择去使用Alembic迁移这个数据库，为了做到它，我们安装alembic，初始化一个alembic环境，写一个迁移脚本去添加链接表，执行迁移，然后使用一个更新过的模型定义去再次访问数据库.

$ alembic init alembic
Creating directory /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic ... done
Creating directory /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/versions ... done
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/env.pyc ... done
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic.ini ... done
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/script.py.mako ... done
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/env.py ... done
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/README ... done
Please edit configuration/connection/logging settings in '/home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic.ini' before proceeding.
 
$ vim alembic.ini # Change the line thats starts with "sqlalchemy.url" into "sqlalchemy.url = sqlite:///alembic_sample.sqlite"
 
$ alembic current
INFO [alembic.migration] Context impl SQLiteImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
Current revision for sqlite:///alembic_sample.sqlite: None
 
$ alembic revision -m "add department_employee_link"
Generating /home/vagrant/python2-workspace/pythoncentral/sqlalchemy_series/alembic/alembic/versions/1da977fd3e6e_add_department_employee_link.py ... done
 
$ alembic upgrade head
INFO [alembic.migration] Context impl SQLiteImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
INFO [alembic.migration] Running upgrade None -> 1da977fd3e6e, add department_employee_link
 
$ alembic current
INFO [alembic.migration] Context impl SQLiteImpl.
INFO [alembic.migration] Will assume non-transactional DDL.
Current revision for sqlite:///alembic_sample.sqlite: None -> 1da977fd3e6e (head), add department_employee_link

The migration script is as follows:
迁移脚本内容如下：

'''
add department_employee_link
 
Revision ID: 1da977fd3e6e
Revises: None
Create Date: 2014-10-23 22:38:42.894194
 
'''
 
# revision identifiers, used by Alembic.
revision = '1da977fd3e6e'
down_revision = None
 
from alembic import op
import sqlalchemy as sa
 
 
def upgrade():
    op.create_table(
        'department_employee_link',
        sa.Column(
            'department_id', sa.Integer,
            sa.ForeignKey('department.id'), primary_key=True
        ),
        sa.Column(
            'employee_id', sa.Integer,
            sa.ForeignKey('employee.id'), primary_key=True
        )
    )
 
 
def downgrade():
    op.drop_table(
        'department_employee_link'
    )

Now that the database alembic_sample.sqlite has been upgraded, we can use an updated piece of model code to access the upgraded database.
现在数据库alembic_sample.sqlite已经被改造升级了，我们可以使用一个更新的模型代码区访问升级过的数据库

import os
 
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
 
 
Base = declarative_base()
 
 
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    employees = relationship(
        'Employee',
        secondary='department_employee_link'
    )
 
 
class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    hired_on = Column(DateTime, default=func.now())
    departments = relationship(
        Department,
        secondary='department_employee_link'
    )
 
 
class DepartmentEmployeeLink(Base):
    __tablename__ = 'department_employee_link'
    department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
    employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
 
 
db_name = 'alembic_sample.sqlite'
 
from sqlalchemy import create_engine
engine = create_engine('sqlite:///' + db_name)
 
from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.bind = engine
s = session()
IT = Department(name='IT')
Financial = Department(name='Financial')
s.add(IT)
s.add(Financial)
cathy = Employee(name='Cathy')
marry = Employee(name='Marry')
john = Employee(name='John')
s.add(cathy)
s.add(marry)
s.add(john)
cathy.departments.append(Financial)
marry.departments.append(Financial)
john.departments.append(IT)
s.commit()
s.close()

Notice that we did not delete the database alembic_sample.sqlite but instead performed a migration to add a link table instead. After the migration, the relationship Department.employees and Employee.departments are working as expected.
注意到我们没有删除数据库 alembic_sample.sqlite而是执行了一个迁移去添加了一个链接表，在迁移之后，Department.employees 和 Employee.departments都像预期那样正常工作了

Summary
总结

Since Alembic is a lightweight database migration tool built specifically for SQLAlchemy, it allows you to re-use the same kind of database model APIs to perform simple migrations. However, it’s not an do-it-all-for-you tool. For very database specific migration, such as adding a trigger function in PostgreSQL, a raw DDL statement is still required.
Alembic是一个为了SQLAlchemy专门建造的轻量级数据库迁移工具，它让你能重用有些数据库模型API去执行简单的迁移。然而，它不是一个万能的工具。针对特定数据库的迁移，比如在PostgreSQL中添加一个trigger函数，一个原始的DDL语句仍然是需要的。

要想完全理解这篇博客的内容，可能还需要如下前置知识点：

• Python 类的基础操作
• SQLAlchemy 基础操作

小伙伴们！相信看到这里的你一定有所收获！
如果我哪里写错欢迎评论区来喷
如果觉得对你有帮助请给个赞哦亲！爛爛爛爛爛爛
爛爛爛最后引用名言一句：我们无论遇到什么困难，都不要怕，微笑着面对它！消除恐惧的最好办法就是面对恐惧！加油！奥力给！