Complete the "processcsv" function in this python script to insert the data from the CSV…
- User
Complete the "process_csv" function in this python script to insert the data from the CSV files to the table "nem_interconnect_5m" with the following structure:
Column Type Comment run_datetime timestamp NULL intervention integer NULL interconnectorid character varying(20) interval_datetime timestamp meteredmwflow numeric(12,5) NULL mwflow numeric(12,5) NULL mwlosses numeric(12,5) NULL marginalvalue numeric(12,5) NULL violationdegree numeric(12,5) NULL mnsp integer NULL exportlimit numeric(12,5) NULL importlimit numeric(12,5) NULL marginalloss numeric(12,5) NULL exportgenconid character varying(40) NULL importgenconid character varying(40) NULL fcasexportlimit numeric(12,5) NULL fcasimportlimit numeric(12,5) NULL lastchanged timestamp NULL
Indexes PRIMARY interconnectorid, interval_datetime
Python Script:
#!/usr/bin/env python3
File name structure: PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip
URL structure: https://nemweb.com.au/Data_Archive/Wholesale_Electricity/MMSDM/YYYY/MMSDM_YYYY_MM/MMSDM_Historical_Data_SQLLoader/DATA/
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 = "dl_interconnect_5m_archive_1.0.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_interconnect_5m_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 # Extract data and map to database columns, adjusting for the CSV structure. # INTERVAL_DATETIME, REGIONID, POWER, LASTCHANGED, TYPE, QI try: run_datetime_str = row[4].strip() # e.g., "2023/01/01 00:00:00" run_datetime = datetime.strptime(run_datetime_str, "%Y/%m/%d %H:%M:%S") region = row[5].strip() power = float(row[6].strip()) if row[6].strip() else 0.0 # Ensure a float value lastchanged_str = row[7].strip() lastchanged = datetime.strptime(lastchanged_str, "%Y/%m/%d %H:%M:%S") if lastchanged_str else None type_ = row[8].strip() qi = float(row[9].strip()) if row[9].strip() else 0.0 # Ensure a float value rows.append((run_datetime,intervention,interconnectorid,interval_datetime,meteredmwflow,mwflow,mwlosses,marginalvalue,violationdegree,mnsp,exportlimit,importlimit,marginalloss,exportgenconid,importgenconid,fcasexportlimit,fcasimportlimit,lastchanged)) except (ValueError, IndexError) as e: print_and_log(f"Skipping row due to data error: {row}. Error: {e}") continue # Skip to the next row if there's an error # Batch insert if there are any rows if rows: cur.executemany( sql.SQL(""" INSERT INTO nem_interconnect_5m (run_datetime,intervention,interconnectorid,interval_datetime,meteredmwflow,mwflow,mwlosses,marginalvalue,violationdegree,mnsp,exportlimit,importlimit,marginalloss,exportgenconid,importgenconid,fcasexportlimit,fcasimportlimit,lastchanged) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (interconnectorid, interval_datetime) 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/" # PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip raw_filename = f"PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_{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
To complete the
process_csvfunction, we need to correctly parse the CSV file content, map the data to thenem_interconnect_5mtable columns, convert data types, and handle potentialNULLvalues.Based on the typical AEMO MMSDM
PUBLIC_DVD_P5MIN_INTERCONNECTORSOLNfile structure, the CSV data records (starting with 'D') generally follow this order:RECORD_TYPE(D)VERSIONINTERVAL_DATETIMEINTERCONNECTORIDRUN_DATETIMEINTERVENTIONMWFLOWMWLOSSESMARGINALVALUEVIOLATIONDEGREEMNSPEXPORTLIMITIMPORTLIMITMARGINALLOSSEXPORTGENCONIDIMPORTGENCONIDFCASIMPORTLIMITFCASEXPORTLIMITLASTCHANGEDMETEREDMWFLOW(This column is often optional or at the very end and might not be present in older files. It's marked asNULLin the database, so we handle its absence.)
The AEMO CSV files typically have a "C" record (control record) as the first line, followed by a "D" record that serves as the header for the data. Thus, we need to skip the first two lines of the CSV reader to get to the actual data.
Here's the completed
process_csvfunction:#!/usr/bin/env python3 # File name structure: PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip # URL structure: https://nemweb.com.au/Data_Archive/Wholesale_Electricity/MMSDM/YYYY/MMSDM_YYYY_MM/MMSDM_Historical_Data_SQLLoader/DATA/ 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 = "dl_interconnect_5m_archive_1.0.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_interconnect_5m_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) # Find the actual CSV file name within the zip, as it's not always just zip_path.stem + ".CSV" # It's usually the same as the zip stem, but can have different case or extensions inside. # A more robust approach would be to list contents of the zip and find the .CSV. # However, assuming AEMO standard name for now: csv_file = zip_path.stem + ".CSV" csv_path = extract_to / csv_file if not csv_path.exists(): # Fallback for common alternative casing or extension csv_file = zip_path.stem + ".csv" csv_path = extract_to / csv_file if not csv_path.exists(): # If still not found, try to list and find the first CSV csv_files_in_zip = [f for f in z.namelist() if f.lower().endswith('.csv')] if csv_files_in_zip: csv_path = extract_to / csv_files_in_zip[0] else: print_and_log(f"No CSV file found in {zip_path}") return None print_and_log(f"Unzipped: {csv_path.name}") 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) # AEMO CSV files typically have a C-record line first, then a D-record header line. # Skip both to get to the actual data rows. try: next(csv_reader) # Skip C-record (control record) next(csv_reader) # Skip D-record header except StopIteration: print_and_log(f"CSV file {csv_path} is too short or malformed (missing header/control records).") return rows_to_insert = [] for row in csv_reader: # Data rows start with 'D'. Minimum number of columns for essential data (up to LASTCHANGED) is 19. if not row or row[0].strip() != "D" or len(row) < 19: print_and_log(f"Skipping malformed or non-data row in {csv_path}: {row}") continue try: # Extract and convert data based on the identified CSV structure # CSV Column Index Mapping (from assumed AEMO PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN structure): # D,VERSION,INTERVAL_DATETIME(2),INTERCONNECTORID(3),RUN_DATETIME(4),INTERVENTION(5), # MWFLOW(6),MWLOSSES(7),MARGINALVALUE(8),VIOLATIONDEGREE(9),MNSP(10), # EXPORTLIMIT(11),IMPORTLIMIT(12),MARGINALLOSS(13),EXPORTGENCONID(14),IMPORTGENCONID(15), # FCASIMPORTLIMIT(16),FCASEXPORTLIMIT(17),LASTCHANGED(18),METEREDMWFLOW(19) (optional) # 1. run_datetime (timestamp NULL) - CSV index 4 run_datetime_str = row[4].strip() run_datetime = datetime.strptime(run_datetime_str, "%Y/%m/%d %H:%M:%S") if run_datetime_str else None # 2. intervention (integer NULL) - CSV index 5 intervention = int(row[5].strip()) if row[5].strip() else None # 3. interconnectorid (character varying(20)) - CSV index 3. Must not be NULL (part of PK) interconnectorid = row[3].strip() if not interconnectorid: raise ValueError("interconnectorid cannot be empty or NULL.") # 4. interval_datetime (timestamp) - CSV index 2. Must not be NULL (part of PK) interval_datetime_str = row[2].strip() interval_datetime = datetime.strptime(interval_datetime_str, "%Y/%m/%d %H:%M:%S") if not interval_datetime: raise ValueError("interval_datetime cannot be empty or NULL.") # 5. meteredmwflow (numeric(12,5) NULL) - CSV index 19 (optional) # Check if row has enough columns for meteredmwflow meteredmwflow = float(row[19].strip()) if len(row) > 19 and row[19].strip() else None # 6. mwflow (numeric(12,5) NULL) - CSV index 6 mwflow = float(row[6].strip()) if row[6].strip() else None # 7. mwlosses (numeric(12,5) NULL) - CSV index 7 mwlosses = float(row[7].strip()) if row[7].strip() else None # 8. marginalvalue (numeric(12,5) NULL) - CSV index 8 marginalvalue = float(row[8].strip()) if row[8].strip() else None # 9. violationdegree (numeric(12,5) NULL) - CSV index 9 violationdegree = float(row[9].strip()) if row[9].strip() else None # 10. mnsp (integer NULL) - CSV index 10 mnsp = int(row[10].strip()) if row[10].strip() else None # 11. exportlimit (numeric(12,5) NULL) - CSV index 11 exportlimit = float(row[11].strip()) if row[11].strip() else None # 12. importlimit (numeric(12,5) NULL) - CSV index 12 importlimit = float(row[12].strip()) if row[12].strip() else None # 13. marginalloss (numeric(12,5) NULL) - CSV index 13 marginalloss = float(row[13].strip()) if row[13].strip() else None # 14. exportgenconid (character varying(40) NULL) - CSV index 14 exportgenconid = row[14].strip() if row[14].strip() else None # 15. importgenconid (character varying(40) NULL) - CSV index 15 importgenconid = row[15].strip() if row[15].strip() else None # 16. fcasexportlimit (numeric(12,5) NULL) - CSV index 17 fcasexportlimit = float(row[17].strip()) if row[17].strip() else None # 17. fcasimportlimit (numeric(12,5) NULL) - CSV index 16 fcasimportlimit = float(row[16].strip()) if row[16].strip() else None # 18. lastchanged (timestamp NULL) - CSV index 18 lastchanged_str = row[18].strip() lastchanged = datetime.strptime(lastchanged_str, "%Y/%m/%d %H:%M:%S") if lastchanged_str else None # Append data in the order of the database table columns rows_to_insert.append(( run_datetime, intervention, interconnectorid, interval_datetime, meteredmwflow, mwflow, mwlosses, marginalvalue, violationdegree, mnsp, exportlimit, importlimit, marginalloss, exportgenconid, importgenconid, fcasexportlimit, fcasimportlimit, lastchanged )) except (ValueError, IndexError) as e: print_and_log(f"Skipping row due to data parsing error in {csv_path}: {row}. Error: {e}") continue # Skip to the next row if there's an error # Batch insert if there are any rows if rows_to_insert: # Construct the INSERT statement with all column names in the correct order insert_query = sql.SQL(""" INSERT INTO nem_interconnect_5m ( run_datetime, intervention, interconnectorid, interval_datetime, meteredmwflow, mwflow, mwlosses, marginalvalue, violationdegree, mnsp, exportlimit, importlimit, marginalloss, exportgenconid, importgenconid, fcasexportlimit, fcasimportlimit, lastchanged ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) ON CONFLICT (interconnectorid, interval_datetime) DO NOTHING """) cur.executemany(insert_query, rows_to_insert) print_and_log(f"Inserted/skipped {len(rows_to_insert)} rows from {csv_path}") else: print_and_log(f"No valid data rows found in {csv_path} to insert.") conn.commit() except Exception as e: conn.rollback() print_and_log(f"Error processing CSV {csv_path}: {e}", e) finally: if conn: # Ensure connection is closed even if it's None initially 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/" # PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip raw_filename = f"PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_{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)- User
The rows don't line up correctly. Here are the first part of the csv to assist: I,P5MIN,INTERCONNECTORSOLN,4,RUN_DATETIME,INTERVENTION,INTERCONNECTORID,INTERVAL_DATETIME,METEREDMWFLOW,MWFLOW,MWLOSSES,MARGINALVALUE,VIOLATIONDEGREE,MNSP,EXPORTLIMIT,IMPORTLIMIT,MARGINALLOSS,EXPORTGENCONID,IMPORTGENCONID,FCASEXPORTLIMIT,FCASIMPORTLIMIT,LASTCHANGED D,P5MIN,INTERCONNECTORSOLN,4,"2024/04/01 00:05:00",0,N-Q-MNSP1,"2024/04/01 00:05:00",-8.8,-9,-0.36063,0,0,0,35.6,-88.8,1.04072,N>N_LSDU_9U6_1,QNTE_ROC,35.6,-88.8,"2024/04/01 00:00:25"
Assistant
You are absolutely correct! My apologies for the previous oversight. AEMO CSV file structures can vary significantly between datasets, and the header you provided clarifies the exact column order for
PUBLIC_DVD_P5MIN_INTERCONNECTORSOLNfiles.The key points from your provided header are:
- The "I" record shows the actual column names and their order.
- The "D" record shows the data corresponding to those columns.
RUN_DATETIMEis at index 4,INTERVENTIONat 5,INTERCONNECTORIDat 6,INTERVAL_DATETIMEat 7, andMETEREDMWFLOWat 8, etc.FCASIMPORTLIMITandFCASEXPORTLIMITare indeed swapped in their original position compared to the table definition, but are consistently listed in the header (FCASEXPORTLIMIT then FCASIMPORTLIMIT). We need to map them to the correct table columns.
Let's update the
process_csvfunction with the correct column mapping and error handling based on the provided CSV structure.#!/usr/bin/env python3 # File name structure: PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip # URL structure: https://nemweb.com.au/Data_Archive/Wholesale_Electricity/MMSDM/YYYY/MMSDM_YYYY_MM/MMSDM_Historical_Data_SQLLoader/DATA/ 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 = "dl_interconnect_5m_archive_1.0.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_interconnect_5m_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) # Find the actual CSV file name within the zip, as it's not always just zip_path.stem + ".CSV" # It's usually the same as the zip stem, but can have different case or extensions inside. csv_files_in_zip = [f for f in z.namelist() if f.lower().endswith('.csv')] if csv_files_in_zip: # Assuming the first CSV file found is the one we want csv_file_name_in_zip = csv_files_in_zip[0] csv_path = extract_to / csv_file_name_in_zip else: print_and_log(f"No CSV file found in {zip_path}") return None print_and_log(f"Unzipped: {csv_path.name}") 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) # AEMO CSV files typically have an "I" record (info) and then a "D" record (header). # Skip both to get to the actual data rows. try: next(csv_reader) # Skip "I" record (info/file metadata) next(csv_reader) # Skip "D" record (data header) except StopIteration: print_and_log(f"CSV file {csv_path} is too short or malformed (missing header/control records).") return rows_to_insert = [] for row in csv_reader: # Data rows start with 'D'. # Based on the provided header, LASTCHANGED is at index 21. # So, a valid data row should have at least 22 columns (indices 0 to 21). if not row or row[0].strip() != "D" or len(row) < 22: print_and_log(f"Skipping malformed or non-data row in {csv_path}: {row}") continue try: # Extract and convert data based on the provided CSV structure and map to DB columns. # CSV Column Mapping (0-indexed) from header: # 0: RECORD_TYPE (D) # 1: P5MIN # 2: INTERCONNECTORSOLN # 3: VERSION # 4: RUN_DATETIME # 5: INTERVENTION # 6: INTERCONNECTORID # 7: INTERVAL_DATETIME # 8: METEREDMWFLOW # 9: MWFLOW # 10: MWLOSSES # 11: MARGINALVALUE # 12: VIOLATIONDEGREE # 13: MNSP # 14: EXPORTLIMIT # 15: IMPORTLIMIT # 16: MARGINALLOSS # 17: EXPORTGENCONID # 18: IMPORTGENCONID # 19: FCASEXPORTLIMIT # 20: FCASIMPORTLIMIT # 21: LASTCHANGED # DB Column Order: # run_datetime, intervention, interconnectorid, interval_datetime, meteredmwflow, # mwflow, mwlosses, marginalvalue, violationdegree, mnsp, exportlimit, # importlimit, marginalloss, exportgenconid, importgenconid, # fcasexportlimit, fcasimportlimit, lastchanged # Helper function to convert to numeric, handling empty strings as None def to_float_or_none(value_str): return float(value_str.strip()) if value_str.strip() else None # Helper function to convert to int, handling empty strings as None def to_int_or_none(value_str): return int(value_str.strip()) if value_str.strip() else None # Helper function to convert to timestamp, handling empty strings as None def to_datetime_or_none(value_str): return datetime.strptime(value_str.strip(), "%Y/%m/%d %H:%M:%S") if value_str.strip() else None # 1. run_datetime (timestamp NULL) - CSV index 4 run_datetime = to_datetime_or_none(row[4]) # 2. intervention (integer NULL) - CSV index 5 intervention = to_int_or_none(row[5]) # 3. interconnectorid (character varying(20)) - CSV index 6. PK, must not be NULL interconnectorid = row[6].strip() if not interconnectorid: # Ensure PK is not empty raise ValueError(f"interconnectorid cannot be empty for row: {row}") # 4. interval_datetime (timestamp) - CSV index 7. PK, must not be NULL interval_datetime = to_datetime_or_none(row[7]) if not interval_datetime: # Ensure PK is not empty raise ValueError(f"interval_datetime cannot be empty for row: {row}") # 5. meteredmwflow (numeric(12,5) NULL) - CSV index 8 meteredmwflow = to_float_or_none(row[8]) # 6. mwflow (numeric(12,5) NULL) - CSV index 9 mwflow = to_float_or_none(row[9]) # 7. mwlosses (numeric(12,5) NULL) - CSV index 10 mwlosses = to_float_or_none(row[10]) # 8. marginalvalue (numeric(12,5) NULL) - CSV index 11 marginalvalue = to_float_or_none(row[11]) # 9. violationdegree (numeric(12,5) NULL) - CSV index 12 violationdegree = to_float_or_none(row[12]) # 10. mnsp (integer NULL) - CSV index 13 mnsp = to_int_or_none(row[13]) # 11. exportlimit (numeric(12,5) NULL) - CSV index 14 exportlimit = to_float_or_none(row[14]) # 12. importlimit (numeric(12,5) NULL) - CSV index 15 importlimit = to_float_or_none(row[15]) # 13. marginalloss (numeric(12,5) NULL) - CSV index 16 marginalloss = to_float_or_none(row[16]) # 14. exportgenconid (character varying(40) NULL) - CSV index 17 exportgenconid = row[17].strip() if row[17].strip() else None # 15. importgenconid (character varying(40) NULL) - CSV index 18 importgenconid = row[18].strip() if row[18].strip() else None # 16. fcasexportlimit (numeric(12,5) NULL) - CSV index 19 fcasexportlimit = to_float_or_none(row[19]) # 17. fcasimportlimit (numeric(12,5) NULL) - CSV index 20 fcasimportlimit = to_float_or_none(row[20]) # 18. lastchanged (timestamp NULL) - CSV index 21 lastchanged = to_datetime_or_none(row[21]) # Append data tuple in the exact order of the database table columns rows_to_insert.append(( run_datetime, intervention, interconnectorid, interval_datetime, meteredmwflow, mwflow, mwlosses, marginalvalue, violationdegree, mnsp, exportlimit, importlimit, marginalloss, exportgenconid, importgenconid, fcasexportlimit, fcasimportlimit, lastchanged )) except (ValueError, IndexError) as e: # Log the specific row causing the error for debugging print_and_log(f"Skipping row due to data parsing error in {csv_path}: {row}. Error: {e}") continue # Skip to the next row if there's an error # Batch insert if there are any rows if rows_to_insert: # Construct the INSERT statement with all column names in the correct order insert_query = sql.SQL(""" INSERT INTO nem_interconnect_5m ( run_datetime, intervention, interconnectorid, interval_datetime, meteredmwflow, mwflow, mwlosses, marginalvalue, violationdegree, mnsp, exportlimit, importlimit, marginalloss, exportgenconid, importgenconid, fcasexportlimit, fcasimportlimit, lastchanged ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) ON CONFLICT (interconnectorid, interval_datetime) DO NOTHING """) cur.executemany(insert_query, rows_to_insert) print_and_log(f"Inserted/skipped {len(rows_to_insert)} rows from {csv_path}") else: print_and_log(f"No valid data rows found in {csv_path} to insert.") conn.commit() except Exception as e: conn.rollback() print_and_log(f"Error processing CSV {csv_path}: {e}", e) finally: if conn: 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/" # PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_YYYYMM010000.zip raw_filename = f"PUBLIC_DVD_P5MIN_INTERCONNECTORSOLN_{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 interconnectors 5-minute data.") parser.add_argument("start_year_month", type=str, help="Start year and month in YYYYMM format (e.g., 202301)") parser.add_argument("end_year_month", type=str, help="End year and month in YYYYMM format (e.g., 202312)") args = parser.parse_args() main(args.start_year_month, args.end_year_month)Key changes made:
- Correct CSV Index Mapping: All column extractions within the
tryblock inprocess_csvhave been adjusted to match the 0-based indices provided by your CSV header (e.g.,RUN_DATETIMEfromrow[4],INTERCONNECTORIDfromrow[6], etc.). - Robust Type Conversion: Added helper functions
to_float_or_none,to_int_or_none, andto_datetime_or_noneto handle empty strings ('') in the CSV asNonefor databaseNULLvalues, and to handle the specific date/time format. - Primary Key Validation: Added explicit checks for
interconnectoridandinterval_datetimeto ensure they are not empty, as they are part of the primary key and cannot beNULL. If they are empty, aValueErroris raised to skip the row. - Minimum Row Length Check: The
if len(row) < 22 or row[0].strip() != "D":condition now correctly checks for the expected number of columns (at least 22, asLASTCHANGEDis at index 21) and that the row is a data record. - CSV Header Skipping: Confirmed that
next(csv_reader)called twice is correct to skip both the "I" (Info) and "D" (Data Header) records. unzip_fileImprovement: Madeunzip_fileslightly more robust by listing the contents of the zip file and picking the first CSV found, in case the filename casing differs fromzip_path.stem + ".CSV".- Argument Parser Description: Clarified the help messages for argparse.
This updated script should now correctly parse and insert the data from your AEMO Interconnector Solution CSV files.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.