python - 通过pyodbc在SQL查询中指定列条件会导致错误

Question

我正在通过 pyodbc 对 MS SQL Server 运行查询:

    conn = pyodbc.connect('DRIVER={ODBC Driver 11 for SQL Server};SERVER=some_server, some_port;DATABASE=MAP;UID='xxx';PWD='xxx')
    cursor = conn.cursor()

    query = """SELECT v_MAMain.MAID, v_MAMain.startDate, v_MAMain.endDate, v_MAMain.fromTime, v_MAMain.toTime, v_MAMain.StatusDesc, v_MAMain.name, v_Event_Statistic.TotalGuestsAtEvent, v_Event_Statistic.PlannedGuestsAtEvent, v_Event_Statistic.City, v_Event_Statistic.Location, v_Event_Statistic.Host, v_Event_Statistic.EventSpeakers, v_MAMain.Owner, v_Event_Statistic.PlannedHoursProjectOwner, v_Event_Statistic.EventManagerName, v_Event_Statistic.PlannedHoursEventManager, v_Event_Statistic.CreditSuisseEventManager, v_MAMain.CostCenter, v_MAMain.[Project Year], v_MAMain.category, MADetails.NameOfEventStaff1, MADetails.PlannedWorkDurationEventStaff1, MADetails.NameOfEventStaff2, MADetails.PlannedWorkDurationEventStaff2, MADetails.NameOfEventStaff3, MADetails.PlannedWorkDurationEventStaff3, MADetails.NameOfEventStaff4, MADetails.PlannedWorkDurationEventStaff4, MADetails.NameOfEventStaff5, MADetails.PlannedWorkDurationEventStaff5, v_Event_Statistic.KPIEventQuality, v_Event_Statistic.KPIBusinessFit
FROM (v_MAMain INNER JOIN v_Event_Statistic ON v_MAMain.MAID = v_Event_Statistic.ProjectID) INNER JOIN MADetails ON v_MAMain.MAID = MADetails.MAID
WHERE (((v_MAMain.StatusDesc)<>"Deleted") AND ((v_MAMain.CostCenter)="0892 / 932" Or (v_MAMain.CostCenter)="0897 / 951") AND ((v_MAMain.[Project Year])=2019) AND ((v_MAMain.category)="Event"));"""

    cursor.execute(query)

我收到以下错误:

pyodbc.ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'Deleted'. (207) (SQLExecDirectW); [42S22] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name '0892 / 932'. (207); [42S22] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name '0897 / 951'. (207); [42S22] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'Event'. (207)")

似乎列条件:Deleted、Event 等被读取为列名称。没有 WHERE 子句的相同查询也可以正常工作。

查询是在 Access 中设计的，自动生成的 SQL 是从 Access 复制粘贴到我的脚本中的。

我是否需要以某种方式更正 SQL，还是其他原因导致了错误？

Answer 1

将常量值周围的双引号更改为单引号。在 SQL Server 中，双引号中的字符串被视为列名。