End To End Text To SQL LLM App Along With Querying SQL Database Using Google Gemini Pro

Introduction

Welcome to this comprehensive guide, where we will develop an end-to-end application that converts natural language text into SQL queries and retrieves data from a SQL database using Google Gemini Pro. This project not only showcases the power of generative AI but also addresses common challenges faced in various domains. So, whether you're a seasoned developer or a beginner, join me on this journey as we implement everything from scratch.

Project Overview

In this video, we aim to demonstrate a simple application built on a student database. As a demonstration, if we input a query such as "Tell me all the students' names," the application will generate the corresponding SQL query to fetch that information from the SQL database. Similarly, for a question like "Tell me all the students' names whose marks are greater than 85," the application should return the appropriate results.

Getting Started

To kick off this project, we'll create a new Python environment and install the required libraries for our application. You can choose between using conda or Python's venv for environment management. Once the environment is set, we will create a requirements.txt file to specify the necessary libraries, which include Streamlit for the front end, Google Generative AI, and more.

Next, we'll write the code to set up our SQLite database. This involves creating a database called student.db, defining a table schema, and inserting some sample records into the table to simulate a real-world scenario.

Implementation Steps

1. Create a SQLite Database:

   • We will use SQLite because it's lightweight and easy to set up locally. The database will store records containing fields such as name, class, section, and marks for the students.

   • Here’s how we create the database using Python:

     import sqlite3
     
     # Connect to SQLite database
     connection = sqlite3.connect('student.db')
     cursor = connection.cursor()
     
     # Create table
     cursor.execute('''
     CREATE TABLE student (
         name TEXT,
         class TEXT,
         section TEXT,
         marks INTEGER
     )
     ''')
     

2. Insert Sample Records:

   • We will add multiple records to our database to ensure there is data to query.

     cursor.execute("INSERT INTO student VALUES ('Krish', ['Data Science'](https://www.topview.ai/blog/detail/what-is-data-science), 'A', 90)")
     cursor.execute("INSERT INTO student VALUES ('Sudhansu', 'Data Science', 'B', 100)")
     cursor.execute("INSERT INTO student VALUES ('Darius', 'Data Science', 'A', 86)")
     

3. Retrieve and Print Records:

   • It’s crucial to ensure that the records are inserted correctly. We can run a simple SELECT query to fetch and print all records from our database.

4. Creating LLM Application with Google Gemini Pro:

   • With the database in place, we will now focus on developing the LLM application using Google Gemini Pro. We will write functions to convert natural language queries into SQL and retrieve results from the SQLite database.

5. Implementing Streamlit App:

   • The application will have a simple user interface where users can input their questions. Upon submitting a query, the application will interact with the Google Gemini Pro model, convert it to SQL, and return the results.

   import streamlit as st
   from google.generative import AI as genAI
   
   # Getting user input
   question = st.text_input("Ask your question:")
   if st.button("Submit"):
       sql_query = get_gemini_response(question)  # Function to get SQL query
       data = read_sql_query(sql_query, 'student.db')  # Retrieve data
       st.write(data)  # Display the results
   

6. Testing Various Queries:

   • Once the app is up and running, we can test it with different types of queries to see how well it converts the natural language into SQL and retrieves results.

Conclusion

Implementing an end-to-end Text to SQL LLM application using Google Gemini Pro opens up vast possibilities for interacting with databases through natural language. This project illustrates how AI can bridge the gap between human language and the structured world of databases.

Keywords

• Google Gemini Pro
• Text to SQL
• LLM Application
• SQLite
• Natural Language Processing
• Streamlit
• Database Management

FAQ

1. What is the primary function of the application?
The application converts natural language queries into SQL queries and retrieves data from an SQLite database.

2. What technology stack is used in this project?
The project uses Python for backend development, Streamlit for the front end, and Google Gemini Pro for the AI model.

3. Can I use a different database instead of SQLite?
Yes, while SQLite is used for simplicity, you can integrate other databases like MySQL or PostgreSQL as well.

4. How can I install the required libraries?
You can install the required libraries by creating a requirements.txt file and using pip install -r requirements.txt.

5. What types of queries can the application handle?
The application can handle a wide range of queries, from simple selects to more complex aggregations and conditions.