Python mysql.connector cursor.execute() Incorrect number of arguments executing prepared statement

Development Code
1

MySQL is reporting an error when I use parameters in my INSERT query. I seem to be misunderstanding the documentation.
Code:

sqlCommand = "INSERT INTO role (role_id, name, notes) VALUES (%s,'%s','%s') ON DUPLICATE KEY UPDATE name='%s',notes='%s';"

    sqlParams = []
    for role in roleList:
        sqlParams.append(
            (role['role_id'],role['name'],role['notes'],role['name'],role['notes'])
            # (role['role_id'],role['name'],role['notes'])
        )
        break

    sqlConnect = mysql.connector.connect(host=mysqlHost,port=mysqlPort,user=mysqlUser,password=mysqlPass,database=mysqlDB)
    sqlCursor = sqlConnect.cursor(prepared=True)
    try:
        sqlCursor.executemany(sqlCommand,sqlParams)
    finally:
        sqlCursor.close()
        sqlConnect.close()

The error:

[(1, ‘default’, ‘Placeholder role/category, feel free to edit’, ‘default’, ‘Placeholder role/category, feel free to edit’)]

Traceback (most recent call last):
File “test-stuff.py”, line 187, in
updateDBRoles(pfRoles)
File “test-stuff.py”, line 178, in updateDBRoles
sqlCursor.executemany(sqlCommand,sqlParams)
File “/mnt/c/Users/2975/OneDrive - cmsd.bc.ca/Projects/CMSD/pffailover/dbsync/lib/python3.7/site-packages/mysql/connector/cursor_cext.py”, line 991, in executemany
self.execute(operation, params)
File “/mnt/c/Users/2975/OneDrive - cmsd.bc.ca/Projects/CMSD/pffailover/dbsync/lib/python3.7/site-packages/mysql/connector/cursor_cext.py”, line 972, in execute
msg="Incorrect number of arguments executing prepared "
mysql.connector.errors.ProgrammingError: 1210: Incorrect number of arguments executing prepared statement

2

I also tried this without success:

sqlCommand = "INSERT INTO role (role_id, name, notes) VALUES (%(roleid)s,'%(name)s','%(notes)s') ON DUPLICATE KEY UPDATE name='%(name)s',notes='%(notes)s';"

    sqlParams = []
    for role in roleList:
        sqlParams.append(
            {'roleid':role['role_id'],'name':role['name'],'notes':role['notes']}
        )
        break
    print(sqlParams)
    sqlConnect = mysql.connector.connect(host=mysqlHost,port=mysqlPort,user=mysqlUser,password=mysqlPass,database=mysqlDB)
    sqlCursor = sqlConnect.cursor(prepared=True)
    try:
        sqlCursor.executemany(sqlCommand,sqlParams)
    finally:
        sqlCursor.close()
        sqlConnect.close()

Result:

[{‘roleid’: 1, ‘name’: ‘default’, ‘notes’: ‘Placeholder role/category, feel free to edit’}]

Traceback (most recent call last):
File “/mnt/c/Users/2975/OneDrive - cmsd.bc.ca/Projects/CMSD/pffailover/dbsync/lib/python3.7/site-packages/mysql/connector/connection_cext.py”, line 465, in cmd_stmt_prepare
return self._cmysql.stmt_prepare(statement)
_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘%(roleid)s,’%(name)s’,’%(notes)s’) ON DUPLICATE KEY UPDATE name=’%(name)s’,no…’ at line 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File “test-stuff.py”, line 188, in
updateDBRoles(pfRoles)
File “test-stuff.py”, line 179, in updateDBRoles
sqlCursor.executemany(sqlCommand,sqlParams)
File “/mnt/c/Users/2975/OneDrive - cmsd.bc.ca/Projects/CMSD/pffailover/dbsync/lib/python3.7/site-packages/mysql/connector/cursor_cext.py”, line 991, in executemany
self.execute(operation, params)
File “/mnt/c/Users/2975/OneDrive - cmsd.bc.ca/Projects/CMSD/pffailover/dbsync/lib/python3.7/site-packages/mysql/connector/cursor_cext.py”, line 961, in execute
self._stmt = self._cnx.cmd_stmt_prepare(operation)
File “/mnt/c/Users/2975/OneDrive - cmsd.bc.ca/Projects/CMSD/pffailover/dbsync/lib/python3.7/site-packages/mysql/connector/connection_cext.py”, line 467, in cmd_stmt_prepare
raise errors.InterfaceError(str(err))
mysql.connector.errors.InterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘%(roleid)s,’%(name)s’,’%(notes)s’) ON DUPLICATE KEY UPDATE name=’%(name)s’,no…’ at line 1

3

MySQL expects ?'s as placeholders for prepared statements.

https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

Since it looks like you’re not using an abstraction library and instead doing raw sql statements please try what I suggested.

Actually, try reading this:

4

I figured it out. I put single quotes around some of the place holders. I did that because if putting the statement all into the string the syntax requires that to denote a string value. However, when using parameters and prepared statements, that is not necessary.
I removed the single quotes and the query works now.

Thanks for the help.

2 Likes
5

I’m glad to have been your rubber duck :duck:

Happy you got it fixed! It’s always the little things.

6

IIRC, prepared statements were not useful with MySQL (details escape we as to why? Are they done clientside?).

I’d interpolate escaped values myself, but I’d use batch queries wherever possible to cut down on network round-trips.

Also, if you’re writing an interactive app, try using a connection pool, (a queue.Queue of pre opened already authenticated connections) so you don’t have to wait for several network round-trips before issuing a query.
You can wrap the queue in context manager to automatically close/reopen connections when you’re done using it, so you have a clean context for next set of queries.

7

I fixed my issue by your tips, thank you for your kindness!

8

The problem appears to be with the amount of arguments you give in your INSERT query. The error notice indicates that the prepared statement is being supplied the wrong amount of arguments.

You’re using %s placeholders for arguments in your SQL statement, which is valid syntax. In the sqlParams list, however, you attach a tuple of five values for each role in roleList.

According to your SQL statement, you appear to have intended to supply just three arguments for each entry in the roleList. As a result, you must alter your sqlParams list to contain just three values for each tuple, as seen below:

sqlParams.append((role['role_id'],role['name'],role['notes']))

This should fix the problem you’re having.