MySQL Python connector does not commit

Question

I have this function that it's supposed should receive json and store the values on a RDS MySQL db.

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() was already tested and it works just fine. The problem is that after running the function, the data is not saved to the DB. I think there is a problem with the commit but I don't see any errors or warning message. If I run the query manually, the data gets stored.

Here is the query that runs after parsing the json:

REPLACE INTO metrics SET metric_seq = (SELECT COALESCE(MAX(row_id), 0) % 2000 + 1 FROM metrics AS m), instance_id = 'i-03932937bd67622c4', service = 'AWS/EC2' , metric_name = 'CPUUtilization', metric_value = 0.670000

If it helps, this is the json that the function receives:

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

I'd appreciate some help.

Regards!

UPDATE:

I found that the problem was related to the formatting codes on the query template. I re wrote the function like this:

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

As you can see, I format the SELECT outside. I believe the whole problem was due to this line:

"metric_value = %(metric_value)f"

I changed to:

"metric_value = %(metric_value)s"

and now it works. I think the formatting was wrong, given a syntax error (tho I don't know how the exception was never thrown).

Thanks to everyone who took time to help me!

The title of your question refers to a failure to commit to the database, but your code and the text of the question refers to the JSON your function is processing. Does code that directly writes to the database also fail? Can you provide the minimum code that demonstrates the problem? — brycem
– brycem, Commented Apr 14, 2016 at 1:12
also can you please print out jsonMetrics['Metrics'] since your commit is inside of a for loop that depends on that not being empty — Tommy
– Tommy, Commented Apr 14, 2016 at 1:23
Where is the object arguments created? It seems to spawn out of nowhere. — Gerrat
– Gerrat, Commented Apr 14, 2016 at 1:27
...and the metric_value in your query that runs after the parsing doesn't match up to the JSON you're giving us. ...not necessarily a problem, but when a bunch of things don't add up, your question becomes more suspect. Are you showing us what is really happening? — Gerrat
– Gerrat, Commented Apr 14, 2016 at 1:33

Gerrat · Accepted Answer · 2016-04-14 03:32:07Z

1

I haven't actually used MySQL, but the docs seem to indicate that calling cursor.execute with multi=True just returns an iterator. If that is true, then it wouldn't actually insert anything - you'd need to call .next() on the iterator (or just iterate over it) to actually insert the record.

It also goes on to advise against using parameters with 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.

tl;dr: remove that parameter, as the default is False.

answered Apr 14, 2016 at 3:32

Gerrat

29.9k9 gold badges78 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Agustin · Accepted Answer · 2016-04-14 19:06:10Z

0

This was the solution. I changed:

"metric_value = %(metric_value)f"

To:

"metric_value = %(metric_value)s"

Doing some troubleshooting I found a syntax error on the SQL. Somehow the exception didn't show.

answered Apr 14, 2016 at 19:06

Agustin

417 bronze badges

Collectives™ on Stack Overflow

MySQL Python connector does not commit

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related