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?

Mathew

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 – Setose

Answer 1

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)

Turki

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. – Cleancut

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: #25094450 – Quadroon

Answer 2

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.

Parricide

Answer 3

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.

Martingale

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. – Epsom