Burak Burak - 11 months ago 108
MySQL Question

inserting JSON object into MySQL using Python

I'm new to python and trying to work out how to insert some JSON into MySQL table.

How can I insert my JSON Object into MySQL using Python?

Here is the code that I am using

import requests
import urllib.request
import json
import pymysql

con = pymysql.connect(host = 'localhost',user = 'root',passwd = 'root',db = 'micro')
cursor = con.cursor()
url = 'https://api.amazon.com/v1/products(onlineAvailability=true)?pageSize=100&show=upc,sku,salePrice&page=45&callback=JSON_CALLBACK&format=json'
urllib.request.urlopen(url).read()
response = urllib.request.urlopen(url).read()
json_obj = str(response, 'utf-8')

cursor.execute("INSERT INTO bestb (sku, upc, salePrice) VALUES (%s,%s,%s)", (sku, upc, salePrice))
con.commit()
con.close()

print (json_obj)


here is the JSON that i'm trying to parse.

"products": [
{
"upc": "715187763623",
"sku": 1833591,
"salePrice": 13.99
},
{
"upc": "8809269504036",
"sku": 26220187,
"salePrice": 16.99
}
]
})


Thanks in advance.

Answer Source

Use json.loads(string) to convert json string to Python object. And then you can use it as normal dictionary and list

BTW: you have incorrect JSON in your example

response = b'''{"products": [
    {
      "upc": "715187763623",
      "sku": 1833591,
      "salePrice": 13.99
    },
    {
      "upc": "8809269504036",
      "sku": 26220187,
      "salePrice": 16.99
    }
  ]
}'''

json_obj = json.loads(response.decode('utf-8'))

#print(json_obj["products"][0]["upc"])

for product in json_obj["products"]:
    print("upc:", product["upc"])
    print("sku:", product["sku"])
    print("salePrice:", product["salePrice"])
    print('---')
    cursor.execute("INSERT INTO bestb (sku, upc, salePrice) VALUES (%s,%s,%s)", (product["sku"], product["upc"], product["salePrice"]))