import pymysql
 
import os
import json
 
# read JSON file which is in the next parent folder
file = 'C://Users//risha//PycharmProjects//Test//video.json'
json_data= open(file).read()
json_obj = json.loads(json_data)
 
 
# do validation and checks before insert
def validate_string(val):
   if val != None:
        if type(val) is int:
            #for x in val:
            #   print(x)
            return str(val).encode('utf-8')
        else:
            return val
 
 
# connect to MySQL
con = pymysql.connect(host = '127.0.0.1',user = 'root',passwd = '',db = 'test',port ='3306')
cursor = con.cursor()
 
 
# parse json data to SQL insert
for i, item in enumerate(json_obj):
    person = validate_string(item.get("person", None))
    year = validate_string(item.get("year", None))
    company = validate_string(item.get("company", None))
 
    cursor.execute("INSERT INTO testp (person,	year,	company) VALUES (%s,	%s,	%s)", (person,	year,	company))
con.commit()
con.close()

aW1wb3J0IHB5bXlzcWwKCmltcG9ydCBvcwppbXBvcnQganNvbgoKIyByZWFkIEpTT04gZmlsZSB3aGljaCBpcyBpbiB0aGUgbmV4dCBwYXJlbnQgZm9sZGVyCmZpbGUgPSAnQzovL1VzZXJzLy9yaXNoYS8vUHljaGFybVByb2plY3RzLy9UZXN0Ly92aWRlby5qc29uJwpqc29uX2RhdGE9IG9wZW4oZmlsZSkucmVhZCgpCmpzb25fb2JqID0ganNvbi5sb2Fkcyhqc29uX2RhdGEpCgoKIyBkbyB2YWxpZGF0aW9uIGFuZCBjaGVja3MgYmVmb3JlIGluc2VydApkZWYgdmFsaWRhdGVfc3RyaW5nKHZhbCk6CiAgIGlmIHZhbCAhPSBOb25lOgogICAgICAgIGlmIHR5cGUodmFsKSBpcyBpbnQ6CiAgICAgICAgICAgICNmb3IgeCBpbiB2YWw6CiAgICAgICAgICAgICMgICBwcmludCh4KQogICAgICAgICAgICByZXR1cm4gc3RyKHZhbCkuZW5jb2RlKCd1dGYtOCcpCiAgICAgICAgZWxzZToKICAgICAgICAgICAgcmV0dXJuIHZhbAoKCiMgY29ubmVjdCB0byBNeVNRTApjb24gPSBweW15c3FsLmNvbm5lY3QoaG9zdCA9ICcxMjcuMC4wLjEnLHVzZXIgPSAncm9vdCcscGFzc3dkID0gJycsZGIgPSAndGVzdCcscG9ydCA9JzMzMDYnKQpjdXJzb3IgPSBjb24uY3Vyc29yKCkKCgojIHBhcnNlIGpzb24gZGF0YSB0byBTUUwgaW5zZXJ0CmZvciBpLCBpdGVtIGluIGVudW1lcmF0ZShqc29uX29iaik6CiAgICBwZXJzb24gPSB2YWxpZGF0ZV9zdHJpbmcoaXRlbS5nZXQoInBlcnNvbiIsIE5vbmUpKQogICAgeWVhciA9IHZhbGlkYXRlX3N0cmluZyhpdGVtLmdldCgieWVhciIsIE5vbmUpKQogICAgY29tcGFueSA9IHZhbGlkYXRlX3N0cmluZyhpdGVtLmdldCgiY29tcGFueSIsIE5vbmUpKQoKICAgIGN1cnNvci5leGVjdXRlKCJJTlNFUlQgSU5UTyB0ZXN0cCAocGVyc29uLAl5ZWFyLAljb21wYW55KSBWQUxVRVMgKCVzLAklcywJJXMpIiwgKHBlcnNvbiwJeWVhciwJY29tcGFueSkpCmNvbi5jb21taXQoKQpjb24uY2xvc2UoKQ==