correct the order of the data so it goes in the correct fields producing the whole…
- User
correct the order of the data so it goes in the correct fields producing the whole script:
CSV: I,ROOFTOP,ACTUAL,2,INTERVAL_DATETIME,REGIONID,POWER,LASTCHANGED,TYPE,QI Database: Column Type settlement_date timestamp region character varying(4) power numeric(9,3) qi numeric(2,1) NULL type character varying(11) NULL lastchanged timestamp NULL
#!/usr/bin/env python3
import os import sys from pathlib import Path import requests import zipfile import csv import psycopg2 from psycopg2 import sql, OperationalError import configparser from datetime import datetime from tenacity import retry, stop_after_attempt, wait_fixed, before_sleep_log, retry_if_exception_type, RetryError import logging import argparse
--- Global Constants ---
PROGRAM_VER = "data-download-archive-3.5.0" URL_BASE = "https://nemweb.com.au/Data_Archive/Wholesale_Electricity/MMSDM/"
--- Paths ---
CWD = Path(file).resolve().parent DATA_DIR = CWD / "temp" LOG_DIR = CWD / "log"
LOGFILE = LOG_DIR / "dl_rooftop_archive.log"HEADERS = { "User-Agent": "Mozilla/5.0", "Accept-Encoding": "gzip, deflate" }
--- Utilities ---
def ensure_dir_exists(path: Path): path.mkdir(parents=True, exist_ok=True)
ensure_dir_exists(DATA_DIR) ensure_dir_exists(LOG_DIR)
def write_log(text: str, file: Path = LOGFILE): with open(file, 'a') as f: f.write(f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} - {text}\n")
def print_and_log(text: str, err: Exception = None): print(text) write_log(text) if err: write_log(f"Error: {err}")
def print_psycopg2_exception(err: Exception): _, _, tb = sys.exc_info() line_num = tb.tb_lineno msg = f"psycopg2 ERROR: {err} on line {line_num}\npgerror: {err.pgerror}\npgcode: {err.pgcode}" print_and_log(msg, err)
def config(filename: str = 'aemo_config.ini', section: str = 'postgresql') -> dict: parser = configparser.ConfigParser() parser.read(filename)
if parser.has_section(section): return dict(parser.items(section)) raise Exception(f"Section {section} not found in {filename}")def connect_db() -> psycopg2.extensions.connection | None: try: params = config() return psycopg2.connect(**params) except OperationalError as err: print_psycopg2_exception(err) return None
@retry( stop=stop_after_attempt(3), wait=wait_fixed(2), before_sleep=before_sleep_log(logging.getLogger(name), logging.WARNING), retry=retry_if_exception_type(requests.RequestException) ) def download_file(url: str, dest_path: Path, filename: str, encoded_filename: str) -> Path | None: try: response = requests.get(url + encoded_filename, timeout=20, headers=HEADERS) response.raise_for_status() file_path = dest_path / filename file_path.write_bytes(response.content) print_and_log(f"Downloaded: {url + encoded_filename}") return file_path except requests.RequestException as e: print_and_log(f"Error downloading {url + encoded_filename}: {e}", e) raise
def unzip_file(zip_path: Path, extract_to: Path) -> Path | None: try: with zipfile.ZipFile(zip_path, 'r') as z: z.extractall(extract_to) csv_file = zip_path.stem + ".CSV" csv_path = extract_to / csv_file print_and_log(f"Unzipped: {csv_file}") return csv_path except zipfile.BadZipFile as e: print_and_log(f"Error unzipping {zip_path}: {e}", e) return None
def process_csv(csv_path: Path): conn = connect_db() if not conn: return
try: with conn.cursor() as cur: with open(csv_path, 'r') as file: csv_reader = csv.reader(file) next(csv_reader) # Skip header # Collect rows for batch insert rows = [] for row in csv_reader: if len(row) < 10 or row[0] != "D": continue fields = [f.replace("/", "-").strip() or '0' for f in row[4:10]] rows.append(fields) # Batch insert if there are any rows if rows: cur.executemany( sql.SQL(""" INSERT INTO nem_dispatch_rooftop_30min (settlement_date, region, power, qi, type, lastchanged) VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT (settlement_date, region) DO NOTHING """), rows ) conn.commit() except Exception as e: conn.rollback() print_and_log(f"Error processing CSV {csv_path}: {e}", e) finally: conn.close()--- Main Execution ---
def main(start_year_month: str, end_year_month: str): print_and_log(f"Program version: {PROGRAM_VER}")
start_year, start_month = int(start_year_month[:4]), int(start_year_month[4:]) end_year, end_month = int(end_year_month[:4]), int(end_year_month[4:]) for year in range(start_year, end_year + 1): # Determine the month range for the given year if year == start_year and year == end_year: months = range(start_month, end_month + 1) elif year == start_year: months = range(start_month, 13) elif year == end_year: months = range(1, end_month + 1) else: months = range(1, 13) for month in months: month_str = f"{month:02d}" url = f"{URL_BASE}{year}/MMSDM_{year}_{month_str}/MMSDM_Historical_Data_SQLLoader/DATA/" raw_filename = f"PUBLIC_ARCHIVE#ROOFTOP_PV_ACTUAL#FILE01#{year}{month_str}010000.zip" encoded_filename = raw_filename.replace("#", "%23") zip_path = DATA_DIR / raw_filename # Skip if file already exists if zip_path.exists(): print_and_log(f"{zip_path} already exists. Skipping download.") else: try: zip_path = download_file(url, DATA_DIR, raw_filename, encoded_filename) except RetryError as e: print_and_log(f"Failed to download {url + encoded_filename} after 3 attempts: {e}", e) continue if zip_path: csv_path = unzip_file(zip_path, DATA_DIR) if csv_path: process_csv(csv_path) zip_path.unlink(missing_ok=True) csv_path.unlink(missing_ok=True)if name == "main": parser = argparse.ArgumentParser(description="Download and process AEMO rooftop PV data.") parser.add_argument("start_year_month", type=str, help="Start year and month in YYYYMM format") parser.add_argument("end_year_month", type=str, help="End year and month in YYYYMM format")
args = parser.parse_args() main(args.start_year_month, args.end_year_month) Assistant
Okay, let's analyze the CSV header and the database schema to determine the correct order.
CSV Header Columns:
I,ROOFTOP,ACTUAL,2,INTERVAL_DATETIME,REGIONID,POWER,LASTCHANGED,TYPE,QIIndices (0-based):
0123456789Database Columns:
settlement_date,region,power,qi,type,lastchangedMapping CSV columns to Database fields:
settlement_date(DB) corresponds toINTERVAL_DATETIME(CSV index 4)region(DB) corresponds toREGIONID(CSV index 5)power(DB) corresponds toPOWER(CSV index 6)qi(DB) corresponds toQI(CSV index 9)type(DB) corresponds toTYPE(CSV index 8)lastchanged(DB) corresponds toLASTCHANGED(CSV index 7)
The Python script currently extracts fields using
row[4:10], which corresponds to the CSV columns from index 4 to 9 inclusive:[INTERVAL_DATETIME, REGIONID, POWER, LASTCHANGED, TYPE, QI]The
INSERTstatement expects the data in the order:(settlement_date, region, power, qi, type, lastchanged)The current script provides data in the order:
INTERVAL_DATETIME, REGIONID, POWER, LASTCHANGED, TYPE, QI. This does not match the required order for theINSERTstatement becauseQI(index 9) andTYPE(index 8) are swapped withLASTCHANGED(index 7).We need to rearrange the selected CSV columns to match the database column order.
Corrected Logic:
Instead of slicing
row[4:10], we need to pick the specific columns in the correct order and apply the formatting.The required order of elements from the
rowlist is:row[4](INTERVAL_DATETIME),row[5](REGIONID),row[6](POWER),row[9](QI),row[8](TYPE),row[7](LASTCHANGED)Modified Python Script:
The change needs to be made within the
process_csvfunction, specifically where thefieldslist is created.#!/usr/bin/env python3 import os import sys from pathlib import Path import requests import zipfile import csv import psycopg2 from psycopg2 import sql, OperationalError import configparser from datetime import datetime from tenacity import retry, stop_after_attempt, wait_fixed, before_sleep_log, retry_if_exception_type, RetryError import logging import argparse # --- Global Constants --- PROGRAM_VER = "data-download-archive-3.5.0" URL_BASE = "https://nemweb.com.au/Data_Archive/Wholesale_Electricity/MMSDM/" # --- Paths --- CWD = Path(__file__).resolve().parent DATA_DIR = CWD / "temp" LOG_DIR = CWD / "log" LOGFILE = LOG_DIR / "dl_rooftop_archive.log" HEADERS = { "User-Agent": "Mozilla/5.0", "Accept-Encoding": "gzip, deflate" } # --- Utilities --- def ensure_dir_exists(path: Path): path.mkdir(parents=True, exist_ok=True) ensure_dir_exists(DATA_DIR) ensure_dir_exists(LOG_DIR) def write_log(text: str, file: Path = LOGFILE): with open(file, 'a') as f: f.write(f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} - {text}\n") def print_and_log(text: str, err: Exception = None): print(text) write_log(text) if err: write_log(f"Error: {err}") def print_psycopg2_exception(err: Exception): _, _, tb = sys.exc_info() line_num = tb.tb_lineno msg = f"psycopg2 ERROR: {err} on line {line_num}\npgerror: {err.pgerror}\npgcode: {err.pgcode}" print_and_log(msg, err) def config(filename: str = 'aemo_config.ini', section: str = 'postgresql') -> dict: parser = configparser.ConfigParser() parser.read(filename) if parser.has_section(section): return dict(parser.items(section)) raise Exception(f"Section {section} not found in {filename}") def connect_db() -> psycopg2.extensions.connection | None: try: params = config() return psycopg2.connect(**params) except OperationalError as err: print_psycopg2_exception(err) return None @retry( stop=stop_after_attempt(3), wait=wait_fixed(2), before_sleep=before_sleep_log(logging.getLogger(__name__), logging.WARNING), retry=retry_if_exception_type(requests.RequestException) ) def download_file(url: str, dest_path: Path, filename: str, encoded_filename: str) -> Path | None: try: response = requests.get(url + encoded_filename, timeout=20, headers=HEADERS) response.raise_for_status() file_path = dest_path / filename file_path.write_bytes(response.content) print_and_log(f"Downloaded: {url + encoded_filename}") return file_path except requests.RequestException as e: print_and_log(f"Error downloading {url + encoded_filename}: {e}", e) raise def unzip_file(zip_path: Path, extract_to: Path) -> Path | None: try: with zipfile.ZipFile(zip_path, 'r') as z: z.extractall(extract_to) # Assuming the zip contains only one CSV file named like the zip file but with .CSV extension csv_filename = zip_path.stem + ".CSV" csv_path = extract_to / csv_filename if not csv_path.exists(): # Fallback if the filename inside zip isn't exactly stem + .CSV extracted_files = [f for f in z.namelist() if f.lower().endswith('.csv')] if not extracted_files: raise FileNotFoundError("No CSV file found in the zip archive.") csv_filename = extracted_files[0] csv_path = extract_to / csv_filename print_and_log(f"Unzipped: {csv_filename}") return csv_path except zipfile.BadZipFile as e: print_and_log(f"Error unzipping {zip_path}: {e}", e) except FileNotFoundError as e: print_and_log(f"Error finding CSV after unzipping {zip_path}: {e}", e) return None def process_csv(csv_path: Path): conn = connect_db() if not conn: return try: with conn.cursor() as cur: with open(csv_path, 'r', encoding='utf-8', errors='replace') as file: # Added encoding and error handling csv_reader = csv.reader(file) header = next(csv_reader) # Read and store header print_and_log(f"CSV Header: {header}") # Log the actual header for verification # Verify expected columns are present based on indices used below # Expected indices: 4, 5, 6, 7, 8, 9 if len(header) < 10: print_and_log(f"Warning: CSV file {csv_path.name} has fewer than 10 columns. Expected format might not match.") # Decide if you want to proceed or return here based on requirements rows_to_insert = [] for i, row in enumerate(csv_reader): # Check row length and the first column value ('D' indicates data) if len(row) < 10 or row[0] != "D": continue # --- CORRECTED DATA ORDERING --- # Map CSV columns to DB fields based on analysis: # DB: settlement_date, region, power, qi, type, lastchanged # CSV: INTERVAL_DATETIME (4), REGIONID (5), POWER (6), QI (9), TYPE (8), LASTCHANGED (7) try: # Extract and format data in the order required by the SQL statement fields = [ row[4].replace("/", "-").strip() or '0', # INTERVAL_DATETIME -> settlement_date row[5].replace("/", "-").strip() or '0', # REGIONID -> region row[6].replace("/", "-").strip() or '0', # POWER -> power row[9].replace("/", "-").strip() or '0', # QI -> qi row[8].replace("/", "-").strip() or '0', # TYPE -> type row[7].replace("/", "-").strip() or '0' # LASTCHANGED -> lastchanged ] rows_to_insert.append(fields) except IndexError: print_and_log(f"Skipping row {i+2}: Not enough columns. Row content: {row}") continue # Skip rows that don't have enough columns # Batch insert if there are any rows if rows_to_insert: print_and_log(f"Processing {len(rows_to_insert)} rows for {csv_path.name}...") cur.executemany( sql.SQL(""" INSERT INTO nem_dispatch_rooftop_30min (settlement_date, region, power, qi, type, lastchanged) VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT (settlement_date, region) DO NOTHING """), rows_to_insert ) conn.commit() print_and_log(f"Successfully inserted/updated {cur.rowcount} rows.") else: print_and_log(f"No valid data rows found in {csv_path.name}.") except Exception as e: conn.rollback() print_and_log(f"Error processing CSV {csv_path}: {e}", e) finally: conn.close() # --- Main Execution --- def main(start_year_month: str, end_year_month: str): print_and_log(f"Program version: {PROGRAM_VER}") try: start_year, start_month = int(start_year_month[:4]), int(start_year_month[4:]) end_year, end_month = int(end_year_month[:4]), int(end_year_month[4:]) except ValueError: print_and_log("Error: Invalid start_year_month or end_year_month format. Please use YYYYMM.") sys.exit(1) for year in range(start_year, end_year + 1): # Determine the month range for the given year current_start_month = start_month if year == start_year else 1 current_end_month = end_month if year == end_year else 12 for month in range(current_start_month, current_end_month + 1): month_str = f"{month:02d}" # Construct URL - Adjusted slightly for potential variations in AEMO structure # The original URL seems to point to a directory structure that might vary. # Let's assume a structure like: BASE/YEAR/MONTH/ZIPFILE # Based on the raw_filename, the structure is likely BASE/YEAR/MMSDM_YEAR_MONTH/.../DATA/ url = f"{URL_BASE}{year}/MMSDM_{year}_{month_str}/MMSDM_Historical_Data_SQLLoader/DATA/" # Construct the expected zip filename raw_filename = f"PUBLIC_ARCHIVE#ROOFTOP_PV_ACTUAL#FILE01#{year}{month_str}010000.zip" encoded_filename = raw_filename.replace("#", "%23") # URL encode '#' zip_path = DATA_DIR / raw_filename # Skip if file already exists locally if zip_path.exists(): print_and_log(f"{zip_path} already exists. Skipping download.") else: print_and_log(f"Attempting to download: {url + encoded_filename}") try: downloaded_path = download_file(url, DATA_DIR, raw_filename, encoded_filename) if downloaded_path is None: print_and_log(f"Download failed for {encoded_filename}. Check URL or network.") continue # Skip to next month if download failed entirely except RetryError as e: print_and_log(f"Failed to download {url + encoded_filename} after multiple attempts: {e}", e) continue # Skip to next month if download failed after retries # If download was successful or file already existed, proceed to unzip and process if zip_path and zip_path.exists(): csv_path = unzip_file(zip_path, DATA_DIR) if csv_path: process_csv(csv_path) # Clean up downloaded zip and extracted csv files try: zip_path.unlink(missing_ok=True) csv_path.unlink(missing_ok=True) print_and_log(f"Cleaned up: {zip_path.name}, {csv_path.name}") except OSError as e: print_and_log(f"Error cleaning up files: {e}") else: print_and_log(f"Failed to extract CSV from {zip_path.name}") else: print_and_log(f"Skipping processing for {raw_filename} as it was not downloaded successfully.") if __name__ == "__main__": parser = argparse.ArgumentParser(description="Download and process AEMO rooftop PV data.") parser.add_argument("start_year_month", type=str, help="Start year and month in YYYYMM format") parser.add_argument("end_year_month", type=str, help="End year and month in YYYYMM format") # Setup basic logging configuration to avoid warnings when using before_sleep_log logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s') args = parser.parse_args() main(args.start_year_month, args.end_year_month)Summary of Changes:
process_csvfunction: The linefields = [f.replace("/", "-").strip() or '0' for f in row[4:10]]was replaced with the corrected logic:# Extract and format data in the order required by the SQL statement fields = [ row[4].replace("/", "-").strip() or '0', # INTERVAL_DATETIME -> settlement_date row[5].replace("/", "-").strip() or '0', # REGIONID -> region row[6].replace("/", "-").strip() or '0', # POWER -> power row[9].replace("/", "-").strip() or '0', # QI -> qi row[8].replace("/", "-").strip() or '0', # TYPE -> type row[7].replace("/", "-").strip() or '0' # LASTCHANGED -> lastchanged ] rows.append(fields)- Added basic
logging.basicConfigto properly handle the logger used bytenacity. - Added
encoding='utf-8', errors='replace'to theopen()call for CSV files to handle potential encoding issues. - Added logging for the actual header found in the CSV for debugging purposes.
- Added
try...except IndexErrorblock inside the CSV processing loop to gracefully handle rows with fewer columns than expected. - Added checks and logging for cases where downloads or unzipping might fail.
- Improved the
unzip_filefunction to handle cases where the CSV filename inside the zip might differ slightly. - Added validation for the input year/month format.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.