create_database.py

import csv
import sqlite3
import os

# Database file name
db_file = 'sustainable_energy.db'

# Remove existing database if it exists
if os.path.exists(db_file):
    os.remove(db_file)

# Connect to SQLite database
conn = sqlite3.connect(db_file)
cursor = conn.cursor()

# Read CSV and get headers
csv_file = 'source_data/global-data-on-sustainable-energy.csv'

with open(csv_file, 'r', encoding='utf-8') as f:
    reader = csv.reader(f)
    headers = next(reader)
    
    # Clean headers - remove newline characters
    headers = [h.replace('\n', ' ').strip() for h in headers]
    
    # Create table with appropriate schema
    # Replace problematic characters in column names for SQL
    sql_columns = []
    for header in headers:
        # Replace spaces and special characters with underscores
        sql_col = header.replace(' ', '_').replace('(', '').replace(')', '').replace('%', 'pct').replace('$', 'USD').replace('/', '_per_')
        sql_col = sql_col.replace(',', '').replace('-', '_')
        # Remove any remaining special characters
        sql_col = ''.join(c if c.isalnum() or c == '_' else '_' for c in sql_col)
        sql_columns.append(sql_col)
    
    # Create table statement
    create_table_sql = f"""
    CREATE TABLE sustainable_energy (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        Entity TEXT,
        Year INTEGER"""
    
    # Add other columns - most will be REAL (float) to handle decimal values
    for i, col in enumerate(sql_columns[2:], start=2):  # Skip Entity and Year
        create_table_sql += f",\n        {col} REAL"
    
    create_table_sql += "\n    )"
    
    cursor.execute(create_table_sql)
    
    # Prepare insert statement
    placeholders = ','.join(['?' for _ in headers])
    insert_sql = f"INSERT INTO sustainable_energy ({','.join(['Entity', 'Year'] + sql_columns[2:])}) VALUES ({placeholders})"
    
    # Read and insert data
    rows_inserted = 0
    for row in reader:
        # Convert empty strings to None (NULL in SQL)
        processed_row = []
        for i, value in enumerate(row):
            if value == '' or value is None:
                processed_row.append(None)
            elif i == 1:  # Year column
                try:
                    processed_row.append(int(value))
                except (ValueError, TypeError):
                    processed_row.append(None)
            else:
                try:
                    # Try to convert to float
                    processed_row.append(float(value))
                except (ValueError, TypeError):
                    # If conversion fails, store as string
                    processed_row.append(value)
        
        cursor.execute(insert_sql, processed_row)
        rows_inserted += 1
        
        if rows_inserted % 100 == 0:
            print(f"Inserted {rows_inserted} rows...")
    
    print(f"Total rows inserted: {rows_inserted}")

# Commit and close
conn.commit()
conn.close()

print(f"\nDatabase '{db_file}' created successfully!")
print(f"Table 'sustainable_energy' contains {rows_inserted} rows.")