回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我有一个函数,它应该接收json并将值存储在RDS MySQL数据库中。在</p>
<pre><code>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()
</code></pre>
<p>RDS_Connect()已经过测试,工作正常。问题是运行函数后,数据没有保存到数据库中。我认为提交有问题,但我没有看到任何错误或警告消息。如果我手动运行查询,数据将被存储。在</p>
<p>以下是解析json后运行的查询:</p>
^{pr2}$
<p>如果有帮助的话,这是函数接收到的json:</p>
<pre><code>{
"Metrics": {
"CPUUtilization": 1.33,
"NetworkIn": 46428.0,
"NetworkOut": 38772.0
},
"Id": "i-03932937bd67622c4",
"Service": "AWS/EC2"
}
</code></pre>
<p>我很感激你的帮助。在</p>
<p>问候!在</p>
<p>更新:</p>
<p>我发现问题与查询模板上的格式代码有关。
我重新写了这个函数:</p>
<pre><code>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()
</code></pre>
<p>如您所见,我在外部格式化SELECT。我相信整个问题都是由于这条线:</p>
<pre><code>"metric_value = %(metric_value)f"
</code></pre>
<p>我改成:</p>
<pre><code>"metric_value = %(metric_value)s"
</code></pre>
<p>现在它起作用了。我认为格式化是错误的,因为有一个语法错误(但我不知道如何从未抛出异常)。在</p>
<p>感谢所有花时间帮助我的人!在</p>