How I made an app that writes SQL

I just built the SQL database agent with my new AI data science team and I want to show share how to do that in this video okay so it's really easy it's actually astonishingly easy to start taking my AI agents that I'm building in this AI data science team and integrating them into this app so let's check this app out so this is what it does it basically keeps a history of it Con connects up to a database so you can connect it to like any database that you want I'm going to show you how

to do that in this video you enter your a API key for open AI you pick a model if you want GPT 40 mini or 40 and what you're going to do is go down through and we can start asking questions about this database we can start generating SQL queries it'll produce data frames and tables it'll aggregate things like this one aggregate sales for each territory and it produces this complex SQL query something that would personally take me a while to type up and then it generates the result so this is the the result after

you execute that query on your database all right so this is what we're going to build today it's part of my new AI data science team so I'm going to link put a link to this in the notes check this GitHub repo out please do me a favor start it it actually helps me it helps you find and stay in touch with the changes but it also helps me because it shows how popular this this new package of data science teams my new AI power data science team is is getting but anyways back to what

we're trying to accomplish here so I want to introduce you really quick to this data science team that I'm building it's an army of Agents literally that you can pull off the shelf so we're going to see the SQL database agent in action today inside of a web application okay so that's what we saw over here underneath the hood one of my agents is running okay I'll share how to do that here in a second but first let's just take a look at the GitHub repo so this AI data science team it's your army of

AI agents that all do T data science tasks the goal is to speed up the stuff that you most L do so 10x speed up of things like feature engineering data cleaning data wrangling SQL database queries machine learning models all that stuff we're getting even into multi-agents I have a I'm probably going to end up building an app around that one soon and then now what I'm talking about here today is data science apps okay so this is the first of many data science apps that I'm going to start building with this AI data science

team very very cool stuff all right so over to the code what I'm going to be doing here is I'm going to be sharing how to use that SQL database agent that I talked about in order to be able to build this web application that automates all the querying and does all that that fun stuff so okay so the code today I this is this comes from my AI tips newsletter do me a favor join that when you join the newsletter what I'm doing is documenting my journey so as I make all of these tutorials

I'm documenting every single one and when you join you not only get access to the code for this tutorial which is right here this 010 SQL database agent but there's more you get access to every single one for free can't beat it um so you get 10 10 tutorials currently and more coming so you also get access to the data that we're going to be running I'm going to be using this North Wind database today and that has a bunch of tables it's it's pretty big database and is great to simulate a company's Erp system

so enterprise resource planning and we're going to build a seal database agent on top of it all right so the code that I'm working out of is this app.py that's this streamlet file here okay and what we're covering today is the SQL database agent what it is so I already kind of showed you what that was second thing expose you to the new AI data science team I also did that make sure you start the GitHub repo and then the third thing how to build the app that's what we're going to do right now make

sure that you uh go to the project re repo make sure you give it a GitHub a star do me a favor Star right up here right now pause the video star that helps out big time the next thing that you're going to do you're going to make sure that you have the latest version of the AI data science team installed you just open up a terminal go terminal new terminal and you'll pop that right in here this line pip install with this get string that's going to go right down here and it's going to

install the software requirements if you want to know all the requirements for this particular app that I have that store here in the requirements.txt file you need your AI data science team make sure you also have streamlet SQL Alchemy pandas async IO I did actually set this up for async which is awesome um Lang chain Lang chain open AI because we're going to be using open AI uh Lang graph and open AI packages all right um then this app is already set up for you to basically use it from you know scratch all right so

whatever your database is or you can even connect it to multiple data bases you're going to put your a label here and where that's going to pop up is it's going to pop up right over here your select database you see this let me escape out of that yeah for some reason the Apple thing keeps popping up but anyways you're going to have a list of databases okay so that's what's that's what this is and then you're going to add a query string okay and that's going to be the string that's going to connect you

to your database if you have a MySQL server or whatever else you're going to put that in here and you're going to connect up to it just like the string that you would use to connect up through SQL Alchemy all right the model list because I have this already all set up for o Open AI I'm using their models so GPT 40 mini and GPT 40 are your two options where those will populate is once you put an API key in this will pop up down here and you can choose your model but you have

