current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

Thanks

share|improve this question
    
Anyone?........ –  user1893354 Jun 12 '13 at 14:44
    
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 Jun 12 '13 at 23:39
add comment

2 Answers

up vote 1 down vote

just had the same issue. Are you using numpy? 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. This worked for me. hth, gr~~~

share|improve this answer
add comment
up vote 1 down vote

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)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.