python - ('The SQL contains 0 parameter markers, but 50 parameters were supplied', 'HY000') or TypeError: 'tuple' object is not callable

Question

import pyodbc,nltk,array
cnxn = pyodbc.connect('Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=information_schema;User=root; Password=1234;Option=3;')
cursor = cnxn.cursor()
cursor.execute("use collegedatabase ;")
cursor.execute("select *  from sampledata ; ")
cnxn.commit()
s=[]
j=[]
x=[]
words = []
w = []
sfq = []
POS=[]
for entry in cursor:
        s.append(entry.injury_type),j.append(entry.injury_desc) 


from nltk.tokenize import PunktWordTokenizer
from nltk.corpus import stopwords
tokenizer = PunktWordTokenizer()
english_stops = set(stopwords.words('english'))
for i in range(0,26): # filter stop words
            tokenizer.tokenize(j[i])
            w.append([word for word in tokenizer.tokenize(j[i]) if word not in english_stops])

for a in range(0 , 26):#CONVERTING tokenzied text ito a string 
            sfq.append(" ".join(w[a])) 

from replacers import RegexpReplacer
replacer =  RegexpReplacer()
for a in range (0,26):#POS TAGGING
            replacer.replace(sfq[a])
            POS.append(tokenizer.tokenize(sfq[a]))                
            x.append(nltk.pos_tag(POS[a])) 
            cursor.executemany("update sampledata SET POS = %s where SRNO =%d",(x[a],a))

The error I get is:

Traceback (most recent call last):
  File "C:UsersVaibhavDropboxDUE BY MONDAY	ryingtofixerror.py", line 35, in 
    cursor.executemany("update sampledata SET POS = %s where SRNO =%d",(x[a],a))
ProgrammingError: ('The SQL contains 0 parameter markers, but 50 parameters were supplied', 'HY000')

After browsing for finding solution to the the problem I thought of replacing last line (line 35)with this statement

cursor.executemany((u'update sampledata SET POS = %s where SRNO =%d'.encode('utf-8'),(x[a],a))(u'MO-MK25'.encode('utf-8'))) 

I do not know if I am progressing in right direction or not but going through so many websites and forums I got an hint that may be there is a indentation or comma error. I do not know what else to do. By the way, the error code generated is given below:

Traceback (most recent call last):
  File "C:UsersVaibhavDropboxDUE BY MONDAY	ryingtofixerror.py", line 35, in 
    cursor.executemany((u'update sampledata SET POS = %s where SRNO =%d'.encode('utf-8'), (x[a],a)) (u'MO-MK25'.encode('utf-8')))
TypeError: 'tuple' object is not callable

Answer 1

The method executemany(sql, seq_of_parameters) executes the same SQL statement multiple times for a set of parameters. Therefore, the second argument, seq_of_parameters, must be a sequence of parameter tuples, not just a single parameter tuple:

cursor.executemany("update sampledata SET POS = ? where SRNO = ?", [(x[a], a)])

If you pass just one tuple, the cursor will assume that the first item, x[a], is a tuple of parameters. I guess it is a string of 50 characters and gets interpreted as a sequence of 50 parameters, whereas the SQL string only expects 2.

Furthermore, notice that I used ? as the placeholder symbol instead of %s, since the latter seems to be unsupported by PyODBC, as it reported that it expected 0 parameters.

In your case, you might want to use the execute() method in the loop, since you only want to run the statement once per iteration:

cursor.execute("update sampledata SET POS = ? where SRNO = ?", (x[a], a))