#!/usr/bin/env python3
"""
Initialize database schema for weather_by_met.
This script creates the weather_forecasts table if it doesn't exist.
"""
import os
import mysql.connector
from mysql.connector import Error as MySQLError
from dotenv import load_dotenv
# Load environment variables
load_dotenv()
DB_CONFIG = {
'host': os.getenv('DB_HOST', 'localhost'),
'user': os.getenv('DB_USER', 'root'),
'password': os.getenv('DB_PASSWORD', ''),
'database': os.getenv('DB_NAME', 'weather_by_met'),
'charset': 'utf8mb4',
'collation': 'utf8mb4_unicode_ci'
}
def create_schema():
"""Create the weather_forecasts table."""
connection = None
cursor = None
try:
connection = mysql.connector.connect(**DB_CONFIG)
cursor = connection.cursor()
create_table_query = """
CREATE TABLE IF NOT EXISTS weather_forecasts (
id INT AUTO_INCREMENT PRIMARY KEY,
location_id VARCHAR(10) NOT NULL,
location_name VARCHAR(100) NOT NULL,
forecast_date DATE NOT NULL,
morning_forecast VARCHAR(100),
afternoon_forecast VARCHAR(100),
night_forecast VARCHAR(100),
summary_forecast VARCHAR(100),
summary_when VARCHAR(50),
min_temp INT,
max_temp INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY unique_location_date (location_id, forecast_date),
KEY idx_forecast_date (forecast_date),
KEY idx_location_id (location_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"""
cursor.execute(create_table_query)
connection.commit()
print("✅ Successfully created weather_forecasts table")
return True
except MySQLError as e:
print(f"❌ Database error: {e}")
return False
except Exception as e:
print(f"❌ Error: {e}")
return False
finally:
if cursor:
cursor.close()
if connection:
connection.close()
if __name__ == "__main__":
print(f"Connecting to {DB_CONFIG['host']}:{DB_CONFIG.get('database', 'N/A')}")
if create_schema():
print("Schema initialization completed successfully!")
else:
print("Schema initialization failed!")
