Project-Samarth / app.py
Nadeem005's picture
Update app.py
1c0ab41 verified
Raw History Blame Contribute Delete
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()