inserting json to mysql using python

Question

Hello I am able to query but unable to insert into my json field with the below code,problem is not very tough but due to my absolute new to mysql unable to figure out..every time i will get a variable call last_time first i need to insert then from second time i need to update the last_time..if i do manually i am able to do and getting output as needed..like the below photo..

import pymysql.cursors
import datetime
import json

last_time='2344' #this value i will get output from my program

connection = pymysql.connect(host='localhost',
                         user='root',
                         password='Admin...',
                         db='cl......',
                         charset='utf8mb4',
                         cursorclass=pymysql.cursors.DictCursor)
print "connect successfull"

try:
   with connection.cursor() as cursor:
      sql = "INSERT INTO `tt_inv_refresh` (`cust_id`, `res_type`, `refresh_dtls`) VALUES (%s, %s, %s)"
      cursor.execute(sql, ('2','elb','{"vot":[{"elb":"name1","last_refreshtime":last_time},{"elb":"name2","last_refreshtime":last_time}]}'))
connection.commit()

except Exception as e:
   print str(e)

print "inserted"

finally:
   connection.close()

will be obliged if anyone point out the mistake in my code...thank you

Answer 1

You have missed the quote around last_time

Correct the line with cursore.execute to

cursor.execute(sql, ('2','elb','{"vot":
      [{"elb":"name1","last_refreshtime":' + last_time + '},
      {"elb":"name2","last_refreshtime":' + last_time+ '}]}'))

To avoid such issues in future, you might consider defining an object and using json.dumps

class Elb:

    def toJSON(self):
        return json.dumps(self, default=lambda o: o.__dict__, 
        sort_keys=True, indent=4)

    # you can use array or dict | I just copied from one of my code
    # which reqd it to be an array
    def mapper(self, detailArray = []): # you can use array or dict
        self.elb = detailArray[0];
        self.last_refreshtime = detailArray[1];

So after you have set the data for an instance say,

el_instance = Elb()
el_instance.mapper(<array-with-data>)

You can call el_instance.toJSON() to get serialized data anywhere.