Langchain SQLDatabaseToolkit无法访问MS SQL视图数据求助

问题描述 #

使用LangChain 0.2.5版本的SQLDatabaseToolkit与create_sql_agent连接MS SQL数据库时，访问预定义VIEW遇到以下问题：

• 查询所有视图时，Agent会优先调用无法识别视图的sql_db_list_tables工具，经历错误后才执行正确的SQL查询获取视图列表。
• 尝试查询指定视图（如view_1）数据时，Agent反复判定数据库无视图，最终返回错误结果。

原实现代码 #

from langchain_aws import ChatBedrock
from langchain.agents.agent_toolkits import SQLDatabaseToolkit
from langchain.sql_database import SQLDatabase
from langchain.agents import create_sql_agent
from langchain.agents import AgentExecutor

import boto3

# 初始化Bedrock客户端
BEDROCK_CLIENT = boto3.client("bedrock-runtime", 'us-west-2')
llm = ChatBedrock(model_id="anthropic.claude-3-sonnet-20240229-v1:0", model_kwargs={"temperature": 0.1}, client=BEDROCK_CLIENT)

# 数据库连接配置
db_user = 'DaShi'
db_password = 'sophon123'
db_host = "db.database.windows.net"
db_name = 'Trisolaris'

# 使用pymssql建立连接
connection_string = f"mssql+pymssql://{db_user}:{db_password}@{db_host}/{db_name}"
db = SQLDatabase.from_uri(
    connection_string,     
    view_support=True)

# Prompt前缀模板
prefix_template = """You are an agent designed to interact with a SQL database.

DO NOT look at how the schemas are arranged.

You do not care about the database schema.

You will FIRST ALWAYS use SELECT table_name FROM information_schema.tables WHERE table_type = 'VIEW' 

DO NOT use sql_db_list_tables AS it does not show 'VIEWS'

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 do not know" as the answer.
"""

# 格式说明模板
format_instructions_template = """Use the following format:

Question: the input question you must answer
Thought: you should always think about what to do
Action: the action to take, should be one of [{tool_names}]
Action Input: the input to the action
Observation: the result of the action
... (this Thought/Action/Action Input/Observation can repeat N times)
Thought: I now know the final answer
Final Answer: the final answer to the original input question"""

# 初始化Toolkit和Agent执行器
toolkit = SQLDatabaseToolkit(llm = llm, db=db)
agent_executor = create_sql_agent(
    llm = llm,
    toolkit = toolkit,
    prefix = prefix_template,
    format_instructions = format_instructions_template,
    verbose = True,
    handle_parsing_errors=True,
    agent_executor_kwargs = {"return_intermediate_steps": True}
)

错误场景示例 #

当查询指定视图数据时，Agent执行日志如下：

Executor Chain:
Thought: To get data from a view, I first need to know the available views in the database.

Action: sql_db_list_tables
Action Input: readings, jobs, people, tags, units
Thought: The list of tables does not include any views. I should double check if there are actually views in this database.

Action: sql_db_query
Action Input: SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
Error: (pymssql.exceptions.OperationalError) (156, b"Incorrect syntax near the keyword 'FULL'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
[SQL: SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';]
Thought: The query to list views in a SQL Server database is different than the one I tried. Let me check the proper syntax.

Action: sql_db_schema
Action Input: INFORMATION_SCHEMA.VIEWS
Error: table_names {'INFORMATION_SCHEMA.VIEWS'} not found in database
Thought: It seems this database does not contain any views. Without views to query, I cannot retrieve data from a view named "view_1".

Final Answer: There are no views in this database, so it is not possible to retrieve data from a view named "view_1".

解决方案 #

1. 自定义工具替换默认列表工具 #

默认的sql_db_list_tables工具无法返回视图，因此自定义工具返回所有表和视图：

from langchain.tools import tool

@tool
def list_all_tables_and_views() -> str:
    """返回数据库中所有表和视图的列表，包含类型标识"""
    query = f"SELECT table_name, table_type FROM information_schema.tables WHERE table_catalog = '{db_name}'"
    result = db.run(query)
    return f"数据库对象列表:\n{result}"

# 替换Toolkit中的工具
tools = toolkit.get_tools()
# 移除原有的sql_db_list_tables
tools = [t for t in tools if t.name != "sql_db_list_tables"]
# 添加自定义工具
tools.append(list_all_tables_and_views)

# 创建Agent时使用自定义工具列表
agent_executor = create_sql_agent(
    llm=llm,
    tools=tools,
    prefix=prefix_template,
    format_instructions=format_instructions_template,
    verbose=True,
    handle_parsing_errors=True,
    agent_executor_kwargs={"return_intermediate_steps": True}
)

2. 强化Prompt约束，明确视图处理逻辑 #

修改Prompt前缀，强制Agent使用正确的视图查询逻辑：

prefix_template = """你是专门处理MS SQL数据库的Agent，需严格遵循以下规则：
1. 如需获取视图列表，**必须直接执行SQL**: SELECT table_name FROM information_schema.tables WHERE table_type = 'VIEW'
2. 查询视图数据时，直接将视图当作表使用，无需额外验证存在性
3. 禁止使用`sql_db_list_tables`工具，它无法返回视图
4. 禁止执行任何DML语句（INSERT、UPDATE、DELETE、DROP等）
5. 若问题与数据库无关，直接返回"I do not know"
"""

3. 让SQLDatabase识别视图为表 #

初始化SQLDatabase时，指定包含所有视图，使默认工具能识别视图：

# 先获取所有视图名称
views_result = db.run("SELECT table_name FROM information_schema.tables WHERE table_type = 'VIEW'")
# 解析结果为视图名称列表（注意根据实际返回格式调整解析逻辑）
view_names = [row[0] for row in eval(views_result)]

# 重新初始化数据库，包含所有视图
db = SQLDatabase.from_uri(
    connection_string,
    include_tables=view_names,
    view_support=True
)

# 重新创建Toolkit和Agent
toolkit = SQLDatabaseToolkit(llm=llm, db=db)
agent_executor = create_sql_agent(
    llm=llm,
    toolkit=toolkit,
    prefix=prefix_template,
    format_instructions=format_instructions_template,
    verbose=True,
    handle_parsing_errors=True,
    agent_executor_kwargs={"return_intermediate_steps": True}
)

4. 简化Agent逻辑，直接引导使用查询工具 #

对于明确的视图查询需求，在Prompt中引导Agent直接执行查询：
在prefix模板中添加：

当用户要求查询某个视图的数据时，直接执行SELECT * FROM [视图名]，无需额外步骤。

内容的提问来源于stack exchange，提问作者PythonNoob