{"cells":[{"attachments":{},"cell_type":"markdown","id":"fc935871-7640-41c6-b798-58514d860fe0","metadata":{},"source":["## 使用SQL进行LLaMA2聊天\n","\n","开源的本地LLMs非常适合考虑用于需要数据隐私的任何应用程序。\n","\n","SQL是一个很好的例子。\n","\n","本手册展示了如何使用各种本地版本的LLaMA2在本地运行进行文本到SQL的转换。\n","\n","## 软件包"]},{"cell_type":"code","execution_count":null,"id":"81adcf8b-395a-4f02-8749-ac976942b446","metadata":{},"outputs":[],"source":["# 导入所需的库\n","! pip install langchain replicate"]},{"cell_type":"markdown","id":"8e13ed66-300b-4a23-b8ac-44df68ee4733","metadata":{},"source":["## LLM\n","\n","有几种访问 LLaMA2 的方式。\n","\n","要在本地运行，我们使用 Ollama.ai。\n","\n","有关安装和设置的详细信息，请参见[这里](/docs/integrations/chat/ollama)。\n","\n","此外，我们还有关于本地 LLMs 的完整指南，请参见[这里](/docs/guides/development/local_llms)。\n","\n","要使用一个非私有的外部 API，我们可以使用 Replicate。"]},{"cell_type":"code","execution_count":1,"id":"6a75a5c6-34ee-4ab9-a664-d9b432d812ee","metadata":{},"outputs":[{"name":"stderr","output_type":"stream","text":["Init param `input` is deprecated, please use `model_kwargs` instead.\n"]}],"source":["# 本地\n","from langchain_community.chat_models import ChatOllama\n","\n","# 创建一个名为llama2_chat的ChatOllama对象，使用llama2:13b-chat模型\n","llama2_chat = ChatOllama(model=\"llama2:13b-chat\")\n","\n","# 创建一个名为llama2_code的ChatOllama对象，使用codellama:7b-instruct模型\n","llama2_code = ChatOllama(model=\"codellama:7b-instruct\")\n","\n","# API\n","from langchain_community.llms import Replicate\n","\n","# 设置REPLICATE_API_TOKEN\n","# REPLICATE_API_TOKEN = getpass()\n","# os.environ[\"REPLICATE_API_TOKEN\"] = REPLICATE_API_TOKEN\n","\n","# 设置replicate_id\n","replicate_id = \"meta/llama-2-13b-chat:f4e2de70d66816a838a89eeeb621910adffb0dd0baba3976c96980970978018d\"\n","\n","# 创建一个名为llama2_chat_replicate的Replicate对象，使用指定的模型和输入参数\n","llama2_chat_replicate = Replicate(\n","    model=replicate_id, input={\"temperature\": 0.01, \"max_length\": 500, \"top_p\": 1}\n",")"]},{"cell_type":"code","execution_count":2,"id":"ce96f7ea-b3d5-44e1-9fa5-a79e04a9e1fb","metadata":{},"outputs":[],"source":["# 简单地设置我们想要使用的LLM\n","llm = llama2_chat"]},{"cell_type":"markdown","id":"80222165-f353-4e35-a123-5f70fd70c6c8","metadata":{},"source":["## 数据库\n","\n","连接到一个SQLite数据库。\n","\n","要创建这个特定的数据库，您可以使用以下代码并按照[这里](https://github.com/facebookresearch/llama-recipes/blob/main/demo_apps/StructuredLlama.ipynb)显示的步骤进行操作。"]},{"cell_type":"code","execution_count":3,"id":"025bdd82-3bb1-4948-bc7c-c3ccd94fd05c","metadata":{},"outputs":[],"source":["from langchain_community.utilities import SQLDatabase\n","\n","db = SQLDatabase.from_uri(\"sqlite:///nba_roster.db\", sample_rows_in_table_info=0)\n","# 创建一个SQLDatabase对象，连接到名为\"nba_roster.db\"的SQLite数据库，设置sample_rows_in_table_info参数为0\n","\n","def get_schema(_):\n","    return db.get_table_info()\n","# 定义一个名为get_schema的函数，接受一个参数_，返回db.get_table_info()的结果\n","\n","def run_query(query):\n","    return db.run(query)\n","# 定义一个名为run_query的函数，接受一个参数query，返回db.run(query)的结果"]},{"cell_type":"markdown","id":"654b3577-baa2-4e12-a393-f40e5db49ac7","metadata":{},"source":["## 查询 SQL 数据库\n","\n","请按照此处的可运行工作流程[链接](https://python.langchain.com/docs/expression_language/cookbook/sql_db)进行操作。"]},{"cell_type":"code","execution_count":4,"id":"5a4933ea-d9c0-4b0a-8177-ba4490c6532b","metadata":{},"outputs":[{"data":{"text/plain":["' SELECT \"Team\" FROM nba_roster WHERE \"NAME\" = \\'Klay Thompson\\';'"]},"execution_count":4,"metadata":{},"output_type":"execute_result"}],"source":["# 提示\n","from langchain_core.prompts import ChatPromptTemplate\n","\n","# 根据 SQL 数据库的类型（如 MySQL、Microsoft SQL Server 等）更新模板\n","template = \"\"\"根据下面的表结构，编写一个 SQL 查询来回答用户的问题：\n","{schema}\n","\n","问题：{question}\n","SQL 查询：\"\"\"\n","prompt = ChatPromptTemplate.from_messages(\n","    [\n","        (\"system\", \"给定一个输入问题，将其转换为 SQL 查询。不需要前言。\"),\n","        (\"human\", template),\n","    ]\n",")\n","\n","# 链接到查询\n","from langchain_core.output_parsers import StrOutputParser\n","from langchain_core.runnables import RunnablePassthrough\n","\n","sql_response = (\n","    RunnablePassthrough.assign(schema=get_schema)  # 从 get_schema 函数中获取表结构\n","    | prompt\n","    | llm.bind(stop=[\"\\nSQLResult:\"])  # 绑定到 llm，停止标记为 \"\\nSQLResult:\"\n","    | StrOutputParser()  # 使用 StrOutputParser 解析输出\n",")\n","\n","sql_response.invoke({\"question\": \"Klay Thompson 在哪个球队？\"})  # 调用 sql_response，传入问题参数"]},{"cell_type":"markdown","id":"a0e9e2c8-9b88-4853-ac86-001bc6cc6695","metadata":{},"source":["我们可以查看结果：\n","\n","* [LangSmith trace](https://smith.langchain.com/public/afa56a06-b4e2-469a-a60f-c1746e75e42b/r) LLaMA2-13 复制 API\n","* [LangSmith trace](https://smith.langchain.com/public/2d4ecc72-6b8f-4523-8f0b-ea95c6b54a1d/r) LLaMA2-13 本地"]},{"cell_type":"code","execution_count":15,"id":"2a2825e3-c1b6-4f7d-b9c9-d9835de323bb","metadata":{},"outputs":[{"data":{"text/plain":["AIMessage(content=' Based on the table schema and SQL query, there are 30 unique teams in the NBA.')"]},"execution_count":15,"metadata":{},"output_type":"execute_result"}],"source":["# 链式回答\n","template = \"\"\"根据下面的表模式、问题、SQL查询和SQL响应，编写一个自然语言回答：\n","{schema}\n","\n","问题: {question}\n","SQL查询: {query}\n","SQL响应: {response}\"\"\"\n","prompt_response = ChatPromptTemplate.from_messages(\n","    [\n","        (\n","            \"system\",\n","            \"给定一个输入问题和SQL响应，将其转换为自然语言回答。没有前言。\",\n","        ),\n","        (\"human\", template),\n","    ]\n",")\n","\n","full_chain = (\n","    RunnablePassthrough.assign(query=sql_response)  # 将sql_response赋值给query\n","    | RunnablePassthrough.assign(\n","        schema=get_schema,  # 获取表模式\n","        response=lambda x: db.run(x[\"query\"]),  # 运行SQL查询并将结果赋值给response\n","    )\n","    | prompt_response  # 使用prompt_response生成自然语言回答\n","    | llm  # 使用llm模型生成最终回答\n",")\n","\n","full_chain.invoke({\"question\": \"有多少个唯一的团队？\"})  # 调用full_chain并传入问题参数"]},{"cell_type":"markdown","id":"ec17b3ee-6618-4681-b6df-089bbb5ffcd7","metadata":{},"source":["我们可以查看结果：\n","\n","* [LangSmith跟踪](https://smith.langchain.com/public/10420721-746a-4806-8ecf-d6dc6399d739/r) LLaMA2-13复制API\n","* [LangSmith跟踪](https://smith.langchain.com/public/5265ebab-0a22-4f37-936b-3300f2dfa1c1/r) LLaMA2-13本地"]},{"cell_type":"markdown","id":"1e85381b-1edc-4bb3-a7bd-2ab23f81e54d","metadata":{},"source":["## 与 SQL 数据库交谈\n","\n","接下来，我们可以添加内存。"]},{"cell_type":"code","execution_count":7,"id":"022868f2-128e-42f5-8d90-d3bb2f11d994","metadata":{},"outputs":[{"data":{"text/plain":["' SELECT \"Team\" FROM nba_roster WHERE \"NAME\" = \\'Klay Thompson\\';'"]},"execution_count":7,"metadata":{},"output_type":"execute_result"}],"source":["# 导入必要的模块\n","from langchain.memory import ConversationBufferMemory\n","from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder\n","\n","# 定义模板字符串，用于生成对话提示\n","template = \"\"\"给定一个输入问题，将其转换为 SQL 查询。不需要序文。基于下面的表结构，编写一个 SQL 查询，以回答用户的问题：\n","{schema}\n","\"\"\"\n","# 创建对话提示模板\n","prompt = ChatPromptTemplate.from_messages(\n","    [\n","        (\"system\", template),\n","        MessagesPlaceholder(variable_name=\"history\"),\n","        (\"human\", \"{question}\"),\n","    ]\n",")\n","\n","# 创建对话缓存内存\n","memory = ConversationBufferMemory(return_messages=True)\n","\n","# 链式调用，将查询与内存连接起来\n","from langchain_core.runnables import RunnableLambda\n","\n","sql_chain = (\n","    RunnablePassthrough.assign(\n","        schema=get_schema,\n","        history=RunnableLambda(lambda x: memory.load_memory_variables(x)[\"history\"]),\n","    )\n","    | prompt\n","    | llm.bind(stop=[\"\\nSQLResult:\"])\n","    | StrOutputParser()\n",")\n","\n","# 定义保存函数\n","def save(input_output):\n","    output = {\"output\": input_output.pop(\"output\")}\n","    memory.save_context(input_output, output)\n","    return output[\"output\"]\n","\n","# 执行 SQL 查询并保存结果到内存\n","sql_response_memory = RunnablePassthrough.assign(output=sql_chain) | save\n","sql_response_memory.invoke({\"question\": \"What team is Klay Thompson on?\"})"]},{"cell_type":"code","execution_count":21,"id":"800a7a3b-f411-478b-af51-2310cd6e0425","metadata":{},"outputs":[{"data":{"text/plain":["AIMessage(content=' Sure! Here\\'s the natural language response based on the given input:\\n\\n\"Klay Thompson\\'s salary is $43,219,440.\"')"]},"execution_count":21,"metadata":{},"output_type":"execute_result"}],"source":["# Chain to answer\n","# 定义模板字符串，包含表格模式、问题、SQL查询和SQL响应，用于生成自然语言响应\n","template = \"\"\"Based on the table schema below, question, sql query, and sql response, write a natural language response:\n","{schema}\n","\n","Question: {question}\n","SQL Query: {query}\n","SQL Response: {response}\"\"\"\n","# 从消息中创建聊天提示模板\n","prompt_response = ChatPromptTemplate.from_messages(\n","    [\n","        (\n","            \"system\",\n","            \"Given an input question and SQL response, convert it to a natural language answer. No pre-amble.\",\n","        ),\n","        (\"human\", template),\n","    ]\n",")\n","\n","# 创建完整的处理链\n","full_chain = (\n","    RunnablePassthrough.assign(query=sql_response_memory)  # 将SQL响应分配给query\n","    | RunnablePassthrough.assign(  # 分配schema和response\n","        schema=get_schema,  # 获取表格模式\n","        response=lambda x: db.run(x[\"query\"]),  # 运行SQL查询并获取响应\n","    )\n","    | prompt_response  # 使用聊天提示模板生成自然语言响应\n","    | llm  # 使用语言模型生成最终的自然语言响应\n",")\n","\n","# 调用完整的处理链并传入问题\n","full_chain.invoke({\"question\": \"What is his salary?\"})"]},{"cell_type":"markdown","id":"b77fee61-f4da-4bb1-8285-14101e505518","metadata":{},"source":["这是[追踪链接](https://smith.langchain.com/public/54794d18-2337-4ce2-8b9f-3d8a2df89e51/r)。"]}],"metadata":{"kernelspec":{"display_name":"Python 3 (ipykernel)","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.9.16"}},"nbformat":4,"nbformat_minor":5}