PyODBC（pypyodbc）SQL Server多WHERE条件参数传递报错排查

在Windows环境下使用pypyodbc操作SQL Server，编写包含JOIN和两个WHERE条件的SELECT语句时，单独使用任一参数化WHERE条件查询正常，但用AND组合两个条件就触发报错。

单独参数查询正常的代码 #

bhfc_cursor.execute("""\
    select inc_auth.staff_id, inc_auth.rank, inc_auth.assignment, inc_auth.dttm from INC_AUTH
     INNER JOIN INC_MAIN ON inc_auth.inci_id = inc_main.inci_id
      where inc_main.inci_id = (?) """, this_incid)
mutual_aid_fdid = bhfc_cursor.fetchone()

bhfc_cursor.execute("""\
    select inc_auth.staff_id, inc_auth.rank, inc_auth.assignment, inc_auth.dttm from INC_AUTH
     INNER JOIN INC_MAIN ON inc_auth.inci_id = inc_main.inci_id
      where inc_auth.auth_type = (?) """, auth_type)
mutual_aid_fdid = bhfc_cursor.fetchone()

组合条件报错的代码 #

bhfc_cursor.execute("""\
    select inc_auth.staff_id, inc_auth.rank, inc_auth.assignment, inc_auth.dttm from INC_AUTH
     INNER JOIN INC_MAIN ON inc_auth.inci_id = inc_main.inci_id
      where inc_auth.auth_type = (?) and inc_main.inci_id = (?) """, auth_type, this_incid)
mutual_aid_fdid = bhfc_cursor.fetchone()

错误信息 #

File "C:\Users\bucky\OneDrive\Python_Installs\PycharmProjects\ReadSQLWireNFIRS\ReadSQQLWriteNFIRS.py", line 228, in <module>
    bhfc_cursor.execute("""\
File "C:\Users\bucky\OneDrive\Python_Installs\PycharmProjects\ReadSQLWireNFIRS\Lib\site-packages\pypyodbc.py", line 1617, in execute
    check_success(self, ret)
File "C:\Users\bucky\OneDrive\Python_Installs\PycharmProjects\ReadSQLWireNFIRS\Lib\site-packages\pypyodbc.py", line 1007, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
File "C:\Users\bucky\OneDrive\Python_Installs\PycharmProjects\ReadSQLWireNFIRS\Lib\site-packages\pypyodbc.py", line 987, in ctrl_err
    raise DatabaseError(state,err_text)
pypyodbc.DatabaseError: ('07002', '[07002] [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error')

排查尝试 #

• 已尝试改用%s替代(?)、将变量转为列表/元组等方法，均无效
• 变量类型：inci_id为<class 'pypyodbc.TupleRow..Row'>，auth_type为<class 'list'>
• 移除JOIN直接查询INC_AUTH表，仍报相同错误
• 将其中一个变量替换为常量后查询正常，证明问题出在变量替换而非语句本身：

bhfc_cursor.execute("""\
select inc_auth.staff_id, inc_auth.rank, inc_auth.assignment, inc_auth.dttm from INC_AUTH
  where (inc_auth.auth_type = 'OC' ) AND (inc_auth.inci_id = (?) ) """, this_incid)
oic_details = bhfc_cursor.fetchone()

相关ODBC日志片段 #

ReadSQQLWriteNF fb68-77d0   ENTER SQLPrepareW 
        HSTMT               0x000001CEFFC70470
        WCHAR *             0x000001CEFF370570 [     164] "    select inc_auth.staff_id, inc_auth.rank, inc_auth.assignment, inc_auth.dttm from INC_AUTH\ a      where (inc_auth.auth_type = (?) ) AND (inc_auth.inci_id = (?) ) "
        SDWORD                   164

ReadSQQLWriteNF fb68-77d0   EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000001CEFFC70470
        WCHAR *             0x000001CEFF370570 [     164] "    select inc_auth.staff_id, inc_auth.rank, inc_auth.assignment, inc_auth.dttm from INC_AUTH\ a      where (inc_auth.auth_type = (?) ) AND (inc_auth.inci_id = (?) ) "
        SDWORD                   164

ReadSQQLWriteNF fb68-77d0   ENTER SQLNumParams 
        HSTMT               0x000001CEFFC70470
        SWORD *             0x000001CEFF3B3C98

ReadSQQLWriteNF fb68-77d0   EXIT  SQLNumParams  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000001CEFFC70470
        SWORD *             0x000001CEFF3B3C98 (2)

ReadSQQLWriteNF fb68-77d0   ENTER SQLDescribeParam 
        HSTMT               0x000001CEFFC70470
        UWORD                        1 
        SWORD *             0x000001CEFF3B3398
        SQLULEN *           0x000001CEFF3B3918
        SWORD *             0x000001CEFF3B3518
        SWORD *             0x000001CEFF3B3718

ReadSQQLWriteNF fb68-77d0   EXIT  SQLDescribeParam  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000001CEFFC70470
        UWORD                        1 
        SWORD *             0x000001CEFF3B3398 (-9)
        SQLULEN *           0x000001CEFF3B3918 (255)
        SWORD *             0x000001CEFF3B3518 (0)
        SWORD *             0x000001CEFF3B3718 (1)

ReadSQQLWriteNF fb68-77d0   ENTER SQLDescribeParam 
        HSTMT               0x000001CEFFC70470
        UWORD                        2 
        SWORD *             0x000001CEFF3B3798
        SQLULEN *           0x000001CEFF3B3398
        SWORD *             0x000001CEFF3B3918
        SWORD *             0x000001CEFF3B3518

ReadSQQLWriteNF fb68-77d0   EXIT  SQLDescribeParam  with return code -1 (SQL_ERROR)
        HSTMT               0x000001CEFFC70470
        UWORD                        2 
        SWORD *             0x000001CEFF3B3798
        SQLULEN *           0x000001CEFF3B3398
        SWORD *             0x000001CEFF3B3918
        SWORD *             0x000001CEFF3B3518

        DIAG [07009] [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index (0) 

ReadSQQLWriteNF fb68-77d0   ENTER SQLGetDiagRecW 
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x000001CEFFC70470
        SQLSMALLINT                  1 
        SQLWCHAR *          0x000001CEFF31A070
        SQLINTEGER *        0x000001CEFF3B2818
        SQLWCHAR *          0x000001CEFF2704E0 
        SQLSMALLINT               1024 
        SQLSMALLINT *       0x000001CEFF3B2618

ReadSQQLWriteNF fb68-77d0   EXIT  SQLGetDiagRecW  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x000001CEFFC70470
        SQLSMALLINT                  1 
        SQLWCHAR *          0x000001CEFF31A070 [       5] "07009"
        SQLINTEGER *        0x000001CEFF3B2818 (0)
        SQLWCHAR *          0x000001CEFF2704E0 [      59] "[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index"
        SQLSMALLINT               1024 
        SQLSMALLINT *       0x000001CEFF3B2618 (59)

内容的提问来源于stack exchange，提问作者buckyswider