pyodbc插入带有变量值的where子句的查询

Question

我想在SQl中插入不同的变量值，其中的值不能使用pyodbc出现在表中，但会出错。

变量

for data in datafield:
id = data.Id
iname = data.name
ivalue = data.value
cursor.execute("INSERT INTO Description (SrId, FieldName, FieldValue) VALUES (?, ?, ?) WHERE IN (SELECT * from Description WHERE SrId <>id AND FieldName<>iname AND FieldValue<>ivalue)", (id, iname, ivalue))
connection.commit()

使用Pyodbc将Sql服务器与python 连接

错误：语法错误

Answer 1

在SQL Server中，没有VALUES...WHERE语法。考虑在表值构造函数上使用LEFT JOIN...NULL进行插入-选择以避免重复：

# PREPARED STATEMENT WITH QMARK PLACEHOLDERS
sql = """INSERT INTO Description (SrId, FieldName, FieldValue) 
SELECT vals.SrId, vals.FieldName, vals.FieldValue
FROM Description d
LEFT JOIN (VALUES (?, ?, ?)) AS vals(SrId, FieldName, FieldValue)   
ON d.SrId  = vals.SrId 
AND d.FieldName = vals.FieldName
AND d.FieldValue = vals.FieldValue
WHERE d.SrId IS NULL 
OR d.FieldName IS NULL
OR d.FielValue IS NULL
"""
# BIND PARAMS
cur.execute(sql, (id, iname, ivalue))
connection.commit()

Answer 2

行末尾有一个额外的"。

cursor.execute("INSERT INTO Description (SrId, FieldName, FieldValue) VALUES (?, ?, ?) WHERE IN (SELECT * from Description WHERE SrId <>id AND FieldName<>iname AND FieldValue<>ivalue)", (id, iname, ivalue)")

应该是

cursor.execute("INSERT INTO Description (SrId, FieldName, FieldValue) VALUES (?, ?, ?) WHERE IN (SELECT * from Description WHERE SrId <>id AND FieldName<>iname AND FieldValue<>ivalue)", (id, iname, ivalue))