标签：db langchain 查询 LangChain SQL query NVARCHAR Qwen1.5

该模块可以让我们向LLM提问时从数据库中查询数据并做出回答。

架构

SQL chain和agent的高层抽象架构：

问题转查询SQL：使用LLM将用户输入转成SQL查询
执行SQL查询：执行SQL语句
回答问题：LLM根据数据库查询结果返回回答内容

环境安装

安装必要环境和包

pip install --upgrade --quiet  langchain langchain-community langchain-openai

在本文章中默认使用openai的模型

import getpass
import os

os.environ["OPENAI_API_KEY"] = getpass.getpass()

# Uncomment the below to use LangSmith. Not required.
# os.environ["LANGCHAIN_API_KEY"] = getpass.getpass()
# os.environ["LANGCHAIN_TRACING_V2"] = "true"

下面的例子均使用SQLite连接Chinook数据库，按照这些安装步骤创建一个Chinook.db文件在当前目录下

将该文件保存成 Chinook_Sqlite.sql
运行sqlite3 Chinook.db
运行 .read Chinook_Sqlite.sql
测试 SELECT * FROM Artist LIMIT 10;

现在，Chinhook.db已经在我们的目录中，我们可以使用基于SQLAlchemy的 SQLDatabase 类与它进行交互：

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')]"

Chain

让我们创建一个简单的Chain，它接收一个问题，将其转换成SQL查询，执行该查询，并将结果用于回答原始问题。

将问题转换为SQL查询

SQL链条或代理的第一步是接收用户输入并将其转换为SQL查询。LangChain内置了用于此目的的链条：create_sql_query_chain

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

'SELECT COUNT(*) FROM Employee'

db.run(response)

'[(8,)]'

我们可以查看 LangSmith 的跟踪以更好地理解这个链在做什么。我们也可以直接检查链的提示。从下面的提示中，我们可以看到它是：

方言特定的。在这种情况下，它明确引用了 SQLite。
具有所有可用表的定义。
每个表都有三个示例行。
这种技术受到了像这样的论文的启发，这些论文建议展示示例行并明确表格可以提高性能。我们也可以这样检查完整的提示：

回答问题

现在我们已经有了自动生成和执行查询的方法，我们只需要将原始问题和SQL查询结果结合起来生成最终答案。我们可以再次将问题和结果传递给LLM来完成这个过程。

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()
chain = (
    RunnablePassthrough.assign(query=write_query).assign(
        result=itemgetter("query") | execute_query
    )
    | answer
)

chain.invoke({"question": "How many employees are there"})

'There are 8 employees.'

下一步

对于更复杂的查询生成，我们可能需要创建少样本提示或添加查询检查步骤。要了解更多关于此类高级技术，请查看以下内容：

提示策略：高级提示工程技术。
查询检查：添加查询验证和错误处理。
大型数据库：处理大型数据库的技术。

Agent

LangChain的SQL Agent是一个设计用于与SQL数据库进行更灵活交互的工具。它的主要优点包括：

基于数据库模式和内容回答问题：SQL Agent不仅能理解并回答有关数据库模式的问题（例如描述一个特定表的结构），还能基于数据库的实际内容回答问题。
错误恢复能力：通过执行生成的查询，如果遇到错误，它可以捕获错误信息(traceback)，然后正确地重新生成查询，从而实现从错误中恢复的能力。
处理依赖多个查询的问题：对于需要多个步骤和依赖不同查询结果的问题，SQL Agent能够有效地管理和执行这些依赖的查询。
节省令牌（tokens）：通过仅考虑相关表的模式，SQL Agent能够更有效地使用令牌，减少不必要的令牌消耗。

使用create_sql_agent函数可以初始化这个代理。这个代理包含了SQLDatabaseToolkit，它提供了一系列工具来：

创建和执行查询
检查查询语法
检索表描述
...等等

这意味着，通过使用SQL Agent和内置的SQLDatabaseToolkit，开发者能够更加灵活和高效地与SQL数据库进行交互，无论是执行复杂的查询，还是处理和维护数据库，都能够得到简化和加速。这对于需要频繁与数据库交互的应用程序来说是一个很大的优势，尤其是在需要动态生成查询语句或处理复杂数据库交互逻辑的时候。

初始化Agent

from langchain_community.agent_toolkits import create_sql_agent

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

agent_executor.invoke(
    {
        "input": "List the total sales per country. Which country's customers spent the most?"
    }
)


> Entering new AgentExecutor chain...

Invoking: `sql_db_list_tables` with `{}`


Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
Invoking: `sql_db_schema` with `Invoice,Customer`



