MySQL Connector/Python-将python变量插入MySQL表 [英] MySQL Connector/Python - insert python variable to MySQL table

问题描述

我正在尝试将python变量插入python脚本中的MySQL表中，但是它不起作用.这是我的代码

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()

这会得到错误

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

但是，当我将变量名称result[1,0]，result[1,1]和result[1,2]替换为它们的实际数值时，它确实起作用.我怀疑python正在传递实际的变量名称，而不是它们所保存的值.我该如何解决?

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?

推荐答案

假设您正在使用mysql.connector(我想是)，请定义自己的转换器类:

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)

这篇关于MySQL Connector/Python-将python变量插入MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！