Python 'int32' cannot be converted to a MySQL type

Question

In Python-MySQL, I have defined a table as follows:

TABLES['clusters'] = (
    "CREATE TABLE `clusters` ("
    "  `pid` int(8) NOT NULL, "
    "  `cluster` int(8), "
    "  `cluster_round` varchar(32), "
    "  PRIMARY KEY (`pid`), "
    "  FOREIGN KEY (`pid`) REFERENCES `persons` (`id`) "
    ") ENGINE=InnoDB")

for name, ddl in TABLES.iteritems():
    self.cursor.execute(ddl)

I now want to add a row as follows:

def set_clustering(self, clustering_dict, cluster_round):
    query = (
        "INSERT INTO clustering " 
        "(pid, cluster, cluster_round) "
        "VALUES (%s, %s, %s) " 
        "ON DUPLICATE KEY UPDATE cluster = cluster")

    for (pid, cluster) in clustering_dict.iteritems():
        self.cursor.execute(query, (pid, cluster, cluster_round))
    self.cnx.commit()

However, when I run this, I get the following error message:

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

Is there an error in my syntax?

Answer 1

i had a similar problem. its probably because you're trying to insert numpy int32 and mysql cant support it. you can convert a numpy int to python int by running numpyint.item().

i would recommend checking the type of each variable you're trying to insert (type(foo)) to make sure all the values are of type that mysql can support.