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()