python - 尝试将 Python 列表传递到 SQL 查询时出现 SQL 错误

Question

我正在尝试将 Python 列表传递给 SQL Server 查询（在 Python 中）。我已正确格式化查询，以便查询中有问号作为占位符。代码如下：

（vsList 是字符串列表的名称）

placeholders = ','.join('?'*len(vsList))


query = ("select distinct cf.cfname \
          from jiraschema.customfield cf inner join jiraschema.customfieldvalue cfv on cf.id = cfv.CUSTOMFIELD \
          where (cfv.DATEVALUE is not null \
                 or cfv.NUMBERVALUE is not null \
                 or cfv.STRINGVALUE is not null \
                 or cfv.TEXTVALUE is not null) \
                 and cf.id not in (select cf.ID \
                                   from jiraschema.customfield cf inner join jiraschema.fieldscreenlayoutitem fsli on 'customfield_' + cast(cf.id as nvarchar) = fsli.FIELDIDENTIFIER \
                                                                  inner join jiraschema.fieldscreentab fst on fsli.FIELDSCREENTAB = fst.ID \
                                                                  inner join jiraschema.fieldscreen fs on fst.FIELDSCREEN = fs.ID \
                                   where fs.id in (%s))" % placeholders)

fields = cur.execute(query, *vsList)

当我执行此代码时，我收到此错误：

Traceback (most recent call last):
  File "C:\Users\deidle\Python\fieldsNotDisplayed.py", line 104, in <module>
    main()
  File "C:\Users\deidle\Python\fieldsNotDisplayed.py", line 100, in main
    ndFields = getFieldsNotDisplayed(viewScreens)
  File "C:\Users\deidle\Python\fieldsNotDisplayed.py", line 76, in 
getFieldsNotDisplayed
    fields = cur.execute(query, *vsList)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server 
Driver][SQL Server]Incorrect syntax near ')'. (102) (SQLExecDirectW); [42000] 
 [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be 
prepared. (8180)")

我检查了许多其他有关将列表传递到 SQL 查询的文档，并尝试了许多解决方案，但无法使用我的代码。就像我说的，我知道查询的格式是正确的，问号作为参数标记，所以我知道它一定是我的列表。

标签： pythonsql-server