to have a an API key and you're going to PO paste that in here so make sure you go to open AI get an API key from them and then you're going to paste that in here and then what you'll be able to do is start running that okay the rest is kind of you know basic stuff for building streamlet apps so you're going to include a title of your app so my title is your SQL database agent I have a mark down here that tells you a little bit about what this agent does so

that's this piece here we've got a little expander here which gives you some sample questions so when you open this um this will be shut but you can just click on it open it and then you got your sample questions here um and then sidebar is going to be these items over here so that that's what's going to populate your database options this St sidebar select box is going to be select your database we're going to store that in the session state and then also create a connection that connection object will be used when we

create the seq agent this is the open AI logic that makes sure that you've got a valid API key before we proceed and then if you do have an open AI API key in there then this stuff will appear down here where you can actually select the model all right next part is the streamlet app so this is the most complicated part we've got a streamlet chat history that's what populates all of this stuff here so the streamlet chat history and then you've got a little indicator here or a little session state where we're going

to add in session State data frames and we're going to keep track of those in our history so because we're going to be producing these data frames here we need to store those somewhere so we're going to keep track as the user interacts with our application we need to store those and make sure that they're persistent as they add new questions like for example down here if I add another question you know aggregate the sales by month for each territory pop that in down here what we don't don't want to have happen is all of

the previous history get deleted or especially these these data frames okay so that's what this is doing it's tracking each one of these given each data frame an ID and so on okay so you see there I've got month now territory this is a completely new query and you can see here that it saves the all of the previous history okay so that's what we're doing with this display chat history and keeping track of these data frames and stuff all right here is the key thing it's one line of code all we're doing is we're

creating a SQL database agent we're giving it the llm model a connection calling it end samples one to minimize the number of samples that are that are going to be going to our query we're going to say log false because we don't want to actually log any of the pipeline functions that would be produced by this SQL database agent when it makes the queries and we're also going to bypass one of the key steps which is a recommendation step just to speed up our process of building queries okay this function here this async defa might

look a little weird if you've ever done asynchronous programming before but basically what this is going to do is it's going to invoke our agent asynchronously why is that important because inside of applications if you got multiple users pounding you know llms and trying to like Ping your you know ping ping your if it's your open AI account and they're all trying to Ping it at once what they would have to do is wait sequentially for each one of those llm calls to be complete this async does what that does is it allows you to

asynchronously if you have multiple users of your app at the same time call those queries asyn asynchronously uh very handy so whenever you're inside of apps you always want to have async functions okay and then this is the part that actually processes the query so what you're going to have is once the user enters a question in and as long as they have a path to their database present what it's going to do is if they have an open AI account that's been but verified uh it's then going to move on to this logic here

so what we're doing is we're going to process and as asynchronously we're going to run that handle question function which is this thing here all it does is just creates from our created SQL database agent it's just going to invoke it with whatever the user question is all right and it's going to return the SQL agent dated with you know as it processes the question it's going to have methods in here that we can call okay so we're going to call those methods down here assuming that there's no error and we we get the asynchronous

result back what we're going to grab out is the SQL query code from that agent and we're going to grab out the data from that SQL query so once the SQL query gets executed on the database it returns a data frame and that's we're going to just take those two and we're going to pop it in so if there's a response we're going to produce the SQL query as marked down and then what we're going to do here is add the data data frame to our session State data frames so we're going to add add

that up here so we can track it and then we're also going to print out the response here okay and that's what we're doing that's all there is to this app so the beautiful part is you can literally take this application update it with your database options it's all ready to run on any database that you want to use let's give it a test spin here I'll refresh it and I'll show you that it's working if I pop in my SQL or my API key oops wrong API key here let me grab an API key

uh here I'll throw this one in here see if this one works and we're good okay so we can see what tables exist in the database boom pop that in here run that we should see this thing running here if I go back up here see the agent is running and it's literally reporting the out output results right here okay very cool so last thing I probably should have mentioned this previously but this is what you use to run the app so if I want to run the app here let's do this say I control

C the app kill the kill this app the app is no longer running I can see here site can't be reached you just run this line of code here so let's paste that in streamlet run and it'll fire up the app for you okay and there we go