“未实现可选功能"；针对Access数据库的Pyodbc查询错误 [英] "Optional feature not implemented" error with pyodbc query against Access database

问题描述

对于任何可以提供帮助的人，请先感谢您.
我正在运行64位Windows 10、64位Office和64位python 3.7.2

To anyone that can help, thank you in advance.
I am running 64bit windows 10, 64bit office and 64bit python 3.7.2

使用pyodbc，我正在尝试执行如下Select语句:

Using pyodbc, I am trying to execute a Select statement such as this:

"SELECT * FROM EconVars WHERE Year(ValueDate) = 1999"

为此，我的代码如下:

existquery = """SELECT * FROM EconVars WHERE Year(ValueDate) = ?"""        
params = (1999,)
cursor.execute(existquery,params)

运行此命令时，出现以下错误 pyodbc.Error :("HYC00"，"[HYC00] [Microsoft] [ODBC Microsoft Access 驱动程序]未实现可选功能(106)(SQLBindParameter)')

When I run this, I get the following error pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106) (SQLBindParameter)')

我读过这可能是因为pyodbc在传递整数值时遇到问题，因此我尝试将其转换为浮点数，因为这似乎对某些人有用.因此，我的代码变为:

I have read this might be because pyodbc has issues passing integer values, therefore I have attempted to convert to float, as this seemed to work for some people. Therefore, my code becomes:

existquery = """SELECT * FROM EconVars WHERE Year(ValueDate) = ?"""
params = (float(1999),)
cursor.execute(existquery,params)

运行此代码时，程序仅停在cursor.execute行上，而不会出现错误.

When running this code, the program simply stops on the cursor.execute line with no error.

我还尝试过使用pypyodbc模块，并且在运行上面完全相同的代码时尝试使用

I have also tried using the pypyodbc module instead and when running the exact same code above

existquery = """SELECT * FROM EconVars WHERE Year(ValueDate) = ?"""
params = [1999,]
cursor.execute(existquery,params)

我收到以下错误

OSError: exception: access violation reading 0x0000000000000000

我已经确认游标连接没有问题，因为同一游标成功运行了INSERT命令.

I have confirmed that there is nothing wrong with the cursor connection because the same cursor successfully runs an INSERT command.

如果有人有任何想法，将不胜感激.

If anyone has any ideas they would be greatly appreciated.

感谢您的回应，我已将其降级为4.0.24，但错误仍然存​​在，下面的代码仍会崩溃而没有错误

Thanks for responding, I have downgraded to 4.0.24 however the error still remains, the code below will still crash out with no error

existquery = """SELECT * FROM EconVars WHERE Year(ValueDate) = ?"""
params = (1999,)
cursor.execute(existquery,params)
existData = cursor.fetchall()

另外，传递字符串也不起作用

Also, passing a string, doesnt work either

existquery = """SELECT * FROM EconVars WHERE Year(ValueDate) = ?"""
params = ('1999',)
cursor.execute(existquery,params)
existData = cursor.fetchall()

但是，我通过传递诸如

existquery = """SELECT * FROM EconVars WHERE ValueDate = ?"""
params = ('#01/04/1999#',)
cursor.execute(existquery,params)
existData = cursor.fetchall()

但是，很明显，这在查找特定年份的数据点时无济于事.由于上述日期实际上被解释为1月4日，因此也会导致英国/美国的日期问题.

However, clearly this doesnt help when looking for datapoints from a specific year. It also leads to UK / US date problems as the date above is actually interpreted as the 4th January.

推荐答案

我能够重现您的问题.当这些查询使用整数参数时，pyodbc 4.0.25中引入的更改似乎会导致对Access数据库的查询出现问题.

I was able to reproduce your issue. A change introduced in pyodbc 4.0.25 appears to cause problems with queries against an Access database when those queries use integer parameters.

该问题已在GitHub 此处上报告.同时，降级到pyodbc 4.0.24.

The issue has been reported on GitHub here. In the meantime, downgrade to pyodbc 4.0.24.

此问题已在pyodbc 4.0.27中修复.

This issue was fixed in pyodbc 4.0.27.

这篇关于“未实现可选功能"；针对Access数据库的Pyodbc查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！