本文主要是介绍flask_migrate结合geoalchemy2迁移postgis，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
代码示例
创建app.py
from flask import Flask
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy
from geoalchemy2 import Geometryapp = Flask(__name__)app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://postgres:111111@localhost:5432/postgres"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = Falsedb = SQLAlchemy(app)
Migrate(app, db)class Point(db.Model):id = db.Column(db.Integer, primary_key=True, autoincrement=True)geom = db.Column(Geometry("POINT", srid=4326, spatial_index=False))# 正确创建空间索引的方式：指定spatial_index=False，并使用以下方式创建空间索引，否则使用flask_migrate会重复创建索引导致报错
# 参考链接：https://github.com/geoalchemy/geoalchemy2/issues/137#issuecomment-1022413828
db.Index("idx_point_geom",Target.__table__.c.geom,postgresql_using='gist',
)@app.route('/')
def index():return "<h1>hello</h1>"if __name__ == '__main__':app.run(debug=True)
迁移
flask db init
输出：
Creating directory /test/migrations ... done
Creating directory /test/migrations/versions ... done
Generating /test/migrations/alembic.ini ... done
Generating /test/migrations/env.py ... done
Generating /test/migrations/README ... done
Generating /test/migrations/script.py.mako ... done
Please edit configuration/connection/logging settings in '/test/migrations/alembic.ini' before proceeding.
flask db migrate
输出：
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'point'
INFO [alembic.autogenerate.compare] Detected removed table 'spatial_ref_sys'
Generating /test/migrations/versions/419c1d992227_.py ... done
flask db upgrade
输出：
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 419c1d992227, empty message...File "/test/migrations/versions/419c1d992227_.py", line 23, in upgradesa.Column('geom', geoalchemy2.types.Geometry(geometry_type='POINT', srid=4326, from_text='ST_GeomFromEWKT', name='geometry'), nullable=True),
NameError: name 'geoalchemy2' is not defined
错误分析
发现/test/migrations/versions/419c1d992227_.py中的geoalchemy2没有导入，并且spatial_ref_sys表也不应该被删除，否则将无法使用空间扩展
解决方案
方法一（推荐）：使用geoalchemy2中的alembic_helpers
...
from geoalchemy2.alembic_helpers import include_object, render_itemfrom app import create_app
from app.model import dbapp = create_app()# compare_server_default=True
migrate = Migrate(app, db, compare_type=True, include_object=include_object, render_item=render_item)
方法二：手动编辑
每次迁移完后修改迁移脚本，如下：
再执行flask db upgrade，输出：
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 419c1d992227, empty message
迁移成功
修改模板
在script.py.mako中添加import geoalchemy2，如下所示：
"""${message}Revision ID: ${up_revision}
Revises: ${down_revision | comma,n}
Create Date: ${create_date}"""
from alembic import op
import sqlalchemy as sa
import geoalchemy2
${imports if imports else ""}# revision identifiers, used by Alembic.
revision = ${repr(up_revision)}
down_revision = ${repr(down_revision)}
branch_labels = ${repr(branch_labels)}
depends_on = ${repr(depends_on)}def upgrade():${upgrades if upgrades else "pass"}def downgrade():${downgrades if downgrades else "pass"}
在env.py中添加include_object函数，忽略spatial_ref_sys表，并在context.configure中添加include_object=include_object，如下所示：
...def include_object(object, name, type_, reflected, compare_to):if type_ == 'table' and name in ('spatial_ref_sys'):return Falsereturn Truedef run_migrations_online():"""Run migrations in 'online' mode.In this scenario we need to create an Engineand associate a connection with the context."""# this callback is used to prevent an auto-migration from being generated# when there are no changes to the schema# reference: http://alembic.zzzcomputing.com/en/latest/cookbook.htmldef process_revision_directives(context, revision, directives):if getattr(config.cmd_opts, 'autogenerate', False):script = directives[0]if script.upgrade_ops.is_empty():directives[:] = []logger.info('No changes in schema detected.')connectable = current_app.extensions['migrate'].db.enginewith connectable.connect() as connection:context.configure(connection=connection,target_metadata=target_metadata,process_revision_directives=process_revision_directives,include_object=include_object,**current_app.extensions['migrate'].configure_args)with context.begin_transaction():context.run_migrations()...
此方法只在数据库迁移前修改一次，随后可直接进行数据库迁移。
升级
接下来我们来添加一个Polygon表
class Polygon(db.Model):id = db.Column(db.Integer, primary_key=True, autoincrement=True)geom = db.Column(Geometry("POLYGON", srid=4326))迁移后脚本如下：
def upgrade():# ### commands auto generated by Alembic - please adjust! ###op.create_table('polygon',sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),sa.Column('geom', geoalchemy2.types.Geometry(geometry_type='POLYGON', srid=4326, from_text='ST_GeomFromEWKT', name='geometry'), nullable=True),sa.PrimaryKeyConstraint('id'))op.drop_index('idx_point_geom', table_name='point')# ### end Alembic commands ###
其中op.drop_index('idx_point_geom', table_name='point')显然是不对的，因为此操作将会删除空间索引，这不是我们想要的。
回到env.py，添加index判断：
def include_object(object, name, type_, reflected, compare_to):if type_ == 'table' and name in ('spatial_ref_sys'):return Falseif type_ == 'index' and name.endswith("_geom"):return Falsereturn True
总结
- 在生产环境中，
migrations目录应该作为持久化数据永久存储! - 对于永久不变的数据库迁移推荐使用
db.create_all()，免去了复杂的配置 - 对于需要添加字段或者新增表的数据库迁移推荐使用
修改模板方式，一次配置永久有效
参考链接：
- https://alembic.sqlalchemy.org/en/latest/autogenerate.html#omitting-table-names-from-the-autogenerate-process
- https://geoalchemy-2.readthedocs.io/en/latest/alembic.html
这篇关于flask_migrate结合geoalchemy2迁移postgis的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
