Prathyusha7113/TextToSQLGenerator

1from dotenv import load_dotenv
2
3load_dotenv()
4import os
5import sqlite3
6import google.generativeai as genai
7import streamlit as st
8
9genai.configure(api_key=os.getenv("GOOGLE_API_KEY"))
10
11
12def get_gemini_response(question, prompt):
13    model = genai.GenerativeModel("gemini-1.5-flash")
14    response = model.generate_content([{"text": prompt}, {"text": question}])
15    return response.text
16
17
18def read_sql_query(sql, db):
19    try:
20        connection = sqlite3.connect(db)
21        cursor = connection.cursor()
22        cursor.execute(sql)
23        columns = [description[0] for description in cursor.description]
24        data = cursor.fetchall()
25        connection.close()
26        return columns, data
27    except Exception as e:
28        st.error(f"SQL Error: {e}")
29        return [], []
30
31
32prompt = [
33    """
34    You are an expert in converting English questions into SQL queries.
35    The SQL Database has the name STUDENT and has a table named STUDENT with the following columns:
36    NAME, CLASS, SECTION.\n\nExample 1-How many entries of records are present?,the SQL query is: SELECT COUNT(*) FROM STUDENT;
37    Example 2-What is the name of the student in class Data Science?,the SQL query is: SELECT NAME FROM STUDENT WHERE CLASS='Data Science';\n\nalso the SQL code should not have ``` in beginning and end and also sql word in output.\n\nNow, convert the question into SQL query.
38    """
39]
40
41st.set_page_config(
42    page_title="SQL Query Generator", page_icon=":guardsman:", layout="wide"
43)
44st.header("Gemini App to Generate SQL Queries")
45question = st.text_input("Enter your question here", key="input")
46
47submit = st.button("Ask the question")
48
49if submit:
50    with st.spinner("Generating SQL query..."):
51        response = get_gemini_response(question, prompt[0])
52        st.write("Generated SQL Query:")
53        st.code(response, language="sql")
54
55        columns, result = read_sql_query(response, "student.db")
56        if result:
57            st.write("Query Results:")
58            st.table([dict(zip(columns, row)) for row in result])
59            st.success("Query executed successfully!")
60        else:
61            st.warning("No results found.")
62