🌦️ Automated Weather Data Logging into MySQL Database

📘 Project Overview

The Automated Weather Data Logging System is a Python-based project designed to collect, process, and store real-time weather data using the OpenWeatherMap API.
The goal is to automate weather monitoring by fetching current conditions for multiple cities and storing them in a MySQL database for further analysis, reporting, or visualization.

This project demonstrates how to build an end-to-end data automation pipeline — from data extraction (API) to transformation (Pandas) and finally to loading (MySQL).

It can be extended to build weather dashboards, forecasting models, or daily summary reports.

---

🎯 Project Objectives

✅ Fetch real-time weather data automatically using API
✅ Transform raw JSON into a clean tabular format using Pandas
✅ Store weather information securely in a MySQL database
✅ Schedule automatic data collection at fixed intervals
✅ Enable historical weather trend analysis and insights

---

🧠 Key Learning Outcomes

By completing this project, you will learn:

• How to use REST APIs in Python (requests library)
• How to manage API keys securely using .env files
• How to connect Python with MySQL databases
• How to build ETL pipelines (Extract → Transform → Load)
• How to automate data collection with time.sleep() or schedulers
• How to prepare weather data for analytics and visualization

---

🗂️ Folder Structure

Automated Weather Data Logging into MySQL Database/ │ ├── Automated Weather Data Logging into MySQL Database.ipynb # Jupyter notebook (core script) ├── .env # API and DB credentials (not uploaded) ├── weather_data.csv # saved dataset export ├── README.md # Project documentation ├── requirements.txt # Python dependencies └── /sql/ └── create_weather_table.sql # SQL table structure

---

🛠️ Tools & Technologies

Tool / Library	Purpose
Python 3.x	Main programming language
OpenWeatherMap API	Source of real-time weather data
Requests	Fetching API data
Pandas	Data cleaning and transformation
SQLAlchemy	MySQL database connection
PyMySQL	MySQL connector driver
Dotenv	Managing API and DB credentials securely
Time / Datetime	Scheduling and timestamping records
MySQL Server	Data storage for long-term analysis

---

⚙️ Setup Instructions

1️⃣ Clone the Repository

git clone https://github.com/yourusername/Automated Weather Data Logging into MySQL Database-automation.git
cd Automated Weather Data Logging into MySQL Database-automation

2️⃣ Install Dependencies

Make sure Python and pip are installed.

pip install requests pandas sqlalchemy pymysql python-dotenv

3️⃣ Configure Environment Variables

Create a .env file in your project root:

OPENWEATHER_API_KEY=your_api_key_here
DB_USER=root
DB_PASS=your_password
DB_HOST=localhost
DB_NAME=weather_db

4️⃣ Create MySQL Database

Open your MySQL shell or Workbench:

CREATE DATABASE weather_db;

---

💻 Code Overview

🔹 Step 1: Import Required Libraries

import requests
import pandas as pd
import os
import time
from datetime import datetime
from sqlalchemy import create_engine
from dotenv import load_dotenv

🔹 Step 2: Load Environment Variables

load_dotenv()
API_KEY = os.getenv("OPENWEATHER_API_KEY")
DB_CONFIG = {
    'user': os.getenv("DB_USER"),
    'password': os.getenv("DB_PASS"),
    'host': os.getenv("DB_HOST"),
    'database': os.getenv("DB_NAME")
}

🔹 Step 3: Define the Target Cities

CITIES = ["Chennai", "Delhi", "Mumbai", "Kolkata", "Bangalore"]

🔹 Step 4: Fetch Data from OpenWeather API

def fetch_weather(city):
    url = f"https://api.openweathermap.org/data/2.5/weather?q={city}&appid={API_KEY}&units=metric"
    response = requests.get(url)
    data = response.json()
    
    return {
        "city": city,
        "temperature": data["main"]["temp"],
        "humidity": data["main"]["humidity"],
        "weather": data["weather"][0]["description"],
        "wind_speed": data["wind"]["speed"],
        "timestamp": datetime.now()
    }

🔹 Step 5: Store Data into MySQL Database

def store_to_mysql(df):
    engine = create_engine(
        f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}/{DB_CONFIG['database']}"
    )
    df.to_sql("weather_data", con=engine, if_exists="append", index=False)

🔹 Step 6: Automate the Process

def automate_weather():
    all_data = []
    for city in CITIES:
        record = fetch_weather(city)
        all_data.append(record)
        time.sleep(1)  # Avoid rate limit
    df = pd.DataFrame(all_data)
    store_to_mysql(df)
    print("✅ Data logged successfully at", datetime.now())

automate_weather()

---

🧩 Database Table Structure

Column	Type	Description
city	VARCHAR(50)	City name
temperature	FLOAT	Temperature in Celsius
humidity	INT	Humidity percentage
weather	VARCHAR(100)	Description of weather (e.g., clear sky)
wind_speed	FLOAT	Wind speed in m/s
timestamp	DATETIME	Date and time of data collection

---

📈 Example Output

city	temperature	humidity	weather	wind_speed	timestamp
Chennai	32.5	66	scattered clouds	2.3	2025-10-28 10:00:00
Delhi	28.4	54	clear sky	1.9	2025-10-28 10:00:00

---

🧠 Workflow Diagram

flowchart TD
A[Start Script] --> B[Load Environment Variables]
B --> C[Fetch Data from OpenWeather API]
C --> D[Transform JSON to DataFrame]
D --> E[Store Data into MySQL]
E --> F[Automate with Scheduler]

Loading

---

📊 Use Cases

• Build Power BI or Tableau dashboards with weather data
• Compare temperature and humidity trends across cities
• Use for machine learning models (e.g., rainfall prediction)
• Integrate with IoT devices or home weather stations

---

🚀 Future Enhancements

• Add forecast API for next 5 or 7 days
• Enable email or Telegram alerts for extreme conditions
• Store data in cloud databases (AWS RDS, Google Cloud SQL)
• Add real-time dashboards using Power BI or Streamlit