CODEWITHSUNDEEP
pythonmysql
Agustin
Agustin

Reputation: 41

MySQL Python connector does not commit

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!

Upvotes: 0

Views: 393

Answers (2)

Agustin
Agustin

Reputation: 41

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.

Upvotes: 0

Gerrat
Gerrat

Reputation: 29680

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.

Upvotes: 1

Related Questions