Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
541 views
in Technique[技术] by (71.8m points)

python - "Optional feature not implemented (106) (SQLBindParameter)" error with pyodbc

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.

The command I'm using is:

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

The error message being returned is:

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

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.

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

Any help would be much appreciated.

Cheers, Kev

Complete code is:

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

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

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)

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

I had the same error with this driver and pyodbc, it turned out that converting integers to floats solved the issue:

row = [1, 2, "foo"]
row = [float(x) if type(x) is int else x for x in row] #Convert all int items to floats
cursor.execute("insert into table1 (a,b,c) values (?,?,?)",row).commit()

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...