Python Mysql Insert - tuple index out of range

Question

Here is the snippet of code I am having problem with

for x in myresult:
    sql = "INSERT INTO `writing_correction` (`autoinc`, `q_usage_id`, `ruleId`, `message`, `replacements`, `offset`, `errorLength`, `category`, `ruleIssueType`) VALUES (NULL, %d, %s, %s, %s, %d, %d, %s, %s )"
#    sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
#    sql = "INSERT INTO `writing_correction` (`autoinc`, `q_usage_id`, `ruleId`, `message`, `replacements`, `offset`, `errorLength`, `category`, `ruleIssueType`) VALUES (NULL, '100', 'ruleid', 'message', 'replacemenet', '12', '3', 'cat', 'ruleissuetype')"
#    val = ("John", "Highway 21")
#    mycursor.execute(sql, val)
    print(x[3])
    matches = tool.check(x[3])
    for y in matches:
#        sql = "INSERT INTO `writing_correction` (`autoinc`, `q_usage_id`, `ruleId`, `message`, `replacements`, `offset`, `errorLength`, `category`, `ruleIssueType`) VALUES (NULL, %d, %s, %s, %s, %d, %d, %s, %s )" % ( x[0], y.ruleId, y.message, y.replacements, y.offset , y.errorLength, y.category, y.ruleIssueType )
        val = ( [ x[0] ], (y.ruleId), (y.message), (y.replacements), [y.offset] , [y.errorLength] , (y.category), (y.ruleIssueType) )
        print(val)
#        mycursor.execute(sql , ( x[0], y.ruleId, y.message, y.replacements, y.offset , y.errorLength, y.category, y.ruleIssueType ) )
        mycursor.executemany(sql, val)

the commented code is my trial and error attempt to make it work but it is not working for some reason.

Currently I am getting following error:

File "/usr/local/lib/python3.8/dist-packages/mysql/connector/cursor.py", line 75, in __call__
    return bytes(self.params[index])
IndexError: tuple index out of range

No idea of SQL, but you can give it a try. Try replacing `mycursor.executemany(sql, val)` with `mycursor.executemany(sql % val)` And in SQL statement also en-quote the format specifiers, for example `%d` → `"%d"` — tbhaxor, Aug 09 '20 at 01:04
@tbhaxor I made this change mycursor.executemany(sql % val) and now it is giving me another error which is TypeError: executemany() missing 1 required positional argument: 'seq_params' I am sorry I have no idea about Python Programming — VIVEK SEDANI, Aug 09 '20 at 01:10
now it says that I have error in sql syntax, Do i need to escape variables ? strings has quotes and rectangular brackets ( [] ) — VIVEK SEDANI, Aug 09 '20 at 03:59
Yes you should, it is considered as a good approach, Please see this https://stackoverflow.com/questions/881194/how-do-i-escape-special-characters-in-mysql — tbhaxor, Aug 09 '20 at 04:46

score 0 · Answer 1 · answered Aug 09 '20 at 01:33

0

val should be an array of tuples. With each tuple corresponding to a row. So fill out the array at a time, and then executemany once.

so:

val = []
for y in matches:
    val.append(  ( x[0], y.ruleId, y.message, y.replacements, y.offset , y.errorLength , y.category, y.ruleIssueType ) )

print(val)

mycursor.executemany(sql, val)

ref: executemany

answered Aug 09 '20 at 01:33

danblack

12,130
2
22
41

ok, While I understood your code and applied your suggestion it is still not working, – VIVEK SEDANI Aug 09 '20 at 01:40
Do I need to Escape String ? It is giving me this error : AttributeError: 'MySQLConverter' object has no attribute '_list_to_mysql' also this, TypeError: Python 'list' cannot be converted to a MySQL type – VIVEK SEDANI Aug 09 '20 at 01:41
Is `mycursor` actually a [MySQLCursor](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor.html)? Are the values places in the tuple simple types (string or ints)? This isn't about escaping. Use the APs and not python string interpolation and you'll never need escaping. What is `tool.check`? – danblack Aug 09 '20 at 01:47
Yes it is indeed a MySQL Cursor, tool.check is from language-tool-python – VIVEK SEDANI Aug 09 '20 at 04:00

Python Mysql Insert - tuple index out of range

1 Answers1