如何通过Python将指定JSON链接数据导入PostgreSQL？

Hey Derek, great question! Since you're new to Pandas and DataFrames, let's break this down step by step—using Pandas is actually a super straightforward way to handle this, and it's perfect for your use case. Here's how to do it:

First, you'll need a few Python packages to make this work. Run this in your terminal:

pip install requests pandas psycopg2-binary sqlalchemy

• requests: Fetches the JSON data from the URL
• pandas: Handles data conversion to a DataFrame and simplifies database imports
• psycopg2-binary: PostgreSQL adapter for Python
• sqlalchemy: Works with Pandas to create a seamless database connection

The BOM JSON has a nested structure, so we need to extract the actual observation data first (it lives under observations -> data). Here's the code:

import requests
import pandas as pd

# Fetch the JSON data
url = "http://www.bom.gov.au/fwo/IDQ60801/IDQ60801.94182.json"
response = requests.get(url)
response.raise_for_status()  # Raise an error if the request fails (e.g., broken link)

# Parse JSON and extract the relevant data
raw_data = response.json()
weather_data = raw_data['observations']['data']

# Convert to a Pandas DataFrame
df = pd.DataFrame(weather_data)

You can run df.head() to check the first 5 rows of the DataFrame—this helps confirm you've pulled the right data before moving on.

Since you already have an expected table structure, make sure the DataFrame's columns and data types match what's in PostgreSQL. For example:

• If your table has a timestamp column, convert the corresponding DataFrame column to datetime:

  df['local_date_time_full'] = pd.to_datetime(df['local_date_time_full'])
  

• If any columns in your table don't allow null values, fill missing data (adjust values based on your use case):

  df.fillna({'air_temp': 0, 'wind_speed': 0}, inplace=True)
  

Using Pandas' to_sql() method makes this process painless. You just need to set up a database connection first:

from sqlalchemy import create_engine

# Replace these with your actual PostgreSQL credentials
db_credentials = {
    'user': 'your_db_username',
    'password': 'your_db_password',
    'host': 'localhost',  # Or your remote database host
    'port': '5432',       # Default PostgreSQL port
    'database': 'your_db_name'
}

# Create a database engine
engine = create_engine(
    f"postgresql://{db_credentials['user']}:{db_credentials['password']}@{db_credentials['host']}:{db_credentials['port']}/{db_credentials['database']}"
)

# Import the DataFrame to your table
df.to_sql(
    name='your_table_name',  # Replace with your actual table name
    con=engine,
    if_exists='append',      # Options: 'append' (add new data), 'replace' (overwrite table), 'fail' (error if table exists)
    index=False,             # Don't import the DataFrame's index as a column
    dtype=None               # Optional: Specify data types to match your table, e.g., {'air_temp': Float()}
)

After running this, your data should be safely in the PostgreSQL table!

Short answer: No, but it's highly recommended for beginners.

Without Pandas, you'd have to manually iterate over the JSON data, write SQL INSERT statements, and handle data type conversions yourself—this is more error-prone and tedious. Here's a quick example of the non-Pandas approach (just to show you what it looks like):

import psycopg2
import requests

url = "http://www.bom.gov.au/fwo/IDQ60801/IDQ60801.94182.json"
response = requests.get(url)
raw_data = response.json()['observations']['data']

# Connect to PostgreSQL
conn = psycopg2.connect(
    user='your_db_username',
    password='your_db_password',
    host='localhost',
    port='5432',
    database='your_db_name'
)
cur = conn.cursor()

# Write your INSERT query (match your table structure exactly)
insert_query = """
    INSERT INTO your_table_name (air_temp, local_date_time_full, wind_dir)
    VALUES (%s, %s, %s)
"""

# Iterate over the data and insert each row
for row in raw_data:
    cur.execute(insert_query, (row['air_temp'], row['local_date_time_full'], row['wind_dir']))

# Commit changes and close connections
conn.commit()
cur.close()
conn.close()

As you can see, this requires more manual work—so sticking with Pandas is the way to go when you're starting out.

• Test with a small subset first (e.g., df.head(10).to_sql(...)) to avoid importing hundreds of rows by mistake.
• Double-check your PostgreSQL table after import to ensure data types and values match your expectations.
• If you hit errors, verify your database credentials, table structure, and data type conversions first.

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