[8기 랭체인] LLM + Langchain으로 자연어로 DB 조회하기

sqlite3 Chinook.db
.read Chinook_Sqlite.sql
SELECT * FROM Artist LIMIT 10;

from langchain.prompts import ChatPromptTemplate

template = """Based on the table schema below, write a SQL query that would answer the user's question:
{schema}

Question: {question}
SQL Query:"""

prompt = ChatPromptTemplate.from_template(template)

from langchain.utilities import SQLDatabase
db = SQLDatabase.from_uri("sqlite:////Users/hwang-injun/development/side-project/Chinook.db")

def get_schema(_):
    return db.get_table_info()

def run_query(query):
    return db.run(query)

from langchain.chat_models import ChatOpenAI
from langchain.schema.output_parser import StrOutputParser
from langchain.schema.runnable import RunnablePassthrough

model = ChatOpenAI(openai_api_key='sk-0pfxOgraYtsManEakYDgT3Bl*******')

sql_response = (
    RunnablePassthrough.assign(schema=get_schema)
    | prompt
    | model.bind(stop=["\nSQLResult:"])
    | StrOutputParser()
)

sql_result = sql_response.invoke({"question": user_question})
print(sql_result)

template = """Write a natural language response based on the table schema, question, SQL query, and SQL response below. And present it in a table with markdown syntax And present it in a table with markdown syntax:
{schema}

Question: {question}
SQL Query: {query}
SQL Response: {response}"""

prompt_response = ChatPromptTemplate.from_template(template)

full_chain = (
    RunnablePassthrough.assign(query=sql_response) 
    | RunnablePassthrough.assign(
        schema=get_schema,
        response=lambda x: db.run(x["query"])
    )
    | prompt_response
    | model
)
result = full_chain.invoke({"question": user_question})

print(result.content)

1.How many pieces of data among composers in Trak are of unknown origin?
-> Query : SELECT COUNT(EmployeeId) AS TotalEmployees FROM Employee
-> Response : The LastName and FirstName of the person with EmployeeId number 1 are Adams and Andrew, respectively

2.What are the LastName and FirstName of the person with EmployeeId number 1
-> Query : SELECT COUNT(EmployeeId) AS TotalEmployees FROM Employee
-> Response : The LastName and FirstName of the person with EmployeeId number 1 are Adams and Andrew, respectively

3. Give me the Name of songs whose composer is Roy Z.
-> Query : 
SELECT Name
FROM Track
WHERE Composer = 'Roy Z'

-> Response : The songs whose composer is Roy Z are: "King In Crimson", "Chemical Wedding", "The Tower", "Gates Of Urizen", "Jerusalem", "Trupets Of Jericho", "The Alchemist", and "Realword".

4.Give me the title of songs whose composer is Roy Z.
-> Query : 
SELECT Title FROM Album 
JOIN Track ON Album.AlbumId = Track.AlbumId 
WHERE Track.Composer = 'Roy Z'

-> Response : The title of the songs whose composer is Roy Z is "Chemical Wedding".

5.Tell me the name of the customer with the largest [Total] value in the Invoice table
-> Query : 
SELECT c.FirstName, c.LastName
FROM Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
ORDER BY i.Total DESC
LIMIT 1;

-> Response : The customer with the largest total value in the Invoice table is Helena Holý.

6.Group and count by [BillingCountry] in the Invoice table
-> Query : 
SELECT BillingCountry, COUNT(*) AS Count
FROM Invoice
GROUP BY BillingCountry

-> Response : The query was used to group and count the number of invoices by billing country in the Invoice table. The response shows the results, indicating the billing country and the corresponding count of invoices for each country. For example, there are 7 invoices from Argentina, 7 from Australia, 7 from Austria, and so on.

7. Group and count by [BillingCountry] in the Invoice table and show the three BillingCountries and Count values in order of highest to lowest.
-> Query : 
SELECT BillingCountry, COUNT(*) as Count
FROM Invoice
GROUP BY BillingCountry
ORDER BY Count DESC

-> Response : The three Billing Countries with the highest counts in the Invoice table are as follows:\n1. USA - 91 invoices\n2. Canada - 56 invoices\n3. France - 35 invoices