Part 9: Building an End-to-End Text-to-SQL Query Application Using Google Gemini Pro

Text-to-SQL Query Application Using Google Gemini Pro

Introduction

In this blog post, we'll guide you through creating a powerful application that converts natural language text into SQL queries and retrieves data from an SQL database. We'll be using Google Gemini Pro for this project. This end-to-end LLM project will demonstrate how generative AI can solve real-world problems across different domains. Let's get started!

Agenda

Here's what we'll cover:

1. Introduction to the Project

2. Setting Up Your Environment

3. Creating the Requirements File

4. Database Setup

5. Writing the Code for the Application

6. Running the Application

7. Testing the Application

8. Conclusion

Introduction to the Project

The goal of this project is to create an application that converts text into SQL queries using Google Gemini Pro and retrieves data from an SQL database. For example, if you input "Tell me all the students' names," the application will generate a SQL query to fetch this data from the database and display the results.

Setting Up Your Environment

Before we get started, ensure you have the following prerequisites:

• Python Version: Python 3.9 or higher.

• API Keys: Ensure you have your Google Gemini Pro API key ready.

Step 1: Create a Virtual Environment

Let's start by setting up a virtual environment to manage our dependencies and keep our project organized.

conda create -p venv python=3.9
conda activate venv/

Step 2: Install Required Packages

Create a requirements.txt file with the following content:

streamlit
google-generativeai
python-dotenv
sqlite3

Install the packages by running:

pip install -r requirements.txt

Database Setup

1. Create a Python Script (mainsqllite.py) for Database Setup:

# Import module 
import sqlite3 

# Connecting to sqlite 
conn = sqlite3.connect('student.db') 

# Creating a cursor object using the cursor() method 
cursor = conn.cursor() 

# Creating table 
table = """CREATE TABLE STUDENT(
            NAME VARCHAR(255), 
            CLASS VARCHAR(255), 
            SECTION VARCHAR(255), 
            MARKS INTEGER);"""
cursor.execute(table) 

# Queries to INSERT records. 
cursor.execute("INSERT INTO STUDENT VALUES ('Revanth', 'Data Science', 'A', 90)") 
cursor.execute("INSERT INTO STUDENT VALUES ('Viswajith', 'Data Science', 'B', 86)") 
cursor.execute("INSERT INTO STUDENT VALUES ('Murthy', 'Devops', 'C', 100)") 
cursor.execute("INSERT INTO STUDENT VALUES ('Satya', 'Data Science', 'C', 78)") 

# Display data inserted 
print("Data Inserted in the table: ") 
data = cursor.execute("SELECT * FROM STUDENT") 
for row in data: 
    print(row) 

# Commit your changes in the database     
conn.commit() 

# Closing the connection 
conn.close()

Run the script to create the SQLite database and insert records:

python mainsqllite.py

Writing the Code for the Application

• Create a Python Script (main.py) for the Application:

from dotenv import load_dotenv
load_dotenv()  # Load all the environment variables

import streamlit as st
import os
import sqlite3
import google.generativeai as genai

# Configure GenAI Key
genai.configure(api_key=os.getenv("GOOGLE_API_KEY"))

# Function to load Google Gemini Model and provide SQL queries as response
def get_gemini_response(question, prompt):
    model = genai.GenerativeModel('gemini-pro')
    response = model.generate_content([prompt[0], question])
    return response.text

# Function to retrieve query from the database
def read_sql_query(sql, db):
    conn = sqlite3.connect(db)
    cur = conn.cursor()
    cur.execute(sql)
    rows = cur.fetchall()
    conn.commit()
    conn.close()
    for row in rows:
        print(row)
    return rows

# Define your prompt
prompt = [
    """
    You are an expert in converting English questions to SQL query!
    The SQL database has the name STUDENT and has the following columns - NAME, CLASS, SECTION, MARKS.
    For example,
    Example 1 - How many entries of records are present?, 
    the SQL command will be something like this SELECT COUNT(*) FROM STUDENT;
    Example 2 - Tell me all the students studying in Data Science class?, 
    the SQL command will be something like this SELECT * FROM STUDENT where CLASS="Data Science"; 
    also the SQL code should not have ``` in beginning or end and sql word in output
    """
]

# Streamlit App
st.set_page_config(page_title="I can Retrieve Any SQL query")
st.header("Gemini App To Retrieve SQL Data")

question = st.text_input("Input: ", key="input")
submit = st.button("Ask the question")

# If submit is clicked
if submit:
    response = get_gemini_response(question, prompt)
    print(response)
    response = read_sql_query(response, "student.db")
    st.subheader("The Response is")
    for row in response:
        print(row)
        st.header(row)

Running the Application

To run your Streamlit app, open your terminal and execute:

streamlit run main.py

Testing the Application

Once the app is running, open the Streamlit interface in your web browser. Type a question in the input box and click "Submit." For example:

1. User: Tell me all the students' names. Generated SQL Query: SELECT NAME FROM STUDENT Response: Revanth, Viswajith, Murthy, Satya

2. User: What are the names of students with marks greater than 85? Generated SQL Query: SELECT NAME FROM STUDENT WHERE MARKS > 85 Response: Revanth, Murthy

Conclusion

Congratulations! You've successfully built a text-to-SQL query application using Google Gemini Pro. This project showcases the power of generative AI in converting natural language into executable SQL queries to retrieve data from a database. Keep experimenting with different prompts and questions to explore the full potential of this application.

Happy coding!