Spaces:
Sleeping
Sleeping
| import sqlite3 | |
| import os | |
| def initialize_db(): | |
| """ | |
| Initialize the SQLite database with necessary tables and sample data if they don't exist. | |
| """ | |
| # Define the database path relative to the project root | |
| db_path = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', 'database', 'sustainable_farming.db')) | |
| # Create the database directory if it doesn't exist | |
| os.makedirs(os.path.dirname(db_path), exist_ok=True) | |
| with sqlite3.connect(db_path) as conn: | |
| cursor = conn.cursor() | |
| # Create farmer_advisor table | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS farmer_advisor ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| Soil_pH REAL, | |
| Soil_Moisture REAL, | |
| Temperature_C REAL, | |
| Rainfall_mm REAL, | |
| Fertilizer_Usage_kg REAL, | |
| Pesticide_Usage_kg REAL, | |
| Crop_Yield_ton REAL, | |
| Crop_Type TEXT, | |
| Sustainability_Score REAL | |
| ) | |
| """) | |
| # Create market_researcher table | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS market_researcher ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| Product TEXT, | |
| Market_Price_per_ton REAL, | |
| Demand_Index REAL, | |
| Supply_Index REAL, | |
| Competitor_Price_per_ton REAL, | |
| Economic_Indicator REAL, | |
| Weather_Impact_Score REAL, | |
| Seasonal_Factor TEXT, | |
| Consumer_Trend_Index REAL | |
| ) | |
| """) | |
| # Create recommendations table | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS recommendations ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| crop TEXT, | |
| score REAL, | |
| rationale TEXT, | |
| market_score REAL, | |
| weather_score REAL, | |
| sustainability_score REAL, | |
| carbon_score REAL, | |
| water_score REAL, | |
| erosion_score REAL, | |
| timestamp DATETIME DEFAULT CURRENT_TIMESTAMP | |
| ) | |
| """) | |
| cursor.execute('''CREATE TABLE IF NOT EXISTS users ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| username TEXT UNIQUE, | |
| farm_name TEXT, | |
| profile_picture TEXT, | |
| created_at TEXT | |
| )''') | |
| # Check if farmer_advisor table is empty and populate with sample data | |
| cursor.execute("SELECT COUNT(*) FROM farmer_advisor") | |
| if cursor.fetchone()[0] == 0: | |
| sample_data = [ | |
| (6.5, 30.0, 25.0, 50.0, 50.0, 2.0, 3.0, "tomatoes", 0.75), | |
| (6.0, 25.0, 24.0, 40.0, 45.0, 1.8, 2.8, "carrots", 0.68), | |
| (7.0, 35.0, 26.0, 60.0, 55.0, 2.2, 3.2, "wheat", 0.70), | |
| (6.2, 28.0, 23.0, 45.0, 48.0, 1.9, 2.9, "corn", 0.72) | |
| ] | |
| cursor.executemany(""" | |
| INSERT INTO farmer_advisor (Soil_pH, Soil_Moisture, Temperature_C, Rainfall_mm, | |
| Fertilizer_Usage_kg, Pesticide_Usage_kg, Crop_Yield_ton, | |
| Crop_Type, Sustainability_Score) | |
| VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) | |
| """, sample_data) | |
| # Check if market_researcher table is empty and populate with sample data | |
| cursor.execute("SELECT COUNT(*) FROM market_researcher") | |
| if cursor.fetchone()[0] == 0: | |
| sample_data = [ | |
| ("tomatoes", 950.0, 0.6, 0.4, 900.0, 0.8, 0.7, "High", 0.6), | |
| ("carrots", 800.0, 0.5, 0.5, 850.0, 0.7, 0.6, "Medium", 0.5), | |
| ("wheat", 600.0, 0.4, 0.6, 650.0, 0.9, 0.8, "Low", 0.7), | |
| ("corn", 700.0, 0.5, 0.5, 720.0, 0.8, 0.7, "Medium", 0.6) | |
| ] | |
| cursor.executemany(""" | |
| INSERT INTO market_researcher (Product, Market_Price_per_ton, Demand_Index, Supply_Index, | |
| Competitor_Price_per_ton, Economic_Indicator, | |
| Weather_Impact_Score, Seasonal_Factor, Consumer_Trend_Index) | |
| VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) | |
| """, sample_data) | |
| conn.commit() | |