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

hello all my name is krishak and welcome to my YouTube channel so guys yet another amazing end to endend llm project using Google Gemini Pro and this time we are probably going to create an application which will be able to convert text into SQL queries and it'll also be able to retrieve data from the SQL database so there are lot many things to be discussed in this specific project we will be implementing completely from scratch please make sure that you watch this video till the end because at the end of the day you know by using generative AI you can definitely use many techniques to solve common problems that we have you know in different different domains so before I go ahead if you are new to this particular Channel please make sure that you hit like share with all your friends and subscribe the channel itself along with this we'll keep the like Target to th000 please try to make it that because it will motivate me to upload more amazing videos for you all so so as we go ahead guys first of all we will try to see the demo so right now I have a student database okay I will show you how to create the database everything I'll show you step by step as we go ahead now if I ask any question over here let's say that I will ask tell me all the students name okay so this is the text that I'm specifically giving so as soon as I click ask the question it is going to give me all the students who studying in the specific all the classes that I have in my database not only this let's say if I go ahead and ask tell me all the students name whose marks is greater than greater than 85 okay so if I go ahead and ask this question now it is going to retrieve all the information from the SQL database so the response that I'm probably getting is this Krish sudhansu and Darius okay so I have three three records where I have all the students whose mask is greater than 85 now how you'll be building this everything we'll be discussing step by step we will try to see how you can actually code it and I will I'll probably write each and every line of code for you all okay so let me just go ahead and open my vs code so this is the VSS code and here I'm going to do my entire coding we will go step by step as usual so the first thing what I will do is that I will go ahead and open uh my terminal and the first thing that I really want to do is that create a new environment now many people have confusion regarding creating a new environment because I I use the cond command okay if you don't want to use the cond command so here you can just go and search for python create virtual EnV right so by this you will be able to get you'll be able to get the command just refer to this you just need to create something like this python MV and B if you do this much right you will be able to create the virtual environment okay and then inside that virtual environment you can go ahead and install all the libraries first of all you need to activate those okay either you can use this command otherwise what you can actually do you can use cond right I usually use cond because I already have Anaconda installed in this so I will go ahead and write cona create minus p v andv will be my environment name with python version as usual as you know Google gin Pro works specifically good with 3. 9 plus right so this version we will use and I'll give a not uh saying that why so that it does not ask me any specific permission over here okay so this is the command that we are going to specifically use now let me quickly go ahead and install all the live create a new environment so at the end of the day a v EnV environment will get created and please go along with me guys because I'm going to to show you each and every steps that is specifically required over here so it may take some amount of time till then what I will do I will go ahead and write my requirement. txt here I will specifically write all the libraries that I really need to install it the next thing is that I will also create EnV file here you'll be able to see in this EnV file I will write my key right the Google jmin pro API key now once you done with this then first of all I will go ahead and write my my SQL py file this file will be responsible in creating the database creating the table you know inserting all the records so all the code I will specifically be writing in SQL dopy and along with that I will go ahead and create one more app.

py file okay so in this app. py I will create my streamlit app from here my entire llm application will be created then this llm application should interact with the database base okay so over here my environment is getting create got created so I will go ahead and WR activate this environment so here I will go ahead and let's say first of all I will clear my screen okay so now I will go ahead and activate VNV okay so my environment has been activated VNV so inside this I will go ahead and install all my requirements now before I go ahead guys some very important points okay first of all we'll try to understand what exactly the entire application is all about so here I will open a notebook file so agenda is basically text to SQL llm application now when I say text this is basically natural language text right to SQL so that basically means my llm application will convert this text to SQL query and then we can use this SQL query to hit our database and retrieve the results okay so overall flow of the ENT entire project will be something like this we will try to create a prompt I will give my own custom prompt saying that let's say that how many students are studying in this class okay or uh tell me which student is first rank second rank something like that okay so over here I will give my prompt this prompt will go to the llm application this llm application which is nothing but Germany Pro Will based on this prompt it will give us a query and then we can use this query to hit the SQL database and finally we'll get the response so here implementation wise right two important thing we need to implement one I will be using a local DB like sqlite you can see you can use my SQL you can use different types of database but just show to show you an example and quickly without much hassleless uh setup you know I will try to use sqlite because I can actually do it in my local also so I will probably create a sqlite DV I will insert some records right and this entirely I'll do with the help of Python programming language so this will be my first step of implementation the second step of implementation will be I will try to create my llm application using this I will go ahead and probably use gini Pro and this gin Pro whatever SQL query it is generating it will go ahead and hit that particular database so the implementation wise these are the two main steps so let's go ahead and start this so first of all in requirement. txt I will go ahead and set my requirement.

txt and here you will require streamlet because we are going to create our front end using streamlet the second library that we specifically require over here is Google generative AI okay the next library that we require is python. EnV okay so these three are my libraries that I will be requiring so I will go ahead and install my requirement. txt okay so quickly we will go ahead and install this because these are the three libraries I will specifically require so once this requirement.

