How to Replace json field /w null, inserting into PostgreSQL db

Question

I've looked at some of the options discussed here, but I am not finding most fit my situation. I load this to a db and the field is set to SMALLINT, but the api feeds "No moonset" for example for the moonset time if it hasn't been updated yet. Or am I overthinking it and there is a simpler soltution?

This is my current code:

#!/usr/bin/env python3
import requests
import json
from decouple import config
from db import *
from datetime import datetime, timedelta, date


def insert_weather():
    insert_query = '''INSERT INTO public.weather (date, temp, dwpt, rhum, prcp, wdir, wspd, pres, updte) 
    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s) 
    ON CONFLICT(date)
    DO UPDATE SET
            temp = excluded.temp, dwpt = excluded.dwpt, rhum = excluded.rhum, prcp = excluded.prcp, wdir = excluded.wdir, wspd = excluded.wspd, pres = excluded.pres, updte = excluded.updte;'''
    valuestoinset = (dt, temp, dwpt, rhum, precip, winddir, wspd, pres, updte)
    cur.execute(insert_query, valuestoinset)


def insert_astra():
    insert_query = '''INSERT INTO public.weather_date(
    date, sunrise, sunset, moonrise, moonset, moon_phase, max_temp_f, min_temp_f, totalprecip_in, updte)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    ON CONFLICT(date)
    DO UPDATE SET
         sunrise = excluded.sunrise, sunset = excluded.sunset, moonrise = excluded.moonrise, moonset = excluded.moonset, moon_phase = excluded.moon_phase, max_temp_f = excluded.max_temp_f, min_temp_f = excluded.min_temp_f, totalprecip_in = excluded.totalprecip_in, updte = excluded.updte;'''
    valuestoinset = (date, sunrise, sunset, moonrise, moonset,
                     moon_phase, max_temp_f, min_temp_f, totalprecip_in, updte)
    cur.execute(insert_query, valuestoinset)


def daterange(start_date, end_date):
    for n in range(int((end_date - start_date).days)):
        yield start_date + timedelta(n)


# Set Date values
hr = 0
a = 0
curr_hr = datetime.now().hour
end_date = date.today()
start_date = date.today() - timedelta(1)

# Set API values
ak = config('ak')
city = config('city')


# Loop through dates
for single_date in daterange(start_date, end_date):
    histurl = 'https://api.weatherapi.com/v1/history.json?key=' + \
        ak + '&q=' + city + '&dt=' + single_date.strftime("%Y-%m-%d")

    r = requests.get(histurl)
    wjson = json.loads(r.text)

    # Insert the astral data for that day
    date = wjson['forecast']['forecastday'][0]['date']
    sunrise = wjson['forecast']['forecastday'][0]['astro']['sunrise']
    sunset = wjson['forecast']['forecastday'][0]['astro']['sunrise']
    moonrise = wjson['forecast']['forecastday'][0]['astro']['moonrise']
    moonset = wjson['forecast']['forecastday'][0]['astro']['moonset']
    moon_phase = wjson['forecast']['forecastday'][0]['astro']['moon_phase']
    max_temp_f = wjson['forecast']['forecastday'][0]['day']['maxtemp_f']
    min_temp_f = wjson['forecast']['forecastday'][0]['day']['mintemp_f']
    totalprecip_in = wjson['forecast']['forecastday'][0]['day']['totalprecip_in']
    updte = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    insert_astra()

    # Loop through the 24 hours of the day
    for i in wjson['forecast']['forecastday'][0]['hour']:
        updte = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        a += 1
        dt = i['time']
        time = datetime.strptime(i['time'][11:16], '%H:%M').time()
        temp = i['temp_f']
        dwpt = i['dewpoint_f']
        rhum = i['humidity']
        precip = i['precip_in']
        winddir = i['wind_dir']
        wspd = i['wind_mph']
        pres = i['pressure_mb']
        if datetime.strptime(dt, '%Y-%m-%d %H:%M') >= datetime.combine(end_date, time):
            break
        insert_weather()

# Commit to DB
conn.commit()

print(a, "record(s) inserted successfully into table")

The Python value None should be translated to NULL by cur.execute(). — Barmar
– Barmar, Commented Jan 20, 2021 at 17:44
Right, but how do I replace that value? This is my first true python program and I have researched, but can't seem to find out how to replace the json value? — Dulanic
– Dulanic, Commented Jan 20, 2021 at 18:38

Barmar · Accepted Answer · 2021-01-20 18:40:55Z

1

Check if the JSON contains No moonset and replace it with None. This will then be converted to NULL when stored in the database.

moonset = wjson['forecast']['forecastday'][0]['astro']['moonset']
if moonset == 'No moonset':
    moonset = None

answered Jan 20, 2021 at 18:40

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

1 Comment

Dulanic Over a year ago

Thanks, I had tried that, but wasn't ware none had to be None, that threw me off.

Collectives™ on Stack Overflow

How to Replace json field /w null, inserting into PostgreSQL db

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related