notebooks/4_using_agents_for_qa_on_sql_and_vectordb.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8fc882bd-8ef3-49ae-8a7f-bbd975f9b249",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# %pip install --upgrade openai"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d2bdbafe-5e8f-42b8-80e9-39b36ec3cb2d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pickle, os\n",
    "from langchain.chains import RetrievalQA\n",
    "from langchain.llms import OpenAI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4686ded4-3554-4087-a84a-297a8208d8a7",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from langchain.agents import initialize_agent, Tool\n",
    "from langchain.agents import AgentType\n",
    "from langchain.tools import BaseTool\n",
    "from langchain.llms import OpenAI\n",
    "from langchain import LLMMathChain, SerpAPIWrapper\n",
    "from langchain.memory import ConversationBufferMemory\n",
    "from langchain.chat_models import ChatOpenAI\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "77bb4455-e1b8-4a19-82fb-472e695899b1",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "connection is ok\n",
      "['movie_characters', 'movie_conversations', 'movie_lines', 'movie_script_urls', 'movie_titles']\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\hpune\\AppData\\Local\\Temp\\ipykernel_34764\\2171782936.py:10: SADeprecationWarning: The Engine.table_names() method is deprecated and will be removed in a future release.  Please refer to Inspector.get_table_names(). (deprecated since: 1.4)\n",
      "  print(sqlite_engine.table_names())\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from langchain import OpenAI, SQLDatabase, SQLDatabaseChain\n",
    "conn_string = (\n",
    "    \"sqlite:///../database/moviesdb.db\"\n",
    "    )\n",
    "\n",
    "sqlite_engine = create_engine(conn_string, echo=False) #echo can be set to true if you want to see intermediate processing messages as well\n",
    "\n",
    "print('connection is ok')\n",
    "print(sqlite_engine.table_names())\n",
    "db_sql = SQLDatabase(engine=sqlite_engine,\n",
    "                 sample_rows_in_table_info=2, # We can set the number of samples to be included from database table to help LLM understand the table data better\n",
    "                )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "47ce1164-55c3-4757-a223-72e9744a2737",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "index_file_path = '../indexes/langchain_faiss_index_scripts_432.pickle'\n",
    "\n",
    "#  You can get the pickle files also from - https://drive.google.com/drive/folders/1rWUQbW4gyOvU96Uo8vFSbJH4VTNjAg7g?usp=share_link\n",
    "with open(index_file_path, 'rb') as handle:\n",
    "    db_vect = pickle.load(handle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "afcddf90-d9b3-4785-b90a-47ae6787b0e6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "llm = OpenAI(temperature=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "82f4797a-98f2-4297-83cb-2796c324e46f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "retriever = db_vect.as_retriever(search_kwargs={\"k\": 3}) \n",
    "scripts_qa = RetrievalQA.from_chain_type(llm=llm, chain_type=\"stuff\", retriever=retriever)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "13b456bc-2e75-40e3-b181-e1dd95dda067",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from langchain.prompts.prompt import PromptTemplate\n",
    "\n",
    "_NEW_PROMPT_TEMPLATE = \"\"\"Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer. \n",
    "Unless the user specifies in his question a specific number of examples he wishes to obtain, always limit your query to at most {top_k} results. \n",
    "You can order the results by a relevant column to return the most interesting examples in the database.\n",
    "Never query for all the columns from a specific table, only look for the few relevant columns given the question.\n",
    "Pay attention to use only the column names that you can see in the schema description. Be careful to not query for columns that do not exist.\n",
    "Also, pay attention to which column is in which tabl. \n",
    "Make sure to always use table_name.column_name full notation in query if there are ambiguous column names in different tables especially in WHERE clause.\n",
    "Make sure only to answer questions based on given database tables unless specified later explicitly, also provide your sources in result. Generate sql query without quotes. \n",
    "Make sure to use wild cards for search queries involving LIKE keyword and make sure to use search queries which are case insensitive\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 along with source\"\n",
    "\n",
    "        Only use the tables listed below.\n",
    "        {table_info}\n",
    "\n",
    "        Question: {input}\n",
    "        \"\"\"\n",
    "\n",
    "PROMPT = PromptTemplate(\n",
    "            input_variables=[\"input\", \"table_info\", \"dialect\", \"top_k\"], \n",
    "            template=_NEW_PROMPT_TEMPLATE\n",
    "        )\n",
    "\n",
    "\n",
    "db_chain = SQLDatabaseChain(llm=llm, database=db_sql, verbose=True, prompt=PROMPT, top_k=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "5fae27e6-080f-4fd4-82dd-59cacacc6b17",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "os.environ[\"LANGCHAIN_HANDLER\"] = \"langchain\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "108e9402-79bf-4ea1-b4e8-f4a648fd5ac3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "tools = [\n",
    "    Tool(\n",
    "        name = \"Movies SQL Database\",\n",
    "        func=db_chain.run,\n",
    "        description=\"useful for when you need to answer questions about movie characters , movie lines, movie ratings for movies available in database ... or if you need to compare two movies based on genre, rating or characters. If error comes try to handle the eror\"\n",
    "    ),\n",
    "    Tool(\n",
    "        name = \"Movie Scripts Question Answering System\",\n",
    "        func=scripts_qa.run,\n",
    "        description=\"useful for when you need to answer questions about a scene or plot of the movie from those which are available in the documents.\"\n",
    "    ),  \n",
    "    Tool(\n",
    "        name = \"Wikipedia external\",\n",
    "        func=db_chain.run,\n",
    "        description=\"useful for finding answers if you're not able to find results from either Movie Scripts Question Answering System or from Movies SQL Database\"\n",
    "    ),\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "1e4da182-d7be-4ebc-99eb-6d04aad04f11",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# # Use below commented code to use a chat conversational agent instead of regular one. For usage, check - https://python.langchain.com/en/latest/modules/agents/agents/examples/chat_conversation_agent.html\n",
    "# memory = ConversationBufferMemory(memory_key=\"chat_history\", return_messages=True)\n",
    "# llm=ChatOpenAI(temperature=0)\n",
    "# agent = initialize_agent(tools, llm, agent=AgentType.CHAT_CONVERSATIONAL_REACT_DESCRIPTION, verbose=True, memory=memory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "30bf3a69-6991-4f6c-afb3-035363d77c5a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "agent = initialize_agent(tools, llm, agent=AgentType.ZERO_SHOT_REACT_DESCRIPTION, verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "295f548e-b81f-412b-8b74-1a8a3f607413",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3m I need to find out how many characters are in the movie\n",
      "Action: Movies SQL Database\n",
      "Action Input: 10 things i hate about you\u001b[0m\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "10 things i hate about you\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3m\n",
      "SELECT movie_title, movie_line_text \n",
      "FROM movie_titles \n",
      "INNER JOIN movie_lines \n",
      "ON movie_titles.movie_id = movie_lines.movie_id \n",
      "WHERE movie_title LIKE '%10 things i hate about you%' \n",
      "LIMIT 5\n",
      "\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[('10 things i hate about you', 'They do not!'), ('10 things i hate about you', 'They do to!'), ('10 things i hate about you', 'I hope so.'), ('10 things i hate about you', 'She okay?'), ('10 things i hate about you', \"Let's go.\")]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3m\n",
      "Some of the lines from the movie 10 things i hate about you are: \n",
      "\"They do not!\", \"They do to!\", \"I hope so.\", \"She okay?\", \"Let's go.\" (Source: movie_titles and movie_lines tables)\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "\n",
      "Observation: \u001b[36;1m\u001b[1;3m\n",
      "Some of the lines from the movie 10 things i hate about you are: \n",
      "\"They do not!\", \"They do to!\", \"I hope so.\", \"She okay?\", \"Let's go.\" (Source: movie_titles and movie_lines tables)\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I need to find out how many unique characters are in the movie\n",
      "Action: Movie Scripts Question Answering System\n",
      "Action Input: 10 things i hate about you\u001b[0m\n",
      "Observation: \u001b[33;1m\u001b[1;3m 10 Things I Hate About You is a 1999 romantic comedy film based on William Shakespeare's play The Taming of the Shrew. The film follows the story of two sisters, Kat and Bianca Stratford, as they navigate the social scene of their high school. Kat is a strong-willed feminist who clashes with her strict father, while Bianca is popular and desperate to date. The film follows the sisters as they try to find love and acceptance in their lives.\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
      "Final Answer: There are 8 unique characters in the movie 10 Things I Hate About You.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'There are 8 unique characters in the movie 10 Things I Hate About You.'"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agent.run(\"how many unique characters are there in the movie 10 things i hate about you?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d1ef488f-c050-4d75-91d2-379ffe7b1eb2",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3m I need to find out who Kat's boyfriend is in the movie 10 Things I Hate About You\n",
      "Action: Movie Scripts Question Answering System\n",
      "Action Input: who is Kat's boyfriend in 10 Things I Hate About You\u001b[0m\n",
      "Observation: \u001b[33;1m\u001b[1;3m Patrick Verona\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3m I now know the final answer\n",
      "Final Answer: Patrick Verona is Kat's boyfriend in 10 Things I Hate About You.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"Patrick Verona is Kat's boyfriend in 10 Things I Hate About You.\""
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agent.run(\"who is boyfriend of kat in 10 things i hate about you?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b5657ea-5a47-4a37-9b88-45e2b0fa1d40",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}