使用 SQLAlchemy 和 Alembic 检查数据库中是否存在表列 [英] Check if a table column exists in the database using SQLAlchemy and Alembic

问题描述

我使用 Alembic 作为迁移工具，我正在一个已经更新的数据库上启动以下伪脚本(Alembic 没有修订条目，数据库架构是最新的).

I'm using Alembic as migration tool and I'm launching the following pseudo script on an already updated database (no revision entries for Alembic, the database schema is just up to date).

revision = '1067fd2d11c8'
down_revision = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.add_column('box', sa.Column('has_data', sa.Boolean, server_default='0'))


def downgrade():
    pass

它只在 PostgreSQL 后面出现以下错误(MySQL 都很好):

It gives me the following error only with PostgreSQL behind (it's all good with MySQL):

INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [root] (ProgrammingError) ERREUR:  la colonne « has_data » de la relation « box » existe déjà

最后一行表示 has_data 列已经存在.

Last line means the column has_data already exists.

我想在op.add_column之前检查该列是否存在.

I want to check that the column exists before op.add_column.

推荐答案

最简单的答案是不要尝试这样做.相反，让您的 Alembic 迁移代表数据库的完整布局.然后，您所做的任何迁移都将基于对现有数据库的更改.

The easiest answer is not to try to do this. Instead, make your Alembic migrations represent the full layout of the database. Then any migrations you make will be based off the changes to the existing database.

如果您已经有一个数据库，要开始迁移，请暂时指向一个空数据库并运行 alembic revision --autogenerate -m "base".然后，指向实际的数据库并运行alembic stamp head 表示数据库的当前状态由最新的迁移表示，而不实际运行它.

To make a starting migration if you already have a database, temporarily point at an empty database and run alembic revision --autogenerate -m "base". Then, point back at the actual database and run alembic stamp head to say that the current state of the database is represented by the latest migration, without actually running it.

如果您出于某种原因不想这样做，您可以选择不使用 --autogenerate 而是生成您想要的操作填充的空修订.Alembic 不会阻止您这样做，只是不太方便.

If you don't want to do that for some reason, you can choose not to use --autogenerate and instead generate empty revisions that you fill in with the operations you want. Alembic won't stop you from doing this, it's just much less convenient.

这篇关于使用 SQLAlchemy 和 Alembic 检查数据库中是否存在表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！