CODEWITHSUNDEEP
pythonsql-serverpyodbc
barciewicz
barciewicz

Reputation: 3773

Specifying column criteria in SQL query through pyodbc causes errors

I am running a query to MS SQL Server through pyodbc:

    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)

I get the following error:

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)")

It seems that the column criteria: Deleted, Event etc. are being read as column names instead. The same query without the WHERE clause will work fine.

The query is designed in Access and the auto-generated SQL is copy-pasted from Access to my script.

Do I need to correct the SQL somehow or is it something else causing the error?

Upvotes: 1

Views: 210

Answers (1)

Piotr Palka
Piotr Palka

Reputation: 3159

Change double quotes to single quotes around constant values. In SQL Server string in double quote is considered a column name.

Upvotes: 1

Related Questions