txt has been installed what I will do is that I will go to SQL py and start creating my first thing that is required basically to create the database and all and as I said I'm going to use sqlite okay so I will have one DB file over here which will have all the tables and all you can open the that in the sqlite GUI interface right you can download it from Google and open it okay so let's wait till this installation is happening once this installation happens so let me just go ahead and write the code so over here I will try to create the database with the help of python inser some records also so let me go ahead and go ahead and import sqlite 3 so site 3 by default comes along with the python environment python setup itself right then I will write a connect uh to sqlite so I will write a command to this so that basically means we will create our database so I'll write connection is equal to sqlite 3 do connect and here I'm going to basically write student. DB okay so here you can see the installation is done and as soon as the installation uh completed see everything is basically coming over here right so this this highlighting is also come right now I'll close this now let's go ahead and create my first DB so DB name is student. DB and then I will create a cursor this cursor will be responsible in inserting retrieving like insert record create table right or retrieve result right it can execute anything right so here I will create this cursor and I'll write connection Dot cursor okay perfect then let me go ahead and create the table also so I'm going to go go ahead and create the table I will write my table info is equal to now what I'm going to write I'm going to write my query so in this particular table I'm going to create three important Fields okay so I have name I have class I have section I have marks so four important uh four columns you can B basically same right so in this table the table name is student a simple SQL query table so here you can see create table student with name vard uh 25 class vcat that basically mean can have numerical values it can have uh alphabetical Val values it can also have alpha numeric values then the third column is basically section section basically means what section you be you belong to like it can be a b c and then I have marks which is an integer value so this by using this command we can execute or we can create a table so for creating our table now I will write cursor do execute cursor.

execute and here I will just go ahead and write my table info okay now let's go ahead and insert some more records okay now we'll go ahead and insert some more records um here I will write cursor. execute and whatever insert records that you really want to do you can write it over here okay so for this I have already created my query which I think if you have some basic knowledge you'll be able to understand so here you can see that I have inserted I'm trying to insert four records so in my first record I saying insert into student values CR data science a section 990 okay insert into student values again the second record is sudhansu data science B 100 okay then again the third record has darus data science a 86 86 is the marks okay and similarly you have vas you have the and all these particular values are there okay now these are the records that I really want to execute so as soon as this command gets executed then all the records will get created okay then I will go and display just to display for our sake I will display all the records perfect so I will say print the inserted records are okay and quickly I will write data is equal to cursor do execute and this time I will go ahead and write my queries select star from student okay so as soon as I execute the query I will write another thing for Row in data print row now since we are reading from the data whatever things we are inserting to complete all the operation we should close the connection right we should close the connection right please make sure that you close the connection otherwise you'll get an error okay so once you close the connection for closing the connection it's very simple you need to write connection. commit because this is your final commit after the insert and along with this what you will do you will go ahead and write connection Dot close Okay so everything I have specifically written over here so this is my entire command this is good enough let's see whether our DB will get created or not so once I execute this entire file you'll be able to see when student.

DB file will get created over here right a SQL Ed file so now what I will do is that I will go ahead and open my terminal okay now in order to run this I will clear the screen I will go ahead and write python SQL dopy so now here you can see that all the records are inserted okay and it is showing that what all insert records are inserted and here you can probably see that student. DV file has also got created so this completes our first and the foremost important step now you can insert any number of Records because at least we need to have some data in the database right now in the Second Step what I'm going to do I'm going to create an llm application with the help of gin Pro here our main aim will be that whenever we give a prompt it should be able to convert into a SQL query okay so then that we can take that SQL query and hit our database okay whatever the SQL I DB database is created so this step is done I will close this terminal I will go ahead and open my app. py now inside this app.

py I will start writing my code so first of all as usual as I said here is what I'm going to create my application so I will write from dot EnV import load uncore do EnV okay then I will go ahead and write load underscore let me see whether I've imported or installed this yeah python. EnV is there perfect so this is there so we will load all our load all the environment variables okay then we will go ahead and import streamlet as St import streamlet as a St I'll save it okay then I will go ahead and import OS along with this we'll import sqlite 3 because I need to hit that specific database now along with this I will also import from import google. generative a as gen AI okay so gen AI is done I'm also going to use this library now we will go ahead and configure configure configure our API key okay now in order to configure it is very simple I will go ahead and write gen.

configure gen AI do configure okay so this is Gen AI okay j. configure and here I'm going to use API key is equal to is equal to OS do get the environment variable and we will go ahead and set our Google API key right we we require the Google API key over here right so Google API key what it is basically there in the ENB file so still we have not created the Google API over here so what I will do I will I have already created my Google API key for gini pro right now if you really need to know how you need to create it just go ahead and just go to this particular website maker. google.

