CODEWITHSUNDEEP
pythonmysql
Mark Switzer
Mark Switzer

Reputation: 51

mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'tuple' cannot be converted to a MySQL type

I'm new to Python, I just couldn't find enough examples of this pattern. Running Python 3.4, with the MySQL Python Connector installed. MySQL Version 5.7.

With this set of code (I've replaced the values for user, password etc with blanks):

import mysql.connector
cnx = mysql.connector.connect(user='', 
                        password='   ',
                        host='    ',
                        database='     ')
cursor = cnx.cursor()  
PortExistsQ = "SELECT Count(*) FROM portfolio WHERE idPortfolio=%s AND YearWeek(Signal_Date)=%s AND Rank=1"
cursor.execute(PortExistsQ,(portID,Yearweek))

I get this error. 

Traceback (most recent call last):
  File "C:\Python34\lib\site-packages\mysql\connector\conversion.py", line 179, in to_mysql
    return getattr(self, "_{0}_to_mysql".format(type_name))(value)
AttributeError: 'MySQLConverter' object has no attribute '_tuple_to_mysql'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 417, in _process_params
    res = [to_mysql(i) for i in res]
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 417, in <listcomp>
    res = [to_mysql(i) for i in res]
  File "C:\Python34\lib\site-packages\mysql\connector\conversion.py", line 182, in to_mysql
    "MySQL type".format(type_name))
TypeError: Python 'tuple' cannot be converted to a MySQL type

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Python34\lib\threading.py", line 920, in _bootstrap_inner
    self.run()
  File "C:\Python34\lib\threading.py", line 868, in run
    self._target(*self._args, **self._kwargs)
  File "C:\workspace\DIY Investing\CSI\CalcP.py", line 56, in calcport
    cursor.execute(PortExistsQ,(portID,Yearweek))
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 539, in execute
    psub = _ParamSubstitutor(self._process_params(params))
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 422, in _process_params
    "Failed processing format-parameters; %s" % err)
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'tuple' cannot be converted to a MySQL type

THe SQL runs as expected in MySQL Workbench. With values for Portfolio of 2 and YearWeek of 201644, (the first value used that caused the error), the result in MySQL is Count(*) of 1.

A code snippet of 

print ("Portfolio", portID, "YearWeek", Yearweek)

Produces the following output

Portfolio 2 YearWeek (201644,)

Any suggestions?

Upvotes: 2

Views: 36964

Answers (2)

e4c5
e4c5

Reputation: 53734

The error says that a tuple cannot be converted to a mysql type and when you do your print statement, it become fairly obvious

print ("Portfolio", portID, "YearWeek", Yearweek)
Produces the following output

Portfolio 2 YearWeek (201644,)

Yearweek is a tuple use Yearweek[0] instead

Upvotes: 4

Dekel
Dekel

Reputation: 62556

Based on your example - Yearweek is a tuple (201644,), and you can't use a tuple as a value for prepared statement.

You can use this instead:

cursor.execute(PortExistsQ,(portID,Yearweek[0]))

Or

Yearweek = Yearweek[0]
cursor.execute(PortExistsQ,(portID,Yearweek))

This way the value you pass to the execute function is the 201644 (which is the first value in the tuple) and not the tuple itself.

Upvotes: 1

Related Questions