记一次有关SQLAlchemy V2版本的shcema问题查找

已于 2024-06-11 11:08:49 修改
阅读量286 收藏
点赞数
分类专栏： 数据库 文章标签： postgresql postgis 数据库
于 2023-07-25 10:02:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/this_is_id/article/details/131910663
版权

SQLAlchemy 2.0版本已经发布有一段时间了，最近在测试的过程中发现在PostGIS数据库迁移过程中有一些问题，接下来回顾一下整个问题的修复过程，整个过程持续大概一周时间

提出问题

环境准备：

pip install -U alembic==1.11.1 Flask-Migrate==4.0.4 Flask-SQLAlchemy==3.0.5 GeoAlchemy2==0.14.0 SQLAlchemy==2.0.19 psycopg2-binary==2.9.3

docker run -d --name=postgis-test -p 5432:5432 -e POSTGRES_PASSWORD=123456 -e POSTGRES_DB=test  postgis/postgis:12-3.2

测试代码：

from flask import Flask
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy
from geoalchemy2 import Geometry
from geoalchemy2.alembic_helpers import include_object, render_item

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://postgres:123456@localhost:5432/test"
db = SQLAlchemy(app)

Migrate(app=app, db=db, compare_type=True, include_object=include_object, render_item=render_item)


class Lake(db.Model):
    __tablename__ = 'lake'
    id = db.Column(db.Integer, primary_key=True)
    geom = db.Column(
        Geometry(
            geometry_type='LINESTRING',
            srid=4326,
            spatial_index=True,
        )
    )

测试步骤：

  1. flask db init
  2. flask db migrate

输出：

(venv) D:\workspace\test\test_sqlalchemy>flask db init
Creating directory D:\workspace\test\test_sqlalchemy\migrations ...  done
Creating directory D:\workspace\test\test_sqlalchemy\migrations\versions ...  done
Generating D:\workspace\test\test_sqlalchemy\migrations\alembic.ini ...  done
Generating D:\workspace\test\test_sqlalchemy\migrations\env.py ...  done
Generating D:\workspace\test\test_sqlalchemy\migrations\README ...  done
Generating D:\workspace\test\test_sqlalchemy\migrations\script.py.mako ...  done
Please edit configuration/connection/logging settings in 'D:\\workspace\\test\\test_sqlalchemy\\migrations\\alembic.ini' before proceeding.

