MySQL Python连接器不提交

Question

我有一个函数，它应该接收json并将值存储在RDS MySQL数据库中。在

def saveMetric(metrics):
    cnx     = RDS_Connect()
    cursor  = cnx.cursor()
    jsonMetrics = json.loads(metrics)
    #print type(jsonMetrics['Metrics'])
    # Every 2000 registries, the script will start overriding values
    persistance = 2000
    save_metrics_query = (
            "REPLACE INTO metrics "
            "SET metric_seq = (SELECT COALESCE(MAX(row_id), 0) %% %(persistance)d + 1 FROM metrics AS m), "
            "instance_id = \'%(instance_id)s\', "
            "service = \'%(service)s\' , "
            "metric_name = \'%(metric_name)s\', "
            "metric_value = %(metric_value)f"
           )
    for metric in jsonMetrics['Metrics']:
        formatData = {}
        formatData['persistance'] = persistance
        formatData['instance_id'] = arguments.dimensionValue
        formatData['service'] = jsonMetrics['Service']
        formatData['metric_name'] = metric
        formatData['metric_value'] = jsonMetrics['Metrics'][metric]

        print save_metrics_query % formatData

        try:
            cursor.execute(save_metrics_query, formatData, multi=True)
            logger('info','Metrics were saved successfully!')
            cnx.commit()
        except mysql.connector.Error as err:
            logger('error', "Something went wrong: %s" % err)
    cursor.close()
    cnx.close()

RDS_Connect（）已经过测试，工作正常。问题是运行函数后，数据没有保存到数据库中。我认为提交有问题，但我没有看到任何错误或警告消息。如果我手动运行查询，数据将被存储。在

以下是解析json后运行的查询：

^{pr2}$

如果有帮助的话，这是函数接收到的json：

{
"Metrics": {
    "CPUUtilization": 1.33, 
    "NetworkIn": 46428.0, 
    "NetworkOut": 38772.0
}, 
"Id": "i-03932937bd67622c4", 
"Service": "AWS/EC2"
}

我很感激你的帮助。在

问候！在

更新：

我发现问题与查询模板上的格式代码有关。 我重新写了这个函数：

def saveMetric(metrics):
    cnx     = RDS_Connect()
    jsonMetrics = json.loads(metrics)
    print json.dumps(jsonMetrics,indent=4)

    persistance = 2000
    row_id_query_template = "SELECT COALESCE(MAX(row_id), 0) % {} + 1 FROM metrics AS m"
    row_id_query = row_id_query_template.format(persistance)

    save_metrics_query = (
        "REPLACE INTO metrics "
        "SET metric_seq = (" + row_id_query + "),"
        "instance_id = %(instance_id)s,"
        "service = %(service)s,"
        "metric_name = %(metric_name)s,"
        "metric_value = %(metric_value)s"
       )

    for metric in jsonMetrics['Metrics']:
        formatData = {}
        formatData['instance_id'] = arguments.dimensionValue
        formatData['service'] = jsonMetrics['Service']
        formatData['metric_name'] = metric
        formatData['metric_value'] = jsonMetrics['Metrics'][metric]

        if arguments.verbose == True:
            print "Data: ",formatData
            print "Query Template: ",save_metrics_query.format(**formatData)

        try:
            cursor  = cnx.cursor()
            cursor.execute(save_metrics_query, formatData)
            logger('info','Metrics were saved successfully!')
            cnx.commit()
            cursor.close()
        except mysql.connector.Error as err:
            logger('error', "Something went wrong: %s" % err)

    cnx.close()

如您所见，我在外部格式化SELECT。我相信整个问题都是由于这条线：

"metric_value = %(metric_value)f"

我改成：

"metric_value = %(metric_value)s"

现在它起作用了。我认为格式化是错误的，因为有一个语法错误（但我不知道如何从未抛出异常）。在

感谢所有花时间帮助我的人！在

Answer 1

这就是解决办法。我改了：

"metric_value = %(metric_value)f"

收件人：

^{pr2}$

在进行一些故障排除时，我发现SQL上有一个语法错误。不知怎么的，这个例外没有出现。在

Answer 2

我实际上没有使用过MySQL，但是docs似乎表示用multi=True调用cursor.execute只会返回一个迭代器。如果这是真的，那么它实际上不会插入任何东西-您需要调用迭代器上的.next()来实际插入记录。在

它还建议不要将参数与multi=True一起使用：

If multi is set to True, execute() is able to execute multiple statements specified in the operation string. It returns an iterator that enables processing the result of each statement. However, using parameters does not work well in this case, and it is usually a good idea to execute each statement on its own.

删除默认参数，太长了，读不下去了。在