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:
Introduction to the Project
Setting Up Your Environment
Creating the Requirements File
Database Setup
Writing the Code for the Application
Running the Application
Testing the Application
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
sqlite3Install the packages by running:
pip install -r requirements.txtDatabase Setup
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.pyWriting 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.pyTesting 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:
User: Tell me all the students' names. Generated SQL Query: SELECT NAME FROM STUDENT Response: Revanth, Viswajith, Murthy, Satya
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!
Comments