MySQL Connector/Python - insert python variable to MySQL table

Question

I'm trying to insert a python variable into a MySQL table within a python script but it is not working. Here is my code

add_results=("INSERT INTO account_cancel_predictions"
            "(account_id,21_day_probability,flagged)"
            "Values(%(account_id)s,%(21_day_probability)s,%(flagged)s)")

data_result={
    'account_id':result[1,0],
    '21_day_probability':result[1,1],
    'flagged':result[1,2]
}

cursor.execute(add_results,data_result)

cnx.commit()
cursor.close()
cnx.close()

This gets the error

ProgrammingError: Failed processing pyformat-parameters; 'MySQLConverter' object has no attribute '_float64_to_mysql'

However, when I replace the variable names result[1,0], result[1,1], and result[1,2] with their actual numerical values it does work. I suspect python is passing the actual variable names rather than the values they hold. How do I fix this?

connection = engine.connect() connection.execute(""" update Players set Gold =(?) where Name=(?)""",(gold,name)) , maybe just a fanboy thing but I tend to use sqlalchemy with python and this tends to simplify much of this and I've never had issues using wildcards with it — theBigChalk
– theBigChalk, Commented Jun 12, 2013 at 23:39

mtrbean · Accepted Answer · 2013-10-21 19:13:49Z

24

Assuming you are using mysql.connector (I think you are), define your own converter class:

class NumpyMySQLConverter(mysql.connector.conversion.MySQLConverter):
    """ A mysql.connector Converter that handles Numpy types """

    def _float32_to_mysql(self, value):
        return float(value)

    def _float64_to_mysql(self, value):
        return float(value)

    def _int32_to_mysql(self, value):
        return int(value)

    def _int64_to_mysql(self, value):
        return int(value)

config = {
    'user'    : 'user',
    'host'    : 'localhost',
    'password': 'xxx',
    'database': 'db1'
}

conn = mysql.connector.connect(**config)
conn.set_converter_class(NumpyMySQLConverter)

answered Oct 21, 2013 at 19:13

mtrbean

9137 silver badges16 bronze badges

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

2 Comments

regeirk Over a year ago

Nice solution! However, I had to include a __init__ function in order for it to work. It might be because of my version. I used something similar to the original MySQLConverter class in conversion.py.

Evan Over a year ago

Where does one put this code? In a Jupyter notebook, in the mysql-connector library... ? I'm not familiar enough with mysql-connector or OOP in general to know. LIkewise, where would an __init__ go? See also: stackoverflow.com/questions/25093950/…

mauris · Accepted Answer · 2016-07-11 07:10:15Z

7

One of your passed values could be of type numpy.float64 which is not recognized by the MySQL connector. Cast it to a genuine python float on populating the dict.

edited Jul 11, 2016 at 7:10

mauris

43.6k16 gold badges102 silver badges132 bronze badges

answered Jul 9, 2013 at 22:49

Thomas Grill

711 bronze badge

Comments

Brad Larson · Accepted Answer · 2018-02-19 19:07:53Z

-1

If you are on python 3 and above, just use mysqlclient pip install mysqlclient. It is recommended by django and all the other drivers are bad.

edited Feb 19, 2018 at 19:07

Brad Larson

170k45 gold badges401 silver badges574 bronze badges

answered Feb 19, 2018 at 18:32

willyhakim

3354 silver badges11 bronze badges

1 Comment

Trent Yarosevich Over a year ago

It can be annoying (in my opinion) to deal with spinning up cloud resources that support mysqlclient if the use-case doesn't require the C++ optimization. This is a niche scenario, but I'd rather wrangle my data types than deal with mysql installation in serverless resources if I don't need to.

Collectives™ on Stack Overflow

MySQL Connector/Python - insert python variable to MySQL table

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related