“未实现的可选功能(106)(SQLBindParameter)" pyodbc错误 [英] "Optional feature not implemented (106) (SQLBindParameter)" error with pyodbc

问题描述

我正在努力解决这个问题.我是第一次使用Python，并试图将从Twitter收集的数据写到Access 2010数据库中.

I'm being driven nuts trying to figure this one out. I'm using Python for the first time, and trying to write data collected from twitter out to an Access 2010 database.

我正在使用的命令是:

cursor.execute('''insert into core_data(screen_name,retweet_count) values (?,?,)''', (sname,int(rcount)))

返回的错误消息是:

Traceback (most recent call last):  File "C:/Documents and Settings/Administrator/PycharmProjects/clientgauge/tw_scraper.py", line 44, in <module>
cursor.execute('''insert into core_data(screen_name,retweet_count) values (?,?,)''', (sname,int(rcount))) 
pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented  (106) (SQLBindParameter)')

我尝试了将数据传递到db的各种排列.如果删除int(rcount)条目，它将发布第一个值sname，没有任何问题.但是，一旦我尝试传递多个参数，就开始出现问题.

I've tried various permutations of passing the data into the db. If I remove the int(rcount) entry, it will post the first value, sname, without any issues. As soon as I try to pass in more than one parameter though, this is when the problems start.

我有一种感觉，我确实缺少一些基本的东西，但是我找不到任何示例，这些示例实际上与我要执行的操作具有相似的外观，并且我要执行的操作并不困难. ..user错误可能是:)

I have a feeling I'm missing something really basic, but I can't find any examples of this which actually have a similar look to what I'm trying to do, and what I'm trying is NOT difficult...user error probably :)

任何帮助将不胜感激.

干杯， 凯夫(Kev)

Cheers, Kev

完整的代码是:

from twython import Twython
import pyodbc
ACCESS_DATABASE_FILE = 'C:\\data\\ClientGauge.accdb'
ODBC_CONN_STR = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;' %ACCESS_DATABASE_FILE
cnxn = pyodbc.connect(ODBC_CONN_STR, autocommit=True)
cursor = cnxn.cursor()
APP_KEY = '<removed>'
APP_SECRET = '<removed>'
# Authenticate on twitter using keys above
t = Twython(APP_KEY, APP_SECRET, oauth_version=2)
# Obtain new access token for this session
ACCESS_TOKEN = t.obtain_access_token()
# Authenticate using new access token
t = Twython(APP_KEY, access_token=ACCESS_TOKEN)
# Carry out search
search = t.search(q='<removed>', #**supply whatever query you want here**
         count=1, result_type='recent')
tweets = search['statuses']
for tweet in tweets:
sname=tweet['user']['screen_name']
rcount=int(tweet['retweet_count'])
fcount=tweet['favorite_count']
coord=tweet['coordinates']
tzone=tweet['user']['time_zone']
cdate=tweet['created_at']
htags=tweet['entities']['hashtags']
sql = "insert into core_data(screen_name,retweet_count,favourited_count) values (?,?,?)", (str(sname),rcount,fcount)
print(sql)
cursor.execute('''insert into core_data(screen_name,retweet_count) values (?,?)''', (sname,rcount))
cursor.commit()
cnxn.close()

我正在使用MS Access 2010，pyodbc-3.0.7.win32-py3.3.exe，Python 3.3& PyCharm.

I'm using MS Access 2010, pyodbc-3.0.7.win32-py3.3.exe, Python 3.3 & PyCharm.

不要判断我的编码能力:) Python对我来说是新的.您将能够看到，我最初尝试将INSERT语句设置为字符串(sql)，并且正在使用以下命令调用游标:

Don't judge my coding prowess :) Python is new to me. You'll be able to see that I've tried setting the INSERT statement up as a string initially (sql), and I was calling the cursor using:

cursor.execute(sql)

不幸的是，这对我也不起作用！如果我将第二个参数替换为数字，例如1 ...，它仍然不起作用.令人沮丧.

Unfortunately, this didn't work for me either! If I replace the second parameter with a number such as 1...it still doesn't work. Frustrating.

推荐答案

您的参数列表中有一个多余的逗号，使您不知所措.以下代码在Python 2.7下对我有用:

You've got an extra comma in your parameters list that is messing you up. The following code works for me under Python 2.7:

import pyodbc
sname = 'Gord'
rcount = 3
cnxn = pyodbc.connect(
        'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' +
        'DBQ=C:\\Users\\Public\\Database1.accdb;')
cursor = cnxn.cursor()
sql = "insert into core_data(screen_name,retweet_count) values (?,?)"
params = (sname, int(rcount))
cursor.execute(sql, params)
cursor.commit()
cnxn.close()

事实证明，pyodbc进行交互时，存在带有整数参数的报告的问题在Python 3.x下运行时使用Access ODBC.一种可能的解决方法是尝试下载并安装pypyodbc，然后尝试以下代码(在Python 3.5.2下适用于我):

It turns out that there is a reported issue with integer parameters when pyodbc interacts with Access ODBC while running under under Python 3.x. One possible workaround would be to try downloading and installing pypyodbc and then trying this code (which works for me under Python 3.5.2):

import pypyodbc
sname = 'Gord'
rcount = 3
cnxn = pypyodbc.connect(
        'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' +
        'DBQ=C:\\Users\\Public\\Database1.accdb;')
cursor = cnxn.cursor()
sql = "insert into core_data(screen_name,retweet_count) values (?,?)"
params = (sname, rcount)
cursor.execute(sql, params)
cursor.commit()
cnxn.close()

这篇关于“未实现的可选功能(106)(SQLBindParameter)" pyodbc错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！