MySQL Connector/Python - insert python variable to MySQL table
12,662
Solution 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)
Solution 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.
Author by
user1893354
Updated on July 03, 2022Comments
-
user1893354 almost 2 years
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]
, andresult[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?-
theBigChalk almost 11 yearsconnection = 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
-
-
regeirk over 9 yearsNice 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 originalMySQLConverter
class inconversion.py
. -
Evan about 6 yearsWhere 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/… -
Trent Yarosevich about 2 yearsIt 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.