自学内容网 自学内容网

langchain实现基于sql的问答

1. 数据准备

import requests

url = "https://storage.googleapis.com/benchmarks-artifacts/chinook/Chinook.db"

response = requests.get(url)

if response.status_code == 200:
    # Open a local file in binary write mode
    with open("Chinook.db", "wb") as file:
        # Write the content of the response (the file) to the local file
        file.write(response.content)
    print("File downloaded and saved as Chinook.db")
else:
    print(f"Failed to download the file. Status code: {response.status_code}")
File downloaded and saved as Chinook.db

2. 数据库链接

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 10;")

输出:

sqlite
['Album', 'Artist', 'Customer', 'Employee', 'Genre', 'Invoice', 'InvoiceLine', 'MediaType', 'Playlist', 'PlaylistTrack', 'Track']
"[(1, 'AC/DC'), (2, 'Accept'), (3, 'Aerosmith'), (4, 'Alanis Morissette'), (5, 'Alice In Chains'), (6, 'Antônio Carlos Jobim'), (7, 'Apocalyptica'), (8, 'Audioslave'), (9, 'BackBeat'), (10, 'Billy Cobham')]"

3. 提示词模板

from langchain import hub
from langgraph.prebuilt import create_react_agent
from langchain_openai import ChatOpenAI
from langchain_community.agent_toolkits import SQLDatabaseToolkit
# Pull prompt (or define your own)
prompt_template = hub.pull("langchain-ai/sql-agent-system-prompt")

system_message = prompt_template.format(dialect="SQLite", top_k=5)

print(prompt_template)
print(system_message)

输出:

