GenerativeAI is one of the most promising branches of the whole AI industry and people are getting benefits from it. there are plenty of scenarios that we can discuss here but let’s talk about how we can interact and chat with our SQL data with GenerativeAI.
Talking to your SQL data using GenerativeAI is a big leverage for many companies for example if you talk about the Sales and Marketing team, they need some real numbers in their meetings or campaigns, and having a system where they can get these real numbers as quickly as possible is a big leverage. Or if you talk about the Finance team where every piece of financial transaction is being handled and also they need a system to track the company finances to make some decisions, they can use this kind of tool to get a quick overview of the financial summary by just simply asking for it.
Let’s dive into how we can build a simple tool that can help the whole company.
Let’s install the dependencies
pip install ––upgrade ––quiet langchain langchain–community langchain–openai
after installing these dependencies, please remember to have your OPENAI_API_KEY ready because we are using OpenAI for this tool. You can use your LLMs as well but for the sake of simplicity, we are using OpenAIs existing LLM.
import getpass
import os
os.environ[“OPENAI_API_KEY”] = getpass.getpass()
We are going to use SQLite as our demo database but you can you any other SQL database. you can download the ChinookDB from this link and it also guides you on how to set it up.
Basic steps to read and query Chinook Sqlite
- Save this file as
Chinook_Sqlite.sql - Run
sqlite3 Chinook.db - Run
.read Chinook_Sqlite.sql - Test
SELECT * FROM Artist LIMIT 20;
Now that you have set up the ChinookDB SQLite and it is in your directory, the next step is to interact with it using SQLAlchemy-driven.
from langchain_community.utilities import SQLDatabase
db = SQLDatabase.from_uri(“sqlite:///Chinook.db”)
print(db.dialect)
print(db.get_usable_table_names())
db.run(“SELECT * FROM Artist LIMIT 20;”)the above code will return results like these
sqlite
[‘Album’, ‘Artist’, ‘Customer’, ‘Employee’, ‘Genre’, ‘Invoice’, ‘InvoiceLine’, ‘MediaType’, ‘Playlist’, ‘PlaylistTrack’, ‘Track’]
awesome, our queries are working fine using this code. it is time now to interact with LLM and human language, now let’s create a simple chain that takes a question and converts it into an SQL query, runs it and returns the results.
from langchain.chains import create_sql_query_chain
from langchain_openai import ChatOpenAI
llm = ChatOpenAI(model=“gpt-3.5-turbo”, temperature=0)
chain = create_sql_query_chain(llm, db)
response = chain.invoke({“question”: “How many employees are there”})
response
the above code will return a query
‘SELECT COUNT(*) FROM Employee’
and if you execute the query you will get a response
db.run(response)
‘[(8,)]’
Now that it is generating SQL queries our next step is to run it they way it does it all by itself like below
from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool
execute_query = QuerySQLDataBaseTool(db=db)
write_query = create_sql_query_chain(llm, db)
chain = write_query | execute_query
chain.invoke({“question”: “How many employees are there”})
results
‘[(8,)]’
Now that we have a way to automatically generate and execute the query let’s refine the code and do it more professionally.
from operator import itemgetter
from langchain_core.output_parsers import StrOutputParser
from langchain_core.prompts import PromptTemplate
from langchain_core.runnables import RunnablePassthroughanswer_prompt = PromptTemplate.from_template(
“””Given the following user question, corresponding SQL query, and SQL result, answer the user question.Question: {question}
SQL Query: {query}
SQL Result: {result}
Answer: “””
)answer = answer_prompt | llm | StrOutputParser()
chain = (
RunnablePassthrough.assign(query=write_query).assign(
result=itemgetter(“query”) | execute_query
)
| answer
)chain.invoke({“question”: “How many employees are there”})
now the above code will get a question and return SQL Query, SQL Result and final answer
‘There are 8 employees.’
I hope that the basics of SQL agent using langchain and GenerativeAI is enough to go towards the advanced level of this agent.
