Transcription
Hello again, this is Leno Tados. In this part two, let's go ahead and get started.
I'm going to use LangChain first to show you how we can actually communicate with a SQL database, in this case, the Affinity database for one of the CMSs, and be able to use that in English to get some responses back. So, let's go ahead.
Alright, so let's click the plus sign again. I'm going to call this 01, and we'll name it LangChain SQL. We'll give it a .py for Python. In here, you can call it anything you want, of course.
Inside of there, I would like to go ahead and bring in `import pyodbc` because this is one that I'm going to be using for my URI against the SQL Server database. Plus, of course, I'm going to be bringing in all the different inputs that I'm going to need in my file.
Let me bring them in. I'm going to see if I can bring in the input from OpenAI, from LangChain OpenAI, and the SQL database from the utilities of LangChain Community. As you can see, all the other ones include SQL create, SQL agents, the agent types, the SQL databases, and the SQL database toolkit.
Again, you'll have all that code available for you in the public repository on GitHub, but this is just to set up everything else so that we know we are bringing in the right things everywhere to get started.
The next thing I'm going to do is actually have a correct URI using my ODBC. So, the first line here I'm going to keep so you can remember what this looks like.
When you create a variable, for instance, like `my_URI`, you will say `ms_sql+pyodbc://username:password@server.database.windows.net:1433/database_name?driver=ODBC Driver 18 for SQL Server`.
If you are going against the Azure SQL database, it will be a name in here, like `server.database.windows.net`. Then, you will put the database name itself that you would like to have access to.
After that, you can actually put something like `driver=`. Of course, this has to be correct syntax for the variable, so there are no spaces. You'll have to put the plus sign in here, so `ODBC Driver 18 for SQL Server` or, as you can see, you can actually do something like `SQL Server Native Client 11`, and so on.
There are a lot of different drivers for that, but of course, this example is using ODBC. Just in case you're a consultant and you're trying to charge by the hour, this will be much better for you and make a lot of money. No, I'm just kidding; it's just too slow.
I usually like to use the SQL Server Native Client 11. Because this is local on my machine, I'm going to use Windows authentication. I don't need to pass the username or the password; I just need to say `app=localhost\SQL2022` (the name of my SQL Express instance), and then the name of my database is `ttbdb`.
We'll put a question mark and then put `driver=` and specify whatever driver you want, whether it's the ODBC one or the native client for .NET. This is a correctly formatted URI, and I'm going to leave it here in the comment at the top.
When you get the code and you'd like to hook up to something actually in Azure or in a different data center that contains SQL base authentication, you are more than welcome to use that as well.
The next part I'm going to use after this is to go ahead and use the SQL database. Remember here, we imported from the community utilities of LangChain, we imported SQL database. So, I can come in here and say `DB = SQLDatabase.from_uri(my_URI)`, and I'm going to be passing that URI right in there.
That's the only thing you need to do to actually get a database object available in memory at that point.
Next, after that, we're going to use the LLM. I'm going to use OpenAI, but you can use whatever you want. Let me show you how I did that. I created a variable called `llm`, and because I am importing OpenAI from LangChain OpenAI, I'm going to say `OpenAI(temperature=0.5)`.
It can go anywhere between 0 to 1; zero means very accurate, one means very creative. So, I want it to be very accurate. I can also pass the API key. There are a lot of ways to do that. You are more than welcome to create an `.env` file, which is the environment file, and load it from the environment itself.
But for right now, I'm just going to go ahead and hardcode the API key right inside of OpenAI itself. Don't worry about actually taking this or trying to use it. After these three videos have been uploaded, I'm going to be deleting my API key and generating another one, so no big deal. I just wanted to show you how this will end up working.
The next two lines of code are extremely important from LangChain. One is to call the SQL database toolkit, passing it the instance of the DB we got from the URI and the LLM I decided to use.
You might actually notice what model did I use. Well, if you put your cursor over OpenAI right there, if you go all the way to the bottom, you see the last line at the bottom. It says `model: str = ""`. If you don't pass the model name, it will automatically use GPT-3.5-turbo instruct.
That means, of course, if you want to use one of the newer ones like GPT-4 or GPT-4.0, you'll have to pass `model=` and specify that. But for right now, I want to show you what happens when I use an older model that doesn't have a lot of tokens, especially when I have over 256 tables.
I'm doing this on purpose to show you what's going on here. When I create a variable called `SQL_toolkit`, and then finally when I say `SQL_toolkit.get_tools()`, that will actually end up getting me all the tools available based on that database.
For instance, to be able to find the dialect of SQL that I'm going to be using, whether it was PostgreSQL, Microsoft SQL, or SQLite, `get_tools()` will actually make sure all the stuff brings in the correct dialect for everything.
Now, believe it or not, we get to the last line of code. This is it; we're done. I'm going to actually bring it in, and I'll tell you the power of that line. I'm going to use `create_sql_agent`. Remember, `create_sql_agent` is part of the LangChain community agent toolkits for SQL.
When I call this, I'm going to pass it the LLM, and I'm going to be passing the toolkit, which has the dialect of Microsoft SQL Server at this point. Then, I'm going to have to choose one of the agent types. There are a lot of them, so you can say `agent_type` (it's an enum).
I chose to use `zero_shot_react_description`. React here means you will be able to react and make an action. Reasoning and action are the most important things. If you want to see what else is available, go after the dot and do control space on the keyboard.
You will notice there are a lot of them. There is this zero-shot react description, there is react doc store, there is open multifunction, and there are many different ones that you can actually use for conversational react, and so on and so forth.
Also, for you to be able to see something very interesting, you can come at the end and say `verbose=True`. I recommend you turn this on in the beginning when you're learning how to do all this because it's very informative.
The reason why I'm saying it's very informative is that sometimes the orchestrator, which is LangChain, and the LLM (in our case here, GPT-3.5-turbo instruct) will negotiate back and forth. The LLM might say, "I'm not convinced; you need to go ahead and do some more work to get me the correct answer."
You will see the conversation if you set `verbose=False`; you won't see anything except the answers at the end. So, keeping `verbose=True` is a good thing to actually learn how all this works.
So, folks, are you ready to make your first English-based query? Let's go ahead and do it. I'm going to come in here, and I'm going to paste a line that says `print(SQL_DB_agent)`, which is the variable that came in from the agent that I just created.
I'm going to invoke it and say, "How many users are in the database, and what are their email addresses?" Notice I didn't tell it which table this is in. For instance, if you're familiar with Sitefinity, usually these are available in a table called `SF_users`.
Of course, their email addresses will be in there, but maybe I will ask questions that require other tables. Maybe it needs the profile tables, or maybe it will use a user profile and link tables.
The beauty of actually using something like this is that the system will figure it out by itself based on the schema. When I actually did the SQL database toolkit, it brought in the entire schema. That means my prompt that I'm using for the `get_tools()` will actually tell the LLM exactly what's going on.
It will tell, "This is my question, and by the way, here is the entire schema for everything inside of there." Of course, if you have 256 tables or so inside of Sitefinity, that's a lot of tokens. So, you have to be careful.
If I'm using an LLM that has a limit of like 2,000 or 4,000 tokens, maybe actually bringing in the entire schema for the entire 256 tables will come back and say, "You exceeded the tokens; I can't ask that question." This means you need something bigger and better than that.
So, are we ready to go ahead and try it out? Let's save this file, and we'll go ahead and run it.
Before I run it, it would be good to show you what it's supposed to bring back. Let me bring it down here. In the database, there is a table called `SF_users`. There it is, `SF_users` in Sitefinity.
We'll say `SELECT TOP 100 * FROM SF_users;` I have four users inside of that: my kids, Kobe and Justin, Leno at the trainingboss.com, and one for my site sync called ttb at the trainingboss.com.
So, these are the four users. Notice I can tell from this table who's an admin, who's an author, and who a designer. That requires other tables, and we will take care of that later on.
But for right now, I do actually have the usernames and the passwords, of course, encrypted with the salt key, and I have also the IDs. Let me show you where the IDs are; they are very important.
We're going to need those later. These are the IDs. Notice Leno has `a28c8`; it starts with these four numbers. We're going to have to remember that because I'll show you how the system will figure it out by itself.
There is the email address for each and every single one. The email address and the username inside Sitefinity are the same.
Now that we know that there are four users, let's go ahead and compile this. I'm going to say `cls` to clear the screen, and I'd like to say `python`.
There you go, Python, and we'll say `python 01_lang_chain_sql.py`.
If I compile this, I want you to see what's going to happen. Please be careful to read the communication and the negotiation going on between the two.
I'm going to run this. Let me make this a little bit bigger, and I will definitely stop and show you all the `verbose=True` output.
I want you to see all the stuff happening in front of us, one step at a time. Alright, it usually takes about maybe the first time we do this about 10 seconds.
Notice there's a lot of stuff going on, a lot of gibberish, but that gibberish means a lot. I will show you in a second what it means.
Let's go all the way to the top here. First of all, `get_tools()` did give me the SQL DB list tables. That means I want to get all the tables. Remember, there are about 256 different tables.
Here are all the tables inside of this Affinity database. After that, it says, "Okay, give me the SQL DB schema for the table that I think I should get this information from."
See how smart it is? It should query the schema of the `SF_users` table to see what columns are available. So, it actually was able to spit out the entire structure of the `CREATE TABLE` of the `SF_users`.
It knows exactly what all the columns are, with their identities and everything inside of there. Not only that, but actually LangChain will also bring the first three records inside of this table just to help it understand what the data looks like.
So, if my query requires two or three tables, the schema of all three tables will come back, and the first three rows of every single one of them will come back as well.
Remember, all the stuff will end up being part of the prompt that will be sent out. So, that's how smart this is.
I'm going to go down here and see if this was successful. It is still actually working on it, and I bet you it's not going to work.
I'll tell you why in a second. It is too much content for GPT-3.5-turbo, so it's going to end up crashing and it will tell me, "You ran out of tokens."
See that? It came back in here: "This model's maximum context length is 497 tokens; however, you requested 41.99 tokens." So, we were pretty close, right?
That is about 256 for the completion itself. So, I'm telling you, please go ahead and get something a little bit more powerful to do this.
Let's go ahead and fix this problem. Let me bring this down a little bit. Instead of using OpenAI, I'm going to use ChatOpenAI.
I'm going to have to change that actually in my call as well. We'll make it `ChatOpenAI`, and right before the API, I should come in here and say `model=`.
Inside of `model=`, I could say `gpt-4`. Okay, that's a much better model. It would be better and actually will allow the tokens to be utilized better.
So, let's go ahead and save this. I don't have to change anything else. If I come back in here, let me make this a little bit bigger again.
`cls` to clear the screen, and I will go ahead and run this again. This time, I'm expecting it to work, and hopefully, you will be impressed that the system, out of 256 tables, found out exactly which table looks like it should be used.
It will create a SQL statement, and not only that, it will take the SQL statement and execute it on your behalf. These are all negotiations going back and forth between the LLM and the orchestrator, which is LangChain.
You see, `SF_users` contains a column named `email`, which stores... So, it's trying to explain in `verbose=True`, and then it will try to do a query check to make sure this is the correct SQL statement.
So, it's negotiating, and then it finds the final answer: "There are users in the database with the following email addresses: Kobe, Justin, Leno, and ttb."
Finally, it says, "How many users are in the database, and what are their email addresses?" The output is: "There are four users in the database with the following email addresses: Kobe at trainingboss.com, Justin at loo, and ttb."
Isn't that pretty cool?
Alright, I hope you enjoyed this video. There is definitely a much better way to do this, and I'm going to do that in the final video, number three, which will show you how to use LlamaIndex to accomplish that.
Thank you for watching! If you liked the video, please click on like, and if you'd like to be notified about any future videos, please also subscribe to the channel. Thank you so much!