Skip to main content

如何进行查询验证

前置条件

本指南假设您熟悉以下内容：

也许任何SQL链或代理中最具错误倾向的部分是编写有效且安全的SQL查询。
在本指南中，我们将介绍一些用于验证查询和处理无效查询的策略。

准备工作​

首先，获取所需的包并设置环境变量：

:::提示 请参阅安装集成包的一般说明部分。 :::

npm install @langchain/community @langchain/openai typeorm sqlite3
export OPENAI_API_KEY="你的API密钥"
# 取消注释以下内容以使用LangSmith（非必需）
# export LANGSMITH_API_KEY="你的API密钥"
# export LANGSMITH_TRACING=true

# 如果你不在无服务器环境中，可以减少追踪延迟
# export LANGCHAIN_CALLBACKS_BACKGROUND=true

以下示例将使用带有Chinook数据库的SQLite连接。按照这些安装步骤在本笔记本的同一目录中创建Chinook.db：

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

现在，Chinook.db已经在我们的目录中，我们可以使用基于Typeorm的SqlDatabase类与其交互：

import { SqlDatabase } from "langchain/sql_db";
import { DataSource } from "typeorm";

const datasource = new DataSource({
  type: "sqlite",
  database: "../../../../Chinook.db",
});
const db = await SqlDatabase.fromDataSourceParams({
  appDataSource: datasource,
});
console.log(db.allTables.map((t) => t.tableName));
/**
[
  'Album',       'Artist',
  'Customer',    'Employee',
  'Genre',       'Invoice',
  'InvoiceLine', 'MediaType',
  'Playlist',    'PlaylistTrack',
  'Track'
]
 */

API Reference:

查询检查器​

或许最简单的策略是让模型本身检查原始查询中的常见错误。
假设我们有以下SQL查询链：

import { StringOutputParser } from "@langchain/core/output_parsers";
import { ChatPromptTemplate, PromptTemplate } from "@langchain/core/prompts";
import { RunnableSequence } from "@langchain/core/runnables";
import { ChatOpenAI } from "@langchain/openai";
import { createSqlQueryChain } from "langchain/chains/sql_db";
import { SqlDatabase } from "langchain/sql_db";
import { DataSource } from "typeorm";

const datasource = new DataSource({
  type: "sqlite",
  database: "../../../../Chinook.db",
});
const db = await SqlDatabase.fromDataSourceParams({
  appDataSource: datasource,
});

const llm = new ChatOpenAI({ model: "gpt-4", temperature: 0 });
const chain = await createSqlQueryChain({
  llm,
  db,
  dialect: "sqlite",
});

/**
 * And we want to validate its outputs. We can do so by extending the chain with a second prompt and model call:
 */

const SYSTEM_PROMPT = `Double check the user's {dialect} query for common mistakes, including:
- Using NOT IN with NULL values
- Using UNION when UNION ALL should have been used
- Using BETWEEN for exclusive ranges
- Data type mismatch in predicates
- Properly quoting identifiers
- Using the correct number of arguments for functions
- Casting to the correct data type
- Using the proper columns for joins

If there are any of the above mistakes, rewrite the query. If there are no mistakes, just reproduce the original query.

Output the final SQL query only.`;

const prompt = await ChatPromptTemplate.fromMessages([
  ["system", SYSTEM_PROMPT],
  ["human", "{query}"],
]).partial({ dialect: "sqlite" });

const validationChain = prompt.pipe(llm).pipe(new StringOutputParser());

const fullChain = RunnableSequence.from([
  {
    query: async (i: { question: string }) => chain.invoke(i),
  },
  validationChain,
]);
const query = await fullChain.invoke({
  question:
    "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010",
});
console.log("query", query);
/**
query SELECT AVG("Total") FROM "Invoice" WHERE "CustomerId" IN (SELECT "CustomerId" FROM "Customer" WHERE "Country" = 'USA' AND "Fax" IS NULL) AND "InvoiceDate" BETWEEN '2003-01-01 00:00:00' AND '2009-12-31 23:59:59'
 */
console.log("db query results", await db.run(query));
/**
db query results [{"AVG(\"Total\")":6.632999999999998}]
 */

// -------------

// You can see a LangSmith trace of the above chain here:
// https://smith.langchain.com/public/d1131395-8477-47cd-8f74-e0c5491ea956/r

// -------------

// The obvious downside of this approach is that we need to make two model calls instead of one to generate our query.
// To get around this we can try to perform the query generation and query check in a single model invocation:

const SYSTEM_PROMPT_2 = `You are a {dialect} expert. Given an input question, create a syntactically correct {dialect} query to run.
Unless the user specifies in the question a specific number of examples to obtain, query for at most {top_k} results using the LIMIT clause as per {dialect}. You can order the results to return the most informative data in the database.
Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (") to denote them as delimited identifiers.
Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
Pay attention to use date('now') function to get the current date, if the question involves "today".

Only use the following tables:
{table_info}

Write an initial draft of the query. Then double check the {dialect} query for common mistakes, including:
- Using NOT IN with NULL values
- Using UNION when UNION ALL should have been used
- Using BETWEEN for exclusive ranges
- Data type mismatch in predicates
- Properly quoting identifiers
- Using the correct number of arguments for functions
- Casting to the correct data type
- Using the proper columns for joins

Use format:

First draft: <<FIRST_DRAFT_QUERY>>
Final answer: <<FINAL_ANSWER_QUERY>>`;

const prompt2 = await PromptTemplate.fromTemplate(
  `System: ${SYSTEM_PROMPT_2}

Human: {input}`
).partial({ dialect: "sqlite" });

const parseFinalAnswer = (output: string): string =>
  output.split("Final answer: ")[1];

const chain2 = (
  await createSqlQueryChain({
    llm,
    db,
    prompt: prompt2,
    dialect: "sqlite",
  })
).pipe(parseFinalAnswer);

const query2 = await chain2.invoke({
  question:
    "What's the average Invoice from an American customer whose Fax is missing since 2003 but before 2010",
});
console.log("query2", query2);
/**
query2 SELECT AVG("Total") FROM "Invoice" WHERE "CustomerId" IN (SELECT "CustomerId" FROM "Customer" WHERE "Country" = 'USA' AND "Fax" IS NULL) AND date("InvoiceDate") BETWEEN date('2003-01-01') AND date('2009-12-31') LIMIT 5
 */
console.log("db query results", await db.run(query2));

/**
db query results [{"AVG(\"Total\")":6.632999999999998}]
 */

// -------------

// You can see a LangSmith trace of the above chain here:
// https://smith.langchain.com/public/e21d6146-eca9-4de6-a078-808fd09979ea/r

// -------------

API Reference:

下一步​

现在你已经了解了一些用于验证生成的SQL查询的策略。

接下来，请查看本部分中的其他指南，例如如何查询大型数据库。

Was this page helpful?


You can also leave detailed feedback on GitHub.