MySQL Connector / Python does not perform the Commit

Question

MySQL Connector / Python does not perform the Commit

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

I have the following function that must take the data from a JSON and save it in a MySQL database

def saveMetric(metrics):
    cnx     = RDS_Connect()
    cursor  = cnx.cursor()
    jsonMetrics = json.loads(metrics)
    #print type(jsonMetrics['Metrics'])
    # Every 2000 registries, the script will start overrriding 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() makes the connection to an instance of RDS, that function already tested and works correctly. The point is that after executing this function, the data does not appear in the DB. I tried executing the query by hand 3 times and it works. Here you see the result of those 3 executions.

I think there might be a problem with commit() but I would not be finding it.

If it helps, the JSON that feeds the function is like this:

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

If someone sees what is happening, I would appreciate the help.

mysql python

asked by Agustin 14.04.2016 в 02:34

source

2 answers

1

It could be the issue of the reuse of the cursors, I had problems with that at some time, you should be using a cursor by transaction instead of using the same cursor for all transactions.

Having said that, it would be worthwhile to try to update your code so that it looks more or less like this:

def saveMetric(metrics):
    cnx = RDS_Connect()    
    # Lo que resta de tu código
    for metric in jsonMetrics['Metrics']: 
        cursor  = cnx.cursor()
        # Tus operaciones
        cursor.close()
    cnx.close()

answered by 14.04.2016 в 14:30

Get id dynamically to hide and show items Variable that needs to be declared as final

score 2 · Accepted Answer

I would say that you mishandle the arguments of the query. The paramstyles of the python DB-API do not use quotes , they do not equal the arguments of the format strings. In particular, the pyformat movie is always %(name)s , without quotes and with the s of string .

Try this way to see:

save_metrics_query = (
        "REPLACE INTO metrics "
        "SET metric_seq = (SELECT COALESCE(MAX(row_id), 0) %% %(persistance)s + 1 FROM metrics AS m), "
        "instance_id = %(instance_id)s, "
        "service = %(service)s , "
        "metric_name = %(metric_name)s, "
        "metric_value = %(metric_value)s"
       )