How to replace empty values from api query to a number

Question

I have a piece of code that pulls API data from an external website. The issue I am having is that during the pull it also saves the data to the database if it does not exist. Well, some of the values from the JSON data I pull are empty and the database is expecting an integer when saving. How do I convert the JSON data empty strings to default 0 ?

The Error

ValueError at /
Field 'he_displaced_threshold_ft' expected a number but got ''.

services.py

def get_runways():
    params = {
        "apiToken": "SECRET",
    }
    api_base = "https://airportdb.io/api/v1/airport/KMIA"
    api_result = requests.get(api_base, params)
    api_response = api_result.json()

    for the runway in api_response["runways"]:
        runway_data, created = Runway.objects.update_or_create(
            airport_ident=Airport.objects.get(ident=runway["airport_ident"]),
            length_ft=runway["length_ft"],
            width_ft=runway["width_ft"],
            surface=runway["surface"],
            lighted=runway["lighted"],
            closed=runway["closed"],
            le_ident=runway["le_ident"],
            le_latitude_deg=runway["le_latitude_deg"],
            le_longitude_deg=runway["le_longitude_deg"],
            le_elevation_ft=runway["le_elevation_ft"],
            le_heading_degT=runway["le_heading_degT"],
            le_displaced_threshold_ft=runway["le_displaced_threshold_ft"],
            he_ident=runway["he_ident"],
            he_latitude_deg=runway["he_latitude_deg"],
            he_longitude_deg=runway["he_longitude_deg"],
            he_elevation_ft=runway["he_elevation_ft"],
            he_heading_degT=runway["he_heading_degT"],
            he_displaced_threshold_ft=runway["he_displaced_threshold_ft"],
        )
        runway_data.save()

    return runway

models.py

class Runway(models.Model):
    airport_ident = models.ForeignKey(Airport, on_delete=models.CASCADE)
    length_ft = models.IntegerField(default=None, blank=True, null=True)
    width_ft = models.IntegerField(default=None, blank=True, null=True)
    surface = models.CharField(max_length=50, blank=True, null=True)
    lighted = models.IntegerField(default=None, blank=True, null=True)
    closed = models.PositiveBigIntegerField(default=None, blank=True, null=True)
    le_ident = models.CharField(max_length=5, blank=True, null=True)
    le_latitude_deg = models.FloatField(default=None, blank=True, null=True)
    le_longitude_deg = models.FloatField(default=None, blank=True, null=True)
    le_elevation_ft = models.IntegerField(default=None, blank=True, null=True)
    le_heading_degT = models.FloatField(default=None, blank=True, null=True)
    le_displaced_threshold_ft = models.IntegerField(blank=True, null=True)
    he_ident = models.CharField(max_length=5, blank=True, null=True)
    he_latitude_deg = models.FloatField(default=None, blank=True, null=True)
    he_longitude_deg = models.FloatField(default=None, blank=True, null=True)
    he_elevation_ft = models.IntegerField(default=None, blank=True, null=True)
    he_heading_degT = models.FloatField(default=0, blank=True, null=True)
    he_displaced_threshold_ft = models.IntegerField(default=0, blank=True, null=True)

    def __str__(self):
        return self.airport_ident.ident

user_13553146 · Answer

I would strongly recommend you to use Pandas instead of performing manual checks as it would allow for more optimized methods for parsing the data as desired and also be helpful for saving it to the database.

import pandas as pd
from pandas import json_normalize
from sqlalchemy import create_engine

apiURL = '' # Your API URL
apiResponse = requests.get(apiURL).json()

normalizedDF = json_normalize(apiResponse)
normalizedDF.fillna(0) # Replacing NaN values with 0

# Inserting dataframe to your database
innoDBEngine = create_engine(f'mysql+mysqlconnector://{DB_USER}:%s@{DB_HOST}:3306/{DB_NAME}' % quote(DB_PASS))
normalizedDF.to_sql(DB_TABLE_NAME, innoDBEngine, index = False, if_exists = 'append', chunksize = 1000)

This method is more optimized in my opinion for the given task at hand. Hope this helps!

How to replace empty values from api query to a number

1 Answers