# -*- coding: utf-8 -*- """app.ipynb - Working version with Hugging Face""" import gradio as gr from langchain_community.utilities import SQLDatabase from huggingface_hub import InferenceClient import os import re # --- 1. SET UP THE DATABASE CONNECTION --- DATABASE_FILE = "project_samarth.db" DATABASE_CONNECTION_STRING = "sqlite:///" + DATABASE_FILE if not os.path.exists(DATABASE_FILE): print(f"Error: Database file not found.") db = None else: print(f"Successfully found database at: {DATABASE_FILE}") db = SQLDatabase.from_uri(DATABASE_CONNECTION_STRING) # --- 2. SET UP THE LLM (HUGGING FACE) --- HF_TOKEN = os.environ.get("HUGGINGFACEHUB_API_TOKEN") if not HF_TOKEN: print("Error: HUGGINGFACEHUB_API_TOKEN not found in secrets.") client = None else: print("Hugging Face API key found and client initialized.") client = InferenceClient(token=HF_TOKEN) # --- 3. GET DATABASE SCHEMA --- if db: table_info = db.get_table_info() print("Database schema loaded.") else: table_info = None # --- 4. HELPER FUNCTIONS --- def clean_sql_query(sql_text): """Extract and clean SQL query from LLM output""" # Remove markdown code blocks sql_text = re.sub(r'```sql\s*', '', sql_text) sql_text = re.sub(r'```\s*', '', sql_text) # Remove common prefixes sql_text = re.sub(r'^(SQL Query:|Query:)\s*', '', sql_text, flags=re.IGNORECASE) # Take only the first SQL statement sql_text = sql_text.split(';')[0].strip() return sql_text def generate_sql(question): """Generate SQL query using Hugging Face's chat completions""" # System prompt provides the instructions system_prompt = f"""Given this database schema: {table_info} Write a SQLite query to answer the user's question. Rules: - Only use tables and columns from the schema. - Return ONLY the SQL query, with no explanations. - Use proper SQLite syntax. - Use aggregations (SUM, AVG, COUNT) when needed. """ # User prompt is just the question user_prompt = question try: # Use the chat completions method response = client.chat.completions.create( model="meta-llama/Llama-3.2-3B-Instruct", messages=[ {"role": "system", "content": system_prompt}, {"role": "user", "content": user_prompt} ], max_tokens=256, temperature=0.1 ) # The response object is different, parse it correctly sql_query = response.choices[0].message.content return clean_sql_query(sql_query) except Exception as e: print(f"Error generating SQL: {e}") raise def format_result(result, sql_query, question): """Format SQL results into a natural language answer""" try: # Parse the result string import ast result_data = ast.literal_eval(result) # If it's empty if not result_data or result_data == []: return "I couldn't find any data matching your question." # Single value (like SUM, COUNT, AVG) if len(result_data) == 1 and len(result_data[0]) == 1: value = result_data[0][0] # Format based on query context if 'production' in question.lower(): return f"The rice production in Bihar in 2015 was {value:,} tonnes." elif 'rainfall' in question.lower(): return f"The rainfall was {value:,} mm." elif 'area' in question.lower(): return f"The area was {value:,} hectares." elif 'COUNT' in sql_query.upper(): return f"There are {value:,} records found." elif 'AVG' in sql_query.upper(): return f"The average is {value:,.2f}." else: return f"The answer is {value:,}." # Multiple rows - format as a readable list if len(result_data) > 1: answers = [] for row in result_data[:10]: # Limit to 10 rows if len(row) == 2: # State and value answers.append(f"{row[0]}: {row[1]:,}") else: answers.append(" - ".join([str(val) for val in row])) result_text = ", ".join(answers) if len(result_data) > 10: result_text += f" (showing 10 of {len(result_data)} results)" return result_text # Single row with multiple columns return " - ".join([str(val) for val in result_data[0]]) except: return f"The result is: {result}" def run_query(question): if not client or not db: return "Error: The system is not initialized. Please check the server logs." # Check if question is about the database question_lower = question.lower() non_db_keywords = ['what is', 'explain', 'tell me about', 'describe', 'how does', 'national action plan', 'policy', 'msp'] if any(keyword in question_lower for keyword in non_db_keywords) and not any(word in question_lower for word in ['production', 'rainfall', 'area', 'state', 'crop', 'data']): return """⚠️ I can only answer questions about the agriculture and climate data in my database. Please ask about crop production, rainfall, area under cultivation, or state-wise data.""" print(f"New question received: {question}") try: print("Generating SQL query...") sql_query = generate_sql(question) print(f"Generated SQL: {sql_query}") # Execute the query result = db.run(sql_query) print(f"Query result: {result}") # Format the result as natural language answer = format_result(result, sql_query, question) return answer except Exception as e: error_msg = str(e) print(f"Error: {error_msg}") return f"Sorry, I encountered an error while processing your question. Please try rephrasing it." # --- 5. CREATE THE QUERY FUNCTION --- def run_query(question): if not client or not db: return "Error: The system is not initialized. Please check the server logs." question_lower = question.lower() non_db_keywords = ['what is', 'explain', 'tell me about', 'describe', 'how does', 'national action plan', 'policy', 'msp'] if any(keyword in question_lower for keyword in non_db_keywords) and not any(word in question_lower for word in ['production', 'rainfall', 'area', 'state', 'crop', 'data']): return """⚠️ I can only answer questions about the agriculture and climate data in my database. Please ask about crop production, rainfall, area under cultivation, or state-wise data.""" print(f"New question received: {question}") try: print("Generating SQL query...") sql_query = generate_sql(question) print(f"Generated SQL: {sql_query}") result = db.run(sql_query) print(f"Query result: {result}") # 👇 THIS IS THE KEY CHANGE - use format_result with 3 parameters answer = format_result(result, sql_query, question) return answer except Exception as e: error_msg = str(e) print(f"Error: {error_msg}") return f"Sorry, I encountered an error while processing your question. Please try rephrasing it." # --- 6. CREATE GRADIO INTERFACE --- with gr.Blocks(theme=gr.themes.Soft(), title="Project Samarth - Agriculture Database Assistant") as demo: # Header gr.Markdown(""" # 🌾 Project Samarth - Agriculture Database Assistant Ask questions about agricultural data in natural language! """) # Example Questions Section gr.Markdown(""" ### Example Questions: - What was the rice production in Bihar in 2015? - Which state had the highest wheat production in 2020? - Compare rice production between West Bengal and Uttar Pradesh in 2017. - Show me rainfall data for Kerala - What is the average rainfall in Punjab from 2015 to 2020? """) # Input Section with gr.Row(): question_input = gr.Textbox( label="Your Question", placeholder="e.g., What was the total rice production in Bihar in 2015?", lines=3, scale=4 ) # Buttons with gr.Row(): submit_btn = gr.Button("Submit Query", variant="primary", size="lg", scale=1) clear_btn = gr.Button("Clear", variant="secondary", size="lg", scale=1) # Separate Result Box gr.Markdown("### Answer:") output = gr.Textbox( label="", lines=5, interactive=False, show_label=False, container=True, elem_classes="result-box" ) # Event Handlers submit_btn.click( fn=run_query, inputs=question_input, outputs=output ) clear_btn.click( fn=lambda: ("", ""), inputs=None, outputs=[question_input, output] ) # Clickable Examples gr.Examples( examples=[ "What was the rice production in Bihar in 2015?", "Which state had the highest wheat production in 2020?", "Show me the total crop production in Punjab for 2018.", "What was the rainfall in Kerala during 2019?", ], inputs=question_input ) # Footer gr.Markdown(""" --- 💡 **Tip:** Ask specific questions about crop production, rainfall, area under cultivation, or state-wise agricultural data. """) # --- 7. LAUNCH THE APP --- if __name__ == "__main__": demo.launch()