t_shirt_sales_llm.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5ff82c00",
   "metadata": {},
   "source": [
    "#### Google Palm LLM & API key setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "6de1952b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.llms import GooglePalm\n",
    "\n",
    "api_key = 'XYZ'\n",
    "\n",
    "llm = GooglePalm(google_api_key=api_key, temperature=0.2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0c0d700",
   "metadata": {},
   "source": [
    "#### Connect with database and ask some basic questions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "f48fbb53",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.utilities import SQLDatabase\n",
    "from langchain_experimental.sql import SQLDatabaseChain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "23f9ae16",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TABLE discounts (\n",
      "\tdiscount_id INTEGER NOT NULL AUTO_INCREMENT, \n",
      "\tt_shirt_id INTEGER NOT NULL, \n",
      "\tpct_discount DECIMAL(5, 2), \n",
      "\tPRIMARY KEY (discount_id), \n",
      "\tCONSTRAINT discounts_ibfk_1 FOREIGN KEY(t_shirt_id) REFERENCES t_shirts (t_shirt_id), \n",
      "\tCONSTRAINT discounts_chk_1 CHECK ((`pct_discount` between 0 and 100))\n",
      ")DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_0900_ai_ci ENGINE=InnoDB\n",
      "\n",
      "/*\n",
      "3 rows from discounts table:\n",
      "discount_id\tt_shirt_id\tpct_discount\n",
      "1\t1\t10.00\n",
      "2\t2\t15.00\n",
      "3\t3\t20.00\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE t_shirts (\n",
      "\tt_shirt_id INTEGER NOT NULL AUTO_INCREMENT, \n",
      "\tbrand ENUM('Van Huesen','Levi','Nike','Adidas') NOT NULL, \n",
      "\tcolor ENUM('Red','Blue','Black','White') NOT NULL, \n",
      "\tsize ENUM('XS','S','M','L','XL') NOT NULL, \n",
      "\tprice INTEGER, \n",
      "\tstock_quantity INTEGER NOT NULL, \n",
      "\tPRIMARY KEY (t_shirt_id), \n",
      "\tCONSTRAINT t_shirts_chk_1 CHECK ((`price` between 10 and 50))\n",
      ")DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_0900_ai_ci ENGINE=InnoDB\n",
      "\n",
      "/*\n",
      "3 rows from t_shirts table:\n",
      "t_shirt_id\tbrand\tcolor\tsize\tprice\tstock_quantity\n",
      "1\tLevi\tBlack\tS\t19\t15\n",
      "2\tNike\tWhite\tS\t24\t64\n",
      "3\tAdidas\tWhite\tXS\t14\t55\n",
      "*/\n"
     ]
    }
   ],
   "source": [
    "db_user = \"root\"\n",
    "db_password = \"root\"\n",
    "db_host = \"localhost\"\n",
    "db_name = \"atliq_tshirts\"\n",
    "\n",
    "db = SQLDatabase.from_uri(f\"mysql+pymysql://{db_user}:{db_password}@{db_host}/{db_name}\",sample_rows_in_table_info=3)\n",
    "\n",
    "print(db.table_info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "b0c86dce",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "How many t-shirts do we have left for nike in extra small size and white color?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT stock_quantity FROM t_shirts WHERE brand = 'Nike' AND color = 'White' AND size = 'XS'\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[(91,)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3m91\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True)\n",
    "qns1 = db_chain(\"How many t-shirts do we have left for nike in extra small size and white color?\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d100193",
   "metadata": {},
   "source": [
    "Above is the correct answer 👍🏼"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "2b436818",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "How much is the price of the inventory for all small size t-shirts?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT SUM(price) FROM t_shirts WHERE size = 'S'\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[(Decimal('314'),)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3m314\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "qns2 = db_chain.run(\"How much is the price of the inventory for all small size t-shirts?\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7632f6dc",
   "metadata": {},
   "source": [
    "It made a mistake here. The price is actually the price per unit but in real life database columns will not have perfect names. We need to tell it somehow that price is price per unit and the actual query should be,\n",
    "\n",
    "SELECT SUM(price*stock_quantity) FROM t_shirts WHERE size = 'S'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "52d613ed",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "SELECT SUM(price*stock_quantity) FROM t_shirts WHERE size = 'S'\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT SUM(price*stock_quantity) FROM t_shirts WHERE size = 'S'\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[(Decimal('22292'),)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3m22292\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "qns2 = db_chain.run(\"SELECT SUM(price*stock_quantity) FROM t_shirts WHERE size = 'S'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b9a4e2c",
   "metadata": {},
   "source": [
    "we will use qns2 value later on in this notebook. So hold on for now and let's check another query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "7da6c762",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "If we have to sell all the Levi’s T-shirts today with discounts applied. How much revenue our store will generate (post discounts)?\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT SUM(price * (1 - pct_discount)) FROM t_shirts JOIN discounts ON t_shirts.t_shirt_id = discounts.t_shirt_id WHERE brand = 'Levi' AND CURDATE() BETWEEN discounts.start_date AND discounts.end_date\u001b[0m"
     ]
    },
    {
     "ename": "OperationalError",
     "evalue": "(pymysql.err.OperationalError) (1054, \"Unknown column 'discounts.start_date' in 'where clause'\")\n[SQL: SELECT SUM(price * (1 - pct_discount)) FROM t_shirts JOIN discounts ON t_shirts.t_shirt_id = discounts.t_shirt_id WHERE brand = 'Levi' AND CURDATE() BETWEEN discounts.start_date AND discounts.end_date]\n(Background on this error at: https://sqlalche.me/e/20/e3q8)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\sqlalchemy\\engine\\base.py:1965\u001b[0m, in \u001b[0;36mConnection._exec_single_context\u001b[1;34m(self, dialect, context, statement, parameters)\u001b[0m\n\u001b[0;32m   1964\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m evt_handled:\n\u001b[1;32m-> 1965\u001b[0m         \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdialect\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdo_execute\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   1966\u001b[0m \u001b[43m            \u001b[49m\u001b[43mcursor\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mstr_statement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43meffective_parameters\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcontext\u001b[49m\n\u001b[0;32m   1967\u001b[0m \u001b[43m        \u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1969\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_has_events \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine\u001b[38;5;241m.\u001b[39m_has_events:\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\sqlalchemy\\engine\\default.py:921\u001b[0m, in \u001b[0;36mDefaultDialect.do_execute\u001b[1;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[0;32m    920\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mdo_execute\u001b[39m(\u001b[38;5;28mself\u001b[39m, cursor, statement, parameters, context\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mNone\u001b[39;00m):\n\u001b[1;32m--> 921\u001b[0m     \u001b[43mcursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\cursors.py:153\u001b[0m, in \u001b[0;36mCursor.execute\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    151\u001b[0m query \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mmogrify(query, args)\n\u001b[1;32m--> 153\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_query\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    154\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_executed \u001b[38;5;241m=\u001b[39m query\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\cursors.py:322\u001b[0m, in \u001b[0;36mCursor._query\u001b[1;34m(self, q)\u001b[0m\n\u001b[0;32m    321\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_clear_result()\n\u001b[1;32m--> 322\u001b[0m \u001b[43mconn\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mquery\u001b[49m\u001b[43m(\u001b[49m\u001b[43mq\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    323\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_do_get_result()\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\connections.py:558\u001b[0m, in \u001b[0;36mConnection.query\u001b[1;34m(self, sql, unbuffered)\u001b[0m\n\u001b[0;32m    557\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_execute_command(COMMAND\u001b[38;5;241m.\u001b[39mCOM_QUERY, sql)\n\u001b[1;32m--> 558\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_affected_rows \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_read_query_result\u001b[49m\u001b[43m(\u001b[49m\u001b[43munbuffered\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43munbuffered\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    559\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_affected_rows\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\connections.py:822\u001b[0m, in \u001b[0;36mConnection._read_query_result\u001b[1;34m(self, unbuffered)\u001b[0m\n\u001b[0;32m    821\u001b[0m     result \u001b[38;5;241m=\u001b[39m MySQLResult(\u001b[38;5;28mself\u001b[39m)\n\u001b[1;32m--> 822\u001b[0m     \u001b[43mresult\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    823\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_result \u001b[38;5;241m=\u001b[39m result\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\connections.py:1200\u001b[0m, in \u001b[0;36mMySQLResult.read\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1199\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m-> 1200\u001b[0m     first_packet \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconnection\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_read_packet\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1202\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m first_packet\u001b[38;5;241m.\u001b[39mis_ok_packet():\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\connections.py:772\u001b[0m, in \u001b[0;36mConnection._read_packet\u001b[1;34m(self, packet_type)\u001b[0m\n\u001b[0;32m    771\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_result\u001b[38;5;241m.\u001b[39munbuffered_active \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mFalse\u001b[39;00m\n\u001b[1;32m--> 772\u001b[0m     \u001b[43mpacket\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_for_error\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    773\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m packet\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\protocol.py:221\u001b[0m, in \u001b[0;36mMysqlPacket.raise_for_error\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    220\u001b[0m     \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124merrno =\u001b[39m\u001b[38;5;124m\"\u001b[39m, errno)\n\u001b[1;32m--> 221\u001b[0m \u001b[43merr\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_mysql_exception\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_data\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\err.py:143\u001b[0m, in \u001b[0;36mraise_mysql_exception\u001b[1;34m(data)\u001b[0m\n\u001b[0;32m    142\u001b[0m     errorclass \u001b[38;5;241m=\u001b[39m InternalError \u001b[38;5;28;01mif\u001b[39;00m errno \u001b[38;5;241m<\u001b[39m \u001b[38;5;241m1000\u001b[39m \u001b[38;5;28;01melse\u001b[39;00m OperationalError\n\u001b[1;32m--> 143\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m errorclass(errno, errval)\n",
      "\u001b[1;31mOperationalError\u001b[0m: (1054, \"Unknown column 'discounts.start_date' in 'where clause'\")",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[124], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m qns3 \u001b[38;5;241m=\u001b[39m \u001b[43mdb_chain\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mrun\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mIf we have to sell all the Levi’s T-shirts today with discounts applied. How much revenue our store will generate (post discounts)?\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\langchain\\chains\\base.py:487\u001b[0m, in \u001b[0;36mrun\u001b[1;34m(self, callbacks, tags, metadata, *args, **kwargs)\u001b[0m\n\u001b[0;32m    453\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mrun\u001b[39m(\n\u001b[0;32m    454\u001b[0m     \u001b[38;5;28mself\u001b[39m,\n\u001b[0;32m    455\u001b[0m     \u001b[38;5;241m*\u001b[39margs: Any,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m    459\u001b[0m     \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs: Any,\n\u001b[0;32m    460\u001b[0m ) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m Any:\n\u001b[0;32m    461\u001b[0m \u001b[38;5;250m    \u001b[39m\u001b[38;5;124;03m\"\"\"Convenience method for executing chain.\u001b[39;00m\n\u001b[0;32m    462\u001b[0m \n\u001b[0;32m    463\u001b[0m \u001b[38;5;124;03m    The main difference between this method and `Chain.__call__` is that this\u001b[39;00m\n\u001b[0;32m    464\u001b[0m \u001b[38;5;124;03m    method expects inputs to be passed directly in as positional arguments or\u001b[39;00m\n\u001b[0;32m    465\u001b[0m \u001b[38;5;124;03m    keyword arguments, whereas `Chain.__call__` expects a single input dictionary\u001b[39;00m\n\u001b[0;32m    466\u001b[0m \u001b[38;5;124;03m    with all the inputs\u001b[39;00m\n\u001b[0;32m    467\u001b[0m \n\u001b[0;32m    468\u001b[0m \u001b[38;5;124;03m    Args:\u001b[39;00m\n\u001b[0;32m    469\u001b[0m \u001b[38;5;124;03m        *args: If the chain expects a single input, it can be passed in as the\u001b[39;00m\n\u001b[0;32m    470\u001b[0m \u001b[38;5;124;03m            sole positional argument.\u001b[39;00m\n\u001b[0;32m    471\u001b[0m \u001b[38;5;124;03m        callbacks: Callbacks to use for this chain run. These will be called in\u001b[39;00m\n\u001b[0;32m    472\u001b[0m \u001b[38;5;124;03m            addition to callbacks passed to the chain during construction, but only\u001b[39;00m\n\u001b[0;32m    473\u001b[0m \u001b[38;5;124;03m            these runtime callbacks will propagate to calls to other objects.\u001b[39;00m\n\u001b[0;32m    474\u001b[0m \u001b[38;5;124;03m        tags: List of string tags to pass to all callbacks. These will be passed in\u001b[39;00m\n\u001b[0;32m    475\u001b[0m \u001b[38;5;124;03m            addition to tags passed to the chain during construction, but only\u001b[39;00m\n\u001b[0;32m    476\u001b[0m \u001b[38;5;124;03m            these runtime tags will propagate to calls to other objects.\u001b[39;00m\n\u001b[0;32m    477\u001b[0m \u001b[38;5;124;03m        **kwargs: If the chain expects multiple inputs, they can be passed in\u001b[39;00m\n\u001b[0;32m    478\u001b[0m \u001b[38;5;124;03m            directly as keyword arguments.\u001b[39;00m\n\u001b[0;32m    479\u001b[0m \n\u001b[0;32m    480\u001b[0m \u001b[38;5;124;03m    Returns:\u001b[39;00m\n\u001b[0;32m    481\u001b[0m \u001b[38;5;124;03m        The chain output.\u001b[39;00m\n\u001b[0;32m    482\u001b[0m \n\u001b[0;32m    483\u001b[0m \u001b[38;5;124;03m    Example:\u001b[39;00m\n\u001b[0;32m    484\u001b[0m \u001b[38;5;124;03m        .. code-block:: python\u001b[39;00m\n\u001b[0;32m    485\u001b[0m \n\u001b[0;32m    486\u001b[0m \u001b[38;5;124;03m            # Suppose we have a single-input chain that takes a 'question' string:\u001b[39;00m\n\u001b[1;32m--> 487\u001b[0m \u001b[38;5;124;03m            chain.run(\"What's the temperature in Boise, Idaho?\")\u001b[39;00m\n\u001b[0;32m    488\u001b[0m \u001b[38;5;124;03m            # -> \"The temperature in Boise is...\"\u001b[39;00m\n\u001b[0;32m    489\u001b[0m \n\u001b[0;32m    490\u001b[0m \u001b[38;5;124;03m            # Suppose we have a multi-input chain that takes a 'question' string\u001b[39;00m\n\u001b[0;32m    491\u001b[0m \u001b[38;5;124;03m            # and 'context' string:\u001b[39;00m\n\u001b[0;32m    492\u001b[0m \u001b[38;5;124;03m            question = \"What's the temperature in Boise, Idaho?\"\u001b[39;00m\n\u001b[0;32m    493\u001b[0m \u001b[38;5;124;03m            context = \"Weather report for Boise, Idaho on 07/03/23...\"\u001b[39;00m\n\u001b[0;32m    494\u001b[0m \u001b[38;5;124;03m            chain.run(question=question, context=context)\u001b[39;00m\n\u001b[0;32m    495\u001b[0m \u001b[38;5;124;03m            # -> \"The temperature in Boise is...\"\u001b[39;00m\n\u001b[0;32m    496\u001b[0m \u001b[38;5;124;03m    \"\"\"\u001b[39;00m\n\u001b[0;32m    497\u001b[0m     \u001b[38;5;66;03m# Run at start to make sure this is possible/defined\u001b[39;00m\n\u001b[0;32m    498\u001b[0m     _output_key \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_run_output_key\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\langchain\\chains\\base.py:292\u001b[0m, in \u001b[0;36m__call__\u001b[1;34m(self, inputs, return_only_outputs, callbacks, tags, metadata, run_name, include_run_info)\u001b[0m\n\u001b[0;32m    259\u001b[0m \u001b[38;5;250m\u001b[39m\u001b[38;5;124;03m\"\"\"Execute the chain.\u001b[39;00m\n\u001b[0;32m    260\u001b[0m \n\u001b[0;32m    261\u001b[0m \u001b[38;5;124;03mArgs:\u001b[39;00m\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m    282\u001b[0m \u001b[38;5;124;03m        `Chain.output_keys`.\u001b[39;00m\n\u001b[0;32m    283\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[0;32m    284\u001b[0m inputs \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mprep_inputs(inputs)\n\u001b[0;32m    285\u001b[0m callback_manager \u001b[38;5;241m=\u001b[39m CallbackManager\u001b[38;5;241m.\u001b[39mconfigure(\n\u001b[0;32m    286\u001b[0m     callbacks,\n\u001b[0;32m    287\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mcallbacks,\n\u001b[0;32m    288\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mverbose,\n\u001b[0;32m    289\u001b[0m     tags,\n\u001b[0;32m    290\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mtags,\n\u001b[0;32m    291\u001b[0m     metadata,\n\u001b[1;32m--> 292\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mmetadata,\n\u001b[0;32m    293\u001b[0m )\n\u001b[0;32m    294\u001b[0m new_arg_supported \u001b[38;5;241m=\u001b[39m inspect\u001b[38;5;241m.\u001b[39msignature(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_call)\u001b[38;5;241m.\u001b[39mparameters\u001b[38;5;241m.\u001b[39mget(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mrun_manager\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m    295\u001b[0m run_manager \u001b[38;5;241m=\u001b[39m callback_manager\u001b[38;5;241m.\u001b[39mon_chain_start(\n\u001b[0;32m    296\u001b[0m     dumpd(\u001b[38;5;28mself\u001b[39m),\n\u001b[0;32m    297\u001b[0m     inputs,\n\u001b[0;32m    298\u001b[0m     name\u001b[38;5;241m=\u001b[39mrun_name,\n\u001b[0;32m    299\u001b[0m )\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\langchain\\chains\\base.py:286\u001b[0m, in \u001b[0;36m__call__\u001b[1;34m(self, inputs, return_only_outputs, callbacks, tags, metadata, run_name, include_run_info)\u001b[0m\n\u001b[0;32m    259\u001b[0m \u001b[38;5;250m\u001b[39m\u001b[38;5;124;03m\"\"\"Execute the chain.\u001b[39;00m\n\u001b[0;32m    260\u001b[0m \n\u001b[0;32m    261\u001b[0m \u001b[38;5;124;03mArgs:\u001b[39;00m\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m    282\u001b[0m \u001b[38;5;124;03m        `Chain.output_keys`.\u001b[39;00m\n\u001b[0;32m    283\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[0;32m    284\u001b[0m inputs \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mprep_inputs(inputs)\n\u001b[0;32m    285\u001b[0m callback_manager \u001b[38;5;241m=\u001b[39m CallbackManager\u001b[38;5;241m.\u001b[39mconfigure(\n\u001b[1;32m--> 286\u001b[0m     callbacks,\n\u001b[0;32m    287\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mcallbacks,\n\u001b[0;32m    288\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mverbose,\n\u001b[0;32m    289\u001b[0m     tags,\n\u001b[0;32m    290\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mtags,\n\u001b[0;32m    291\u001b[0m     metadata,\n\u001b[0;32m    292\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mmetadata,\n\u001b[0;32m    293\u001b[0m )\n\u001b[0;32m    294\u001b[0m new_arg_supported \u001b[38;5;241m=\u001b[39m inspect\u001b[38;5;241m.\u001b[39msignature(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_call)\u001b[38;5;241m.\u001b[39mparameters\u001b[38;5;241m.\u001b[39mget(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mrun_manager\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m    295\u001b[0m run_manager \u001b[38;5;241m=\u001b[39m callback_manager\u001b[38;5;241m.\u001b[39mon_chain_start(\n\u001b[0;32m    296\u001b[0m     dumpd(\u001b[38;5;28mself\u001b[39m),\n\u001b[0;32m    297\u001b[0m     inputs,\n\u001b[0;32m    298\u001b[0m     name\u001b[38;5;241m=\u001b[39mrun_name,\n\u001b[0;32m    299\u001b[0m )\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\langchain_experimental\\sql\\base.py:198\u001b[0m, in \u001b[0;36mSQLDatabaseChain._call\u001b[1;34m(self, inputs, run_manager)\u001b[0m\n\u001b[0;32m    194\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mException\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m exc:\n\u001b[0;32m    195\u001b[0m     \u001b[38;5;66;03m# Append intermediate steps to exception, to aid in logging and later\u001b[39;00m\n\u001b[0;32m    196\u001b[0m     \u001b[38;5;66;03m# improvement of few shot prompt seeds\u001b[39;00m\n\u001b[0;32m    197\u001b[0m     exc\u001b[38;5;241m.\u001b[39mintermediate_steps \u001b[38;5;241m=\u001b[39m intermediate_steps  \u001b[38;5;66;03m# type: ignore\u001b[39;00m\n\u001b[1;32m--> 198\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m exc\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\langchain_experimental\\sql\\base.py:143\u001b[0m, in \u001b[0;36mSQLDatabaseChain._call\u001b[1;34m(self, inputs, run_manager)\u001b[0m\n\u001b[0;32m    139\u001b[0m     intermediate_steps\u001b[38;5;241m.\u001b[39mappend(\n\u001b[0;32m    140\u001b[0m         sql_cmd\n\u001b[0;32m    141\u001b[0m     )  \u001b[38;5;66;03m# output: sql generation (no checker)\u001b[39;00m\n\u001b[0;32m    142\u001b[0m     intermediate_steps\u001b[38;5;241m.\u001b[39mappend({\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msql_cmd\u001b[39m\u001b[38;5;124m\"\u001b[39m: sql_cmd})  \u001b[38;5;66;03m# input: sql exec\u001b[39;00m\n\u001b[1;32m--> 143\u001b[0m     result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdatabase\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mrun\u001b[49m\u001b[43m(\u001b[49m\u001b[43msql_cmd\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    144\u001b[0m     intermediate_steps\u001b[38;5;241m.\u001b[39mappend(\u001b[38;5;28mstr\u001b[39m(result))  \u001b[38;5;66;03m# output: sql exec\u001b[39;00m\n\u001b[0;32m    145\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\langchain\\utilities\\sql_database.py:414\u001b[0m, in \u001b[0;36mrun\u001b[1;34m(self, command, fetch)\u001b[0m\n\u001b[0;32m    412\u001b[0m     first_result = cursor.fetchone()\n\u001b[0;32m    413\u001b[0m     result = [] if first_result is None else [first_result._asdict()]\n\u001b[1;32m--> 414\u001b[0m else:\n\u001b[0;32m    415\u001b[0m     raise ValueError(\"Fetch parameter must be either 'one' or 'all'\")\n\u001b[0;32m    416\u001b[0m return result\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\langchain\\utilities\\sql_database.py:397\u001b[0m, in \u001b[0;36mSQLDatabase._execute\u001b[1;34m(self, command, fetch)\u001b[0m\n\u001b[0;32m    395\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdialect \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mmssql\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m    396\u001b[0m     \u001b[38;5;28;01mpass\u001b[39;00m\n\u001b[1;32m--> 397\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdialect \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mtrino\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m    398\u001b[0m     connection\u001b[38;5;241m.\u001b[39mexec_driver_sql(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mUSE ?\u001b[39m\u001b[38;5;124m\"\u001b[39m, (\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_schema,))\n\u001b[0;32m    399\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdialect \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mduckdb\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m    400\u001b[0m     \u001b[38;5;66;03m# Unclear which parameterized argument syntax duckdb supports.\u001b[39;00m\n\u001b[0;32m    401\u001b[0m     \u001b[38;5;66;03m# The docs for the duckdb client say they support multiple,\u001b[39;00m\n\u001b[0;32m    402\u001b[0m     \u001b[38;5;66;03m# but `duckdb_engine` seemed to struggle with all of them:\u001b[39;00m\n\u001b[0;32m    403\u001b[0m     \u001b[38;5;66;03m# https://github.com/Mause/duckdb_engine/issues/796\u001b[39;00m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\sqlalchemy\\engine\\base.py:1412\u001b[0m, in \u001b[0;36mConnection.execute\u001b[1;34m(self, statement, parameters, execution_options)\u001b[0m\n\u001b[0;32m   1410\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m exc\u001b[38;5;241m.\u001b[39mObjectNotExecutableError(statement) \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01merr\u001b[39;00m\n\u001b[0;32m   1411\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m-> 1412\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mmeth\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   1413\u001b[0m \u001b[43m        \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1414\u001b[0m \u001b[43m        \u001b[49m\u001b[43mdistilled_parameters\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1415\u001b[0m \u001b[43m        \u001b[49m\u001b[43mexecution_options\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;129;43;01mor\u001b[39;49;00m\u001b[43m \u001b[49m\u001b[43mNO_OPTIONS\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1416\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\sqlalchemy\\sql\\elements.py:483\u001b[0m, in \u001b[0;36mClauseElement._execute_on_connection\u001b[1;34m(self, connection, distilled_params, execution_options)\u001b[0m\n\u001b[0;32m    481\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m TYPE_CHECKING:\n\u001b[0;32m    482\u001b[0m         \u001b[38;5;28;01massert\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(\u001b[38;5;28mself\u001b[39m, Executable)\n\u001b[1;32m--> 483\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mconnection\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_execute_clauseelement\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m    484\u001b[0m \u001b[43m        \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mdistilled_params\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mexecution_options\u001b[49m\n\u001b[0;32m    485\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    486\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m    487\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m exc\u001b[38;5;241m.\u001b[39mObjectNotExecutableError(\u001b[38;5;28mself\u001b[39m)\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\sqlalchemy\\engine\\base.py:1635\u001b[0m, in \u001b[0;36mConnection._execute_clauseelement\u001b[1;34m(self, elem, distilled_parameters, execution_options)\u001b[0m\n\u001b[0;32m   1623\u001b[0m compiled_cache: Optional[CompiledCacheType] \u001b[38;5;241m=\u001b[39m execution_options\u001b[38;5;241m.\u001b[39mget(\n\u001b[0;32m   1624\u001b[0m     \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mcompiled_cache\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine\u001b[38;5;241m.\u001b[39m_compiled_cache\n\u001b[0;32m   1625\u001b[0m )\n\u001b[0;32m   1627\u001b[0m compiled_sql, extracted_params, cache_hit \u001b[38;5;241m=\u001b[39m elem\u001b[38;5;241m.\u001b[39m_compile_w_cache(\n\u001b[0;32m   1628\u001b[0m     dialect\u001b[38;5;241m=\u001b[39mdialect,\n\u001b[0;32m   1629\u001b[0m     compiled_cache\u001b[38;5;241m=\u001b[39mcompiled_cache,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   1633\u001b[0m     linting\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdialect\u001b[38;5;241m.\u001b[39mcompiler_linting \u001b[38;5;241m|\u001b[39m compiler\u001b[38;5;241m.\u001b[39mWARN_LINTING,\n\u001b[0;32m   1634\u001b[0m )\n\u001b[1;32m-> 1635\u001b[0m ret \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_execute_context\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   1636\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdialect\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1637\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdialect\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecution_ctx_cls\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_init_compiled\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1638\u001b[0m \u001b[43m    \u001b[49m\u001b[43mcompiled_sql\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1639\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdistilled_parameters\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1640\u001b[0m \u001b[43m    \u001b[49m\u001b[43mexecution_options\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1641\u001b[0m \u001b[43m    \u001b[49m\u001b[43mcompiled_sql\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1642\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdistilled_parameters\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1643\u001b[0m \u001b[43m    \u001b[49m\u001b[43melem\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1644\u001b[0m \u001b[43m    \u001b[49m\u001b[43mextracted_params\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1645\u001b[0m \u001b[43m    \u001b[49m\u001b[43mcache_hit\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcache_hit\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   1646\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1647\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m has_events:\n\u001b[0;32m   1648\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdispatch\u001b[38;5;241m.\u001b[39mafter_execute(\n\u001b[0;32m   1649\u001b[0m         \u001b[38;5;28mself\u001b[39m,\n\u001b[0;32m   1650\u001b[0m         elem,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   1654\u001b[0m         ret,\n\u001b[0;32m   1655\u001b[0m     )\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\sqlalchemy\\engine\\base.py:1844\u001b[0m, in \u001b[0;36mConnection._execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[0;32m   1839\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_exec_insertmany_context(\n\u001b[0;32m   1840\u001b[0m         dialect,\n\u001b[0;32m   1841\u001b[0m         context,\n\u001b[0;32m   1842\u001b[0m     )\n\u001b[0;32m   1843\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m-> 1844\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_exec_single_context\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   1845\u001b[0m \u001b[43m        \u001b[49m\u001b[43mdialect\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcontext\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\n\u001b[0;32m   1846\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\sqlalchemy\\engine\\base.py:1984\u001b[0m, in \u001b[0;36mConnection._exec_single_context\u001b[1;34m(self, dialect, context, statement, parameters)\u001b[0m\n\u001b[0;32m   1981\u001b[0m     result \u001b[38;5;241m=\u001b[39m context\u001b[38;5;241m.\u001b[39m_setup_result_proxy()\n\u001b[0;32m   1983\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mBaseException\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m e:\n\u001b[1;32m-> 1984\u001b[0m     \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_handle_dbapi_exception\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   1985\u001b[0m \u001b[43m        \u001b[49m\u001b[43me\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mstr_statement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43meffective_parameters\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcursor\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcontext\u001b[49m\n\u001b[0;32m   1986\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1988\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m result\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\sqlalchemy\\engine\\base.py:2339\u001b[0m, in \u001b[0;36mConnection._handle_dbapi_exception\u001b[1;34m(self, e, statement, parameters, cursor, context, is_sub_exec)\u001b[0m\n\u001b[0;32m   2337\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m should_wrap:\n\u001b[0;32m   2338\u001b[0m     \u001b[38;5;28;01massert\u001b[39;00m sqlalchemy_exception \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[1;32m-> 2339\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m sqlalchemy_exception\u001b[38;5;241m.\u001b[39mwith_traceback(exc_info[\u001b[38;5;241m2\u001b[39m]) \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01me\u001b[39;00m\n\u001b[0;32m   2340\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m   2341\u001b[0m     \u001b[38;5;28;01massert\u001b[39;00m exc_info[\u001b[38;5;241m1\u001b[39m] \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\sqlalchemy\\engine\\base.py:1965\u001b[0m, in \u001b[0;36mConnection._exec_single_context\u001b[1;34m(self, dialect, context, statement, parameters)\u001b[0m\n\u001b[0;32m   1963\u001b[0m                 \u001b[38;5;28;01mbreak\u001b[39;00m\n\u001b[0;32m   1964\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m evt_handled:\n\u001b[1;32m-> 1965\u001b[0m         \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdialect\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdo_execute\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   1966\u001b[0m \u001b[43m            \u001b[49m\u001b[43mcursor\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mstr_statement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43meffective_parameters\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcontext\u001b[49m\n\u001b[0;32m   1967\u001b[0m \u001b[43m        \u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1969\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_has_events \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mengine\u001b[38;5;241m.\u001b[39m_has_events:\n\u001b[0;32m   1970\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdispatch\u001b[38;5;241m.\u001b[39mafter_cursor_execute(\n\u001b[0;32m   1971\u001b[0m         \u001b[38;5;28mself\u001b[39m,\n\u001b[0;32m   1972\u001b[0m         cursor,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   1976\u001b[0m         context\u001b[38;5;241m.\u001b[39mexecutemany,\n\u001b[0;32m   1977\u001b[0m     )\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\sqlalchemy\\engine\\default.py:921\u001b[0m, in \u001b[0;36mDefaultDialect.do_execute\u001b[1;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[0;32m    920\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mdo_execute\u001b[39m(\u001b[38;5;28mself\u001b[39m, cursor, statement, parameters, context\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mNone\u001b[39;00m):\n\u001b[1;32m--> 921\u001b[0m     \u001b[43mcursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mstatement\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparameters\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\cursors.py:153\u001b[0m, in \u001b[0;36mCursor.execute\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    149\u001b[0m     \u001b[38;5;28;01mpass\u001b[39;00m\n\u001b[0;32m    151\u001b[0m query \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mmogrify(query, args)\n\u001b[1;32m--> 153\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_query\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    154\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_executed \u001b[38;5;241m=\u001b[39m query\n\u001b[0;32m    155\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m result\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\cursors.py:322\u001b[0m, in \u001b[0;36mCursor._query\u001b[1;34m(self, q)\u001b[0m\n\u001b[0;32m    320\u001b[0m conn \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_get_db()\n\u001b[0;32m    321\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_clear_result()\n\u001b[1;32m--> 322\u001b[0m \u001b[43mconn\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mquery\u001b[49m\u001b[43m(\u001b[49m\u001b[43mq\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    323\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_do_get_result()\n\u001b[0;32m    324\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mrowcount\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\connections.py:558\u001b[0m, in \u001b[0;36mConnection.query\u001b[1;34m(self, sql, unbuffered)\u001b[0m\n\u001b[0;32m    556\u001b[0m     sql \u001b[38;5;241m=\u001b[39m sql\u001b[38;5;241m.\u001b[39mencode(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mencoding, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msurrogateescape\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m    557\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_execute_command(COMMAND\u001b[38;5;241m.\u001b[39mCOM_QUERY, sql)\n\u001b[1;32m--> 558\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_affected_rows \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_read_query_result\u001b[49m\u001b[43m(\u001b[49m\u001b[43munbuffered\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43munbuffered\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    559\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_affected_rows\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\connections.py:822\u001b[0m, in \u001b[0;36mConnection._read_query_result\u001b[1;34m(self, unbuffered)\u001b[0m\n\u001b[0;32m    820\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m    821\u001b[0m     result \u001b[38;5;241m=\u001b[39m MySQLResult(\u001b[38;5;28mself\u001b[39m)\n\u001b[1;32m--> 822\u001b[0m     \u001b[43mresult\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    823\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_result \u001b[38;5;241m=\u001b[39m result\n\u001b[0;32m    824\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m result\u001b[38;5;241m.\u001b[39mserver_status \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\connections.py:1200\u001b[0m, in \u001b[0;36mMySQLResult.read\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1198\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mread\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[0;32m   1199\u001b[0m     \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m-> 1200\u001b[0m         first_packet \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconnection\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_read_packet\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1202\u001b[0m         \u001b[38;5;28;01mif\u001b[39;00m first_packet\u001b[38;5;241m.\u001b[39mis_ok_packet():\n\u001b[0;32m   1203\u001b[0m             \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_read_ok_packet(first_packet)\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\connections.py:772\u001b[0m, in \u001b[0;36mConnection._read_packet\u001b[1;34m(self, packet_type)\u001b[0m\n\u001b[0;32m    770\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_result \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_result\u001b[38;5;241m.\u001b[39munbuffered_active \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mTrue\u001b[39;00m:\n\u001b[0;32m    771\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_result\u001b[38;5;241m.\u001b[39munbuffered_active \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mFalse\u001b[39;00m\n\u001b[1;32m--> 772\u001b[0m     \u001b[43mpacket\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_for_error\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    773\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m packet\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\protocol.py:221\u001b[0m, in \u001b[0;36mMysqlPacket.raise_for_error\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    219\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m DEBUG:\n\u001b[0;32m    220\u001b[0m     \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124merrno =\u001b[39m\u001b[38;5;124m\"\u001b[39m, errno)\n\u001b[1;32m--> 221\u001b[0m \u001b[43merr\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_mysql_exception\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_data\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\pymysql\\err.py:143\u001b[0m, in \u001b[0;36mraise_mysql_exception\u001b[1;34m(data)\u001b[0m\n\u001b[0;32m    141\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m errorclass \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m    142\u001b[0m     errorclass \u001b[38;5;241m=\u001b[39m InternalError \u001b[38;5;28;01mif\u001b[39;00m errno \u001b[38;5;241m<\u001b[39m \u001b[38;5;241m1000\u001b[39m \u001b[38;5;28;01melse\u001b[39;00m OperationalError\n\u001b[1;32m--> 143\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m errorclass(errno, errval)\n",
      "\u001b[1;31mOperationalError\u001b[0m: (pymysql.err.OperationalError) (1054, \"Unknown column 'discounts.start_date' in 'where clause'\")\n[SQL: SELECT SUM(price * (1 - pct_discount)) FROM t_shirts JOIN discounts ON t_shirts.t_shirt_id = discounts.t_shirt_id WHERE brand = 'Levi' AND CURDATE() BETWEEN discounts.start_date AND discounts.end_date]\n(Background on this error at: https://sqlalche.me/e/20/e3q8)"
     ]
    }
   ],
   "source": [
    "qns3 = db_chain.run(\"If we have to sell all the Levi’s T-shirts today with discounts applied. How much revenue our store will generate (post discounts)?\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "180f02fd",
   "metadata": {},
   "source": [
    "Above, it returned a wrong query which generated an error during query execution. It thinks discount\n",
    "table would have start and end date which is normally true but in our table there is no start or end date column.\n",
    "One thing we can do here is run the query directly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28a4b3c3",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "sql_code = \"\"\"\n",
    "select sum(a.total_amount * ((100-COALESCE(discounts.pct_discount,0))/100)) as total_revenue from\n",
    "(select sum(price*stock_quantity) as total_amount, t_shirt_id from t_shirts where brand = 'Levi'\n",
    "group by t_shirt_id) a left join discounts on a.t_shirt_id = discounts.t_shirt_id\n",
    " \"\"\"\n",
    "\n",
    "qns3 = db_chain.run(sql_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18dbf6ca",
   "metadata": {},
   "source": [
    "It produced a correct answer when explicit query was given. 17462 is the total revenue without discounts. The total discount is 736.6. Hence revenue post discount is 17462-736.6=16725.4\n",
    "\n",
    "Now this is not much interesting because what is the point of giving it the ready made query? Well, we will use this same query later on for few shot learning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b04ab6a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "qns4 = db_chain.run(\"SELECT SUM(price * stock_quantity) FROM t_shirts WHERE brand = 'Levi'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e822ee3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "qns5 = db_chain.run(\"How many white color Levi's t shirts we have available?\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62ee0e11",
   "metadata": {},
   "source": [
    "Once again above is the wrong answer. We need to use SUM(stock_quantity). Let's run the query explicitly. We will use the result of this query later on in the notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30d15ec1",
   "metadata": {},
   "outputs": [],
   "source": [
    "qns5 = db_chain.run(\"SELECT sum(stock_quantity) FROM t_shirts WHERE brand = 'Levi' AND color = 'White'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afe780d9",
   "metadata": {},
   "source": [
    "#### Few shot learning\n",
    "\n",
    "We will use few shot learning to fix issues we have seen so far"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a75ae546",
   "metadata": {},
   "outputs": [],
   "source": [
    "few_shots = [\n",
    "    {'Question' : \"How many t-shirts do we have left for Nike in XS size and white color?\",\n",
    "     'SQLQuery' : \"SELECT sum(stock_quantity) FROM t_shirts WHERE brand = 'Nike' AND color = 'White' AND size = 'XS'\",\n",
    "     'SQLResult': \"Result of the SQL query\",\n",
    "     'Answer' : qns1},\n",
    "    {'Question': \"How much is the total price of the inventory for all S-size t-shirts?\",\n",
    "     'SQLQuery':\"SELECT SUM(price*stock_quantity) FROM t_shirts WHERE size = 'S'\",\n",
    "     'SQLResult': \"Result of the SQL query\",\n",
    "     'Answer': qns2},\n",
    "    {'Question': \"If we have to sell all the Levi’s T-shirts today with discounts applied. How much revenue  our store will generate (post discounts)?\" ,\n",
    "     'SQLQuery' : \"\"\"SELECT sum(a.total_amount * ((100-COALESCE(discounts.pct_discount,0))/100)) as total_revenue from\n",
    "(select sum(price*stock_quantity) as total_amount, t_shirt_id from t_shirts where brand = 'Levi'\n",
    "group by t_shirt_id) a left join discounts on a.t_shirt_id = discounts.t_shirt_id\n",
    " \"\"\",\n",
    "     'SQLResult': \"Result of the SQL query\",\n",
    "     'Answer': qns3} ,\n",
    "     {'Question' : \"If we have to sell all the Levi’s T-shirts today. How much revenue our store will generate without discount?\" ,\n",
    "      'SQLQuery': \"SELECT SUM(price * stock_quantity) FROM t_shirts WHERE brand = 'Levi'\",\n",
    "      'SQLResult': \"Result of the SQL query\",\n",
    "      'Answer' : qns4},\n",
    "    {'Question': \"How many white color Levi's shirt I have?\",\n",
    "     'SQLQuery' : \"SELECT sum(stock_quantity) FROM t_shirts WHERE brand = 'Levi' AND color = 'White'\",\n",
    "     'SQLResult': \"Result of the SQL query\",\n",
    "     'Answer' : qns5\n",
    "     }\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30f7b3c9",
   "metadata": {},
   "source": [
    "### Creating Semantic Similarity Based example selector\n",
    "\n",
    "- create embedding on the few_shots\n",
    "- Store the embeddings in Chroma DB\n",
    "- Retrieve the the top most Semantically close example from the vector store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54306b8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.prompts import SemanticSimilarityExampleSelector\n",
    "from langchain.embeddings import HuggingFaceEmbeddings\n",
    "from langchain.vectorstores import Chroma\n",
    "\n",
    "\n",
    "embeddings = HuggingFaceEmbeddings(model_name='sentence-transformers/all-MiniLM-L6-v2')\n",
    "\n",
    "to_vectorize = [\" \".join(example.values()) for example in few_shots]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95513027",
   "metadata": {},
   "outputs": [],
   "source": [
    "to_vectorize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d806f39b",
   "metadata": {},
   "outputs": [],
   "source": [
    "vectorstore = Chroma.from_texts(to_vectorize, embeddings, metadatas=few_shots)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9cc4b0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "example_selector = SemanticSimilarityExampleSelector(\n",
    "    vectorstore=vectorstore,\n",
    "    k=2,\n",
    ")\n",
    "\n",
    "example_selector.select_examples({\"Question\": \"How many Adidas T shirts I have left in my store?\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "485d270b",
   "metadata": {},
   "outputs": [],
   "source": [
    "### my sql based instruction prompt\n",
    "mysql_prompt = \"\"\"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 {top_k} 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: Query to run with no pre-amble\n",
    "SQLResult: Result of the SQLQuery\n",
    "Answer: Final answer here\n",
    "\n",
    "No pre-amble.\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "357ca60f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from langchain.prompts import FewShotPromptTemplate\n",
    "from langchain.chains.sql_database.prompt import PROMPT_SUFFIX, _mysql_prompt\n",
    "\n",
    "print(PROMPT_SUFFIX)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20224496",
   "metadata": {},
   "source": [
    "### Setting up PromptTemplete using input variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3337e4a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.prompts.prompt import PromptTemplate\n",
    "\n",
    "example_prompt = PromptTemplate(\n",
    "    input_variables=[\"Question\", \"SQLQuery\", \"SQLResult\",\"Answer\",],\n",
    "    template=\"\\nQuestion: {Question}\\nSQLQuery: {SQLQuery}\\nSQLResult: {SQLResult}\\nAnswer: {Answer}\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5157663d",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(_mysql_prompt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5736eb12",
   "metadata": {},
   "outputs": [],
   "source": [
    "few_shot_prompt = FewShotPromptTemplate(\n",
    "    example_selector=example_selector,\n",
    "    example_prompt=example_prompt,\n",
    "    prefix=mysql_prompt,\n",
    "    suffix=PROMPT_SUFFIX,\n",
    "    input_variables=[\"input\", \"table_info\", \"top_k\"], #These variables are used in the prefix and suffix\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c23d156c",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True, prompt=few_shot_prompt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12a32c68",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_chain(\"How many white color Levi's shirt I have?\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1127786d",
   "metadata": {},
   "source": [
    "Now this is working ok. Previously for this same question it was giving wrong answer because it did not use SUM clause around stock_quantity column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30a05344",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "new_chain(\"How much is the price of the inventory for all small size t-shirts?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d38d4cc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_chain(\"How much is the price of all white color levi t shirts?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "510f8e47",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "new_chain(\"If we have to sell all the Nike’s T-shirts today with discounts applied. How much revenue  our store will generate (post discounts)?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3188a887",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "new_chain(\"If we have to sell all the Van Heuson T-shirts today with discounts applied. How much revenue  our store will generate (post discounts)?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b10fd4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_chain.run('How much revenue  our store will generate by selling all Van Heuson TShirts without discount?')"
   ]
  }
 ],
 "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}