跳至主要内容

SQL 数据库工具包 SQLDatabase Toolkit

这将帮助您开始使用 SQL 数据库工具包。有关所有 SQLDatabaseToolkit 功能和配置的详细文档，请转到API 参考。 This will help you getting started with the SQL Database toolkit. For detailed documentation of all SQLDatabaseToolkit features and configurations head to the API reference.

SQLDatabaseToolkit 中的工具旨在与 SQL 数据库交互。 Tools within the SQLDatabaseToolkit are designed to interact with a SQL database.

一个常见的应用是使代理能够使用关系数据库中的数据来回答问题，可能以迭代的方式（例如，从错误中恢复）。 A common application is to enable agents to answer questions using data in a relational database, potentially in an iterative fashion (e.g., recovering from errors).

⚠️ 安全提示 ⚠️

构建 SQL 数据库的问答系统需要执行模型生成的 SQL 查询。这样做存在固有风险。确保您的数据库连接权限始终尽可能狭窄地限定在链/代理的需求范围内。这将减轻（但不会消除）构建模型驱动系统的风险。有关一般安全最佳实践的更多信息，请参阅此处。 Building Q&A systems of SQL databases requires executing model-generated SQL queries. There are inherent risks in doing this. Make sure that your database connection permissions are always scoped as narrowly as possible for your chain/agent's needs. This will mitigate though not eliminate the risks of building a model-driven system. For more on general security best practices, see here.

设置​

如果您希望从单个工具的运行中获得自动跟踪，您还可以通过取消以下注释来设置您的LangSmith API 密钥 If you want to get automated tracing from runs of individual tools, you can also set your LangSmith API key by uncommenting below

# os.environ["LANGSMITH_API_KEY"] = getpass.getpass("Enter your LangSmith API key: ")
# os.environ["LANGSMITH_TRACING"] = "true"

安装​

此工具包位于 langchain-community 包中 This toolkit lives in the langchain-community package

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

出于演示目的，我们将访问 LangChain Hub 中的提示。我们还需要 langgraph 来演示如何将工具包与代理一起使用。这对于使用工具包不是必需的。 For demonstration purposes, we will access a prompt in the LangChain Hub. We will also require langgraph to demonstrate the use of the toolkit with an agent. This is not required to use the toolkit.

%pip install --upgrade --quiet langchainhub langgraph

实例化​

SQLDatabaseToolkit 工具包需要 The SQLDatabaseToolkit toolkit requires

下面，我们使用这些对象实例化工具包。让我们首先创建一个数据库对象。 Below, we instantiate the toolkit with these objects. Let's first create a database object.

本指南使用基于这些说明的示例 Chinook 数据库。 This guide uses the example Chinook database based on these instructions.

下面我们将使用 requests 库来提取 .sql 文件并创建一个内存中的 SQLite 数据库。请注意，这种方法很轻量级，但它是短暂的且非线程安全的。如果您愿意，您可以按照说明将文件本地保存为 Chinook.db 并通过 db = SQLDatabase.from_uri("sqlite:///Chinook.db") 实例化数据库。 Below we will use the requests library to pull the .sql file and create an in-memory SQLite database. Note that this approach is lightweight, but ephemeral and not thread-safe. If you'd prefer, you can follow the instructions to save the file locally as Chinook.db and instantiate the database via db = SQLDatabase.from_uri("sqlite:///Chinook.db").

import sqlite3

import requests
from langchain_community.utilities.sql_database import SQLDatabase
from sqlalchemy import create_engine
from sqlalchemy.pool import StaticPool


def get_engine_for_chinook_db():
    """Pull sql file, populate in-memory database, and create engine."""
    url = "https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sql"
    response = requests.get(url)
    sql_script = response.text

    connection = sqlite3.connect(":memory:", check_same_thread=False)
    connection.executescript(sql_script)
    return create_engine(
        "sqlite://",
        creator=lambda: connection,
        poolclass=StaticPool,
        connect_args={"check_same_thread": False},
    )


engine = get_engine_for_chinook_db()

db = SQLDatabase(engine)
API 参考：SQLDatabase

我们还需要一个 LLM 或聊天模型 We will also need a LLM or chat model

pip install -qU langchain-openai
import getpass
import os

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

from langchain_openai import ChatOpenAI

llm = ChatOpenAI(model="gpt-4o-mini")

现在我们可以实例化工具包了 We can now instantiate the toolkit

from langchain_community.agent_toolkits.sql.toolkit import SQLDatabaseToolkit

toolkit = SQLDatabaseToolkit(db=db, llm=llm)
API 参考：SQLDatabaseToolkit

工具​

查看可用工具 View available tools

