02. SQL

This tutorial covers how to create SQL queries using a chain created using create_sql_query_chain, execute them, and retrieve answers. We will also look at the differences in how it works compared to SQL Agent.

Copy

# Configuration file for managing API KEY as environment variable
from dotenv import load_dotenv

# Load API KEY information
load_dotenv()

Copy

True

Copy

# Set up LangSmith tracking. https://smith.langchain.com
# !pip install langchain-teddynote
from langchain_teddynote import logging

# Enter a project name.
logging.langsmith("SQL")

Copy

Start tracking LangSmith.
[Project Name]
SQL

Retrieve SQL Database information.

Copy

from langchain_openai import ChatOpenAI
from langchain.chains import create_sql_query_chain
from langchain_community.utilities import SQLDatabase

# Connects to a SQLite database.
db = SQLDatabase.from_uri("sqlite:///data/finance.db")

# Outputs the database.
print(db.dialect)

# Prints available table names.
print(db.get_usable_table_names())

Copy

sqlite
['accounts', 'customers', 'transactions']

Create an LLM object and pass LLM and DB as parameters to create a chain.

This tutorial will proceed with gpt-3.5-turbo as changing the model here may not work smoothly.

Copy

# model specifies gpt-3.5-turbo
llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)

# Create a chain by inputting LLM and DB as parameters.
chain = create_sql_query_chain(llm, db)

(Optional) You can specify the Prompt directly in the following way.

When writing directly, you can add a descriptive column description along with table_info.

Copy

from langchain_core.prompts import PromptTemplate

prompt = PromptTemplate.from_template(
    """Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer. Unless the user specifies in his question a specific number of examples he wishes to obtain, always limit your query to at most {top_k} results. You can order the results by a relevant column to return the most interesting examples in the database.
Use the following format:

Question: "Question here"
SQLQuery: "SQL Query to run"
SQLResult: "Result of the SQLQuery"
Answer: "Final answer here"

Only use the following tables:
{table_info}

Here is the description of the columns in the tables:
`cust`: customer name
`prod`: product name
`trans`: transaction date

Question: {input}"""
).partial(dialect=db.dialect)

# model specifies gpt-3.5-turbo
llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)

# Create a chain by inputting LLM and DB as parameters.
chain = create_sql_query_chain(llm, db, prompt)

Running chain generates queries based on the DB.

Copy

# Runs chain and prints the results.
generated_sql_query = chain.invoke({"question": "고객의 이름을 나열하세요"})

# Prints the generated query.
print(generated_sql_query.__repr__())

Copy

'SELECT name\nFROM customers'

Next, it's time to check if the query we created works correctly.

Copy

from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool

# Create a tool to execute the generated query.
execute_query = QuerySQLDataBaseTool(db=db)

Copy

execute_query.invoke({"query": generated_sql_query})

Copy

"[('Teddy',), ('Paul',), ('Shirley',), ('Minsu',), ('Jiyoung',), ('Eunjung',)]"

Copy

from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool

# equipment
execute_query = QuerySQLDataBaseTool(db=db)

# SQL query generation chain
write_query = create_sql_query_chain(llm, db)

# Create a chain to execute the generated query.
chain = write_query | execute_query

Copy

# Check execution results
chain.invoke({"question": "테디의 이메일을 조회하세요"})

Copy

"[('[e-mail]',)]"

답변을 LLM 으로 증강-생성

Using the chain created in the previous step, the answer is output in a short answer format. You can adjust this to a chain in LCEL grammar to get a more natural answer.

Copy

from operator import itemgetter
from langchain_core.output_parsers import StrOutputParser
from langchain_core.prompts import PromptTemplate
from langchain_core.runnables import RunnablePassthrough

answer_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()

# Create a chain to execute the generated query and output the results.
chain = (
    RunnablePassthrough.assign(query=write_query).assign(
        result=itemgetter("query") | execute_query
    )
    | answer
)

Copy

# Check execution results
chain.invoke({"question": "Find the sum of Teddy's transactions"})

Copy

'The sum of Teddy's transactions is -965.7.'

Agent

You can use Agent to create SQL queries and output the execution results as answers.

Copy

from langchain_openai import ChatOpenAI
from langchain_community.utilities import SQLDatabase
from langchain_community.agent_toolkits import create_sql_agent

# model specifies gpt-3.5-turbo
llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)

# Connects to a SQLite database.
db = SQLDatabase.from_uri("sqlite:///data/finance.db")

# create agent
agent_executor = create_sql_agent(llm, db=db, agent_type="openai-tools", verbose=True)

Copy

# Check execution results
agent_executor.invoke(
    {"input": "Find and compare the sum of Teddy and Shirley's transactions."}
)

Copy

Entering new SQL Agent Executor chain...

Invoking: `sql_db_list_tables` with `{}`
accounts, customers, transactions

Invoking: `sql_db_schema` with `{'table_names': 'transactions'}`

CREATE TABLE transactions (
    transaction_id INTEGER, 
    account_id INTEGER, 
    amount REAL, 
    transaction_date TEXT, 
    PRIMARY KEY (transaction_id), 
    FOREIGN KEY(account_id) REFERENCES accounts (account_id)
)

/*
3 rows from transactions table:
transaction_id  account_id  amount  transaction_date
1   1   74.79   2024-07-13
2   1   -224.1  2024-05-13
3   1   -128.9  2024-01-25
*/

Invoking: `sql_db_query` with `{'query': 'SELECT account_id, SUM(amount) AS total_amount FROM transactions WHERE account_id IN (1, 2) GROUP BY account_id'}`


[(1, -965.7), (2, 743.13)] 
Teddy's trade total is -965.7 and Shirley's trade total is 743.13.

Finished chain.

Copy

{'input': 'Sum Teddy and Shirley's transactions and compare', 'output': 'Teddy's sum of transactions is -965.7, and Shirley's sum is 743.13.'}