pyodbc: i can't realize a query with condition where-CodePudding

i have this code:

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)

and when i run it i get this response (i deleted the query for privacy):

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

the problem is after the first condition, because when i remove the second and third condition it works fine.

I suspect that the problem is because of the data type, but when I work the database from arcmap the query is written the same.

CodePudding user response：

Per MSDN docs, in MS Access SQL, the ampersand operator, &, is the concatenation operator to join two or more values together and is interchangeable with plus operator, , for strings.

For logical expressions in WHERE clause, you want to use the AND operator to run the multiple conditions together. Also, consider using single quotes (though double quotes is allowable in MS Access) as best practice for general SQL.

WHERE ID = 1758 AND PROCESS = '1587' AND CATEGORY IN ('HDDS', 'HDD', 'SSD')