(venv) D:\workspace\test\test_sqlalchemy>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 'lake'
INFO  [alembic.autogenerate.compare] Detected added index 'idx_lake_geom' on '['geom']'
INFO  [alembic.ddl.postgresql] Detected sequence named 'faces_gid_seq' as owned by integer column 'faces(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_tiger_faces_countyfp' on 'faces'
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_tiger_faces_tfid' on 'faces'
INFO  [alembic.autogenerate.compare] Detected removed index 'tiger_faces_the_geom_gist' on 'faces'
INFO  [alembic.autogenerate.compare] Detected removed table 'faces'
INFO  [alembic.ddl.postgresql] Detected sequence named 'addrfeat_gid_seq' as owned by integer column 'addrfeat(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_addrfeat_geom_gist' on 'addrfeat'
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_addrfeat_tlid' on 'addrfeat'
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_addrfeat_zipl' on 'addrfeat'
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_addrfeat_zipr' on 'addrfeat'
INFO  [alembic.autogenerate.compare] Detected removed table 'addrfeat'
INFO  [alembic.autogenerate.compare] Detected removed table 'zip_lookup_base'
INFO  [alembic.ddl.postgresql] Detected sequence named 'pagc_gaz_id_seq' as owned by integer column 'pagc_gaz(id)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed table 'pagc_gaz'
INFO  [alembic.ddl.postgresql] Detected sequence named 'cousub_gid_seq' as owned by integer column 'cousub(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed index 'tige_cousub_the_geom_gist' on 'cousub'
INFO  [alembic.autogenerate.compare] Detected removed table 'cousub'
INFO  [alembic.autogenerate.compare] Detected removed table 'zip_lookup_all'
INFO  [alembic.autogenerate.compare] Detected removed index 'countysub_lookup_name_idx' on 'countysub_lookup'
INFO  [alembic.autogenerate.compare] Detected removed index 'countysub_lookup_state_idx' on 'countysub_lookup'
INFO  [alembic.autogenerate.compare] Detected removed table 'countysub_lookup'
INFO  [alembic.autogenerate.compare] Detected removed table 'zip_state_loc'
INFO  [alembic.autogenerate.compare] Detected removed table 'layer'
INFO  [alembic.ddl.postgresql] Detected sequence named 'pagc_lex_id_seq' as owned by integer column 'pagc_lex(id)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed table 'pagc_lex'
INFO  [alembic.autogenerate.compare] Detected removed index 'county_lookup_name_idx' on 'county_lookup'
INFO  [alembic.autogenerate.compare] Detected removed index 'county_lookup_state_idx' on 'county_lookup'
INFO  [alembic.autogenerate.compare] Detected removed table 'county_lookup'
INFO  [alembic.ddl.postgresql] Detected sequence named 'county_gid_seq' as owned by integer column 'county(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_tiger_county' on 'county'
INFO  [alembic.autogenerate.compare] Detected removed table 'county'
INFO  [alembic.autogenerate.compare] Detected removed table 'loader_platform'
INFO  [alembic.ddl.postgresql] Detected sequence named 'bg_gid_seq' as owned by integer column 'bg(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed table 'bg'
INFO  [alembic.ddl.postgresql] Detected sequence named 'zcta5_gid_seq' as owned by integer column 'zcta5(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed table 'zcta5'
INFO  [alembic.autogenerate.compare] Detected removed index 'direction_lookup_abbrev_idx' on 'direction_lookup'
INFO  [alembic.autogenerate.compare] Detected removed table 'direction_lookup'
INFO  [alembic.ddl.postgresql] Detected sequence named 'addr_gid_seq' as owned by integer column 'addr(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_tiger_addr_tlid_statefp' on 'addr'
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_tiger_addr_zip' on 'addr'
INFO  [alembic.autogenerate.compare] Detected removed table 'addr'
INFO  [alembic.autogenerate.compare] Detected removed table 'state_lookup'
INFO  [alembic.ddl.postgresql] Detected sequence named 'pagc_rules_id_seq' as owned by integer column 'pagc_rules(id)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed table 'pagc_rules'
INFO  [alembic.ddl.postgresql] Detected sequence named 'state_gid_seq' as owned by integer column 'state(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_tiger_state_the_geom_gist' on 'state'
INFO  [alembic.autogenerate.compare] Detected removed table 'state'
INFO  [alembic.ddl.postgresql] Detected sequence named 'featnames_gid_seq' as owned by integer column 'featnames(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_tiger_featnames_lname' on 'featnames'
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_tiger_featnames_snd_name' on 'featnames'
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_tiger_featnames_tlid_statefp' on 'featnames'
INFO  [alembic.autogenerate.compare] Detected removed table 'featnames'
INFO  [alembic.autogenerate.compare] Detected removed table 'loader_lookuptables'
INFO  [alembic.autogenerate.compare] Detected removed table 'tabblock20'
INFO  [alembic.autogenerate.compare] Detected removed table 'geocode_settings_default'
INFO  [alembic.ddl.postgresql] Detected sequence named 'tract_gid_seq' as owned by integer column 'tract(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed table 'tract'
INFO  [alembic.ddl.postgresql] Detected sequence named 'tabblock_gid_seq' as owned by integer column 'tabblock(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed table 'tabblock'
INFO  [alembic.autogenerate.compare] Detected removed index 'secondary_unit_lookup_abbrev_idx' on 'secondary_unit_lookup'
INFO  [alembic.autogenerate.compare] Detected removed table 'secondary_unit_lookup'
INFO  [alembic.autogenerate.compare] Detected removed table 'loader_variables'
INFO  [alembic.autogenerate.compare] Detected removed table 'geocode_settings'
INFO  [alembic.ddl.postgresql] Detected sequence named 'place_gid_seq' as owned by integer column 'place(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed index 'tiger_place_the_geom_gist' on 'place'
INFO  [alembic.autogenerate.compare] Detected removed table 'place'
INFO  [alembic.autogenerate.compare] Detected removed index 'street_type_lookup_abbrev_idx' on 'street_type_lookup'
INFO  [alembic.autogenerate.compare] Detected removed table 'street_type_lookup'
INFO  [alembic.autogenerate.compare] Detected removed table 'zip_state'
INFO  [alembic.ddl.postgresql] Detected sequence named 'edges_gid_seq' as owned by integer column 'edges(gid)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_edges_tlid' on 'edges'
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_tiger_edges_countyfp' on 'edges'
INFO  [alembic.autogenerate.compare] Detected removed index 'idx_tiger_edges_the_geom_gist' on 'edges'
INFO  [alembic.autogenerate.compare] Detected removed table 'edges'
INFO  [alembic.autogenerate.compare] Detected removed table 'zip_lookup'
INFO  [alembic.autogenerate.compare] Detected removed index 'place_lookup_name_idx' on 'place_lookup'
INFO  [alembic.autogenerate.compare] Detected removed index 'place_lookup_state_idx' on 'place_lookup'
INFO  [alembic.autogenerate.compare] Detected removed table 'place_lookup'
INFO  [alembic.autogenerate.compare] Detected removed table 'topology'
Generating D:\workspace\test\test_sqlalchemy\migrations\versions\7375114c2e0a_.py ...  done

