参数错误太少，而没有使用参数占位符 [英] Too few parameters error, while no parameters placeholders used

问题描述

我正在尝试使用PYODBC在Access数据库中执行SQL查询，并且出现以下错误:

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驱动程序] 参数太少.预期1.(-3010)(SQLExecDirectW)')

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)

查询本身(如果在MS-Access中使用的话)可以正常工作.此外，连接正常，因为其他查询已正确执行. 我在做什么错了?

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

推荐答案

此类查询中的常量是有问题的-您永远不知道布尔值，字符串等的确切底层语法-即使它在MS-Access中有效，也可以在您使用的中间库中有所不同.

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)

这篇关于参数错误太少，而没有使用参数占位符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！