Python解析JSON的正确方法：从URL读取并写入PostgreSQL的技术问询

我来帮你一步步搞定这个任务，从获取JSON数据到写入PostgreSQL，每个环节都给你清晰的代码示例和注意事项：

1. 安装必要依赖 #

首先得装两个核心库：requests用来从URL拉取数据，psycopg2-binary用来操作PostgreSQL（选binary版本是为了避免本地编译的麻烦）：

pip install requests psycopg2-binary

2. 读取并解析URL中的JSON数据 #

用requests发送GET请求拿数据，再转成Python字典。记得加异常处理，防止请求失败直接崩掉：

import requests

def fetch_json_from_url(url):
    try:
        response = requests.get(url)
        response.raise_for_status()  # 触发HTTP错误（比如404、500）
        return response.json()
    except requests.exceptions.RequestException as e:
        print(f"拉取URL数据失败: {e}")
        return None

# 替换成你的目标URL
json_data = fetch_json_from_url("https://your-target-url.com/forecast.json")
if not json_data:
    exit(1)  # 数据拉取失败就直接退出

3. 提取JSON中的字段值 #

你的JSON里forecasts是个数组，每个元素还嵌套了night和day对象。我们得遍历数组，把每个字段的值提出来（注意class是Python关键字，所以用class_代替）：

# 遍历所有forecast条目
for forecast in json_data.get("forecasts", []):
    # 提取顶层字段
    class_ = forecast.get("class")
    expire_time_gmt = forecast.get("expire_time_gmt")
    
    # 提取night嵌套对象的字段
    night_info = forecast.get("night", {})
    night_fcst_valid = night_info.get("fcst_valid")
    night_fcst_local = night_info.get("fcst_valid_local")
    night_golf_cat = night_info.get("golf_category")
    
    # 提取day嵌套对象的字段
    day_info = forecast.get("day", {})
    day_uv_warning = day_info.get("uv_warning")
    day_uv_desc = day_info.get("uv_desc")
    day_golf_index = day_info.get("golf_index")
    day_golf_cat = day_info.get("golf_category")
    
    # 可以先打印验证提取结果
    print(f"提取到的条目: 类型={class_}, UV描述={day_uv_desc}")

用.get()方法的好处是，就算JSON里某个字段缺失，也不会抛出KeyError，程序能继续跑。

4. 连接PostgreSQL并写入数据 #

首先得在PostgreSQL里建对应的表，你可以用pgAdmin或者psql工具执行下面的SQL：

CREATE TABLE weather_forecasts (
    id SERIAL PRIMARY KEY,
    class VARCHAR(50),
    expire_time_gmt BIGINT,
    night_fcst_valid BIGINT,
    night_fcst_valid_local VARCHAR(50),
    night_golf_category VARCHAR(50),
    day_uv_warning INT,
    day_uv_desc VARCHAR(50),
    day_golf_index INT,
    day_golf_category VARCHAR(50)
);

然后用Python把提取的数据写入表，一定要用参数化查询，绝对不能直接拼SQL字符串，防止SQL注入：

import psycopg2
from psycopg2 import OperationalError

def connect_to_db(db_name, db_user, db_pass, db_host, db_port):
    conn = None
    try:
        conn = psycopg2.connect(
            database=db_name,
            user=db_user,
            password=db_pass,
            host=db_host,
            port=db_port
        )
        print("PostgreSQL连接成功")
    except OperationalError as e:
        print(f"数据库连接失败: {e}")
    return conn

# 替换成你的数据库信息
db_conn = connect_to_db(
    "your_db_name",
    "your_db_user",
    "your_db_password",
    "localhost",
    "5432"
)

if db_conn:
    cursor = db_conn.cursor()
    
    # 参数化的插入SQL模板
    insert_sql = """
    INSERT INTO weather_forecasts (
        class, expire_time_gmt, night_fcst_valid, night_fcst_valid_local,
        night_golf_category, day_uv_warning, day_uv_desc, day_golf_index, day_golf_category
    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    
    # 遍历forecasts，逐个插入数据
    for forecast in json_data.get("forecasts", []):
        # 重新提取字段（也可以复用之前的变量，这里为了完整展示）
        class_ = forecast.get("class")
        expire_time_gmt = forecast.get("expire_time_gmt")
        
        night_info = forecast.get("night", {})
        night_fcst_valid = night_info.get("fcst_valid")
        night_fcst_local = night_info.get("fcst_valid_local")
        night_golf_cat = night_info.get("golf_category")
        
        day_info = forecast.get("day", {})
        day_uv_warning = day_info.get("uv_warning")
        day_uv_desc = day_info.get("uv_desc")
        day_golf_index = day_info.get("golf_index")
        day_golf_cat = day_info.get("golf_category")
        
        # 准备参数元组
        data_tuple = (
            class_, expire_time_gmt, night_fcst_valid, night_fcst_local,
            night_golf_cat, day_uv_warning, day_uv_desc, day_golf_index, day_golf_cat
        )
        
        try:
            cursor.execute(insert_sql, data_tuple)
            db_conn.commit()
            print("一条数据插入成功")
        except Exception as e:
            print(f"插入失败: {e}")
            db_conn.rollback()  # 插入失败回滚事务
    
    # 关闭资源
    cursor.close()
    db_conn.close()
    print("数据库连接已关闭")

几个关键提醒 #

• 参数化查询：这是必须的，直接拼SQL会有被注入的风险，而且容易出错。
• 异常处理：不管是HTTP请求还是数据库操作，都要加捕获，避免程序突然崩溃。
• 资源管理：用完数据库连接和游标一定要关闭，也可以用with语句自动管理（比如with psycopg2.connect(...) as conn:）。
• 字段兼容：如果JSON里的字段可能为null，要确保PostgreSQL表的字段允许空值（上面的建表语句已经默认允许）。

内容的提问来源于stack exchange，提问作者Derek Lee