insert JSON values into a MySQL table using python

Question

I have a JSON file recover from requests.get

here is some of my JSON:

 [{"order":{"id":"B4589B26","status_order_id":5,"status_order_name":"Sent","customer_id":326
"order_products":[{"order_product":{"id":96218,"order_id":96538,"product_id":59320,}}],"customer_email":"[email protected]","customer_company":"SARL","customer_name":"user user", .....

here is my code:

token = "xxxx"

r = requests.get('url', auth=('[email protected]', token))

mydb = pymysql.connect(host='localhost',
user='root',
passwd='user',
db='ytm_db')

cursor = mydb.cursor()

data = r.json()
json_obj = json.loads(r)

for ord in json_obj["order"]:
    print("id:", ord["id"])
    print("status_id:", ord["status_order_id"])
    print('---')
    cursor.execute("INSERT INTO table_test (id, status_order_id, customer_id) VALUES (%s,%s,%s)", (ord["id"], ord["status_order_id"], ord["customer_id"]))

#close the connection to the database.
mydb.commit()
cursor.close()
print ("Done")

I have this error:

    'not {!r}'.format(s.__class__.__name__))
TypeError: the JSON object must be str, bytes or bytearray, not 'Response'

Rakesh · Accepted Answer · 2018-08-01 11:30:55Z

5

You do not need this line json_obj = json.loads(r) . r.json() returns you a json response.

Ex:

json_obj = r.json()

for ord in json_obj["order"]:
    print("id:", ord["id"])
    print("status_id:", ord["status_order_id"])
    print('---')
    cursor.execute("INSERT INTO table_test (id, status_order_id, customer_id) VALUES (%s,%s,%s)", (ord["id"], ord["status_order_id"], ord["customer_id"]))

#close the connection to the database.
mydb.commit()
cursor.close()

answered Aug 1, 2018 at 11:30

Rakesh

82.9k17 gold badges85 silver badges122 bronze badges

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

3 Comments

user_1330 Over a year ago

I tried with this code but I have this error : ** for ord in json_obj["order"]: TypeError: list indices must be integers or slices, not str** @Rakesh you have an idea please?

Rakesh Over a year ago

Looks like json_obj is a list try iterating it first

user_1330 Over a year ago

I am a beginner in python I did not understand, can you explain how to do or what to look for, please!

user_1330 · Accepted Answer · 2021-03-26 08:32:58Z

2

This is the right solution :

json_obj = r.json()

for ord in json_obj:
    print("id:", ord["order"]["id"])
    print("status_id:", ord["order"]["status_order_id"])
    print('---')
    cursor.execute("INSERT INTO table_test (id, status_order_id, customer_id) VALUES (%s,%s,%s)", (ord["order"]["id"], ord["order"]["status_order_id"], ord["order"]["customer_id"]))

#close the connection to the database.
mydb.commit()
cursor.close()

edited Mar 26, 2021 at 8:32

answered Aug 7, 2018 at 12:38

user_1330

5041 gold badge6 silver badges23 bronze badges

Collectives™ on Stack Overflow

insert JSON values into a MySQL table using python

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related