Python用psycopg2批量Upsert几何数据时解析错误的解决方法

在pgAdmin查询工具中执行以下SQL，可正常完成带几何字段表的批量Upsert操作：

INSERT INTO mytable (id, geometry) 
VALUES (1, ST_SetSRID(ST_GeomFromText('Point(15.5 17.7)'),4326), <更多元组>)
ON CONFLICT (id) DO UPDATE 
SET geometry = EXCLUDED.geometry;

但用Python的psycopg2实现相同逻辑时，编写的模拟代码执行后触发parse error - invalid geometry错误，代码如下：

# 模拟代码：
# 准备用于Upsert的GeoDataFrame：
points = ["ST_SetSRID(ST_GeomFromText('Point({} {})'), 4326)".format(geo.x, geo.y) for geo in gdf.geometry]
gdf = gdf.assign(points=points)

upsert_tuples = [tuple(x) for x in gdf[['points', 'id', <其他相关列>]].to_numpy()]

# SQL语句
upsert_sql =  '''INSERT INTO mytable (geometry, id, <其他列>) VALUES %s 
              ON CONFLICT (id) DO UPDATE SET
              geometry = EXCLUDED.geometry,
              <其他列 = EXCLUDED.其他列>;'''

# 数据库连接...
extras.execute_values(cursor, upsert_sql, upsert_tuples)
# ...

请问如何通过Python实现几何数据的批量Upsert？

错误根源是你把SQL函数的字符串直接作为值传入，psycopg2会将其当作普通字符串传递给PostgreSQL，而非执行该SQL函数生成几何对象，导致PostgreSQL无法解析这个字符串为有效的几何类型。

以下是几种正确的实现方式：

方法1：利用psycopg2的PostGIS类型适配（推荐） #

psycopg2支持直接传递Shapely几何对象（GeoDataFrame的geometry列默认就是Shapely对象），无需手动拼接SQL函数。先确保安装了psycopg2-binary和shapely：

import psycopg2
from psycopg2 import extras
import geopandas as gpd

# 假设gdf是你的GeoDataFrame，包含geometry、id及其他列
# 直接提取Shapely几何对象，不需要转换为SQL字符串
upsert_tuples = [tuple(x) for x in gdf[['geometry', 'id', <其他相关列>]].to_numpy()]

upsert_sql = '''INSERT INTO mytable (geometry, id, <其他列>) 
                VALUES %s 
                ON CONFLICT (id) DO UPDATE SET
                    geometry = EXCLUDED.geometry,
                    <其他列> = EXCLUDED.<其他列>;'''

# 连接数据库
conn = psycopg2.connect("dbname=你的数据库名 user=用户名 password=密码 host=主机地址")
cur = conn.cursor()

# 执行批量Upsert
extras.execute_values(cur, upsert_sql, upsert_tuples)
conn.commit()

cur.close()
conn.close()

psycopg2会自动将Shapely对象转换为PostgreSQL可识别的几何类型，无需手动处理SRID（前提是GeoDataFrame的CRS已经是4326，若不是可先通过gdf.to_crs(epsg=4326)转换）。

方法2：在SQL模板中使用ST_MakePoint函数 #

如果不想依赖Shapely的类型适配，可以直接在SQL语句中调用ST_MakePoint，仅传递坐标数值：

# 提取坐标和其他字段
upsert_tuples = [(geo.x, geo.y, row.id, <其他列值>) for idx, (geo, row) in enumerate(zip(gdf.geometry, gdf.itertuples()))]

upsert_sql = '''INSERT INTO mytable (geometry, id, <其他列>) 
                VALUES %s 
                ON CONFLICT (id) DO UPDATE SET
                    geometry = EXCLUDED.geometry,
                    <其他列> = EXCLUDED.<其他列>;'''

# 调整SQL模板，让每组值调用ST_MakePoint生成几何
upsert_sql = upsert_sql.replace('%s', '(ST_SetSRID(ST_MakePoint(%s, %s), 4326), %s, %s)')

# 执行批量Upsert
extras.execute_values(cur, upsert_sql, upsert_tuples)
conn.commit()

这种方式直接传递坐标数值，在SQL端生成几何对象，避免了字符串拼接的问题。

方法3：使用psycopg2的sql模块构建安全SQL语句 #

如果需要更灵活的SQL构建，可用psycopg2的sql模块避免SQL注入风险：

from psycopg2 import sql

# 定义字段列表
fields = ['geometry', 'id', '<其他列>']
# 构建UPDATE子句
update_clause = sql.SQL(', ').join(
    [sql.Identifier(col) + sql.SQL(' = EXCLUDED.') + sql.Identifier(col) for col in fields]
)

# 构建完整Upsert SQL
upsert_sql = sql.SQL('''INSERT INTO mytable ({fields}) 
                        VALUES %s 
                        ON CONFLICT (id) DO UPDATE SET {update_clause};''').format(
    fields=sql.SQL(', ').join(map(sql.Identifier, fields)),
    update_clause=update_clause
)

# 执行批量Upsert，传入包含Shapely对象的元组
extras.execute_values(cur, upsert_sql, upsert_tuples)
conn.commit()

内容的提问来源于stack exchange，提问作者skna.1000