Question 401362 in Mysql Question posted in
The official MySQL documentation can be found here.

How do I fill in missing data when importing json data from Python to MySQL

I am rather new to all of this but I am trying use python to grab API data from a url and push it into a MySQL database. The problem I am running into is that some data is missing from the Json file and that is causing the import to stop and fail with a ‘Key Error:’. Is there a way to fill in the blanks with null or is it just bad data? Below is my code.

### Import needed Modules
import requests
import json
import mysql.connector
#from mysql.connector import errorcode


### Create a variable that has the API URL in it

api_url = 'https://prices.runescape.wiki/api/v1/osrs/mapping'

### Create a header variable for the Wiki Admins to see who is requesting the data

headers = {'user-agent': 'volume_tracker -@Discordname'}

### Do a GET request with the API URL variable and header and put answer into a variable

response = requests.get(api_url, headers=headers)

### An IF statement that checks to make sure the response code is successful
### Puts the data into a variable in json format if successful
### And prints out a confirmation of it working

if response.status_code == 200:
    data = response.json()
    print("Worked!")


#### This section is used to connect to the Mysql Database

### Creates a variable containing connection information

mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "password",
    database = "osrsprice")

### Puts the connection into a variable to use with a mysql cursor
    
mycursor = mydb.cursor()

### SQL code used to insert data into testPrices database

sqlcode = """INSERT INTO osrsprice.testPrices
            (
                examine,
                id,
                members,
                lowalch,
                value,
                highalch,
                icon,
                name
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"""


### This FOR statement takes the data variable and goes through it line-by-line
### Each line is put into the variable 'value', and used in the execute command
### After each instance of i it will commit the data to the database

#The current issue with the below line of code is that not all parts of the json file contain lowalch/highalch
#This causes the command to fail as soon as it hits one that lacks the called for information

for i in data:
    value = i   
    mycursor.execute(sqlcode, (value['examine'],value['id'],value['members'],value['lowalch'],value['value'],value['highalch'],value['icon'],value['name']))
    mydb.commit()

Here is a normal dataset:

{
    "examine": "Fabulously ancient mage protection enchanted in the 3rd Age.",
    "id": 10344,
    "members": true,
    "lowalch": 20200,
    "limit": 8,
    "value": 50500,
    "highalch": 30300,
    "icon": "3rd age amulet.png",
    "name": "3rd age amulet"
  }

Here is where the data fails on imports:

{
    "members": true,
    "name": "Calcified moth",
    "examine": "A fossilised moth infused with dwarven magic.",
    "id": 29090,
    "value": 10000,
    "icon": "Calcified moth 5.png",
    "limit": 100
  }

When I remove the problem fields from the import, everything works and all 4166 rows are entered without the removed data.

Tags:

2

Answers


  1. 0

    use value.get(‘field_name’, default_value) instead of value[‘field_name’].

    Login or Signup to reply.
  2. 0

    I believe your error is on this line:

    mycursor.execute(sqlcode, (value['examine'],value['id'],value['members'],value['lowalch'],value['value'],value['highalch'],value['icon'],value['name']))

    Because your value variable is a dict with missing keys, hence your KeyError exception.

    What you could do, is simply using the get() method from dict. This will tell Python to use a default value if the key does not exist.

    Example:

    # Key 'lowalch' is missing
data = {
    "examine": "Fabulously ancient mage protection enchanted in the 3rd Age.",
    "id": 10344,
    "members": true
}

# This will raise a KeyError exception
print(data["lowalch"])

# This will print "unknown"
print(data.get("lowalch", "unknown"))

    So in your case, you can do:

    mycursor.execute(sqlcode, (value.get('examine', 'unknown'),value.get('id', 'unknown'),value.get('members', 'unknown'),value.get('lowalch', 'unknown'),value.get('value', 'unknown'),value.get('highalch', 'unknown'),value.get('icon', 'unknown'),value.get('name', 'unknown')))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search