Spaces:
Runtime error
Runtime error
Download app.py from Nadeem005/Project-Samarth: direct link, hf CLI and curl.
- Browser
- Download file 9.77 kB
-
https://huggingface.co/spaces/Nadeem005/Project-Samarth/resolve/main/app.py
- Command line
-
hf download hf://spaces/Nadeem005/Project-Samarth/app.py
-
curl -L -o app.py https://huggingface.co/spaces/Nadeem005/Project-Samarth/resolve/main/app.py
9.77 kB
| # -*- 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() | |