使用Psycopg向PostGIS表插入数据仅成功2行的问题求助

你的代码存在几个核心问题，这才是导致插入行数异常的关键，和“循环速度快于execute”无关：

1. SQL语句严重语法错误 #

你的INSERT语句完全不合法：

• 表名用单引号包裹是错误的，PostgreSQL中表名无需引号，仅特殊标识符才用双引号；
• 缺少VALUES子句和占位符，数据库无法识别要插入的数据位置。

修正后的INSERT语句示例（替换a_col,b_col,c_col为你的表字段名）：

fieldRecord = """INSERT INTO tablename (a_col, b_col, c_col) VALUES (%s, %s, %s);"""

2. 未捕获异常导致脚本中断 #

如果某次execute执行报错（比如语法错误），Python脚本会直接终止，后续循环不会执行，这就是仅插入少数行的直接原因。添加异常捕获排查错误：

def shptoPosGIS(a,b,c):
    fieldRecord = """INSERT INTO tablename (a_col, b_col, c_col) VALUES (%s, %s, %s);"""
    fieldValue = (a,b,c)
    try:
        db_cursor.execute(fieldRecord, fieldValue)
    except Exception as e:
        print(f"插入失败: {e}，数据: {a,b,c}")

3. 频繁提交的低效与风险 #

每次插入都调用commit()是极低效的操作，还可能引发锁冲突。建议批量提交，比如每1000条提交一次：

# 把commit从函数中移除
def shptoPosGIS(a,b,c):
    fieldRecord = """INSERT INTO tablename (a_col, b_col, c_col) VALUES (%s, %s, %s);"""
    fieldValue = (a,b,c)
    try:
        db_cursor.execute(fieldRecord, fieldValue)
    except Exception as e:
        print(f"插入失败: {e}，数据: {a,b,c}")

batch_size = 1000
for i in range(20000):
    shptoPosGIS(data["a"].iloc[i], data["b"].iloc[i], data["c"].iloc[i])
    if (i+1) % batch_size == 0:
        db_connection.commit()
# 提交最后一批剩余数据
db_connection.commit()

4. 改用批量插入提升效率与稳定性 #

Psycopg的executemany支持批量插入，比循环调用execute高效数倍，也能避免循环中的潜在问题：

# 整理所有待插入数据
insert_data = [(data["a"].iloc[i], data["b"].iloc[i], data["c"].iloc[i]) for i in range(20000)]
fieldRecord = """INSERT INTO tablename (a_col, b_col, c_col) VALUES (%s, %s, %s);"""

try:
    db_cursor.executemany(fieldRecord, insert_data)
    db_connection.commit()
except Exception as e:
    print(f"批量插入失败: {e}")
    db_connection.rollback()

额外检查项 #

• 确认db_cursor和db_connection全程有效，未被中途关闭或重置；
• 检查data数据集是否真的包含20000条有效记录，避免iloc取到空值；
• 查看数据库日志，确认是否有SQL执行错误的详细记录。

内容的提问来源于stack exchange，提问作者HelpOverFlow