Alembic SQLAlchemy 进行数据迁移

一.模块安装

创建Python虚拟环境：https://blog.csdn.net/chenliang1038/article/details/129295440

(py379_env) PS D:\PersonalProject\TG-Test> pip install psycopg2
(py379_env) PS D:\PersonalProject\TG-Test> pip install SQLAlchemy
(py379_env) PS D:\PersonalProject\TG-Test> pip install alembic

二、声明基类模型

2-1.创建model.py文件

(py379_env) PS D:\PersonalProject\TG-Test> type nul > model.py

2-2.创建基类模型（添加在model.py）

from sqlalchemy.ext.declarative import declarative_base
ModelBase = declarative_base()

2-3.完整model.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, ForeignKey, Column
from sqlalchemy.types import Unicode, Integer, DateTime, String
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from flask_migrate import Migrate
ModelBase = declarative_base()  # 使用declarative_base()创建基类模型，其他映射类，都是继承自它
engine = create_engine('postgresql+psycopg2://postgres:qaz123@127.0.0.1:5432/test')
Session = sessionmaker(bind=engine)   # 绑定引擎 创建会话
session = Session()  # 实例会话对象
class UserInfo(ModelBase):    # 只要声明<类>的继承<映射基类>，表示为映射类
    __tablename__ = 'auth_user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(25), name="username")
    password = Column(String(25), name="password")

三、alembic初始化

3-1.执行初始化命令

(py379_env) PS D:\PersonalProject\TG-Test> alembic init alembic

3-2.修改alembic配置文件(alembic.ini)

# sqlalchemy.url替换为你的数据库链接，相当于引擎
sqlalchemy.url = postgresql://postgres:qaz123@127.0.0.1:5432/test

3-3.配置文件内容

[alembic]
script_location = alembic
prepend_sys_path = .
version_path_separator = os  # Use os.pathsep. Default configuration used for new projects.
sqlalchemy.url = postgresql://postgres:qaz123@127.0.0.1:5432/test
[post_write_hooks]
[loggers]
keys = root,sqlalchemy,alembic
[handlers]
keys = console
[formatters]
keys = generic
[logger_root]
level = WARN
handlers = console
qualname =
[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine
[logger_alembic]
level = INFO
handlers =
qualname = alembic
[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic
[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

3-4.修改alembic.env.py文件内容

# target_metadata = None 找到这，并替换余下内容
import sys
from os.path import abspath, dirname
from model import ModelBase   # 这里是model.py模板 ModelBase基类模型
# 告诉alembic 你的表数据在哪
sys.path.append(dirname(dirname(abspath(__file__))))
target_metadata = ModelBase.metadata

四、数据迁移

alembic revision --autogenerate -m "test"  # 自定义迁移文件名
# 在versions文件夹在，生成<..._test.py>的文件
alembic upgrade head

数据库