python - 如何替换 json 字段 /w null，插入 PostgreSQL 数据库

Question

我已经查看了这里讨论的一些选项，但我没有发现最适合我的情况。我将它加载到数据库并且该字段设置为 SMALLINT，但是如果尚未更新，则 api 会为例如月落时间提供“无月落”。还是我想多了，有一个更简单的解决方案？

这是我当前的代码：

#!/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")

标签： pythonsqljson

Answer 1

检查 JSON 是否包含No moonset并将其替换为None. 然后将其转换为NULL存储在数据库中的时间。

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