Spaces:
Runtime error
Runtime error
File size: 9,765 Bytes
7c67ff2 8733074 7c67ff2 8733074 7c67ff2 8733074 7c67ff2 8733074 7c67ff2 8733074 7c67ff2 8733074 65312af 8733074 65312af 7c67ff2 8733074 c5caf96 8733074 c5caf96 65312af b105a01 65312af 8733074 155a227 7c67ff2 8733074 a811e3c 7c67ff2 8733074 a811e3c 8733074 155a227 8733074 7c67ff2 a811e3c 8733074 a811e3c 8733074 a811e3c 155a227 faf0b3d 155a227 faf0b3d 155a227 faf0b3d 155a227 faf0b3d 155a227 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d 1c0ab41 faf0b3d | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 | # -*- 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()
|