CREATE TABLE "Customer" (
    "CustomerId" INTEGER NOT NULL, 
    "FirstName" NVARCHAR(40) NOT NULL, 
    "LastName" NVARCHAR(20) NOT NULL, 
    "Company" NVARCHAR(80), 
    "Address" NVARCHAR(70), 
    "City" NVARCHAR(40), 
    "State" NVARCHAR(40), 
    "Country" NVARCHAR(40), 
    "PostalCode" NVARCHAR(10), 
    "Phone" NVARCHAR(24), 
    "Fax" NVARCHAR(24), 
    "Email" NVARCHAR(60) NOT NULL, 
    "SupportRepId" INTEGER, 
    PRIMARY KEY ("CustomerId"), 
    FOREIGN KEY("SupportRepId") REFERENCES "Employee" ("EmployeeId")
)

/*
3 rows from Customer table:
CustomerId  FirstName   LastName    Company Address City    State   Country PostalCode  Phone   Fax Email   SupportRepId
1   Luís    Gonçalves   Embraer - Empresa Brasileira de Aeronáutica S.A.    Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP  Brazil  12227-000   +55 (12) 3923-5555  +55 (12) 3923-5566  [email protected]    3
2   Leonie  Köhler  None    Theodor-Heuss-Straße 34 Stuttgart   None    Germany 70174   +49 0711 2842222    None    [email protected]   5
3   François    Tremblay    None    1498 rue Bélanger   Montréal    QC  Canada  H2G 1A7 +1 (514) 721-4711   None    [email protected] 3
*/


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:
InvoiceId   CustomerId  InvoiceDate BillingAddress  BillingCity BillingState    BillingCountry  BillingPostalCode   Total
1   2   2009-01-01 00:00:00 Theodor-Heuss-Straße 34 Stuttgart   None    Germany 70174   1.98
2   4   2009-01-02 00:00:00 Ullevålsveien 14    Oslo    None    Norway  0171    3.96
3   8   2009-01-03 00:00:00 Grétrystraat 63 Brussels    None    Belgium 1000    5.94
*/
Invoking: `sql_db_query` with `SELECT c.Country, SUM(i.Total) AS TotalSales FROM Invoice i JOIN Customer c ON i.CustomerId = c.CustomerId GROUP BY c.Country ORDER BY TotalSales DESC LIMIT 10;`
responded: To list the total sales per country, I can query the "Invoice" and "Customer" tables. I will join these tables on the "CustomerId" column and group the results by the "BillingCountry" column. Then, I will calculate the sum of the "Total" column to get the total sales per country. Finally, I will order the results in descending order of the total sales.

Here is the SQL query:

```sql
SELECT c.Country, SUM(i.Total) AS TotalSales
FROM Invoice i
JOIN Customer c ON i.CustomerId = c.CustomerId
GROUP BY c.Country
ORDER BY TotalSales DESC
LIMIT 10;
```

Now, I will execute this query to get the total sales per country.

[('USA', 523.0600000000003), ('Canada', 303.9599999999999), ('France', 195.09999999999994), ('Brazil', 190.09999999999997), ('Germany', 156.48), ('United Kingdom', 112.85999999999999), ('Czech Republic', 90.24000000000001), ('Portugal', 77.23999999999998), ('India', 75.25999999999999), ('Chile', 46.62)]The total sales per country are as follows:

1. USA: $523.06
2. Canada: $303.96
3. France: $195.10
4. Brazil: $190.10
5. Germany: $156.48
6. United Kingdom: $112.86
7. Czech Republic: $90.24
8. Portugal: $77.24
9. India: $75.26
10. Chile: $46.62

To answer the second question, the country whose customers spent the most is the USA, with a total sales of $523.06.

> Finished chain.

Qwen1.5连接Hive并查表示例

需要提前安装pyhive环境以及xinference

from langchain_community.utilities import SQLDatabase

db = SQLDatabase.from_uri("hive://user:[email protected]:10000/mydatabase")
print(db.dialect)
print(db.get_usable_table_names())
db.run("SELECT * FROM dim_static.dim_loan_part_number_df LIMIT 10")

xinference启动Qwen1.5

from langchain.chains import create_sql_query_chain
from langchain_openai import ChatOpenAI
from langchain.llms import Xinference
from langchain.prompts import PromptTemplate

llm = Xinference(server_url="http://localhost:9997", model_uid="Qwen1.5-14B-Chat",temperature=0.3)

chain = create_sql_query_chain(llm, db)
response = chain.invoke({"question": "How many employer are there"})
response

db.run(response)

参考文献

https://python.langchain.com/docs/use_cases/sql/quickstart

标签：db,langchain,查询,LangChain,SQL,query,NVARCHAR,Qwen1.5
From： https://www.cnblogs.com/AlwaysSui/p/18104385

LangChain SQL介绍以及使用Qwen1.5执行SQL查询教程

架构