美文网首页
使用alembic配置迁移多个数据库

使用alembic配置迁移多个数据库

作者: 空山晚来秋 | 来源:发表于2022-10-11 13:56 被阅读0次

    基本说明

    alembic --help命令查看使用说明

    (py38-fastapi) ➜  面向 alembic --help
    usage: alembic [-h] [--version] [-c CONFIG] [-n NAME] [-x X] [--raiseerr]
                   {branches,current,downgrade,edit,ensure_version,heads,history,init,list_templates,merge,revision,show,stamp,upgrade}
                   ...
    
    positional arguments:
      {branches,current,downgrade,edit,ensure_version,heads,history,init,list_templates,merge,revision,show,stamp,upgrade}
        branches            Show current branch points.
        current             Display the current revision for a database.
        downgrade           Revert to a previous version.
        edit                Edit revision script(s) using $EDITOR.
        ensure_version      Create the alembic version table if it doesn't exist already .
        heads               Show current available heads in the script directory.
        history             List changeset scripts in chronological order.
        init                Initialize a new scripts directory.
        list_templates      List available templates.
        merge               Merge two revisions together. Creates a new migration file.
        revision            Create a new revision file.
        show                Show the revision(s) denoted by the given symbol.
        stamp               'stamp' the revision table with the given revision; don't run any
                            migrations.
        upgrade             Upgrade to a later version.
    
    optional arguments:
      -h, --help            show this help message and exit
      --version             show program's version number and exit
      -c CONFIG, --config CONFIG
                            Alternate config file; defaults to value of ALEMBIC_CONFIG environment
                            variable, or "alembic.ini"
      -n NAME, --name NAME  Name of section in .ini file to use for Alembic config
      -x X                  Additional arguments consumed by custom env.py scripts, e.g. -x
                            setting1=somesetting -x setting2=somesetting
      --raiseerr            Raise a full stack trace on error
    

    看着与git比较类似,有init merge不用的是有upgrade

    -c命令,指自定义ini文件的名字

    因此若生成指定的ini文件,及使用指定的ini文件进行迁移,需要使用如下命令:

    # 初始化
    alembic -c test1.ini init test1
    # 提交更改
    alembic -c test1.ini revision --autogenerate -m "height commit"
    # 执行更改
    alembic  -c test1.ini upgrade head
    

    更详细的执行可查看SQLAlchemy + alembic版本迁移数据库 - 简书 (jianshu.com)

    若需要不同的迁移目录对应不同的数据库,需要对数据库的Base进行分割

    拆分Base

    说是拆分Base,其实是将建表的类继承自不同的对象基类

    # models/base.py
    
    from sqlalchemy.ext.declarative import declarative_base
    # 创建对象基类
    Base1 = declarative_base()
    Base2 = declarative_base()
    

    创建表时继承对应的基类即可

    举例:User表

    # models/user/models.py
    
    from sqlalchemy import Column, String
    
    from models.base import Base2 as Base
    
    
    class User(Base):
        """
        用户表
        """
        __tablename__ = "users"
        __table_args__ = {"comment": "用户表"}
        user_id = Column(String(80), primary_key=True, unique=True, comment="用户id")
        username = Column(String(30), nullable=False, unique=True, comment="登陆用户名")
        create_time = Column(String(20), default="", comment="创建时间")
    
        def __repr__(self):
            return f"<User(username={self.username})>"
    
    

    Product表:

    # models/product/models.py
    
    
    from sqlalchemy import Column, Integer, String
    
    from models.base import Base1 as Base
    
    
    class ProductInfo(Base):
        """
        用户表
        """
        __tablename__ = "product_info"
        __table_args__ = {"comment": "项目表"}
        product_id = Column(Integer, primary_key=True,
                            autoincrement=True, comment="项目id")
        user_id = Column(String(80), index=True, comment="创建项目用户id")
        product_name = Column(String(30), nullable=False,
                              unique=True, comment="项目名")
        create_time = Column(String(20), default="", comment="创建时间")
    
        def __repr__(self):
            return (
                f"<ProductInfo(product_name={self.product_name}, user_id={self.user_id})>"
            )
    
    

    以下解决方案来自 alembic + sqlalchemy 多个数据库答案 - 爱码网 (likecs.com)

    Alembic 提供了一个模板来处理多个数据库:

    alembic init --template multidb ./multidb
    
    1. 修改ini
    #alembic.ini
    
    databases = engine1, engine2
    
    [engine1]
    sqlalchemy.url = driver://user:pass@localhost/dbname
    
    [engine2]
    sqlalchemy.url = driver://user:pass@localhost/dbname2
    
    
    1. multidb/env.py 中的target_metadata 指向对应模型【有更改】
    # multidb/env.py
    
    # 添加以下代码
    import os
    import sys
    # 把当前项目路径加入到path中
    sys.path.append(os.path.dirname(os.path.dirname(__file__)))
    from models.base import Base1
    from models.base import Base2
    
    target_metadata = {
          'engine1':Base1.metadata,
          'engine2':Base2.metadata
    }
    
    1. 测试Alembic设置
    alembic revision --autogenerate -m "test"
    
    1. 使用Alembic迁移
    alembic revision --autogenerate -m "one more db"
    alembic upgrade head
    

    其它模式

    也可以基于不同的base生成不同的迁移目录,做到脚本配置与数据库一一对应。当下我没有此需求,故未做探究

    相关文章

      网友评论

          本文标题:使用alembic配置迁移多个数据库

          本文链接:https://www.haomeiwen.com/subject/qmbtzrtx.html