带有where条件的Pyodbc SQL插入查询

Question

我正试图使用where(NOT IN(子句将数据插入表中，以避免插入重复数据

nvalue = data.value
nature = data.type
sid = data.Id
cursor.execute("INSERT INTO TABLE_NAME1 (ID, FieldName, FieldValue) VALUES (?, ?, ?) WHERE sid NOT IN (SELECT ID FROM TABLE_NAME1 )", (sid, nature, nvalue)
connection.commit()

错误：

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WHERE'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

还尝试将where子句中的"sid"替换为"？"：

cursor.execute("INSERT INTO TABLE_NAME1 (ID, FieldName, FieldValue) VALUES (?, ?, ?) WHERE ? NOT IN (SELECT Id FROM TABLE_NAME1)", (sid, nature, nvalue, sid)

Answer 1

ID，而不是sid，因为它指的是TABLE_NAME1中每条记录的列ID。同时，sid是WHERE子句过滤后要插入的值。两者之间没有任何关系。

cursor.execute("INSERT INTO TABLE_NAME1 (ID, FieldName, FieldValue) VALUES (?, ?, ?) WHERE ID NOT IN (SELECT ID FROM TABLE_NAME1 )", (id, nature, nvalue)

Answer 2

SQL server不支持insert into where子句，请检查此项。

正如那里建议的那样，你可以使用

IF NOT EXISTS(SELECT 1 FROM Payments WHERE ID = ?)
INSERT INTO TABLE_NAME1(ID,FieldName,FieldValue)
VALUES(?,?,?)

使用值：

(sid, sid, nature, nvalue)

这意味着：

cursor.execute("IF NOT EXISTS(SELECT 1 FROM Payments WHERE ID = ?) INSERT INTO TABLE_NAME1(ID,FieldName,FieldValue) VALUES(?,?,?)", (sid, sid, nature, nvalue))

我希望它能有所帮助。