Chat with MySQL Database with Python | LangChain Tutorial
HTML-код
- Опубликовано: 28 май 2024
- Discover how to interact with a MySQL database using Python and LangChain in our latest tutorial. This comprehensive guide walks you through the process of creating a LangChain chain, detailing every step with a helpful diagram for a clearer understanding. Whether you're a beginner or an experienced developer, this video will equip you with the knowledge to execute SQL queries using an innovative approach.
IMPORTANT: Remember to NOT use a MySQL user with WRITE privileges. Use only READ and limit the scope. Otherwise your user could ask your chain to delete data.
USEFUL LINKS:
📌 Article (follow along): alejandro-ao.com/chat-with-my...
📊 Chinook database: github.com/lerocha/chinook-da...
💬 Join the Discord Help Server: link.alejandro-ao.com/HrFKZn
❤️ Buy me a coffee... or a beer (thanks): link.alejandro-ao.com/l83gNq
Timestamps:
0:00 Intro
1:02 How this works
2:59 Our test data
4:58 Load the test data
6:47 Notebook setup
9:23 Create SQL Chain Prompt
13:00 Load MySQL Database in Python
17:01 Create SQL Chain
26:02 Create run_query Function & Final Prompt
29:45 Create Full Chain
35:54 Conclusion
In this tutorial, you'll learn:
- How to set up LangChain to communicate with a MySQL database.
- The intricacies of creating and utilizing SQL queries within LangChain.
- Implementing a full chain that includes SQL query generation and natural language response construction.
- Best practices for interacting with databases using Python and LangChain.
Key Highlights:
- LangChain Integration: Learn how to create a LangChain chain for database queries.
- SQL Query Generation: Understand the process behind generating SQL queries from natural language questions.
- Interactive Diagram: A detailed diagram explains the architecture and process flow.
- Comprehensive Code Walkthrough: From setting up your environment to executing queries, every step is covered.
Who Should Watch?
This tutorial is perfect for developers, data scientists, and tech enthusiasts interested in leveraging LangChain for database interactions. Whether you're looking to enhance your projects or explore new technologies, this guide has something for everyone.
Stay Connected: Don't forget to subscribe to our channel for more tutorials on cutting-edge technologies. Join our Discord server for community discussions and updates. Join us here.
Happy Coding! 🚀
Hi, I reviewed this "Chat with MySQL DB" tutorial yet again and decided to try a more difficult SQL query: "determine the most popular artist in the database based upon total sales.". Of course, I manually ran the full Select -- with three table joins -- to make sure that this new query would work and produce the correct answer(s). To my surprise, the "natural language query" was properly processed by my modified version of your program and produced the correct response: " Iron Maiden with total sales of $138.60 " ! To further test the program, I changed the question to "top three most popular artists". The correct result was again returned: "Iron Maiden, U2, and Metallica" ! Glad to see 'U2' near the top ! Since "langchain-openai" is only at version 0.0.8 and "SQLDatabase" at 0.0.26, we may expect even more NLP Query improvements in the near future [i.e., if 'Altman' & 'Musk' do not mess everything up for all of us !?!]. P.S. Looking forward to the next MySQL vid(s) that you post !
I got an error when I tried your first query - the query was correct (validated in database) but it was prepended by "sql" like this:
[SQL: ```sql
SELECT a.Name AS Artist, SUM(il.UnitPrice * il.Quantity) AS TotalSales
FROM Artist a
JOIN Album al ON a.ArtistId = al.ArtistId
JOIN Track t ON al.AlbumId = t.AlbumId
JOIN InvoiceLine il ON t.TrackId = il.TrackId
JOIN Invoice i ON il.InvoiceId = i.InvoiceId
GROUP BY a.Name
ORDER BY TotalSales DESC
LIMIT 1;
```]
@@EricLofland Thank You! For the feedback. The natural language query worked well for me and ChatGPT4 validated the SQL Query. I have since moved on to testing SQLite and also saving query vectors in ChromaDB. Later, I will try to re-check and reproduce the MySQL Database query and compare it to your results.
@@EricLofland I reset my laptop for my extension of Alejandro's "Chat with MySQL" Python program. It ran as before and produced both the SQL and
Natural Language Query. SQL: run_query("select artist.Name AS ArtistName, COUNT(*) AS TotalSales from \
invoiceline join track on invoiceline.TrackId = track.TrackId \
join album on track.AlbumId = album.AlbumId \
join artist on album.ArtistId = artist.ArtistId \
group by artist.Name order by TotalSales desc limit 3;")
NLQ: user_question = 'determine the top three most popular artists in the database based upon total sales.'
BOTH Results were very similar: "'The top three most popular artists in the database based on total sales are Iron Maiden with $138.60, U2 with $105.93, and Metallica with $90.09.".
Further, I researched any conditions under which "sql" would be prepended to the SELECT statement. I did NOT find any examples of this artifact, however, I did find a good very recent "Medium" article on this subject by " Senthil E " at " levelup.gitconnected.com/llms-meet-sql-revolutionizing-data-querying-with-natural-language-processing-52487337f043 ". As we say "Hope This Helps!" and thanks again for your detailed feedback.
@@davidtindell950 Are you writing up any of your experiments/tests? I'd be interested in seeing the caching of queries and responses into a vector database.
@@warrenmarkham8891 Hi, I expect to write a new "Medium" article fairly soon, however, I am still currently continuing my R&D. In addition to Alejandro's excellent tutorials, I have found another good tutorial that includes the employment of PyTorch for fast processing of large vector databases: " Maximize ChromaDB Embedding Vectorization Speed with NVidia CUDA GPU and Python Multiprocessing " " Johnny Code " " ruclips.net/video/7FvdwwvqrD4/видео.html " and " ruclips.net/video/7FvdwwvqrD4/видео.htmlsi=a18-dKxTYk2UvRMT ". Also, there are several good current "Medium" articles available on this research subject.
Thanks for including MySQL and not just SQLite.
sure :)
Dude, incredible tutorial, right on the money for what i needed.
Nice, looking forward to part 2!
Coming soon! 😎
I really love your tutorials, you are teaching us to create such powerful AI tools in Python that are really useful. I'm surprised that you have only 26K subscribers, you should have millions.
thank you man! i hope i will get there someday!
Loving this video, Alejandro! 2 things I'd like to add:
1. I removed the {schema} part from the full_chain prompt and it works great. Only the sql_chain needs schema (to produce the sql query).
2. Your website is down :(
Thanks for explaining every bit of your code so well!
Awesome. Looking forward to part 2....
First time here and I'm glad I gound your channel.Thanks for sharing!
hey there! welcome to the channel :) very happy to have you here :)
@@alejandro_ao Thanks!!
Love to watch your tutorials. It's very details.
thanks! keep it up!
I love your content, thanks for all your efforts ❤
i appreciate it! let me know what you want to see next
Amazing Video. Great job
Love your videos, started watching all your langchain and it really has helped me and I wanted to say thanks. I would also like to see the use of agents if it isnt too much to ask
hey there! thank you for telling me this :) keep it up and keep learning 🚀 i'll bring up agents here very soon!
Thanks Alejandro!
Thank you man, you are amazing
This is a great tutorial. Thanks for explaining it in a way that's easy to understand
thanks Teddy! i’m glad it was useful!
Hi Alejandro. Great post! It helped me a lot. I was trying to find a Gemini alternative to a solution that I learned from a Udemy course, using LangChain and OpenAI Agents to access a SQLite database and pass the results to the OpenAI LLM. I searched for days and found nothing, until I saw your video. Now my code is running and I can see many possibilities for accessing enterprise databases to enable users to obtain results using generative AI. Thank you and greetings from Brazil.
Great stuff! Many thanks 💪
No problem 👍
Your videos are the best!
you are the best
Thank you for your videos. As a subscriber to your channel, I look forward to each new one. I would like to make a comment, and perhaps a suggestion for a future video. As a Finance Manager for the last 40+ years, I have come to find these three areas important as it relates to the retrieval of information. What every office need is a way to search:
1) URL's , EXCEL, and PDF on the internet.
2) EXCEL and CSV files locally. (and Securely)
3) PDF and DOC documents locally. (and Securely)
And nowadays, you could never get a NON GUI program adopted by the office staff. And of course, all three of the search types would be incorporated into the same GUI.
Python based program would be the preferred language. OLLAMA based. NO DOCKER. No wrapper programs like streamlit, etc. Thanks for your time, and keep up the good work.
Hey there, thanks for following the channel and for your suggestions!
I see what you mean and agree that in order to get an app adopted internally, it should be very straightforward and easy to use.
However, I don't see why it shouldn't use streamlit? Streamlit is just a way to build the GUI in a few lines of code. And if what you are interested in is the privacy, you can totally host it internally and have your data never leave your network.
Thank you so much for this video, this is really helpful!! Looking forward to using huggingface models
Great! Which models in particular are you interested in?
Mistral AI
Thanks. What about long ot short term memory during conversation?
Will this work with a Microsoft SQL relational database that is much bigger too?
for the GUI, what tool would you suggest one can use to return a table, just like in mysql
Thanks for useful video)
it's my pleasure :)
great as always bro
thank you bro
I love you!! You are the best!!!
i love you more
When i write the code in py file, the schema variable in full_chain function s returning an error (it expect dict type and get_schema return str), do you have any idea on how to fix it ? i've checked langchain doc + you article but i still cant find a solution...
thanks for all the tutorials i've learned a lot, keep going !
Having the same issue
You really made my day. i was trying to figure out this code from Langchain templates from many days. But you gave a perfect clarity giving step by step understanding. Thanks alot for that. Can u further enhance this in your next video as said in the end of the video with ollama & vizualization from the response using PandasAI or LIDA AI or something better please.
it's great to hear this! thank you for letting me know. and congrats for finally getting through it! keep it up 👍 that's actually a great idea. i'll see if i can put it in the next video or make a dedicated video about this!
Excellent! ♥
i really need to update my video on memory. i'll look into it!
Amazing video!! Thanks for creating it!! Is there a way to apply the same principles but with another LLMs (open-source)?? If I had gone over all the database I am working with and I had identified the queries related to the most frequent questions, how can I finetune my queries to these frequent questions? ..should I think about adding RAG logic to it?
Awesome. Could you include streaming in part 2 as well?
streaming is coming very soon 😎
Fabulous!
hey Brandon! thanks!
Thanks for posting this. Very helpful. is there any open source LLM which can convert Natural language to SQL ? would Llama, Flan T5 etc work instead of gpt ?
Awesome tutorial! I tried to implement the same on collar, using a sqlite database(chinook). but consistently get the error:"'NoneType' object has no attribute 'get_table_info'" when I try to ge the table schema. Any work arounds?
Could you please let me know if it's possible to use LangChain to query multiple tables and generate data visualizations on the chatbot interface? I've seen solutions for a single CSV file using PandasAI or LIDA, but I haven't found anything that works with multiple tables stored in a database.
I was wondering if your calendly link is working for consultations ?it seems that its down
Have done it with SAP Hana DB 6 month back..issue related hallucinations are faced and token size limit is also a constraint with open ai GPT 3.5 turbo
Did you make it work at the end? Or was not worth the struggle?
@@vojtechkaiser2525 Not much work done on that further but of course you can use combination of SQL agent and combinations of prompt techniques but the issues is different as you SAP would not support or recommend working directly with SQL tables but I would still try it as Q&A bot simply as an "art of possible" solution...I am now trying to do the same using combination of OData API and through BTP using function calls and agent architecture
Hey , is it possible to run DML queries using chains or agents?
Hey, thanks for the video. However, few questions if you don't mind:
1. Do we need to assign the sql_chain inputs again when building a full_chain ? Won't it remember from its own structure. I see that as complexity grows, we end up adding a whole bunch of assignment in full chain.
2. For full chain, is there a way to use pipe operator to let the sql_chain response be passed to the next step where you run_query ? Basically, RunnablePassthrough.assign(sql_chain) | run_query | prompt | llm | StrOutput... ?
Thanks again for your wonderful tutorials.
you just think too complicated 😉
Hey very nice,
I had one query , LLM based Application to assess the quality of language being used by parents and give practice sessions to improve them.
Can you show us "It is a kind of Therapy for special children to make understand the words
Great tutorial as usual Alejandro! Is it possible to do this with SQL Server? I look at the documentation and it doesn't appear so but maybe I'm missing something.
Thanks! I am not sure how it would work with MS SQL Server. I suppose you would need a driver to connect it. Since SQLAlchemy supports it, I suppose that it can be done. Maybe if you add the driver to the URI like we did here, but instead of adding the MySQL driver, you add one for MS SQL Server? I checked and this driver might work, but I am not on Windows, so I have no way of testing it right away:
```python
from langchain_community.utilities import SQLDatabase
db_uri = "mssql+pyodbc://username:password@hostname:port/DatabaseName?driver=SQL+Server"
db = SQLDatabase.from_uri(db_uri)
```
@@alejandro_aoI'll give that a try. Thanks much!!
Thanks!
Hi - how does this work when the response returns table data - example - show me top 10 artists by song streaming count?
hey there! thank you so much for the tip!! it totally would work. the results are always a table actually. what happens is that the LLM receives a table-like prompt and reads it as though it were simple text. so in this case, your LLM would receive the table in the prompt and return something like "the top 10 artists are...." and it may even give you more details depending on your initial instructions :)
Hi Alejandro
I have been trying to do the same thing. The problem I do not have credit in OpenAI and i wanted to know if there is any other way using opensource models to achieve the same result..specially in LangChain. Is there any other way?
Thanks so much Alejandro! Great contents. Btw seems your website is down today>?
hey there, thanks! can you check again? i think my dns has been struggling with some changes i did recently :S
@@alejandro_ao Thank you Alejandro. All works fine now!
Very Informative video ❤, can you create this video in PHP
hey, thanks! i would like to but unfortunately langchain does not have a PHP version that I know of :( if you are trying to create something like this for a PHP app, I would create an API that deals with these processes in python or js and then have my PHP app query this API
Can you create langchain based streaming chatbot? Would be very helpful. Thanks for creating high quality contents!
coming very soon!
Could you please elaborate on the following with respect to SQL agents:
Handling Relationships with Multiple Tables:
How can we manage relations involving multiple tables, specifically with primary and foreign keys, given token limitations and potentially multiple agents?
Similarity Search in Databases:
How can we perform a similarity search within a SQL database? For example, if we have several film plots stored in the database, how can we find films with similar plots?
Orchestration Between SQL and JSON Databases (MongoDB):
How can we orchestrate data between SQL databases and JSON-based databases like MongoDB without converting everything to SQL, which can be time-consuming?
Displaying Subsets of Tables:
How can we present a subset of data from multiple tables in the database to the user? Should we parse JSON or CSV formats for this purpose?
Utility of Graph Databases:
Can Graph databases provide solutions to the above challenges?
Should I make predictions about the future based on the data in my table?
Can we not using Oracle DB for this test?
Hi, been following you for a long time, very cool content. Can you please tell me how to use Langchain for MSSQL or Postgres?
hey there! thanks for following the content :) i'll be putting up a video about postgres soon. i actually haven't tested if this would work using a mssql driver. but look, apparently you can pass in a driver just like we did here with mysql, but for MSSQL: docs.sqlalchemy.org/en/20/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc
Videos are exceptional just very high level and the mic seems close when speaking so its not pleaseant for me to watch the vids with that type of mic calibration. Luckily the website is a better walkthough for me. Thanks for your work
hey thank you for the feedback, glad you found the tutorial useful mate. i’ll improve the sound in future vids :)
Thanks for such amazing resource, Alejandro.. i am getting this error: attributeerror : dict object has no attribute get_table_info.. when i try to invoke chain
seems to me like your database instance is not being created. try logging the type of your SQL client to see if it was actually defined
Thank you. This is a real interesting idea. I wonder how complex of a question you can ask it. Can it come up with a query which requires a merging of tables, or returning multiple values? You have me curious.
I have used this method to join across a db of 34 tables to form a master document archive for customer data. I was happy with it.
absolutely, it all depends on your LLM's accuracy at executing the queries. in my experience, GPT3 16k is very good. as @sanjayojha1 mentioned, a coding-specific LLM might give you even better results. just don't forget to limit the scope of your MySQL user to avoid security problems!
@@mwdcodeninja wow, it's glad you've been able to use this before and it performs a join. I'm working on a project with bigquery which has so many data, it doesn't give the desired output as it doesn't perform join, I'll be glad if we can connect and work on it together. Thank you in advance
@@alejandro_ao please how do we implement a code specific LLM to the chain.
@@chibuzoemelike6403 would you be able to share the schema?
❤❤Can u apply RAG on schema to accommodate bigger Databases with huge tables in next video?
can you tell me at timestamp 14:29 what is grep SQL, i confused when I was doing , it shows it not recognizing
hey there, that's just to only return the lines that contain the string "sql" when doing 'pip freeze'. otherwise i would get the huge list of all the packages installed. 'grep' is a unix command that allows you to filter the output text and return only the lines that contain the passed string 👍
I made the sqldb chatbot using fastapi and every thing is working fine except that the chat memory history. Can you suggest how can we implement memory with fastapi.
Can you please answer this
Does this can be implemented to the large Databases ?
absolutely, just be careful that these two things fall within your context window:
- the table schemas of your database (unless you have a humungous number of tables, it should be fine).
- the results from your query (as they they will be sent back to the model for interpretation).
the second point is more tricky than the first one. you may want to update your prompt to make sure that it does not allow to query more than X number of records at a time.
@@alejandro_ao i thought about this topic to use Views to handle sets form large database and pre-agregate it there
Great as always. I personally find the RunnablePassthrough ugly syntax and confusing. Also, using a coding specific LLM we might get better SQL query with less hallucination.
hey good to see you again. thanks! i totally agree with you that RunnablePassthrough could be better. and about the LLM, totally. not only better, but also faster, as it would be a smaller model 🤔
Please what do you mean by coding specific LLM?
Thanks for the content. The link to your blog post doesn't work for me.
hey there, can you try again? i think my dns server was giving me trouble last week
@@alejandro_ao Still not working I'm afraid.
@@warrenmarkham8891 just refreshed the DNS settings on netlify! Should be up now!!
@@alejandro_ao Yep, you punched the right ticket that time. It is now working great.
It's a wonderful session but the link to your blog post doesn't working.
just fixed it! DNS propagation problem after meddling with some records on netlify :S
Great tutorial, Thanks a lot. I am watching from India and your website is not opening. Don't know why.
Awesome videos.. really appreciate your efforts 👍
Could you please make a video to create a chat bot for WordPress websites.. because scrapping the content from WordPress websites is a bit tricky and passing them to divide into chunks are throwing errors...please help
@UC1oXUA7qgs0GZc_yk46K2OQ hi, I am grateful you replied to my comment 😊🙏
Actually I don't have access to deal with database..the idea of my project is to create a webchat bot for dynamic WordPress websites, where I can scrape all the content from sitemap.xml and then divide it into chunks -> store in any database like faiss or vector store and finally with streamlit I would able to chat with the content on the site.
So I need your help in this, as I am beginner and new to these technologies. Please
@UC1oXUA7qgs0GZc_yk46K2OQ hi, I am grateful you replied to my comment 😊🙏
Actually I don't have access to deal with database..the idea of my project is to create a webchat bot for dynamic WordPress websites, where I can scrape all the content from sitemap.xml and then divide it into chunks -> store in any database like faiss or vector store and finally with streamlit I would able to chat with the content on the site.
So I need your help in this, as I am beginner and new to these technologies. Please
chatting with website using gemini pro plz make a video on this???????
When I execute full_chain.invoke
({"question": "how many albums are there in the database?"}) it returns {'question': 'how many albums are there in the database?'}
i am facing the same issue, did you get any solution ?
Nice, how to do column mapping.
Like "How many creator are there" as input question but we have artist instead. I tried as below but still it not works
sql_chain = (
RunnablePassthrough.assign(schema=get_schema, column_mapping=get_column_mapping)
| prompt
| llm.bind(stop=["\
SQLResult:"])
| StrOutputParser()
)
column_mapping = {
r'creator|artists': 'artist',
# Add more mappings as needed
}
def get_column_mapping(_):
column_mapping_str = "
".join([f"{key}: {value}" for key, value in column_mapping.items()])
return column_mapping_str
I have a question: If the database belongs to an e-commerce website with a substantial product inventory, and a user query such as 'SHOW ME PRODUCTS' risks exceeding the ChatGPT token limit of 60000, how can this issue be effectively managed?
Yeah, maybe that will require a bit of prompt engineering to make sure that you never index more than X number of records. You could add something like "if you are selecting records, please never call more than 100 records in a single query" or something like that. another alternative would be to use an agent, that would make it more flexible
@@alejandro_ao Thanks for reply.. I tested this and it actually worked. Thanks bro
There is no password in my SQL server what to put in db_uri sir
This tutorial looks nice in "theory". There are several things that are wrong with this approach. Firstly, it is very insecure to fetch the database schema to a third-party provider, whoever this is. These langchain packages are not very secure, even if they are used by the community. Secondly, running the generated sql query without verification is a red flag. Yes, you can play with this, but running this in production may cause a mess. Thirdly, the prompt included a very basic query. How does this work with more complex code that is typically required in a production environment? I would really like to see the accuracy of these generated queries and the probability of actually getting a correct query from the get-go. Not to mention, how would you test this in production? How would you assign roles and permissions to run queries on top of the database. Man, there are too many wrongs things with this that seemed to be avoided, because this a new flashy approach versus the traditional way. Just because it's new, it doesn't make it better.
💯 agreed on your view..still there are challenges LLM struggle on column names which are complex or similar in synonyms although we are providing schema.
I wish I knew this before 😢
@@LeeYeon-qv1tz What happened?
@@AA-hb6sc I developed an application which generates query for given data and question. It worked for simple queries. I didn't check for complex query. Later when I showed this to someone, they asked complex queries. None was answered well. Iam really disappointed.
@@wheresthecode Hey buddy! Based on your response, it seems like you don't have a lot of experience with production-level systems. However, I am going to respond back:
1. sure, you can create a read-only select account. However, if you are going to send a ton of queries to the database, you are going to overload it and you have to have it properly tuned to support all these concurrent queries, especially if you have a lot of users. There are use-cases, especially with more complicated queries whether this cannot be easily achieved and it will take more than to generate the query, if it could be guaranteed that it will be correctly created.
2. nobody has time to create comments about the database schema and doing this, again adds additional data governance and security issues, which need to be mitigated. In some cases, the people that created the database schemas do not even want to reveal the column names or provide any information, because it is a breach of intellectual property. Having such a trace, could potentially mean that you are actually replicating the database schema.
3. how do you ensure that this template cannot be hacked or accessed in the process?
one video on SORA by openai it's look's Amaze and it's may be a Opecity between reality and virtual reality
I'll do that as soon as I get my hands on it 😈
Can you do this with NoSQL like MongoDB🙏?
great idea
❤❤❤
is open ai compulsary?
absolutely not. you can import any language model that langchain supports: python.langchain.com/docs/integrations/chat/
Could you please do with memory
we would have to update the chain and add memory to it! i'll make a video about it!
@@alejandro_ao update the chain how?
Chat with MongoDB Database is Possible?
a bit more complex but totally possible. i'll make a video about it soon
tenes un canal en español?
hola! lamentablemente no :( pero creo que youtube traduce los subtítulos automáticamente, no?
how to manage that the first chain doest find the information in the SQL Schema?
need your email
hello@alejandro-ao.com :)
Should I make predictions about the future based on the data in my table?
you could, but that would require training a ML model. that's usually something that you would do separately as you will need a lot of data processing and cleaning to do that
chatting with website using gemini pro plz make a video on this???????
hey again! and huggingface coming soon too!!!!!!!!!
@@alejandro_ao waiting for that
@@alejandro_ao hugging face also using gemini