代理商

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

import getpass
import os
 
os.environ["OPENAI_API_KEY"] = getpass.getpass()
 
# 若要使用LangSmith，请取消下面的注释。不是必需的。
# os.environ["LANGCHAIN_API_KEY"] = getpass.getpass()
# os.environ["LANGCHAIN_TRACING_V2"] = "true"

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

from langchain_community.agent_toolkits import create_sql_agent
from langchain_openai import ChatOpenAI
 
llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)
agent_executor = create_sql_agent(llm, db=db, agent_type="openai-tools", verbose=True)

agent_executor.invoke(
    "列出每个国家的总销售额。哪个国家的客户花费最多？"
)

> 进入新的AgentExecutor链...

调用: ‘sql_db_list_tables’，参数: ‘{}’


Album，Artist，Customer，Employee，Genre，Invoice，InvoiceLine，MediaType，Playlist，PlaylistTrack，Track
调用: ‘sql_db_schema’，参数: ‘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”)
)

/*
Customer表中的3行：
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”)
)

/*
Invoice表中的3行：
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
*/
调用: ‘sql_db_query’，参数: ‘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;’
响应: 要列出每个国家的总销售额，我可以查询“Invoice”和“Customer”表。我将在“CustomerId”列上加入这些表，并根据“BillingCountry”列对结果进行分组。然后，我将计算“Total”列的总和以获得每个国家的总销售额。最后，我将按总销售额的降序对结果进行排序。

这是SQL查询：

```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;
```

现在，我将执行此查询以获取每个国家的总销售额。

