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

Question

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.

Answer 1

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