原因分析

  • Postgis 似乎将搜索路径设置为 "$user", public, topology, tiger 而不是默认路径。
  • 在检测数据库表时检测了所有schema的变化，往往我们仅需要检测public中的表

解决方案

sqlalchemy和alembic的作者们给了两种方案：
第一种：直接更改Postgis的搜索路径：

https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#setting-alternate-search-paths-on-connect

app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://postgres:123456@localhost:5432/test?options=-csearch_path=public"

第二种：使用include_schemas=True和include_name两个配置项：

def include_name(name, type_, parent_names):
    if type_ == "schema":
        return False
    else:
        return True


Migrate(app=app, db=db, compare_type=True, include_object=include_object, render_item=render_item, include_schemas=True, include_name=include_name)

总的来说使用第一种方案更方便使用

整个讨论过程：

https://github.com/sqlalchemy/alembic/discussions/1282

llc的足迹
关注
专栏目录
python-sqlalchemy
08-02
官网版本sqlalchemy
sqlalchemy1.4风格（2022新语法配合协程更优）
有勇气的牛排博客
02-27 823
Python SQLAlchemy新语法解析，抛弃query方式。
关于sqlalchemy 2.0版本的查询
最新发布
u014593144的博客
07-24 208
最近工作需要sqlalchemy的方式去查询数据库，看了很多网上的教程，发现查询的时候用的是engine.execute(select(表名))的方式去查询，报错，这好像是1.x版本的做法吗？
SqlAlchemy 2.0 中文文档（一）
龙哥盟
06-22 1243
原文：docs.sqlalchemy.org/en/20/intro.htmlSQLAlchemy SQL 工具包和对象关系映射器是一套全面的用于处理数据库和 Python 的工具集。它有几个不同的功能区域，可以单独使用或组合在一起。其主要组件如下图所示，组件依赖关系组织成层次结构：上面，SQLAlchemy 最重要的两个面向用户的部分是对象关系映射器（ORM）和核心（Core）。核心部分包含了 SQLAlchemy 的 SQL 和数据库集成以及描述服务，其中最突出的部分是SQL 表达式语言。
Python库学习(十四):ORM框架-SQLAlchemy
weixin_39001207的博客
01-07 1796
相反，它仅是为了创建一个数据库引擎对象，该对象封装了连接到数据库的配置和行为，但直到实际执行数据库操作时才会尝试建立连接。会话允许你在应用程序中创建、更新、删除数据库中的数据，并提供了一系列方法来管理事务的提交和回滚。模型类相关代码，不用我们挨个去写模型，它的实现原理：通过连接到数据库，然后分析数据库结构，最后生成对应的。返回查询结果的第一列的第一个值，通常用于获取单个聚合函数的结果，如。执行外连接操作，返回左表中的所有记录以及右表中匹配的记录。在插入新记录时，如果没有提供该列的值，则将使用默认值。
基于Flask和SQLAlchemy的日程记录系统设计源码
04-06
本资源提供了一套基于Flask和SQLAlchemy的日程记录系统的设计源码，包含25个文件，其中包括8个XML配置...这些文件详细展示了如何使用Flask和SQLAlchemy构建一个日程记录系统，非常适合用于学习和参考Python项目的开发。
Python的Flask框架中SQLAlchemy使用时的乱码问题解决
12-23
在Python的Flask框架中，使用SQLAlchemy进行数据库操作时可能会遇到乱码问题。这个问题通常出现在查询结果的显示上，表现为非UTF-8编码的数据在输出时无法正确解码，从而呈现为乱码。以下是对这个问题的深入分析及...
浅谈pycharm下找不到sqlalchemy的问题
12-24
问题始于我在pycharm下建了一个flask工程，然后导入sqlalchemy的包： from flask import Flask from flask.ext.sqlalchemy import SQLAlchemy 然后执行时报错，找不到sqlalchemy, “ImportError: No module named ...
Flask SQLAlchemy一对一,一对多的使用方法实践
12-23
Flask-SQLAlchemy安装和建表操作请参考这里。 复制代码 代码如下:# Role表class Role(db.Model): id=db.Column(db.Integer,primary_key=True) name=db.Column(db.String(80))# RoleType表class Role_type(db....
SqlAlchemy 2.0 中文文档（七十四）
龙哥盟
06-22 663
原文：docs.sqlalchemy.org/en/20/contents.html SQLAlchemy 1.2 中的新内容是什么？ 原文：docs.sqlalchemy.org/en/20/changelog/migration_12.html 关于本文档 本文描述了 SQLAlchemy 1.1 版本与 SQLAlchemy 1.2 版本之间的更改。 简介 本指南介绍了 SQLAlchemy 版本 1.2 中的新功能，并记录了影响用户将其应用程序从 SQLAlchemy 1.1 系列迁移到 1
SqlAlchemy 2.0 中文文档（四十九）
龙哥盟
06-22 1067
原文：docs.sqlalchemy.org/en/20/contents.html MySQL 和 MariaDB 原文：docs.sqlalchemy.org/en/20/dialects/mysql.html 支持 MySQL / MariaDB 数据库。 以下表总结了数据库发布版本的当前支持级别。 支持的 MySQL / MariaDB 版本 支持类型 版本 CI 中完全测试 5.6, 5.7, 8.0 / 10.8, 10.9 正常支持 5.6+ / 10+ 尽力而
SQLAlchemy
withjc的博客
03-16 207
以前用的时候要么pymysql要么mysql-python要么mysql-connector，之前一直用的mysql-connector，这次用sqlalchemy，发现它pymysql和mysql-python都用，具体细节也不是很清楚，至少碰到了以下问题，这个是mysql-python的版本不对，要降会1.2.4b4，然而在pip的时候下载distribute的时候报了ssl的错，所以手动下载distribute的tar包然后放到mysql-python的安装包下面。
【无标题】sqlalchemy版本不对，导致ORM操作出问题，_mapper_args__ = {“order_by“: country_code}报错unexpected keyword argum
Deng872347348的博客
09-07 1720
sqlalchemy版本不对，导致ORM操作出问题，_mapper_args__ = {"order_by": country_code}报错unexpected keyword argument 'order_by'
mysql之sqlalchemy
luan_225的博客
11-15 209
mysql之sqlalchemy 安装 pip install SQLAlchemy 查看安装版本 import sqlalchemy sqlalchemy.__version__ 连接数据库 from sqlalchemy import create_engine import pymysql pymysql.install_as_MySQLdb() DB_CON_STR = 'mysql...
python常用库之数据库orm框架之SQLAlchemy
热门推荐
西京刀客
10-08 1万+
SQLAlchemy是一个基于Python实现的SQL工具包和ORM框架,提供了高层抽象来管理数据库交互。
5.Marshmallow-SQLAlchemy轻量级序列化和反序列化包
qq_44623314的博客
09-04 646
Marshmallow，中文译作：棉花糖。是一个轻量级的数据格式转换的模块，也叫序列化和反序列化模块，常用于将复杂的orm模型对象与python原生数据类型之间相互转换。marshmallow提供了丰富的api功能基本使用。
SQL Alchemy介绍和安装
zy010101博客
05-15 5474
SQL Alchemy介绍 SQLAlchemy是一个用于Python语言处理数据库的工具。它具有几个不同的功能区域，可以单独使用或组合使用。其主要组件如下图所示。 SQLAlchemy的两个最重要的前端部分是ORM和Core，而DBAPI是python语言连接数据库的规范实现，但是现实是不同的数据库都拥有各自的“方言”。因此，在使用SQL Alchemy的时候，基本上都需要根据使用的数据库来选择一个相应的驱动。 SQL Alchemy安装 支持的平台 CPython 2.7 CPython 3.
ORM框架SQLAlchemy入门
chunjusu2447的博客
07-08 495
相关资料/官网地址 1、安装sqlalchemy并查看版本 命令行下执行下面语句安装： pip install SQLAlchemy 安装完成后查看版本: import sqlalchemy print(sqlalchemy.__version__) 此外还需要安装PyMySQL，否则在...
SQLAlchemy版本回退操作（其他数据库相关bug）
mu66mu的博客
01-11 8832
1：track_modifications = app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] 报错，需要进行flask的sqlalchemy版本进行回退操作语句 pip install Flask-SQLAlchemy==2.1  2:This Session's transaction has been rolled back due to a pre...
SQLAlchemy 1.4.39 版本特性分析与应用
2. SQLAlchemy版本1.4.39表示这是该库的一个稳定版本，发布于特定的时间点，拥有特定的特性集和修正。 3. "py310"表明这个安装包是为Python版本3.10设计的。 4. "h2bbff1b_0"可能是该版本的构建编号或者是某个特定...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

llc的足迹

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值