使用pyodbc向PostgreSQL插入含引号XML数据时遇42601语法错误求助

问题根源 #

你现在的代码用字符串拼接生成SQL语句，当字段（比如filtr）包含单引号这类特殊字符时，会直接破坏SQL的语法结构。比如你的filtr数据里有'KondorSDEvent_uat1'，拼接后SQL会出现未闭合的单引号，数据库自然抛出语法错误。

正确解决方法：参数化查询 #

别再手动拼接SQL了，用pyodbc的参数化查询功能。这种方式会自动处理特殊字符的转义，还能避免SQL注入，是数据库操作的标准规范。

修改后的完整代码 #

import re
import os
import pyodbc
import xml.etree.ElementTree as ET

login_db = '******'
passwd_db ='*******'
db_str = ("Driver={PostgreSQL UNICODE(x64)};"
"Server=*******;"
"Port=5432;"
"Database=inventory_UAT;"
"Uid=%s;"
"Pwd=%s") % (login_db, passwd_db)

db_cnxn = pyodbc.connect(db_str, autocommit=True)
db_cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
db_cnxn.setencoding(encoding='utf-8')
db_cursor = db_cnxn.cursor()

tree1 = ET.parse(r"C:\\Mine\\diverts.xml")
root1 = tree1.getroot()

for div in root1.findall('{urn:activemq:core}divert'):
    source = div.find('{urn:activemq:core}address').text
    target = div.find('{urn:activemq:core}forwarding-address').text
    name = div.attrib['name']
    filtr = ""
    for i in div.findall('{urn:activemq:core}filter'):
        filtr = i.attrib['string']
    
    # 用?作为参数占位符，替代手动字符串拼接
    qry = """INSERT INTO inventory.artemis_divert_inventory
    (divert_name,from_address,to_address,filter,instance_type,last_update)
    VALUES (?,?,?,?,?,NOW());"""
    
    # 将参数以元组形式传入execute方法
    db_cursor.execute(qry, (name, source, target, filtr, 'CORP-UAT'))

关键修改点 #

1. SQL语句中用?代替原来的'%s'作为参数占位符，pyodbc会自动完成占位符替换和特殊字符转义
2. execute方法的第二个参数是参数元组，顺序要和SQL里的占位符一一对应
3. 彻底移除字符串拼接操作，从根源上避免特殊字符导致的语法错误

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