编程错误：("SQL 包含 1 个参数标记，但提供了 2 个参数"，"HY000")

Question

当运行下面的脚本时，它以以下错误结束，因为我确实找到了关于这个错误的其他帖子，所以我没有找到与我的脚本类似的修复程序。谢谢你的帮助！

*Traceback (most recent call last):
  File "D:DVOBoMarketing Hoursarchiveactive_assets.py", line 9, in <module>
    r"D:User foldersVOD_TitlesBCST_assets.txt"
  File "D:DVOBoMarketing Hoursarchiveseac_assets.py", line 67, in main
    assets = [asset_record(*row) for results in mssql_query(connect_string, sql_string, [today_string, today_string]) for row in results]
  File "D:DVOBoMarketing Hoursarchiveseac_assets.py", line 67, in <listcomp>
    assets = [asset_record(*row) for results in mssql_query(connect_string, sql_string, [today_string, today_string]) for row in results]
  File "D:DVOBoMarketing Hoursarchiveseac_assets.py", line 24, in mssql_query
    db_cursor.execute(sql_string, vars)
pyodbc.ProgrammingError: ('The SQL contains 1 parameter markers, but 2 parameters were supplied', 'HY000')*

这是主脚本，调用seac_assets

import seac_assets
if __name__ == "__main__":
  online = True
  if online:
    seac_assets.main(
      "DRIVER={SQL Server};SERVER=172.18.192.68;DATABASE=IdwData;UID=suddenlink;PWD=5udd3nlink",
      r"D:User foldersVOD_TitlesBCST_assets.txt"
    )
    seac_assets.main(
      "DRIVER={SQL Server};SERVER=172.19.241.51;DATABASE=IdwData;UID=suddenlink;PWD=5udd3nlink",
      r"D:User foldersVOD_TitlesCHRM_assets.txt"
    )

这是使用pyodbc的实际代码，最终出现错误

import pyodbc
import csv
from collections import defaultdict, namedtuple
from datetime import datetime, timedelta
asset_record = namedtuple("asset_record", """
wuid,
name,
title_brief,
activate_date,
deactivate_date""")
def mssql_query(connect_string,
                sql_string,
                vars=None,
                TIMEOUT_VALUE=300,
                FETCH_ROWS=5000,
                MAX_ROWS=10 ** 6):
  # Set up the database connection and cursor
  db_conn = pyodbc.connect(connect_string, timeout=TIMEOUT_VALUE)
  db_cursor = db_conn.cursor()
  # Execute the passed SQL statements
  db_cursor.execute(sql_string, vars)
  r = [] # Holds one batch of results
  L = 0
  while True:
    r = db_cursor.fetchmany(FETCH_ROWS)
    if r == []: # fetchmany() returns [] when finished
      break
    L += len(r)
    if L > MAX_ROWS:
      print(">>Fetched more than ", MAX_ROWS, ", aborting.")
      break
    yield r
    print("Fetched {} rows".format(L))
  print(">>Finished fetching rows.")
  #End the database connection and return the results
  db_cursor.close()
  db_conn.close()
def main(connect_string, outfile_path):
  sql_string = """
SELECT
  ref.WUID,
  ref.Name,
  a.TitleBrief,
  dates.ActivateDate,
  dates.DeactivateDate
FROM
  dbo.WHIDS_Asset AS ref WITH (NOLOCK)
  LEFT JOIN dbo.WHIDS_ATitleBrief AS a WITH (NOLOCK)
    ON ref.WUID = a.WUID
  LEFT JOIN dbo.WHIDS_AActivateDeactivateDates AS dates WITH (NOLOCK)
    ON ref.WUID = dates.WUID
WHERE
  dates.DeactivateDate >= ?
  AND a.TitleBrief IS NOT NULL
ORDER BY 
  dates.DeactivateDate"""
  today = datetime.today()
  today_string = today.strftime("%Y-%m-%d")
  print (asset_record)
  assets = [asset_record(*row) for results in mssql_query(connect_string, sql_string, [today_string, today_string]) for row in results]
  print (assets)
  with open(outfile_path, "w") as outfile:
    print("WUID",
          "NAME",
          "TITLE_BRIEF",
          "ACTIVATE_DATE",
          "DEACTIVATE_DATE",
          sep='t',
          file=outfile)
    for a in assets:
      print(a.wuid,
            a.name,
            a.title_brief,
            a.activate_date.strftime("%Y-%m-%d %H:%M"),
            a.deactivate_date.strftime("%Y-%m-%d %H:%M"),
            sep='t',
            file=outfile)

Answer 1

错误表明您有一个参数标记和两个参数。我看看你的sql:

SELECT
  ref.WUID,
  ref.Name,
  a.TitleBrief,
  dates.ActivateDate,
  dates.DeactivateDate
FROM
  dbo.WHIDS_Asset AS ref WITH (NOLOCK)
  LEFT JOIN dbo.WHIDS_ATitleBrief AS a WITH (NOLOCK)
    ON ref.WUID = a.WUID
  LEFT JOIN dbo.WHIDS_AActivateDeactivateDates AS dates WITH (NOLOCK)
    ON ref.WUID = dates.WUID
WHERE
  dates.DeactivateDate >= ?
  AND a.TitleBrief IS NOT NULL
ORDER BY 
  dates.DeactivateDate

我看到一个参数标记（where子句中的?）。

当我看到你提交查询的地方时：

mssql_query(connect_string, sql_string, [today_string, today_string])

我看到您传递了today_string的两个副本作为参数。正如错误消息所说：一个参数标记，两个参数。尝试将查询更改为

mssql_query(connect_string, sql_string, [today_string])