A-Asif/Text2SQLQuery_Application

1from dotenv import load_dotenv2load_dotenv()3 4import streamlit as st5import os6import sqlite37from google import genai8 9# Initialize Gemini client10client = genai.Client(api_key=os.getenv("GEMINI_API_KEY"))11 12### Function to convert user input to SQL using Gemini13def get_gemini_sql(user_input):14    # Prepend "Database question" to make intent explicit15    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: STUDENT23Columns: NAME, CLASS, SECTION24 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 Gemini36    response = client.models.generate_content(37        model="gemini-2.5-pro",38        contents=prompt39    )40 41    # Clean up output for SQLite42    sql_query = response.text.strip().replace('"', "'")43 44    # Validate SQL starts with a common command45    if not sql_query.lower().startswith(("select", "insert", "update", "delete", "count")):46        return None47    return sql_query48 49 50### Function to execute SQL query51def 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 rows58 59 60### Streamlit App61st.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 input69    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