insert_hist_data.py

import os
import sqlite3
import pandas as pd
from datetime import datetime

# 定义下载后的合并数据目录
output_dir = "merged_history_data"
db_file = "price_demand_data.db"

# 定义 SQLite 表格结构
def create_table(c):
    c.execute('''CREATE TABLE IF NOT EXISTS actual
                (region_id TEXT, settlement_date DATETIME, rrp REAL, total_demand REAL,
                scheduled_generation REAL, semi_scheduled_generation REAL,
                net_interchange REAL,
                PRIMARY KEY (region_id, settlement_date))''')

# 插入数据到表格中
def insert_data(c, conn, region_id, data):
    for index, row in data.iterrows():
        try:
            settlement_date = datetime.strptime(row['SETTLEMENTDATE'], "%Y/%m/%d %H:%M:%S")
            c.execute('''INSERT OR IGNORE INTO actual 
                        (region_id, settlement_date, rrp, total_demand, scheduled_generation,
                         semi_scheduled_generation, net_interchange) 
                         VALUES (?, ?, ?, ?, ?, ?, ?)''',
                         (region_id, settlement_date, row['RRP'], row['TOTALDEMAND'], 
                          row.get('SCADAVALUE', None), row.get('SEMI_SCADAVALUE', None), 
                          row.get('NETINTERCHANGE', None)))
        except Exception as e:
            print(f"Failed to insert data for {region_id} on {row['SETTLEMENTDATE']}: {e}")
    conn.commit()

# 处理所有合并后的文件并插入到数据库
def process_files():
    cur_dir = os.path.dirname(__file__)
    target_dir = os.path.join(cur_dir, 'data')
    # 检查目录是否存在
    if not os.path.exists(target_dir):
        # 如果不存在，则创建目录
        os.makedirs(target_dir)
        print(f"Directory {target_dir} created.")
        
    db_path = os.path.join(cur_dir, 'data/energy_data.db')
    
    # 连接到SQLite数据库
    conn = sqlite3.connect(db_path)
    c = conn.cursor()

    # 遍历所有合并后的CSV文件
    for region_file in os.listdir(output_dir):
        if region_file.endswith(".csv"):
            region_id = region_file.split('_')[0]
            file_path = os.path.join(output_dir, region_file)

            # 读取CSV文件
            try:
                data = pd.read_csv(file_path)
                print(f"Processing {region_file}...")

                # 插入数据到表格
                insert_data(c, conn, region_id, data)

            except Exception as e:
                print(f"Error processing {region_file}: {e}")

    # 关闭数据库连接
    conn.close()
    print("All files processed and data inserted into SQLite.")

# 执行流程
if __name__ == "__main__":
    process_files()