good morning everyone how's it going today welcome back to the channel and welcome to this new video tutorial in which I'm going to be showing you how to build the application that you see right in front of you okay it is a chat but that allows you to chat with a SQL database in natural language which means that it is going to first uh generate a SQL query by itself given your query and then it's going to read the results from that query and it's going to give read the result interpret the results to you and give you an answer in natural language okay so just to show you how this works I'm going to connect here to this database that I have in my local host which is called shinuk I'm going to connect and this is a database that has some um artists and albums and tracks and it's supposed to be the database from a music store so I'm going to ask a question something like which artist has the most tracks and now as you will and as I will show you later in the video this is generating the SQL query and reading the results so here you can see that the artist with the most tracks in the database is Iron Maiden with a total of 213 tracks and as you can see it also has conversational memory so I can ask a follow-up question something like which one is second and it's supposed to be able to know that I'm referring to the artist that has the second most tracks so the artist with the second highest number of tracks is the in the database is You2 with a total of 135 tracks seems to be working correctly so let's get to actually building it and something that I wanted to tell you as well is that thank you very much for waiting for me last week I was not able to publish a video because I was very busy actually getting my diploma so now that that is done we can go back to focusing on making super cool tutorials for you to build super cool project s to Showcase to your clients uh future employers and your friends okay so without any further Ado let's get right into [Music] [Music] it okay so let's actually start off with this super quick tutorial I'm going to try to make it as short and concise as possible but also making it as clear as possible so that you don't have any problems here um as you can see I have created my root repository sorry my root directory called Chad my SQL GUI I have my source directory with my app.py file which is where my application is going to live I have mydmv file with all these secrets right here that of course I am going to be deleting before I upload a video but as you can see I have my open AI API key because we're going to be using gp4 for this um this chat putut and I also have my Gro API key because we're also going to be using mistol I'm going to show you how to use Gro in this video to interact as well with the my SQL database and this other two you can ignore them this is just to use lsmith which is good for logging I'm probably going to show you a little bit about how that works in the end of the video if it's not too long okay so yeah about the the development environment also we have our virtual environment with a with a cond environment in case you want to create your own using cond which is what I recommend you can create it using cond create-- name and then you name your environment and then you give a python version that you want to use in this case I am using python 3.10 okay once you have done that all of the dependencies that you install using pip are going to be contained within this environment and it's just going to make it super easy to manage your dependencies okay so there you go now first of all we are going to have to install the dependencies that we're going to be needing in my case I'm going to be pip installing streamlit because we're going to be building the front end of the application using streamlit I am going to be using Lang chain for this as well I am going to be using Lang chain open AI because remember that open AI now now has its own package for the Lang chain integration I am also going to be using Lang chain Gro Gro because also Gro also has its own package for its integration um since we're going to be using a MySQL um database we're going to need a my SQL driver for for SQL Alchemy which is the package that we're going to that I mean that Lang chain uses behind the scenes so we're going to use my my SQL connector for python like this and finally we are going to use python. tnv to be able to actually connect and to use ourv file okay so I'm going to run this of course that was super fast because I already have it installed but to you it might take a little bit of time um once you have all of it installed we can actually jump into app.py and start coding our application great so the first thing that you're going to want to do is load the tnv file okay so we're going to first of all we're going to do load dot. EnV which is the function that is going to allow us to use all of ourv Secrets okay and and in order to actually be able to use it you first have to import it so you have to do from. tnv import load. tnv there you go and second of all we're actually going to just start using uh uh creating our stream lad page so first of all we're going to do from uh sorry now import going to do import stream lit s St and then we're going to just first set up the configuration for the Page by doing set I think it was set page config and here you can just specify the title of the page we're going to call it chat with my SQL and the layout we're going to set it to just leave it as is uh let's add a it's not supposed to be title it's supposed to be page title and here we're going to call this page icon let's call it a speech balloon and let layout we're going to leave it as is there you go um now remember that in order to run your streamlit application you're going to want to do streamlet run sort and then the path to your file and let's see how this is working we have our application let's actually just add a quick title chat with my SQL let's see how that works chat with my SQL there we go so now that the layout is a little bit set up let's actually just add our side bar with the where we're going to set up the information for the database okay so in this case we're going to want to in order to create a sidebar we're going to do with st. sidebar we're going to add a subheader for settings and this is simple chat application using my SQL and connect to the database and start chatting so first of all we're going to do an st. text input and this is going to be the host first of all the default value we're going to set it to Local Host just so that I don't have to retype the name of the of the host post by hand every single time I want to test it let's also add the port uh we're going to going to we're also going to set a default value of 3306 which is the default value for most of my SQL databases we're also going to add a user um default set it as root then we're going to add an input for password and in my case I'm going to add also uh default value of admin because that's what I set my password to to be for my local instance of my SQL yes I know I am very good at um at security um and last but not least we're going to add the name of our database right so database and the value is going to be shinuk there you go all right so so and then of course let's just add a button sd. button connect so let's see how that looks now there you go so we have our settings we have our host Port user password and this of course you can update it I mean you can change the port to be whatever you want but I just set a default one because this is the values for the ones that I have here in my terminal let me just show you if I do if I do Brew Services I think it's list I think yeah I have my SQL that started and as you can see I have my SQL rude and as I told you before my password is admin so show databases I have shinuk right here and if I want to use shinuk I can see that I have 11 tables right here and yeah I mean it's supposed to represent a media store which is a which sells albums and each album has a has an artist and each art I mean each album is comprised of tracks we also have invoices employees and customers that have bought albums Etc okay so that's how that's what we're going to be dealing with so now what we're going to be doing is let's just add our chat interface as well okay so actually right now what I'm going to do is I'm just going to add the St chat chat input right here chat input type a message let's see how that looks there you go type a message and actually before creating the whole message interaction and stuff like that let's actually connect to the database that we're going to be connecting to so in this case as I told you before I'm going to be using my local host and these are the this is the credentials that I'm going to be using to connect to the database but the thing right here is that I want to do that whenever I click on this button connect right so whenever I click here I want my computer my application to be connected to the database you in order to do that I am going to create a new function right here that is going to be called um let's call it initialize init database like that and we already run load. tnv so what we're going to do is we're going to remember that we're going to be using a SQL Alchemy wrapper from Lang chain and this one actually comes in their package from Lang Ching Community because remember that the Integrations from Lang chain come in the package Lang chain community so from blanching community. utilities I'm going to import SQL data base I don't know what I don't have auto complete here um let's hope this works and um so we're initializing the database right here and and this one right here takes a URI of the database so I'm just going to initialize my URI and I'm going to say that this one is going to be a F string my SQL we're going to have the user the password at the host going to have the port and the name of the database and well of course here we're going to need also this all of this all right we're going to need all of this there you go all right sounds good and then we're going to return our SQL database in order to initialize it we're going to call from URI and we're going to pass in our database URI that we initialized right here okay so there we have it here is our function that's supposed to initialize with the connection to our database and let's add actually initialize it when we click on the button connect okay so let's say if the button is clicked what we're going to do is we're going to say let's just add a spinner right here with st. spinner remember that a spinner in langin allows you in Lang in streamlet allows you to connect I mean to show the user uh real time that you're working that the application is thinking on something it's just like this little Roundy thing that shows like just user user experience wise it's pretty convenient so with the spinner we're going to say that we're going to show the message connecting to the database and then after that we're going to initialize our database by passing the user password host port and then the database and then let's just set the database to the database and then just show a success um message right here however here we're using session State variables and we did not include them right here so let's just add a key right here to each one of these so that they are stored in our session state so hopefully this is going to work let's see how that goes if I reload this thing and remember that my my SQL database is running so if I click on connect I am supposed to be able to connect however no module named my SQL database all right here's a here we we made a mistake right here I mean I made a mistake uh in order to use a my SQL database you actually need to use uh the driver that I was telling you about before so in order to do this you're going to need to add here the driver and since we're using a my SQL database I'm going to use my SQL connector like that just going to save this let's see if this runs now so if I click connect can't load plug-in dialects my SQL connector we have a problem here let's see what's going on um um Runner script from URI host [Music] password oh I missed here my SQL not my there you go so maybe this is going to work now if I click on connect streamlet API exception session State database cannot be modified after which it with key databases initiated what's going on here oh yeah we update we we set it to the same name this is not supposed to be this let's just call it let's just call it DB like this DB like that now it's supposed to work there we go we're connected to the database and now everything seems to be working correctly there you go so now that we're connected to the database we can actually start quering it and creating our two chains the SQL chain and the get response chain and let me just show you actually a diagram with what's actually going to be happening behind the scenes here great so if you watch the previous video of how to do the mySQL database chain you probably already know what I'm talking about but in case you haven't watched that video let me just tour you again through what's actually going to be happening behind the scenes in this program so the idea right here as I told you before is we're going to have a user question in natural language I mean in English and then by the end we're going to get a natural language answer from this chain okay just to be clear this is not an agent not yet we're going to be working only with chains for now and the idea here is that the user question is going to enter a SQL chain which is the first chain that we're going to be building this chain is going to take the database schema which is pretty much just a list of all the table names and all the table uh column names and the types data types for each column and we're going to pass into the language model and we're going to be asking the question something like so given this schema and this user question what is a usel what is a SQL query that is going to answer that question and then it's going to return a SQL query then we're going to run that query towards the database and then we're going to take everything to a to the language model again and be like all right so we have this user question we have this database schema we have this user query uh SQL query and we have this response I mean this uh response from this SQL query uh now give us a give me a natural language answer to the user question and that's when when the full chain is going to be returning a natural language answer or something like uh so if we ask something like which artist has the most tracks this one is uh this one is going to get select blah blah blah limit one then we're going to run it then it's going to get the response and then this one's going to be like all right so the artist that has the most tracks is I don't remember I think it was Iron Maiden so there you go and that's what's actually going to be happening behind the scenes okay um I mean of course here is pretty much everything explained more in detail about how this is actually working also if you want to uh check more like The Notebook on what we're doing here I recommend that you watch this other video I'll put a link somewhere up here uh which is the video that actually shows a little bit more in detail what's going on behind the scenes in a Jupiter notebooks okay so now let's actually start building this first chain right here this SQL chain let's do that okay so actually before creating all of this um before creating the SQL chain uh let's just create the chat chat interactivity part I mean the interactive chat part um in order to actually be able to pass in the chat history to our SQL chain okay so in order to do that the first thing that I am going to do is I'm going to initialize a persistent variable that is going to be in the session state for the chat history okay now in order to do that we're going to do if chat history if chat history is not in the session stage we're just going to initialize it here as you can see they are I mean we could initialize it with an empty array like that however I am going to initialize it with an AI message now actually I did this off camera so let's do this again um remember that in Lang chain you can use schemas for the messages that is is going to allow you to do to organize your conversations a little bit more uh neatly and the idea is that you have different classes for the AI message for human message and for system message as well in this case I'm just going to be using the AI and human message so in order to do that we're going to do from L chain core and we're going to tap into the messages module we're going to import AI message like that and and we're also going to import human message like that and here we're going to initialize this message with an AI message and let's just say something like hello I am a SQL assistant um ask me anything about your database okay and oops and this one right here is supposed to be the entire the entire chat history actually I'm not sure if this does this by default um but actually this is supposed to be the content of the message okay so there you go now what we're going to do is actually create I mean display display this messages in the on top of the chat input right so what we want to do is we're going to we're going to want to display the messages up here so in order to do that we're going to first of all up here on top of chat input we're going to do for message in session State chat history If the message is an instance of AI message I'm going to do with st. chat message and it's going to be an AI message I'm going to St write the message content this St and actually I'm not going to use write I'm going to use markdown because remember that sometimes we're going to be displaying lists we want to be able to to use mark down inside of the message right and that is if this is an AI message if not and if it's a human message we're going to do pretty much the same thing but with the human Okay so let's just save this and let's see how this looks so here we have hello I am an I'm a SQL assistant ask me anything about your database so there you go now actually let's add the interactivity here so when a user asks a question we're going to add it to our history so to do um let's just store whatever the user um writes in the chat input into its own variable we're going to do something like we're going to store this in the user query variable like this and so here we have the chat input so if the user query is not is not none and it is not [Music] empty we're going to first of all we're going to take the user query and we're going to add it to the history of the conversation right so we're going to take the st. chat history and we're going to append the human message with the content of the user query that's the first thing we're going to do secondly we're going to show that message in the conversation so we're going to do St chat message and this one is going to be the human message message and remember that in order to use this you have to do with st markdown and we're just going to show the user message right here okay so let's see how this works so far if I do this I reload I say hello there you have my message now let's pretend that we're getting the response okay we're not getting the response yet but let's just pretend that we are we're going to do that with St ch chat message and this is going to be the AI the response is going to be so far here we're going to add the function get response but so far we're just going to return I don't know every single time I don't know I don't know how to respond to that let's go and let's just st. markdown response like that okay and then what we're going to do is after showing the message we're just going to append it to the chat history like that so now chat history we also have the AI message there we go so now let's see how this is going hello I don't know how are you there there we go so now we have a chat message and we have our message history right here and we're going to be able to use it inside of our SQL chain okay this is a step up from the previous time we did we dealt with SQL databases because I mean with with with the pre from the previous video which is this one because in the previous video we were actually not taking into account the history of the conversation in the SQL chain in this video we are and and so we're going to be seeing how it actually works so now let's actually build that SQL uh chain let's do that now okay so in order to create first this SQL chain I am going to create a new function up here called get SQL chain okay so this one is going to be called get SQL SQL chain like this that and this one is actually going to take the database object that we're going to be returning from this one so I'm just going to call it DB for now and right here actually we have a few things that we want to we're going to want to do the first thing that we're going to want to do is create a template for our prompt okay because remember that we are going to be sending a prompt to our language model and the prompt is going to include something like so given this database schema and given this user question um and given this user conversation history give me a SQL query that is going to respond to the user question and actually that's quite a complex uh template a complex prompt so I I prepared it here for you let me tell let just tell me what you think about it so I'm going to create a template and here's my prompt uh I mean it's let let let me just tell me what you think about it uh here you have you are a data analyst at a company you are interacted with a user who is asking you questions about the company's database based on the table schema below and we're going to pass in the schema write a SQL query that would answer the user's question take the conversation history into account okay and here we're going to pass in the schema here we're going to pass in the conversation history and then actually I was having trouble when doing some tests for this application because in the language model would respond something like certainly here is your SQL query and actually we want we want to have only the SQL query we don't have to have any certainly or yes of course or any comment beside it we only want the SQL query so I sent it a few examples um uh we I sent it uh two short examples here so we have um here here I I mean just to show it what I expect from it to respond okay this is called fuse fuse shot learning so here we have the question which three artists have the most tracks and then here we have the SQL query of some like giving me the actual query for this but here as you can see I am showing it what I want I want only the SQL query here I have another example and here I also have the response with only the SQL query okay and now I tell it your turn here's the question and then I just give it the the prompt right here so that it only completes the usql query okay some language models did better than others in this case but I mean these few short examples are very much to to make the language model work on this part of the this part of the debugging of the application okay so this this template does the job for me let me know if you have a better idea of how to create this prompt and let's actually just create it so in order to do this we're going to do prompt we're going to initialize it from chat prompt template okay so from from Lang chain core prompts we're going to import chat prom template and here we're going to do The Prompt is going to be equal to chat promp template and here in order to initialize it we'll have to say that we're going to initialize it from a template and here just pass this template right here and there you go let's just initialize our language model now the first thing that I'm going to show you is I'm going to be using open Ai and I am going to be using gp4 so the gp4 model as at the time of writing the latest one is this one and then we are going to need a tool oh this is supposed to be chat open Ai and we actually have not imported it so let's say from L chain open AI we're going to import shat open AI like that I'm not sure if I have actually installed shatow open AI uh let's see in case you haven't remember that what you have to do if you're going to I mean first be sure that you're using your your environment so I'm going to activate chat with my SQL and I'm going to see is that actually the environment that I'm using here chat with my SQL yeah and I am going to be using I'm going to be installing lank chain open AI yeah it was already installed I don't know why this is not autocomplete so there you go so from lch we're going to import chat open AI so chat open AI seems to be a problem with the indentation here so now we have our language model we have our prompt now we need the tool that we're going to be using the tool is going to be get schema because this is the function that we're going to be executing in order to get the schema from the from the database this remember D DB is the database W the database return from this function right here which is a wrapper around SQL Alchemy and this is this actually allows us to get the to this is not the function it's not get the schema it's get table info get table get table info and this one actually will return the schema of the of the database okay so there we go this is the tool that we're going to be using the language model The Prompt and now we can actually just return our our chain so let's return the chain remember that in order to return the chain the first thing that you got to do is we're going to pass in a runnable and actually to uh import the runnable I'm going to have to import it from Lang chain core from Lang chain core do runnables we're going to import a runnable pass through that's going to allow us to pass in this function right here as a runnable runable pass through and this one is going to be the AL sorry actually here we going to have to assign one of the variables in my in my prompt okay so in this case we want to assign schema and we want this one to be the result from this function right here so there you go now the second part of this is going to be the prompt the third part is going to be my language model and then let's just add an output parser from L chain core. output parsers I'm going to import a string output poer this is because we want to make sure that whatever this chain returns is only going to be a string okay I mean of course you can just tap into the property returned by the chain that is the string which is probably going to be the content property but let's just leave the let's just let the live leave that to the output parsers they just make the world the work a little bit easier and here we're going to initialize the string output parser okay so this seems to be working here we have the chat history the question so I mean just to be clear this chain is going to take as input it's going to take the question it's going to take the chat history and not the schema because we already have the schema right here that is that is um executed from the get schema function okay I mean that is returned from the get schema function so there we go now how about we actually test this function all right uh sorry this uh chain so in order to do that I am going to say that the response is going to [Music] be first of all I'm just going to create the chain right here um SQL chain get SQL chain I'm going to pass into database like that then the response I'm going to say that it's the SQL chain and I'm going to invoke it and here remember that it take it it takes an uh a dictionary with all of the variables that you want to populate from The Prompt so in this case the first one that I want to populate is chat history with my session State chat history and the other one is the question which is the user query and there we go so let's actually I mean I'm not sure this is actually going to work so let's see how this is see how this works so first of all we're going to have to connect to our database in order to actually initialize this database object and now we can ask a question something like how many artists are there and let's see what it returns it's probably not going to get it right oh there you go so select count from artist so there you go this apparently seems to be working uh the chain is actually returning our SQL query perfect so now what we want to do is put this chain inside of another chain that will return I mean that will execute this chain in the database will read the uh that will exit this query in the database sorry we'll read the results from the database and then we'll um uh report to us in natural language what the results are okay so let's do that right now okay so I mean just to be clear what we're going to be doing right now is we have this SQL chain that we just be that we just finished building and now we're just going to put it inside of a bigger chain that is going to take whatever this chain returns which is the SQL query run it towards the database and feed it back to the language model so that the language model can return a natural language answer okay so in order to do that what I'm going to do is I'm going to create a new function that is going to be called get response okay so I'm going to do this function called get response and this function is going to take a few parameters it's going to take first of all it is going to take a user query then it's going to take the database and it is going to take also the chat history okay now this is of course not complete we want the SQL chain to be to be initialized right here but we also want to create a new chain that is going to use that chain inside of it so the first thing to do is to create a template uh just like we did before up here uh we're going to create a similar template but this time it's going to return the actual the actual natural language response that we're supposed to get at the end so you're we're going to be saying you're a data analyst at a company you're interacting with a user who's asking you questions about the company's database and here we actually are supposed to already have all of this information to send back to the language model so we have based on the table schema below the question the SQL query which is this one the SQL response which is the response that the database returned from running the SQL query on it write a natural language response to to this question okay so let's just put this here user question so that it knows that this is the actual user question um so there we go now now what we're going to do is we're going to to initialize the prompt from this template so the prompt is going to equal chat prompt template we're going to initialize it from a template like that then I'm going to initialize my language model again um just going to continue it open AI but I'm going to show you in a moment because a lot of you have been asking how to do this with free with with free models I'm going to show you how to do this with frock um and mistol which is completely free at least for now and um and once that we have this actually you can pretty much just start doing the chain as far as I remember so we can do chain equal and the first thing that we're going to do is we're going to run a pass in a runnable which is going to be our SQL chain and we're going to first assign the doesn't it's not supposed to take the schema it is supposed to take the query you know to populate this variable right here and the query is actually going to be returned from our SQL chain okay now this itself is going to return another runnable that we can also use to assign other variables and here is where things can get a little tricky runnables are H not probably not the best I mean the most beautiful design here um maybe they will change at they will change at some future version of L chain at least I don't find them super easy to use but um just stick with me and follow along with this code I am going to explain a little bit later in the video in more detail what's actually going on behind the scenes here so I just want to show you that it works and then I'm going to show you an intermediate step to make it easier to understand okay so don't um don't give up if you don't understand this in the next three M three minutes I'm going to explain this again um before the end of the video so stick to the end um all right so here what we're going to do is we're going to assign other variables the first one is going to be the schema the schema and for the schema what we're going to do is instead of actually recreating this function right here I'm just going to do what GitHub what GitHub co-pilot is suggesting I'm going to use the Lambda function that just takes the database and just Returns the DB uh get T table info just like we did with this wrapper right here and the second thing that we're going to want to populate is the response because here we have the response uh here we have the response and we want to populate it so the response is going to equal Lambda here I'm going to call it vars and I am going to run I think it's is run like that yeah I'm going to run from the variables returned I'm going to return the query okay so there you go this is supposed to work if I'm not mistaken once we have the runnable finished we can which was the harder the harder part we can do just passing the prompt the language model and just like we did before the string output parser and then just close the chain and then return whatever chain return whatever the chain um uh returns okay so in this case we're going to return remember that in order to invoke a chain we're going to want to pass in the variables that we want to populate from The Prompt and in this case as you might have as you might remember from this runnables we have already populated the query we have already populated the response and we have already populated the schema so all that we need is is the question and the chat history so let's do that so the question is going to be the user query that we passed right here and the chat history oops and the chat history is going to be the chat history variable that we also passed in as a parameter okay so there you go um then we save this and of course there are much more efficient ways to do this here for example we are uh querying the database to get the schema every single time the user asks a question that's absolutely a terrible design just trying to show you how this works real quick uh this is of course not supposed to be production ready um so here what we're going to want to do is instead of calling the SQL chain directly bom we're just going to get the response from get response and remember that this takes the user query the database and the chat history and then we're just going to showcase the response like that now if I'm not mistaken this is supposed to be working um so how about we just test it right here let's see let's go back here and say first of all remember that we have to connect in order to actually have our DB object uh initialized and here we're going to do something like um how many artists are there so now it's thinking I haven't haded streaming yet so based on the information from the database there are 275 artists in total and I mean if you want to see what's actually going on we can see the lsmith um the lsmith trace back right here and as you can see the first here we have it the for the first runnable which is the first chain that we run we were we asked it how many ARS are there and here is the history and the output was correctly our select count from artist which is the name of the table let's see what the prompt was so the prompt returned you are data analyst at a company you're interacting with the user who's asking you questions here we have the schema of the entire database then at the end just like we saw we have the conversation history which has been correctly populated with AI message hello I am an SQL assistant and then our message which is how many artists are there then we have the SQL query for not uh right only the SQL query and nothing else blah blah blah and then we have our prompt our question and the response was of course select count from artist and then same thing happened up here in the second chain in which we sent as input our question and the query remember which is what we did right somewhere right here the query and the question which was which was here the query and the question and there we go seems to be correctly the output was correct the schema and here we have the chat PR template you are data analyst at a company based on the table schema question SQL query and response write a natural language response here we have the schema which goes all the way to the bottom which is pretty long which really should be storing that in a variable or something instead of querying it every single time um we have the conversation history we have the SQL query which was select count from artist we have the user question which is how many artists are there and we have the response and then of course we have the response from the language model which was based on the information from the database there are 275 artists in total um I mean just to let you know what is going on here this is is uh lsmith which allows you just to see uh every single step of your chains and what is actually happening behind the scenes uh it's pretty useful it's like super cool um and uh I think it's free for now uh if you want to use it for your personal projects um but yeah I mean totally recommend and um so there you go that's that's how to do that let me just explain to you what actually happened right here um and also maybe we can oh and add Mist all right let's do that now okay so let me show you what's actually going behind the scenes in this part right here so now that you saw that it actually works um this part right here was a little sketchy um and I mean I understand why it's weird because I mean runnables um this interface for the runnables is probably not the most user friendly so let me show you what's actually going on behind the scenes we have this runnable right here where we passed in the SQL chain in order to get the query okay so that's the first runnable and that runnable is going to return some variables as an output and those variables uh we can use them in in in this other method okay so here we have the vars which is where these output is stored and actually to show you what's going on behind the scenes I'm just going to comment out the place where we run the query towards the database and I'm going to show you what's happening here so what we're going to do is here I'm going to comment this out and instead of using it I am going to print it so let's let's say the variables and let's just let's just pass in the vars that we got from here okay now this is of course going to break the application um because we're not going to be running the query towards the the database but this will probably this will hopefully show you what's actually going on um behind the scenes here so now that this is saved I'm going to go back here remember to connect before running and now let's do well how many artists are there now of course this going to say that there was an error um let's see what happens here um it's trying to retrieve the data but I mean we're not we never generated the database we never generated it so it looks like there was an issue rriv the data yes of course and now what we can do is we can see here that we have the variables um here here is the variables that I locked with the print method and here we have the question how many artist are there the chat history and here we have the query that was generated from my the query that was generated from my previous pass through so this is the one that we want to use to run on the database okay so that's why I did DB DB run vars query because I want to tap into this this variable right here that was returned from the runnable so that's that's why this is um that's why we did it like this now if we save it it works again how many artists are there uh yeah have to connect first how many artists are there and now it Returns the actual answer uh what I'm going to do now is I'm going to show you how to stream that because I mean we did that in the previous video so why not implement it here as well and let's also use Grog from mistol so let's let's do those two bonus things now great all right so now let me show you how to do pretty much the same thing but instead of using the paid model of open AI we we can use um we can use Mixr which is free and we're going to be using uh Gro in rock in order to to use Mixr okay so in case you're not familiar with rock it is a very I mean it's a new A New Concept I mean they they provide an interface that allows you to run some language models in a much much faster way they just have a different way to to process the the completions uh which they call lpu I'm not going to go into details about how this works but just know that it supposed to be I mean it is much faster than calling the API directly of any of these models um so yeah I mean if you haven't heard of rock just take a look at it and it's pretty good it does have a bit of a latency problem at the beginning but uh it's super fast so what we're going to be doing in order to use frog with mixol what we're going to do is I'm going to go back here and remember that at the beginning we installed this package called lunching Rock and that is very that is that's going to install everything that you need to to use Rock here so what we're going to do is we're going to do from L chain from L chain grck we're going to import chat GR like that and then we're just going to replace the language model every time we initialize it right here so here instead of calling the Lang instead of calling the language model from open AI we're going to call it from Rock and then just rock you you're going to want to pass in the name of the model that you want to use so in my case I want to use mix strol this one right here so I'm just going to populate the parameter right here saying that the the model that I want is mixt dral and I'm just going to set the temperature to zero as well just to be sure that it's not getting too creative with the SQL queries so that's for this one and here is the SQL chain so we're going to do the same thing with the get response chain right here let's comment out open AI add in Rock and just save this and let's see how this is behaving um I mean in in your case you probably want to test a few models see which one is actually doing better with the SQL queries that you're using and with SQL queries in general um it doesn't matter I mean it's it's difficult to to know in advance which language model is going to behave best in a particular uh in a in a given task so I encourage you to test uh several of these of course if this was going to go to production I would probably find tuna model to do well with my SQL chain with my SQL uh query generation because here we're using a general model and which is probably an Overkill and it's probably not as sufficient as fine tuning model for my SQL queries but let's see how how mix does let's ask a question of something like which artists have which artists yeah which artists have the most albums let's say which three artists have the most albums um It's thinking the three artists with the most albums are the ones with I is 90 22 and 58 so apparently it it looked for the artists by ID so let's see uh so this is what the prompt was what was returned as a query so select artist IDs yeah so it's it's selecting for artist ID which I mean it's okay but it's probably not what we want we want the actual names of the artists um well at least it identify that these are the IDS and not the names of the artists so let's just ask it a follow-up question right let's do something like which artists artist names correspond to those IDs let's see if it gets it right mixol come on do don't all right so the artist with ID is 1922 and 58 let zein purple and item Maiden respectively okay so seems to be working correctly great all right so let's see the prompt here let's see the prompt right here um the output the r2r blah blah and did the select name from artist Vari is yeah so I mean it actually did it correctly great uh very surprised um because this is not an agent this is just a chain and this is a quite sophisticated quite a sophisticated uh application of a chain in real life you would probably use an agent instead of a chain for this kind of uh applications but wow works pretty good so yeah I mean I'll I'll add a link to this article in the description in case you want to go deeper into how this works uh behind the scenes and yeah congratulations for following all the way up to here in this tutorial and yeah as homework for you I encourage you to try to implement streaming into this chatbot uh my previous video shows you how to do that so uh hopefully that's going to be useful for you too so thank you very much for watching this video and thank you very much for being here part of the channel it's amazing join the Discord Community if you're free I'll be more active now that I have finally gotten my diploma and um thank you very much again I will see you next [Music] [Music] [Music] time [Music]