pyodbc连接Access数据库时WHERE子句多条件查询语法错误求助

用户使用pyodbc连接Access数据库执行查询时触发语法错误，仅保留WHERE子句第一个条件可正常运行，但ArcMap中相同写法无异常：

原代码 #

import pandas as pd

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ = test_db.mdb;')
cursor = conn.cursor()

consulta = '''
            SELECT COL_NAME1, COL_NAME2, COL_NAME3, COL_NAME4
            FROM TABLENAME1
            WHERE ID = 1758 & PROCESS = "1587" & CATEGORY IN ('HDDS', 'HDD', 'SSD')
            '''
df = pd.read_sql(consulta, conn)

报错信息 #

DatabaseError: Execution failed on sql '
            SELECT  ...
            FROM ...
            WHERE ...'
: ('42000', "[42000] [Microsoft][Controlador ODBC Microsoft Access] Error de sintaxis (falta operador) en la expresión de consulta ' ... '. (-3100) (SQLExecDirectW)")

核心原因 #

ArcMap对Access SQL语法做了兼容转换，但pyodbc依赖的ODBC驱动严格遵循标准SQL规则，代码存在两处语法错误：

• Access SQL中&是字符串拼接符，并非逻辑与运算符；逻辑与必须用AND
• 混合使用单双引号会引发语法歧义

基础修正 #

1. 替换逻辑运算符：将所有&改为AND
2. 统一字符串引号：把"1587"改为'1587'，与IN子句的引号格式保持一致

修正后代码：

import pandas as pd

# 去掉DBQ后的空格，避免潜在解析问题
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=test_db.mdb;')

consulta = '''
            SELECT COL_NAME1, COL_NAME2, COL_NAME3, COL_NAME4
            FROM TABLENAME1
            WHERE ID = 1758 AND PROCESS = '1587' AND CATEGORY IN ('HDDS', 'HDD', 'SSD')
            '''
df = pd.read_sql(consulta, conn)

进阶优化（彻底规避数据类型问题） #

若仍存在数据类型匹配异常，建议使用参数化查询，自动处理类型转换，同时避免SQL注入风险：

import pandas as pd

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=test_db.mdb;')

# 用?作为参数占位符
consulta = '''
            SELECT COL_NAME1, COL_NAME2, COL_NAME3, COL_NAME4
            FROM TABLENAME1
            WHERE ID = ? AND PROCESS = ? AND CATEGORY IN (?, ?, ?)
            '''
# 按顺序传递参数
params = (1758, '1587', 'HDDS', 'HDD', 'SSD')
df = pd.read_sql(consulta, conn, params=params)

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