| import sqlite3 | |
| import os | |
| # ---------------------------- | |
| # 1. Create project folders | |
| # ---------------------------- | |
| folders = [ | |
| "data", | |
| "data/raw_satellites", | |
| "data/raw_satellites/china", | |
| "data/processed", | |
| "logs" | |
| ] | |
| for folder in folders: | |
| os.makedirs(folder, exist_ok=True) | |
| print("Folders created ✅") | |
| # ---------------------------- | |
| # 2. Create SQLite database | |
| # ---------------------------- | |
| db_path = "data/satellites.db" | |
| conn = sqlite3.connect(db_path) | |
| cursor = conn.cursor() | |
| print("Database created ✅") | |
| # ---------------------------- | |
| # 3. Create tables | |
| # ---------------------------- | |
| # Countries table | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS countries ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| country TEXT, | |
| country_code TEXT, | |
| country_url TEXT | |
| ) | |
| """) | |
| # Categories table | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS categories ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| country TEXT, | |
| category TEXT, | |
| category_url TEXT | |
| ) | |
| """) | |
| # Satellite index table (MOST IMPORTANT) | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS satellites ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| country TEXT, | |
| category TEXT, | |
| satellite_name TEXT, | |
| satellite_url TEXT, | |
| scraped INTEGER DEFAULT 0 | |
| ) | |
| """) | |
| conn.commit() | |
| conn.close() | |
| print("All tables created ✅") | |
| print("Database ready 🚀") | |