事倍功半是蠢蛋17 python下使用alembic对SQLAlchemy数据库管理

本博客基于cursor问答

省流：

首先安装

pip install alembic

初始化

alembic init migrations

改ini配置

自动生成迁移脚本

alembic revision --autogenerate -m "我这次修改了：：XXX"

执行迁移-升级到最新版本

alembic upgrade head

csdn教程
https://blog.csdn.net/penriver/article/details/141438518

class GitHubProjectCardModel(ModelBase):
    """GitHub项目表"""
    __tablename__ = "github_projects_cards"

    project_id: Mapped[str] = mapped_column(String(32), comment='项目id')
    title: Mapped[str] = mapped_column(String(256), comment='标题')
    content: Mapped[str] = mapped_column(Text, comment='内容')

概述

Q：我现在面临这样一个问题：我的sqlalchemy深受本地修改线上报错的困扰：
我本地修改了数据库格式如table=xxx这样的表结构修改了字段
导致跟docker中的数据对不上，我需要一种同步软件格式类似java里的flyway还是什么
能让每次修改表结构后不需要清空数据库重设表（丢数据肯定不行）

A:我理解你的问题。对于SQLAlchemy的数据库迁移管理，我推荐使用Alembic，它是SQLAlchemy作者开发的数据库迁移工具，类似于Flyway。以下是具体实施步骤：

安装并配置

# 首先安装
pip install alembic
# 初始化
alembic init migrations
# migrations文件夹里是生成的文件

#我不修改alembic.ini配置，在env里配置因为我要按docker来所以直接设成空，本地你自己用应该直接改ini里的url就行 应该吧
#直接看他ini里注释文档解释的都很详细。

# ***注意alembic.ini中不能有中文***

#sqlalchemy.url = driver://user:password@localhost/mydb_name
sqlalchemy.url =

设置数据库连接URL 动态从其他地方 如docker中获取:

直接看这个教程 ： https://www.cnblogs.com/goldsunshine/p/18660250
ps::生产环境开发环境不同连接密码，所以点开migrations修改env.py如下适配我自己的docker

from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context
from app.models.model_base import ModelBase  #我自己的的modelbase




# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config


def get_url():
    # 环境变量映射 从DOCER里拿
    env_mapping = {
        "postgresql_user": "POSTGRESQL_USER",
        "postgresql_password": "POSTGRESQL_PASSWORD",
        "postgresql_host": "POSTGRESQL_HOST",
        "postgresql_port": "POSTGRESQL_PORT",
        "postgresql_db": "POSTGRESQL_DB"
    }

    return "postgresql://{}:{}@{}:{}/{}".format(
        os.getenv(env_mapping["postgresql_user"], "user"),
        os.getenv(env_mapping["postgresql_password"], "password"),
        os.getenv(env_mapping["postgresql_host"], "postgres"),
        os.getenv(env_mapping["postgresql_port"], "5432"),
        os.getenv(env_mapping["postgresql_db"], "mydb")
    )

# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
# target_metadata = None
target_metadata = ModelBase.metadata  #我自己的的modelbase


# ============================以下都是自动配置，需要改的是offline和online里都获取url================================
# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def run_migrations_offline() -> None:
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """

    # NEW
    url = get_url()

    # OLD
    # url = config.get_main_option("sqlalchemy.url")


    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """

    # NEW
    configuration = config.get_section(config.config_ini_section)
    configuration["sqlalchemy.url"] = get_url()  # 使用get_url()设置数据库URL

    connectable = engine_from_config(
        configuration,
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    # OLD

    # connectable = engine_from_config(
    #     config.get_section(config.config_ini_section, {}),
    #     prefix="sqlalchemy.",
    #     poolclass=pool.NullPool,
    # )

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata
        )

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

接下来自动生成迁移脚本

# 自动生成迁移脚本
alembic revision --autogenerate -m "我这次修改了：：XXX"

# 或者手动创建空迁移脚本
alembic revision -m "description of changes"

# 执行迁移-升级到最新版本
alembic upgrade head

# 升级到指定版本
alembic upgrade <revision>

# 回滚一个版本
alembic downgrade -1