Too few parameters error, while no parameters placeholders used

Question

I am trying to execute SQL query within Access database using PYODBC and I get following error:

pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (-3010) (SQLExecDirectW)')

The problem is that I am not using any additional parameters. Here is the code:

access_con_string = r"Driver={};Dbq={};".format(driver, base)
cnn = pyodbc.connect(access_con_string)
db_cursor = cnn.cursor()
expression = """SELECT F_ARODES.ARODES_INT_NUM, F_ARODES.TEMP_ADRESS_FOREST,F_AROD_LAND_USE.ARODES_INT_NUM, F_ARODES.ARODES_TYP_CD
FROM F_ARODES LEFT JOIN F_AROD_LAND_USE ON F_ARODES.ARODES_INT_NUM = F_AROD_LAND_USE.ARODES_INT_NUM
WHERE (((F_AROD_LAND_USE.ARODES_INT_NUM) Is Null) AND ((F_ARODES.ARODES_TYP_CD)="wydziel") AND ((F_ARODES.TEMP_ACT_ADRESS)=True));"""

db_cursor.execute(expression)

Query itself, if used inside MS-Access works fine. Also, connection is OK, as other queries are executed properly. What am I doing wrong?

BartoszKP BartoszKP · Accepted Answer · 2017-03-16T13:14:16

Constants in such queries are problematic - you never know the exact underlying syntax for booleans, strings etc. - even if it works in MS-Access, it can be different inside the intermediary library you're using.

The safest way is to extract them as parameters anyway:

expression = """SELECT F_ARODES.ARODES_INT_NUM, F_ARODES.TEMP_ADRESS_FOREST,F_AROD_LAND_USE.ARODES_INT_NUM, F_ARODES.ARODES_TYP_CD FROM F_ARODES LEFT JOIN F_AROD_LAND_USE ON F_ARODES.ARODES_INT_NUM = F_AROD_LAND_USE.ARODES_INT_NUM WHERE (((F_AROD_LAND_USE.ARODES_INT_NUM) Is Null)
AND ((F_ARODES.ARODES_TYP_CD)=?) AND ((F_ARODES.TEMP_ACT_ADRESS)=?));"""

db_cursor.execute(expression, "wydziel", True)

Too few parameters error, while no parameters placeholders used

2 Answers