d:\soft\anaconda\envs\langchain\Lib\site-packages\langsmith\client.py:354: LangSmithMissingAPIKeyWarning: API key must be provided when using hosted LangSmith API
  warnings.warn(


input_variables=['dialect', 'top_k'] input_types={} partial_variables={} metadata={'lc_hub_owner': 'langchain-ai', 'lc_hub_repo': 'sql-agent-system-prompt', 'lc_hub_commit_hash': '31156d5fe3945188ee172151b086712d22b8c70f8f1c0505f5457594424ed352'} messages=[SystemMessagePromptTemplate(prompt=PromptTemplate(input_variables=['dialect', 'top_k'], input_types={}, partial_variables={}, template='You are an agent designed to interact with a SQL database.\nGiven an input question, create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.\nUnless the user specifies a specific number of examples they wish to obtain, always limit your query to at most {top_k} results.\nYou can order the results by a relevant column to return the most interesting examples in the database.\nNever query for all the columns from a specific table, only ask for the relevant columns given the question.\nYou have access to tools for interacting with the database.\nOnly use the below tools. Only use the information returned by the below tools to construct your final answer.\nYou MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.\n\nDO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.\n\nTo start you should ALWAYS look at the tables in the database to see what you can query.\nDo NOT skip this step.\nThen you should query the schema of the most relevant tables.'), additional_kwargs={})]
System: You are an agent designed to interact with a SQL database.
Given an input question, create a syntactically correct SQLite query to run, then look at the results of the query and return the answer.
Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most 5 results.
You can order the results by a relevant column to return the most interesting examples in the database.
Never query for all the columns from a specific table, only ask for the relevant columns given the question.
You have access to tools for interacting with the database.
Only use the below tools. Only use the information returned by the below tools to construct your final answer.
You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.

DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.

To start you should ALWAYS look at the tables in the database to see what you can query.
Do NOT skip this step.
Then you should query the schema of the most relevant tables.

4. 数据库工具箱

toolkit = SQLDatabaseToolkit(db=db, llm=llm)
tools = toolkit.get_tools()
tools

输出:

[QuerySQLDataBaseTool(description="Input to this tool is a detailed and correct SQL query, output is a result from the database. If the query is not correct, an error message will be returned. If an error is returned, rewrite the query, check the query, and try again. If you encounter an issue with Unknown column 'xxxx' in 'field list', use sql_db_schema to query the correct table fields.", db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x00000182B25C3770>),
 InfoSQLDatabaseTool(description='Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables. Be sure that the tables actually exist by calling sql_db_list_tables first! Example Input: table1, table2, table3', db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x00000182B25C3770>),
 ListSQLDatabaseTool(db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x00000182B25C3770>),
 QuerySQLCheckerTool(description='Use this tool to double check if your query is correct before executing it. Always use this tool before executing a query with sql_db_query!', db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x00000182B25C3770>, llm=ChatOpenAI(client=<openai.resources.chat.completions.Completions object at 0x00000182B778E750>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x00000182B77B0140>, root_client=<openai.OpenAI object at 0x00000182B778C620>, root_async_client=<openai.AsyncOpenAI object at 0x00000182B778E7B0>, model_name='GLM-4-Plus', temperature=0.0, model_kwargs={}, openai_api_key=SecretStr('**********'), openai_api_base='https://open.bigmodel.cn/api/paas/v4/'), llm_chain=LLMChain(verbose=False, prompt=PromptTemplate(input_variables=['dialect', 'query'], input_types={}, partial_variables={}, template='\n{query}\nDouble check the {dialect} query above for common mistakes, including:\n- Using NOT IN with NULL values\n- Using UNION when UNION ALL should have been used\n- Using BETWEEN for exclusive ranges\n- Data type mismatch in predicates\n- Properly quoting identifiers\n- Using the correct number of arguments for functions\n- Casting to the correct data type\n- Using the proper columns for joins\n\nIf there are any of the above mistakes, rewrite the query. If there are no mistakes, just reproduce the original query.\n\nOutput the final SQL query only.\n\nSQL Query: '), llm=ChatOpenAI(client=<openai.resources.chat.completions.Completions object at 0x00000182B778E750>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x00000182B77B0140>, root_client=<openai.OpenAI object at 0x00000182B778C620>, root_async_client=<openai.AsyncOpenAI object at 0x00000182B778E7B0>, model_name='GLM-4-Plus', temperature=0.0, model_kwargs={}, openai_api_key=SecretStr('**********'), openai_api_base='https://open.bigmodel.cn/api/paas/v4/'), output_parser=StrOutputParser(), llm_kwargs={}))]

6. 流程图

llm = ChatOpenAI(
    temperature=0,
    model="GLM-4-Plus",
    openai_api_key="your api key",
    openai_api_base="https://open.bigmodel.cn/api/paas/v4/"
)

# Create agent
agent_executor = create_react_agent(
    llm, tools , state_modifier=system_message
)

from IPython.display import Image, display

try:
    display(Image(agent_executor.get_graph(xray=True).draw_mermaid_png()))
except Exception:
    # This requires some extra dependencies and is optional
    pass

在这里插入图片描述

7. 示例

example_query = "Which sales agent made the most in sales in 2009?"

events = agent_executor.stream(
    {"messages": [("user", example_query)]},
    stream_mode="values",
)
for event in events:
    event["messages"][-1].pretty_print()

输出:

================================[1m Human Message [0m=================================

Which sales agent made the most in sales in 2009?
==================================[1m Ai Message [0m==================================
Tool Calls:
  sql_db_list_tables (call_-9197044058595346666)
 Call ID: call_-9197044058595346666
  Args:
=================================[1m Tool Message [0m=================================
Name: sql_db_list_tables

Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
==================================[1m Ai Message [0m==================================
Tool Calls:
  sql_db_schema (call_-9197044092955150896)
 Call ID: call_-9197044092955150896
  Args:
    table_names: Employee,Invoice
=================================[1m Tool Message [0m=================================
Name: sql_db_schema


CREATE TABLE "Employee" (
"EmployeeId" INTEGER NOT NULL, 
"LastName" NVARCHAR(20) NOT NULL, 
"FirstName" NVARCHAR(20) NOT NULL, 
"Title" NVARCHAR(30), 
"ReportsTo" INTEGER, 
"BirthDate" DATETIME, 
"HireDate" DATETIME, 
"Address" NVARCHAR(70), 
"City" NVARCHAR(40), 
"State" NVARCHAR(40), 
"Country" NVARCHAR(40), 
"PostalCode" NVARCHAR(10), 
"Phone" NVARCHAR(24), 
"Fax" NVARCHAR(24), 
"Email" NVARCHAR(60), 
PRIMARY KEY ("EmployeeId"), 
FOREIGN KEY("ReportsTo") REFERENCES "Employee" ("EmployeeId")
)

/*
3 rows from Employee table:
EmployeeIdLastNameFirstNameTitleReportsToBirthDateHireDateAddressCityStateCountryPostalCodePhoneFaxEmail
1AdamsAndrewGeneral ManagerNone1962-02-18 00:00:002002-08-14 00:00:0011120 Jasper Ave NWEdmontonABCanadaT5K 2N1+1 (780) 428-9482+1 (780) 428-3457andrew@chinookcorp.com
2EdwardsNancySales Manager11958-12-08 00:00:002002-05-01 00:00:00825 8 Ave SWCalgaryABCanadaT2P 2T3+1 (403) 262-3443+1 (403) 262-3322nancy@chinookcorp.com
3PeacockJaneSales Support Agent21973-08-29 00:00:002002-04-01 00:00:001111 6 Ave SWCalgaryABCanadaT2P 5M5+1 (403) 262-3443+1 (403) 262-6712jane@chinookcorp.com
*/


CREATE TABLE "Invoice" (
"InvoiceId" INTEGER NOT NULL, 
"CustomerId" INTEGER NOT NULL, 
"InvoiceDate" DATETIME NOT NULL, 
"BillingAddress" NVARCHAR(70), 
"BillingCity" NVARCHAR(40), 
"BillingState" NVARCHAR(40), 
"BillingCountry" NVARCHAR(40), 
"BillingPostalCode" NVARCHAR(10), 
"Total" NUMERIC(10, 2) NOT NULL, 
PRIMARY KEY ("InvoiceId"), 
FOREIGN KEY("CustomerId") REFERENCES "Customer" ("CustomerId")
)

/*
3 rows from Invoice table:
InvoiceIdCustomerIdInvoiceDateBillingAddressBillingCityBillingStateBillingCountryBillingPostalCodeTotal
122009-01-01 00:00:00Theodor-Heuss-Straße 34StuttgartNoneGermany701741.98
242009-01-02 00:00:00Ullevålsveien 14OsloNoneNorway01713.96
382009-01-03 00:00:00Grétrystraat 63BrusselsNoneBelgium10005.94
*/
==================================[1m Ai Message [0m==================================
Tool Calls:
  sql_db_query_checker (call_-9197047906887043597)
 Call ID: call_-9197047906887043597
  Args:
    query: SELECT e.FirstName, e.LastName, SUM(i.Total) as TotalSales FROM Employee e JOIN Invoice i ON e.EmployeeId = i.CustomerId WHERE strftime('%Y', i.InvoiceDate) = '2009' GROUP BY e.EmployeeId ORDER BY TotalSales DESC LIMIT 1
=================================[1m Tool Message [0m=================================
Name: sql_db_query_checker

```sql
SELECT e.FirstName, e.LastName, SUM(i.Total) as TotalSales 
FROM Employee e 
JOIN Invoice i ON e.EmployeeId = i.CustomerId 
WHERE strftime('%Y', i.InvoiceDate) = '2009' 
GROUP BY e.EmployeeId 
ORDER BY TotalSales DESC 
LIMIT 1
```
==================================[1m Ai Message [0m==================================
Tool Calls:
  sql_db_query (call_-9197041344174917718)
 Call ID: call_-9197041344174917718
  Args:
    query: SELECT e.FirstName, e.LastName, SUM(i.Total) as TotalSales FROM Employee e JOIN Invoice i ON e.EmployeeId = i.CustomerId WHERE strftime('%Y', i.InvoiceDate) = '2009' GROUP BY e.EmployeeId ORDER BY TotalSales DESC LIMIT 1
=================================[1m Tool Message [0m=================================
Name: sql_db_query

[('Nancy', 'Edwards', 24.75)]
==================================[1m Ai Message [0m==================================

The sales agent who made the most in sales in 2009 is Nancy Edwards, with a total of $24.75 in sales.

参考链接:https://langchain-ai.github.io/langgraph/tutorials/sql-agent/
如果有任何问题,欢迎在评论区提问。


原文地址:https://blog.csdn.net/qq_41472205/article/details/144185389

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!