A-Asif/Text2SQLQuery_Application

1from dotenv import load_dotenv
2load_dotenv()
3
4import streamlit as st
5import os
6import sqlite3
7from google import genai
8
9# Initialize Gemini client
10client = genai.Client(api_key=os.getenv("GEMINI_API_KEY"))
11
12### Function to convert user input to SQL using Gemini
13def get_gemini_sql(user_input):
14    # Prepend "Database question" to make intent explicit
15    db_input = f"Database question: {user_input}"
16
17    prompt = f"""
18You are an expert in converting English questions into SQL queries for an SQLite database.
19ONLY generate SQL. Do NOT provide explanations, notes, or general knowledge.
20Any response that is not valid SQL should be ignored.
21
22Database table: STUDENT
23Columns: NAME, CLASS, SECTION
24
25Examples:
26- Question: How many students are studying in Artificial Intelligence class?
27  SQL: SELECT NAME FROM STUDENT WHERE CLASS='Artificial Intelligence';
28- Question: How many records are in the database?
29  SQL: SELECT COUNT(*) FROM STUDENT;
30
31Database Question: {db_input}
32SQL Query:
33"""
34
35    # Call Gemini
36    response = client.models.generate_content(
37        model="gemini-2.5-pro",
38        contents=prompt
39    )
40
41    # Clean up output for SQLite
42    sql_query = response.text.strip().replace('"', "'").strip(" ;")
43
44    # Validate SQL starts with a common command
45    if not sql_query.lower().startswith(("select", "insert", "update", "delete", "count")):
46        return None
47    return sql_query
48
49
50### Function to execute SQL query
51def read_sql_query(sql, db="students.db"):
52    connection = sqlite3.connect(db)
53    cur = connection.cursor()
54    cur.execute(sql)
55    rows = cur.fetchall()
56    connection.close()
57    return rows
58
59
60### Streamlit App
61st.set_page_config(page_title="Gemini Text-to-SQL")
62st.header("Gemini App to Retrieve SQL Data")
63
64user_input = st.text_input("Enter your database question (e.g., 'List students in AI class')")
65submit = st.button("Retrieve Data")
66
67if submit:
68    # Require meaningful input
69    if len(user_input.strip()) < 5:
70        st.error("Please enter a clear database-related question, e.g., 'How many students are in Artificial Intelligence?'")
71    else:
72        sql_query = get_gemini_sql(user_input)
73
74        if sql_query is None:
75            st.error("Gemini did not generate a valid SQL query. Please rephrase your question.")
76        else:
77            st.subheader("Generated SQL Query:")
78            st.code(sql_query, language="sql")
79
80            try:
81                rows = read_sql_query(sql_query)
82                st.subheader("Query Results:")
83                if rows:
84                    for row in rows:
85                        st.write(row)
86                else:
87                    st.write("No results found.")
88            except Exception as e:
89                st.error(f"Error executing SQL: {e}")
90