Python操作SQLite更新新增经纬度列报错及全表更新问题

已完成apartments数据表创建，后续为表新增lat（纬度）、lon（经度）字段，需为存量数据匹配填充对应经纬度值，经纬度数据存储在mass数组中。
此前两种实现均存在问题：

1. 嵌套循环写法运行抛出OperationalError: unrecognized token: ":"错误，且逻辑存在缺陷
2. 无WHERE条件的UPDATE写法会将全表所有行更新为同一个值，不符合业务需求

• 第一种写法：双层循环未建立记录ID和mass数据的映射关系，会将mass内所有值逐次覆盖到每一行记录，最终所有行数据都会被最后一次循环的值覆盖；同时直接使用f-string拼接SQL语句，值中包含的特殊字符（如报错提示的冒号）会触发SQL语法解析错误，还存在SQL注入风险。
• 第二种写法：UPDATE语句未添加WHERE筛选条件，语句默认作用于全表所有行，导致全表值被统一覆盖。

优先使用参数化查询+批量执行的方案，从根源避免语法错误，同时执行效率远高于循环单条提交。

前置说明：推荐优先使用ID映射的方案，避免按顺序匹配导致的数据错位问题。

方案1（推荐）：ID映射匹配 #

如果mass数组内每个元素为(link_id, lon, lat)结构（即每个元素自带对应记录的唯一ID），直接使用以下代码：

# 定义参数化更新SQL，sqlite用?作为占位符，pymysql等MySQL驱动替换为%s即可
update_sql = "UPDATE apartments SET lon = ?, lat = ? WHERE link_id = ?"
# 构造批量更新参数
params_list = [(item[1], item[2], item[0]) for item in mass]
# 批量执行更新
cursorObj.executemany(update_sql, params_list)
con.commit()

方案2：顺序匹配（仅当确认顺序完全一致时使用） #

如果mass数组仅存储经纬度值，且顺序和表中存量记录顺序完全一一对应，可以和查询到的ID列表打包匹配：

# 查询所有存量记录的ID
id_apartment = [row[0] for row in cursorObj.execute('SELECT link_id FROM apartments').fetchall()]
update_sql = "UPDATE apartments SET lon = ?, lat = ? WHERE link_id = ?"
params_list = []
# 将ID和对应经纬度按顺序打包
for record_id, coord in zip(id_apartment, mass):
    # 按mass实际存储的索引调整lon、lat取值即可
    params_list.append((coord[0], coord[1], record_id))
# 批量执行
cursorObj.executemany(update_sql, params_list)
con.commit()

• 禁止直接使用f-string拼接SQL语句中的值，所有传入的变量统一用参数化占位符传递，避免特殊字符触发语法错误，同时防范SQL注入风险。
• 不要在循环内单条执行就提交事务，批量执行完成后统一commit可以大幅提升更新效率，数据量较大时差异尤其明显。
• 执行全量更新前建议先备份表数据，避免匹配逻辑错误导致数据污染。

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