Chain链之数据库查询链.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "997a05a1-171b-47a8-932e-d06a6615aabb",
   "metadata": {},
   "source": [
    "# 介绍langchain的数据库查询链"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4807d613-8fd7-4cbd-a203-d685642ea2a3",
   "metadata": {},
   "source": [
    "## langchain推荐的LCEL方式 create_sql_query_chain"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ae21ebf-bda3-484f-ad24-5e83c9665331",
   "metadata": {},
   "source": [
    "### 建立一个本地的mysql数据库，建立数据库连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "081ceecb-47a7-437a-a5f2-1c8a999a02c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mysql\n",
      "['user']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"[(1, 'John Doe', 'admin', 'p@ssword1'), (2, 'Alice Smith', 'manager', 'secure2'), (3, 'Bob Johnson', 'employee', 'pass123'), (4, 'Eva Davis', 'admin', 'adminPass'), (5, 'Charlie Brown', 'manager', '12345678'), (6, 'Grace White', 'employee', 'pwd987'), (7, 'Daniel Lee', 'admin', 'danny12'), (8, 'Olivia Moore', 'manager', 'pass432'), (9, 'Frank Miller', 'employee', 'fMiller'), (10, 'Sophia Taylor', 'admin', 'sophia7')]\""
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from langchain_community.utilities import SQLDatabase\n",
    "\n",
    "db_user = \"root\"\n",
    "db_password = \"161212\"\n",
    "db_host = \"127.0.0.1\"\n",
    "db_name = \"langchain\"\n",
    "\n",
    "#注意要安装pymysql这个库\n",
    "\n",
    "db = SQLDatabase.from_uri(f\"mysql+pymysql://{db_user}:{db_password}@{db_host}/{db_name}\")\n",
    "\n",
    "print(db.dialect)\n",
    "print(db.get_usable_table_names())\n",
    "db.run(\"SELECT * FROM user;\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c6827c5e-1712-417b-a6ab-c7dcfe5483e3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pymysqlNote: you may need to restart the kernel to use updated packages.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "[notice] A new release of pip available: 22.2.2 -> 23.3.2\n",
      "[notice] To update, run: python.exe -m pip install --upgrade pip\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "  Downloading PyMySQL-1.1.0-py3-none-any.whl (44 kB)\n",
      "     -------------------------------------- 44.8/44.8 kB 548.1 kB/s eta 0:00:00\n",
      "Installing collected packages: pymysql\n",
      "Successfully installed pymysql-1.1.0\n"
     ]
    }
   ],
   "source": [
    "pip install pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6f6a642c-9671-449e-a53b-d43583cdc1a2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SELECT COUNT(*) FROM `user`'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "from langchain.chains import create_sql_query_chain\n",
    "from langchain_openai import ChatOpenAI\n",
    "\n",
    "llm = ChatOpenAI(\n",
    "    temperature=0,\n",
    "    openai_api_key = os.getenv(\"OPENAI_API_KEY\"),\n",
    "    base_url = os.getenv(\"OPENAI_BASE_URL\")\n",
    ")\n",
    "\n",
    "chain = create_sql_query_chain(llm, db)\n",
    "response = chain.invoke({\"question\": \"How many people are there in the user table?\"})\n",
    "response"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11e66315-3d13-44d8-b560-a155d274dab3",
   "metadata": {},
   "source": [
    "## 用 get_prompts()方法来查看langchain内置的prompt样式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f9f08d14-aa22-4b26-890c-28e3ab8544c1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "You are a MySQL expert. Given an input question, first create a syntactically correct MySQL query to run, then look at the results of the query and return the answer to the input question.\n",
      "Unless the user specifies in the question a specific number of examples to obtain, query for at most \u001b[33;1m\u001b[1;3m{top_k}\u001b[0m results using the LIMIT clause as per MySQL. You can order the results to return the most informative data in the database.\n",
      "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 backticks (`) to denote them as delimited identifiers.\n",
      "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.\n",
      "Pay attention to use CURDATE() function to get the current date, if the question involves \"today\".\n",
      "\n",
      "Use the following format:\n",
      "\n",
      "Question: Question here\n",
      "SQLQuery: SQL Query to run\n",
      "SQLResult: Result of the SQLQuery\n",
      "Answer: Final answer here\n",
      "\n",
      "Only use the following tables:\n",
      "\u001b[33;1m\u001b[1;3m{table_info}\u001b[0m\n",
      "\n",
      "Question: \u001b[33;1m\u001b[1;3m{input}\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "chain.get_prompts()[0].pretty_print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cf5739dc-05a2-4775-adc6-fcb38d384b4a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SELECT COUNT(*) FROM `user`'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = chain.invoke({\"question\": \"user表里有多少用户?\"})\n",
    "response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3253b1fc-e348-4f57-af0a-1ed05d95e492",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"SELECT COUNT(*) FROM `user` WHERE `role` = 'admin'\""
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response = chain.invoke({\"question\": \"user表里有多少个admin用户?\"})\n",
    "response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2e17a83a-8c25-484f-8d57-fa72f3904ff0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[(4,)]'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool\n",
    "\n",
    "execute_query = QuerySQLDataBaseTool(db=db)\n",
    "write_query = create_sql_query_chain(llm, db)\n",
    "chain = write_query | execute_query\n",
    "chain.invoke({\"question\": \"user表里有多少个admin用户?\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d78abc8-cde5-4b25-a38d-c83a62d097d1",
   "metadata": {},
   "source": [
    "## 构造一个回答问题的链answer，这个链的输入有question ,  SQL语句query， SQL执行结果 result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "283320cc-8f70-41c3-980a-2074b0b415c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "from operator import itemgetter\n",
    "\n",
    "from langchain_core.output_parsers import StrOutputParser\n",
    "from langchain_core.prompts import PromptTemplate\n",
    "from langchain_core.runnables import RunnablePassthrough\n",
    "\n",
    "answer_prompt = PromptTemplate.from_template(\n",
    "\"\"\"Given the following user question, corresponding SQL query, and SQL result, answer the user question. 用中文回答最终答案\n",
    "\n",
    "Question: {question}\n",
    "SQL Query: {query}\n",
    "SQL Result: {result}\n",
    "Answer: \"\"\"\n",
    ")\n",
    "\n",
    "\n",
    "answer = answer_prompt | llm | StrOutputParser()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "045a772b-250b-4020-a27f-c24dac1511ef",
   "metadata": {},
   "source": [
    "## 构造一个 生成SQL query、执行SQL query 并将结果传递给 answer的最终链 final_chain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "00096c64-3ce4-47bf-ab87-98485969acba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'user表里有4个admin用户。'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_chain = (\n",
    "    RunnablePassthrough.assign(query = write_query).assign(result = itemgetter(\"query\") | execute_query)\n",
    "    | answer\n",
    ")\n",
    "\n",
    "final_chain.invoke({\"question\": \"user表里有多少个admin用户?\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "cbc27275-0bbf-4547-8e84-0a920d38abe6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "first=RunnableAssign(mapper={\n",
      "  query: {\n",
      "           input: RunnableLambda(...),\n",
      "           top_k: RunnableLambda(...),\n",
      "           table_info: RunnableLambda(...)\n",
      "         }\n",
      "         | PromptTemplate(input_variables=['input', 'table_info', 'top_k'], template='You are a MySQL expert. Given an input question, first create a syntactically correct MySQL query to run, then look at the results of the query and return the answer to the input question.\\nUnless 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 MySQL. You can order the results to return the most informative data in the database.\\nNever 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 backticks (`) to denote them as delimited identifiers.\\nPay 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.\\nPay attention to use CURDATE() function to get the current date, if the question involves \"today\".\\n\\nUse the following format:\\n\\nQuestion: Question here\\nSQLQuery: SQL Query to run\\nSQLResult: Result of the SQLQuery\\nAnswer: Final answer here\\n\\nOnly use the following tables:\\n{table_info}\\n\\nQuestion: {input}')\n",
      "         | RunnableBinding(bound=ChatOpenAI(client=<openai.resources.chat.completions.Completions object at 0x000001F3051D7130>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x000001F3061D9E40>, temperature=0.0, openai_api_key='sk-Zq40bukilVJ01Ga097486d2790Ae483cA5F7A4C86b5dAaE3', openai_api_base='https://apejhvxcd.cloud.sealos.io/v1', openai_proxy=''), kwargs={'stop': ['\\nSQLResult:']})\n",
      "         | StrOutputParser()\n",
      "         | RunnableLambda(_strip)\n",
      "}) middle=[RunnableAssign(mapper={\n",
      "  result: RunnableLambda(itemgetter('query'))\n",
      "          | QuerySQLDataBaseTool(db=<langchain_community.utilities.sql_database.SQLDatabase object at 0x000001F37AE850F0>)\n",
      "}), PromptTemplate(input_variables=['query', 'question', 'result'], template='Given the following user question, corresponding SQL query, and SQL result, answer the user question. 用中文回答最终答案\\n\\nQuestion: {question}\\nSQL Query: {query}\\nSQL Result: {result}\\nAnswer: '), ChatOpenAI(client=<openai.resources.chat.completions.Completions object at 0x000001F3051D7130>, async_client=<openai.resources.chat.completions.AsyncCompletions object at 0x000001F3061D9E40>, temperature=0.0, openai_api_key='sk-Zq40bukilVJ01Ga097486d2790Ae483cA5F7A4C86b5dAaE3', openai_api_base='https://apejhvxcd.cloud.sealos.io/v1', openai_proxy='')] last=StrOutputParser()\n"
     ]
    }
   ],
   "source": [
    "print(final_chain)"
   ]
  }
 ],
 "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.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}