toolkit.get_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 0x105e02860>),
 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 0x105e02860>),
 ListSQLDatabaseTool(db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x105e02860>),
 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 0x105e02860>, llm=ChatOpenAI(client=<openai.resources.chat.completions.Completions object at 0x1148a97b0>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x1148aaec0>, temperature=0.0, openai_api_key=SecretStr('**********'), openai_proxy=''), llm_chain=LLMChain(prompt=PromptTemplate(input_variables=['dialect', 'query'], 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 0x1148a97b0>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x1148aaec0>, temperature=0.0, openai_api_key=SecretStr('**********'), openai_proxy='')))]

API 参考 API references

在代理中使用​

按照SQL 问答教程，下面我们将为一个简单的问答代理配备工具包中的工具。首先，我们提取一个相关的提示并使用其所需的参数填充它 Following the SQL Q&A Tutorial, below we equip a simple question-answering agent with the tools in our toolkit. First we pull a relevant prompt and populate it with its required parameters

from langchain import hub

prompt_template = hub.pull("langchain-ai/sql-agent-system-prompt")

assert len(prompt_template.messages) == 1
print(prompt_template.input_variables)
['dialect', 'top_k']
system_message = prompt_template.format(dialect="SQLite", top_k=5)

然后我们实例化代理 We then instantiate the agent

from langgraph.prebuilt import create_react_agent

agent_executor = create_react_agent(
    llm, toolkit.get_tools(), state_modifier=system_message
)

并发出查询 And issue it a query

example_query = "Which country's customers spent the most?"

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

Which country's customers spent the most?
================================== Ai Message ==================================
Tool Calls:
  sql_db_list_tables (call_eiheSxiL0s90KE50XyBnBtJY)
 Call ID: call_eiheSxiL0s90KE50XyBnBtJY
  Args:
================================= Tool Message =================================
Name: sql_db_list_tables

Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
================================== Ai Message ==================================
Tool Calls:
  sql_db_schema (call_YKwGWt4UUVmxxY7vjjBDzFLJ)
 Call ID: call_YKwGWt4UUVmxxY7vjjBDzFLJ
  Args:
    table_names: Customer, Invoice, InvoiceLine
================================= Tool Message =================================
Name: sql_db_schema


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   2021-01-01 00:00:00 Theodor-Heuss-Straße 34 Stuttgart   None    Germany 70174   1.98
2   4   2021-01-02 00:00:00 Ullevålsveien 14    Oslo    None    Norway  0171    3.96
3   8   2021-01-03 00:00:00 Grétrystraat 63 Brussels    None    Belgium 1000    5.94
*/


CREATE TABLE "InvoiceLine" (
    "InvoiceLineId" INTEGER NOT NULL, 
    "InvoiceId" INTEGER NOT NULL, 
    "TrackId" INTEGER NOT NULL, 
    "UnitPrice" NUMERIC(10, 2) NOT NULL, 
    "Quantity" INTEGER NOT NULL, 
    PRIMARY KEY ("InvoiceLineId"), 
    FOREIGN KEY("TrackId") REFERENCES "Track" ("TrackId"), 
    FOREIGN KEY("InvoiceId") REFERENCES "Invoice" ("InvoiceId")
)

/*
3 rows from InvoiceLine table:
InvoiceLineId   InvoiceId   TrackId UnitPrice   Quantity
1   1   2   0.99    1
2   1   4   0.99    1
3   2   6   0.99    1
*/
================================== Ai Message ==================================
Tool Calls:
  sql_db_query (call_7WBDcMxl1h7MnI05njx1q8V9)
 Call ID: call_7WBDcMxl1h7MnI05njx1q8V9
  Args:
    query: SELECT c.Country, SUM(i.Total) AS TotalSpent FROM Customer c JOIN Invoice i ON c.CustomerId = i.CustomerId GROUP BY c.Country ORDER BY TotalSpent DESC LIMIT 1
================================= Tool Message =================================
Name: sql_db_query

[('USA', 523.0600000000003)]
================================== Ai Message ==================================

Customers from the USA spent the most, with a total amount spent of $523.06.

我们还可以观察代理从错误中恢复 We can also observe the agent recover from an error

example_query = "Who are the top 3 best selling artists?"

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

Who are the top 3 best selling artists?
================================== Ai Message ==================================
Tool Calls:
  sql_db_query (call_9F6Bp2vwsDkeLW6FsJFqLiet)
 Call ID: call_9F6Bp2vwsDkeLW6FsJFqLiet
  Args:
    query: SELECT artist_name, SUM(quantity) AS total_sold FROM sales GROUP BY artist_name ORDER BY total_sold DESC LIMIT 3
================================= Tool Message =================================
Name: sql_db_query

Error: (sqlite3.OperationalError) no such table: sales
[SQL: SELECT artist_name, SUM(quantity) AS total_sold FROM sales GROUP BY artist_name ORDER BY total_sold DESC LIMIT 3]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
================================== Ai Message ==================================
Tool Calls:
  sql_db_list_tables (call_Gx5adzWnrBDIIxzUDzsn83zO)
 Call ID: call_Gx5adzWnrBDIIxzUDzsn83zO
  Args:
