Sustainable Energy Database

This project contains a SQLite database populated with global sustainable energy data from the CSV file.

Files

• sustainable_energy.db - SQLite database containing all the data
• source_data/global-data-on-sustainable-energy.csv - Source CSV file
• create_database.py - Python script used to create the database
• sample_queries.sql - Example SQL queries to explore the data
• app.py - Flask web application for SQL query interface
• templates/index.html - Web UI for querying the database
• start_server.sh - Script to start the web server

Database Structure

Table: sustainable_energy

Columns:

• id - Primary key (auto-increment)
• Entity - Country/region name (TEXT)
• Year - Year of the data (INTEGER)
• Access_to_electricity_pct_of_population - Percentage of population with electricity access
• Access_to_clean_fuels_for_cooking - Access to clean cooking fuels
• Renewable_electricity_generating_capacity_per_capita - Renewable capacity per person
• Financial_flows_to_developing_countries_US_USD - Financial flows in USD
• Renewable_energy_share_in_the_total_final_energy_consumption_pct - Renewable energy percentage
• Electricity_from_fossil_fuels_TWh - Electricity from fossil fuels in TWh
• Electricity_from_nuclear_TWh - Electricity from nuclear in TWh
• Electricity_from_renewables_TWh - Electricity from renewables in TWh
• Low_carbon_electricity_pct_electricity - Low-carbon electricity percentage
• Primary_energy_consumption_per_capita_kWh_per_person - Energy consumption per capita
• Energy_intensity_level_of_primary_energy_MJ_per_USD2017_PPP_GDP - Energy intensity
• Value_co2_emissions_kt_by_country - CO2 emissions in kilotons
• Renewables_pct_equivalent_primary_energy - Renewables as percentage of primary energy
• gdp_growth - GDP growth rate
• gdp_per_capita - GDP per capita
• Density_nP_per_Km2 - Population density
• Land_AreaKm2 - Land area in square kilometers
• Latitude - Geographic latitude
• Longitude - Geographic longitude

Usage

🌐 Web UI (Easiest Way!)

The simplest way to query the database is using the web interface:

# Start the web server
python3 app.py

# Or use the startup script
./start_server.sh

Then open your browser and go to: http://localhost:5000

You'll see a beautiful web interface where you can:

• Write SQL queries in a text area
• Click example queries to try them out
• See results in a formatted table
• Use Ctrl+Enter to quickly execute queries

Note: Only SELECT queries are allowed for security reasons.

Using SQLite Command Line

# Open the database
sqlite3 sustainable_energy.db

# Run a query
SELECT Entity, Year, Access_to_electricity_pct_of_population 
FROM sustainable_energy 
WHERE Entity = 'Italy';

# Run queries from a file
sqlite3 sustainable_energy.db < sample_queries.sql

# Exit SQLite
.quit

Using Python

import sqlite3

conn = sqlite3.connect('sustainable_energy.db')
cursor = conn.cursor()

cursor.execute("""
    SELECT Entity, Year, Renewable_energy_share_in_the_total_final_energy_consumption_pct
    FROM sustainable_energy
    WHERE Entity = 'Italy' AND Year >= 2010
    ORDER BY Year
""")

for row in cursor.fetchall():
    print(row)

conn.close()

Recreating the Database

If you need to recreate the database:

python3 create_database.py

Statistics

• Total Records: 3,649 rows
• Data Source: Global data on sustainable energy
• Time Period: 2000-2020 (varies by country)
• Countries: Multiple countries and regions

Notes

• Empty values in the CSV are stored as NULL in the database
• Column names have been sanitized for SQL compatibility (spaces and special characters replaced with underscores)
• The database uses SQLite, which is a file-based database that doesn't require a server