inserting JSON object into MySQL using Python

Question

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.

json_obj = json.loads(response.decode('utf-8')) and then you can get json_obj["products"][0]["upc"] — furas
– furas, Commented Oct 25, 2016 at 18:48
@furas I'm getting this error \Python35-32\lib\json\decoder.py", line 339, in decode obj, end = self.raw_decode(s, idx=_w(s, 0).end()) File "C:\Users\MTA\AppData\Local\Programs\Python\Python35-32\lib\json\decoder.py", line 357, in raw_decode raise JSONDecodeError("Expecting value", s, err.value) from None json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0) — Burak
– Burak, Commented Oct 25, 2016 at 18:55
you have incorrectly formatted JSON - it has to start with { and end with } - without ) — furas
– furas, Commented Oct 25, 2016 at 19:00

furas · Accepted Answer · 2016-10-25 19:02:07Z

2

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"]))

answered Oct 25, 2016 at 19:02

furas

149k12 gold badges121 silver badges171 bronze badges

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

4 Comments

Burak Over a year ago

wasn't able to insert to mysql. here's the error, raise JSONDecodeError("Expecting value", s, err.value) from None json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)

furas Over a year ago

again the same problem - I don't know why but you have incorrectly formated JSON. In your example it miss { at start.

furas Over a year ago

btw. run you url without callback=JSON_CALLBACK - it gives you not JSON result but javascript code with function JSON_CALLBACK() and with JSON as function argument.

Burak Over a year ago

Yes, you're right. I runned the URL without callback=JSON_CALLBACK and worked perfectly. Thank you for your help.

Collectives™ on Stack Overflow

inserting JSON object into MySQL using Python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related