================================= Tool Message =================================
Name: sql_db_list_tables

Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
================================== Ai Message ==================================
Tool Calls:
  sql_db_schema (call_ftywrZgEgGWLrnk9dYC0xtZv)
 Call ID: call_ftywrZgEgGWLrnk9dYC0xtZv
  Args:
    table_names: Artist, Album, InvoiceLine
================================= Tool Message =================================
Name: sql_db_schema


CREATE TABLE "Album" (
    "AlbumId" INTEGER NOT NULL, 
    "Title" NVARCHAR(160) NOT NULL, 
    "ArtistId" INTEGER NOT NULL, 
    PRIMARY KEY ("AlbumId"), 
    FOREIGN KEY("ArtistId") REFERENCES "Artist" ("ArtistId")
)

/*
3 rows from Album table:
AlbumId Title   ArtistId
1   For Those About To Rock We Salute You   1
2   Balls to the Wall   2
3   Restless and Wild   2
*/


CREATE TABLE "Artist" (
    "ArtistId" INTEGER NOT NULL, 
    "Name" NVARCHAR(120), 
    PRIMARY KEY ("ArtistId")
)

/*
3 rows from Artist table:
ArtistId    Name
1   AC/DC
2   Accept
3   Aerosmith
*/


CREATE TABLE "InvoiceLine" (
    "InvoiceLineId" INTEGER NOT NULL, 
    "InvoiceId" INTEGER NOT NULL, 
    "TrackId" INTEGER NOT NULL, 
    "UnitPrice" NUMERIC(10, 2) NOT NULL, 
    "Quantity" INTEGER NOT NULL, 
    PRIMARY KEY ("InvoiceLineId"), 
    FOREIGN KEY("TrackId") REFERENCES "Track" ("TrackId"), 
    FOREIGN KEY("InvoiceId") REFERENCES "Invoice" ("InvoiceId")
)

/*
3 rows from InvoiceLine table:
InvoiceLineId   InvoiceId   TrackId UnitPrice   Quantity
1   1   2   0.99    1
2   1   4   0.99    1
3   2   6   0.99    1
*/
================================== Ai Message ==================================
Tool Calls:
  sql_db_query (call_i6n3lmS7E2ZivN758VOayTiy)
 Call ID: call_i6n3lmS7E2ZivN758VOayTiy
  Args:
    query: SELECT Artist.Name AS artist_name, SUM(InvoiceLine.Quantity) AS total_sold FROM Artist JOIN Album ON Artist.ArtistId = Album.ArtistId JOIN Track ON Album.AlbumId = Track.AlbumId JOIN InvoiceLine ON Track.TrackId = InvoiceLine.TrackId GROUP BY Artist.Name ORDER BY total_sold DESC LIMIT 3
================================= Tool Message =================================
Name: sql_db_query

[('Iron Maiden', 140), ('U2', 107), ('Metallica', 91)]
================================== Ai Message ==================================

The top 3 best selling artists are:
1. Iron Maiden - 140 units sold
2. U2 - 107 units sold
3. Metallica - 91 units sold

特定功能​

SQLDatabaseToolkit 实现了一个.get_context 方法，方便在提示或其他上下文中使用。 SQLDatabaseToolkit implements a .get_context method as a convenience for use in prompts or other contexts.

⚠️ 免责声明 ⚠️：代理可能会生成插入/更新/删除查询。当这并非预期时，请使用自定义提示或创建没有写入权限的 SQL 用户。 ⚠️ Disclaimer ⚠️ : The agent may generate insert/update/delete queries. When this is not expected, use a custom prompt or create a SQL users without write permissions.

最终用户可能会通过询问诸如“运行尽可能大的查询”之类的简单问题来过载您的 SQL 数据库。生成的查询可能如下所示 The final user might overload your SQL database by asking a simple question such as "run the biggest query possible". The generated query might look like

SELECT * FROM "public"."users"
    JOIN "public"."user_permissions" ON "public"."users".id = "public"."user_permissions".user_id
    JOIN "public"."projects" ON "public"."users".id = "public"."projects".user_id
    JOIN "public"."events" ON "public"."projects".id = "public"."events".project_id;

对于事务性 SQL 数据库，如果上述其中一个表包含数百万行，则该查询可能会给使用相同数据库的其他应用程序带来麻烦。 For a transactional SQL database, if one of the table above contains millions of rows, the query might cause trouble to other applications using the same database.

大多数面向数据仓库的数据库支持用户级配额，以限制资源使用。 Most datawarehouse oriented databases support user-level quota, for limiting resource usage.

API 参考​

有关所有 SQLDatabaseToolkit 功能和配置的详细文档，请转到API 参考。 For detailed documentation of all SQLDatabaseToolkit features and configurations head to the API reference.

此页面是否有帮助？ Was this page helpful?


您还可以留下详细的反馈 You can also leave detailed feedback 在 GitHub 上 on GitHub.