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?

Just a question about storing your schema in a dict. If you have references between tables (as you do), how do you make sure that persons exists before you create clusters? — Joe
– Joe, Commented Jul 23, 2014 at 14:01
I don't have an answer to that, I am also still looking for that. As a workaround, I just create the tables twice. — physicalattraction
– physicalattraction, Commented Jul 23, 2014 at 14:03

Bar Tzadok · Accepted Answer · 2019-08-03 11:13:56Z

2

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.

answered Aug 3, 2019 at 11:13

Bar Tzadok

5264 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

willyhakim · Accepted Answer · 2018-02-19 19:04:17Z

1

This is likely due to one of the sh**ty drivers for mysql. My very opinionated solution. Update to python 3 and use mysqlclient pip install mysqlclient and the problem will be solved! Voila!

answered Feb 19, 2018 at 19:04

willyhakim

3354 silver badges11 bronze badges

Collectives™ on Stack Overflow

Python 'int32' cannot be converted to a MySQL type

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related