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

What is the column type on your refresh_dtls table? In addition, can you share a traceback? — Kyle
– Kyle, Commented Sep 21, 2017 at 3:00
there are 4 column..id -int,cust_id-int,res_type-enum,refresh_dtls-json..hope this ans u asked?? — pupu
– pupu, Commented Sep 21, 2017 at 4:14

Kishor Kundan · Accepted Answer · 2017-09-21 03:20:02Z

2

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.

answered Sep 21, 2017 at 3:20

Kishor Kundan

3,1651 gold badge25 silver badges30 bronze badges

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

5 Comments

pupu Over a year ago

if i want to update again with last_time=1000

sql="UPDATE tt_inv_refresh SET refresh_dtls= JSON_SET(refresh_dtls, {"elb":"name2","last_refreshtime":' + last_time+ '}) where id = 2" 		cursor.execute(sql)

not working

Kishor Kundan Over a year ago

try with sql="UPDATE tt_inv_refresh SET refresh_dtls = " + JSON_SET(refresh_dtls, '{"elb":"name2","last_refreshtime":' + str(last_time) + '}') + +' where id = 2;' cursor.execute(sql)

Kishor Kundan Over a year ago

pythonforbeginners.com/concatenation/… is a greate place to learn why your bugs are existing. Cheers !

pupu Over a year ago

Thanks kishor again for ur wonderful reply ,name 'JSON_SET' is not defined issue is coming,i can use also JSON_REPLACE ..still same issue..where need to define in my python code..json_set??

pupu Over a year ago

is this for NULL ??

Collectives™ on Stack Overflow

inserting json to mysql using python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related