使用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.

要进行初始迁移（如果已经有数据库），请临时指向一个空数据库，然后运行智能修订--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屋！