com/ apppp API key and you just need to click over here create an API key so I've already created it so once you click over here the API key will get generated okay just go to this website maker. google. com /a/ API key now here is my API key which I have already put in the environment variable now I will go ahead and consume that right so it will be for the same name Google API key okay so I hope it is clear till here right you have configured in a very simple manner you have configured this entire API key now the next step now what will be the next step with respect to this right so first of all as usual I will do one important thing is that that I will go ahead and create my function to load Google Gemini model okay and provide provide query as response right the SQL query so we are going to probably provide the SQL query as the response right so first thing first what I'm going to do I'm going to write get definition get Jin response and here I'm going to basically give two thing one is the question that I'm asking and the prompt prompt this prompt is like how we want a Google gin Pro to behave right so here I'm going to basically write model do gen do generate content so here I will wrate generative model okay and we are going to load Lo our model gini Pro okay so generative model gen.

generative model so this will basically be my uh way of calling the gmin pro or loading the Gin pro model and then I will just go ahead and write my model response so it will be model dot generate generate underscore content okay so this generate underscore content is the function where I will give two important information one is the prompt and since I'm going to one is the prompt and the second thing that I'm going to give is my question right the first parameter that you see over here we are basically seeing from this prompt how this model should behave like and this is basically my question my natural language text which I really want to convert into a SQL query and then finally we will go ahead and return response. text perfect so done this is all done over here I think it works absolutely fine till here uh we have everything ready and this is my function to load the gini model and then provide SQL query as response perfect so let me just see so till here everything looks good now we will go ahead and create another function we'll go ahead and create another function to retrieve to retrieve query from the SQL database okay so see this method is basically giving a SQL query from natural language text it is converting into a SQL query now this method will basically give you will take that SQL query hit the database and get the result so here I'm going to write definition read SQL query and here I'm going to provide two parameters SQL and DB I'll create the connection and go ahead and write sqlite 3 dot connect my database whatever database name I'm giving over here and then I'm going to create my cursor so it will be CN dot cursor right then cursor. execute okay and here I'm going to give my SQL command whatever SQL command is over here I'm going to give it then row is equal to current dot fetchall whatever execute query I'm basically writing connection do commit and then finally connection do close so we are retrieving the results keeping all the values in rows okay and then I can go ahead and print this rows also if you want so for Row in rows print all the records along with this I can return all the rows right something like this so this is the query this is the function which will take the uh the SQL query that has been generated by this llm model and it is going to hit this this SQL database and the database name will be available over here so this is what this entire function is doing so in short if I talk about more over here right this is basically the Second Step that is happening right now once we Define this two function now I will go ahead and Define my prompt now this is the most important thing if you don't Define this prompt properly okay then this becomes a challenge so take your time in reading this prompt now this prompt is the information that I'm giving my model like how it should behave like okay okay so over here you can see Define your prompt I'm saying that you are an expert in converting English question to SQL query the SQ database has the name student and has the following columns name class section so for example name class section and marks okay for example how many entries of records are present the SQL command will be something like select count star from student example two tell me the all the students study in the data science class right and here you can see the SQL command will be something like select start from student where class is equal to data science also the SQL code should not have this quotes in the beginning or end and the SQL word in the output okay so this is what is the prompt that I have actually defined and this prompt will be passed over here right in this particular parameter like this prompt parameter and right now this is in the form of list so what I can do I can also give multiple prompts if you want but I'll make it as prompt of zero since I just have one prompt now this is done the next step will be creating a streamlet app so here is my streamlet app I'll say I can retrieve any SQL query gy app to retrieve SQL data question as I've taken one input box one submit button okay only two things we require now if submit is clicked okay so here I'll go ahead and write if submit okay the response will be get gemin response and here I'm going to give two parameter one is the question whatever question I have over here and the other one is specifically my prompt okay whatever prompt I have defined over here so this two information will go to Google JY response here and then it will probably take this two information and generate the SQL query okay after we get the SQL query I can also print the response over here okay I will print the response response okay so let me make the same spelling once we print the response now I will also again create another response so here let me go ahead and write um data okay because we'll take this response and now I will go ahead and call my read SQL query I will take this response which will be in the form of query comma I will give my database name so database name is nothing but student.

DB okay once I get the data once I get the data all I will do is that I'll write st. subheader and let me go ahead and write the response is the response is for Row in response okay I will go ahead and write print row I can print all the row sorry from Row in data I have to write not response because this will find be my final one and then I will go ahead and display it in my SD do header and I will go ahead and write row so this is what is my stream late app and now let's check whether this particular code will run or not so I'll go ahead open my terminal now it's time to basically run this code and let's see whether it will run or not so I will go ahead and write streamlit run app. py see right now I've just given two examples guys you can give any number of examples okay uh no module named okay I've done a spelling mistake let's see uh Google okay that is the reason it was not getting uploaded generative generative let's see now it should work rerun google.

generative generative AI okay sorry I missed AI rerun now it is there now let's see once I write the query as you all know in my query what all information I had I basically inserted chis Sanu this this this right then I will go ahead and say provide me the average marks of all students okay let's see this becomes a complicated query 72. 2 so here just to verify what all query it has developed we will see to it so here you can see select average marks from student and remember this previous error was because of the previous one select average marks from student 72. 2 see here you clearly getting this and the query is also generated well so if I say provide the average marks of all student class wise okay let's see so devops 42.

5 data science 92.