[('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)]每个国家的总销售额如下：

1. 美国：523.06美元
2. 加拿大：303.96美元
3. 法国：195.10美元
4. 巴西：190.10美元
5. 德国：156.48美元
6. 英国：112.86美元
7. 捷克共和国：90.24美元
8. 葡萄牙：77.24美元
9. 印度：75.26美元
10. 智利：46.62美元

要回答第二个问题，花费最多的国家是美国，总销售额为523.06美元。

> 完成链。





{'input': '列出每个国家的总销售额。哪个国家的客户花费最多？',
 'output': '每个国家的总销售额如下：\n\n1. 美国：523.06美元\n2. 加拿大：303.96美元\n3. 法国：195.10美元\n4. 巴西：190.10美元\n5. 德国：156.48美元\n6. 英国：112.86美元\n7. 捷克共和国：90.24美元\n8. 葡萄牙：77.24美元\n9. 印度：75.26美元\n10. 智利：46.62美元\n\n要回答第二个问题，花费最多的国家是美国，总销售额为523.06美元。'}

agent_executor.invoke("描述playlisttrack表")

> 进入新的AgentExecutor链...

调用: ‘sql_db_list_tables’，参数: ‘{}’


Album，Artist，Customer，Employee，Genre，Invoice，InvoiceLine，MediaType，Playlist，PlaylistTrack，Track
调用: ‘sql_db_schema’，参数: ‘PlaylistTrack’



CREATE TABLE “PlaylistTrack” (
  “PlaylistId” INTEGER NOT NULL, 
  “TrackId” INTEGER NOT NULL, 
  PRIMARY KEY (“PlaylistId”, “TrackId”), 
  FOREIGN KEY(“TrackId”) REFERENCES “Track” (“TrackId”), 
  FOREIGN KEY(“PlaylistId”) REFERENCES “Playlist” (“PlaylistId”)
)

/*
PlaylistTrack表中的3行：
PlaylistId	TrackId
1	3402
1	3389
1	3390
*/“PlaylistTrack”表有两列：“PlaylistId”和“TrackId”。它是一个表示播放列表和曲目之间多对多关系的联接表。 

这是“PlaylistTrack”表的模式：

```
CREATE TABLE “PlaylistTrack” (
  “PlaylistId” INTEGER NOT NULL, 
  “TrackId” INTEGER NOT NULL, 
  PRIMARY KEY (“PlaylistId”, “TrackId”), 
  FOREIGN KEY(“TrackId”) REFERENCES “Track” (“TrackId”), 
  FOREIGN KEY(“PlaylistId”) REFERENCES “Playlist” (“PlaylistId”)
)
```

“PlaylistId”列是一个外键，引用“Playlist”表中的“PlaylistId”列。“TrackId”列是一个外键，引用“Track”表中的“TrackId”列。

这是来自“PlaylistTrack”表的三个示例行：

```
PlaylistId   TrackId
1            3402
1            3389
1            3390
```

如果还有其他问题，请告诉我，我可以提供帮助。

> 完成链。





{'input': '描述playlisttrack表',
 'output': '“PlaylistTrack”表有两列：“PlaylistId”和“TrackId”。它是一个表示播放列表和曲目之间多对多关系的联接表。 \n\n这是“PlaylistTrack”表的模式：\n\n```\nCREATE TABLE “PlaylistTrack” (\n  “PlaylistId” INTEGER NOT NULL, \n  “TrackId” INTEGER NOT NULL, \n  PRIMARY KEY (“PlaylistId”, “TrackId”), \n  FOREIGN KEY(“TrackId”) REFERENCES “Track” (“TrackId”), \n  FOREIGN KEY(“PlaylistId”) REFERENCES “Playlist” (“PlaylistId”)\n)\n```\n\n“PlaylistId”列是一个外键，引用“Playlist”表中的“PlaylistId”列。“TrackId”列是一个外键，引用“Track”表中的“TrackId”列。\n\n这是来自“PlaylistTrack”表的三个示例行：\n\n```\nPlaylistId   TrackId\n1            3402\n1            3389\n1            3390\n```\n\n如果还有其他问题，请告诉我，我可以提供帮助。'}

examples = [
    {"input": "List all artists.", "query": "SELECT * FROM Artist;"},
    {
        "input": "Find all albums for the artist 'AC/DC'.",
        "query": "SELECT * FROM Album WHERE ArtistId = (SELECT ArtistId FROM Artist WHERE Name = 'AC/DC');",
    },
    {
        "input": "List all tracks in the 'Rock' genre.",
        "query": "SELECT * FROM Track WHERE GenreId = (SELECT GenreId FROM Genre WHERE Name = 'Rock');",
    },
    {
        "input": "Find the total duration of all tracks.",
        "query": "SELECT SUM(Milliseconds) FROM Track;",
    },
    {
        "input": "List all customers from Canada.",
        "query": "SELECT * FROM Customer WHERE Country = 'Canada';",
    },
    {
        "input": "How many tracks are there in the album with ID 5?",
        "query": "SELECT COUNT(*) FROM Track WHERE AlbumId = 5;",
    },
    {
        "input": "Find the total number of invoices.",
        "query": "SELECT COUNT(*) FROM Invoice;",
    },
    {
        "input": "List all tracks that are longer than 5 minutes.",
        "query": "SELECT * FROM Track WHERE Milliseconds > 300000;",
    },
    {
        "input": "Who are the top 5 customers by total purchase?",
        "query": "SELECT CustomerId, SUM(Total) AS TotalPurchase FROM Invoice GROUP BY CustomerId ORDER BY TotalPurchase DESC LIMIT 5;",
    },
    {
        "input": "Which albums are from the year 2000?",
        "query": "SELECT * FROM Album WHERE strftime('%Y', ReleaseDate) = '2000';",
    },
    {
        "input": "How many employees are there",
        "query": 'SELECT COUNT(*) FROM "Employee"',
    },
]

from langchain_community.vectorstores import FAISS
from langchain_core.example_selectors import SemanticSimilarityExampleSelector
from langchain_openai import OpenAIEmbeddings
 
example_selector = SemanticSimilarityExampleSelector.from_examples(
    examples,
    OpenAIEmbeddings(),
    FAISS,
    k=5,
    input_keys=["input"],
)

from langchain_core.prompts import (
    ChatPromptTemplate,
    FewShotPromptTemplate,
    MessagesPlaceholder,
    PromptTemplate,
    SystemMessagePromptTemplate,
)
 
system_prefix = """You are an agent designed to interact with a SQL database.
Given an input question, create a syntactically correct {dialect} 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 {top_k} 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 given tools. Only use the information returned by the 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.
 
If the question does not seem related to the database, just return "I don't know" as the answer.
 
Here are some examples of user inputs and their corresponding SQL queries:"""
 
few_shot_prompt = FewShotPromptTemplate(
    example_selector=example_selector,
    example_prompt=PromptTemplate.from_template(
        "User input: {input}\nSQL query: {query}"
    ),
    input_variables=["input", "dialect", "top_k"],
    prefix=system_prefix,
    suffix="",
)

full_prompt = ChatPromptTemplate.from_messages(
    [
        SystemMessagePromptTemplate(prompt=few_shot_prompt),
        ("human", "{input}"),
        MessagesPlaceholder("agent_scratchpad"),
    ]
)

# 示例格式化的提示
prompt_val = full_prompt.invoke(
    {
        "input": "How many arists are there",
        "top_k": 5,
        "dialect": "SQLite",
        "agent_scratchpad": [],
    }
)
print(prompt_val.to_string())

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 given tools. Only use the information returned by the 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.

If the question does not seem related to the database, just return "I don't know" as the answer.

Here are some examples of user inputs and their corresponding SQL queries:

User input: List all artists.
SQL query: SELECT * FROM Artist;

User input: How many employees are there
SQL query: SELECT COUNT(*) FROM "Employee"

User input: How many tracks are there in the album with ID 5?
SQL query: SELECT COUNT(*) FROM Track WHERE AlbumId = 5;

User input: List all tracks in the 'Rock' genre.
SQL query: SELECT * FROM Track WHERE GenreId = (SELECT GenreId FROM Genre WHERE Name = 'Rock');

User input: Which albums are from the year 2000?
SQL query: SELECT * FROM Album WHERE strftime('%Y', ReleaseDate) = '2000';
Human: How many arists are there

agent = create_sql_agent(
    llm=llm,
    db=db,
    prompt=full_prompt,
    verbose=True,
    agent_type="openai-tools",
)

agent.invoke({"input": "How many artists are there?"})

> Entering new AgentExecutor chain...

调用: `sql_db_query`，参数为 `{'query': 'SELECT COUNT(*) FROM Artist'}`。


[(275,)]数据库中有275位艺术家。

> Finished chain.





{'input': 'How many artists are there?',
 'output': '数据库中有275位艺术家。'}## 处理高基数列

import ast
import re
 
 
def query_as_list(db, query):
    res = db.run(query)
    res = [el for sub in ast.literal_eval(res) for el in sub if el]
    res = [re.sub(r"\b\d+\b", "", string).strip() for string in res]
    return list(set(res))
 
 
artists = query_as_list(db, "SELECT Name FROM Artist")
albums = query_as_list(db, "SELECT Title FROM Album")
albums[:5]

['Os Cães Ladram Mas A Caravana Não Pára',
 'War',
 'Mais Do Mesmo',
 "Up An' Atom",
 'Riot Act']

from langchain.agents.agent_toolkits import create_retriever_tool
 
vector_db = FAISS.from_texts(artists + albums, OpenAIEmbeddings())
retriever = vector_db.as_retriever(search_kwargs={"k": 5})
description = """用于查找要筛选值的工具。输入是对专有名词的近似拼写，输出是有效的专有名词。使用与搜索最相似的名词。"""
retriever_tool = create_retriever_tool(
    retriever,
    name="search_proper_nouns",
    description=description,
)

system = """你是一个与SQL数据库交互的代理。
给定一个输入问题，创建一个语法正确的{dialect}查询来运行，然后查看查询结果并返回答案。
除非用户指定了他们希望获得的具体示例数，否则每次限制查询至多{top_k}个结果。
您可以通过对相关列进行排序，返回数据库中最有趣的示例。
不要查询特定表的所有列，只需根据问题要求的相关列查询。
您可以使用与数据库交互的工具。
只使用给定的工具。只使用工具返回的信息来构建最终答案。
在执行查询之前，必须仔细检查查询。如果在执行查询时出现错误，请重新编写查询并重试。
 
不要对数据库进行任何DML语句（INSERT、UPDATE、DELETE、DROP等）。
 
如果您需要对专有名词进行筛选，则必须始终使用“search_proper_nouns”工具查找筛选值！
 
您可以访问以下表格：{table_names}
 
如果问题似乎与数据库无关，请只返回“我不知道”作为答案。"""
 
prompt = ChatPromptTemplate.from_messages(
    [("system", system), ("human", "{input}"), MessagesPlaceholder("agent_scratchpad")]
)
agent = create_sql_agent(
    llm=llm,
    db=db,
    extra_tools=[retriever_tool],
    prompt=prompt,
    agent_type="openai-tools",
    verbose=True,
)

agent.invoke({"input": "alis in chain有多少张专辑？"})

> 进入新的AgentExecutor链...

调用: `search_proper_nouns`，参数为 `{'query': 'alis in chain'}` 


Alice In Chains

Aisha Duo

Xis

Da Lama Ao Caos

A-Sides
调用: `sql_db_query`，参数为 `SELECT COUNT(*) FROM Album WHERE ArtistId = (SELECT ArtistId FROM Artist WHERE Name = 'Alice In Chains')` 


[(1,)]Alice In Chains有1张专辑。

> 完成链。





{'input': 'alis in chain有多少张专辑？',
 'output': 'Alice In Chains有1张专辑。'}

%pip install --upgrade --quiet langchain langchain-openai

import getpass
import os
 
os.environ["OPENAI_API_KEY"] = getpass.getpass()
 
# 如果想使用LangSmith，请取消下面的注释
# os.environ["LANGCHAIN_TRACING_V2"] = "true"
# os.environ["LANGCHAIN_API_KEY"] = getpass.getpass()

from langchain_core.tools import tool
 
 
@tool
def multiply(first_int: int, second_int: int) -> int:
    """Multiply two integers together."""
    return first_int * second_int
 
 
@tool
def add(first_int: int, second_int: int) -> int:
    "Add two integers."
    return first_int + second_int
 
 
@tool
def exponentiate(base: int, exponent: int) -> int:
    "Exponentiate the base to the exponent power."
    return base**exponent
 
 
tools = [multiply, add, exponentiate]

from langchain import hub
from langchain.agents import AgentExecutor, create_openai_tools_agent
from langchain_openai import ChatOpenAI

# 获取要使用的提示 - 您可以修改此内容！
prompt = hub.pull("hwchase17/openai-tools-agent")
prompt.pretty_print()

# 选择将驱动该Agent的LLM
# 只有某些模型支持此功能
model = ChatOpenAI(model="gpt-3.5-turbo-1106", temperature=0)
 
# 构建OpenAI Tools agent
agent = create_openai_tools_agent(model, tools, prompt)

# 通过传入agent和tools来创建agent executor
agent_executor = AgentExecutor(agent=agent, tools=tools, verbose=True)

agent_executor.invoke(
    {
        "input": "Take 3 to the fifth power and multiply that by the sum of twelve and three, then square the whole result"
    }
)

> 进入新的AgentExecutor链...

调用：`exponentiate`，参数为`{'base': 3, 'exponent': 5}`

243
调用：`add`，参数为`{'first_int': 12, 'second_int': 3}`

15
调用：`multiply`，参数为`{'first_int': 243, 'second_int': 15}`

3645
调用：`exponentiate`，参数为`{'base': 3645, 'exponent': 2}`

13286025将3的五次方乘以十二和三的和，然后平方整个结果的结果是13,286,025。

> 完成链。

{'input': 'Take 3 to the fifth power and multiply that by the sum of twelve and three, then square the whole result',
 'output': 'The result of raising 3 to the fifth power and multiplying that by the sum of twelve and three, then squaring the whole result is 13,286,025.'}