agent_client.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Define the methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import time\n",
    "from functools import wraps\n",
    "\n",
    "def timer(func):\n",
    "    @wraps(func)\n",
    "    def wrapper(*args, **kwargs):\n",
    "        start_time = time.time()\n",
    "        result = func(*args, **kwargs)\n",
    "        end_time = time.time()\n",
    "        execution_time = end_time - start_time\n",
    "        print(f\"Function '{func.__name__}' executed in {execution_time:.4f} seconds\")\n",
    "        return result\n",
    "    return wrapper\n",
    "\n",
    "\n",
    "BASE_URL = 'http://localhost:8001/api'\n",
    "\n",
    "\n",
    "def start_conversation(connection_id):\n",
    "    response = requests.post(f\"{BASE_URL}/start_conversation\", params={\"connection_id\": connection_id})\n",
    "    if response.status_code == 200:\n",
    "        return response.json()['id']\n",
    "    else:\n",
    "        raise Exception(\"Failed to start conversation\")\n",
    "\n",
    "\n",
    "@timer\n",
    "def send_message(conversation_id, input_text):\n",
    "    print('processing.....\\n')\n",
    "    response = requests.post(f\"{BASE_URL}/send_message\", params={\"input\": input_text, \"conversation_id\": conversation_id})\n",
    "    if response.status_code == 200:\n",
    "        # print(response.json())\n",
    "        return response.text\n",
    "    else:\n",
    "        raise Exception(\"Failed to send message\")\n",
    "\n",
    "\n",
    "def display_messages(messages:list):\n",
    "    for message in messages:\n",
    "        content = message[1]\n",
    "        # print(f'############/n{content}')\n",
    "        if message[0] == 'ai':\n",
    "            try:\n",
    "                outputs = json.loads(content)\n",
    "                sql, knowledge, thought = outputs['sql'], outputs['knowledge'], outputs['thought']\n",
    "                print(f'AI: \\n############ SQL ############ \\n{sql} \\n############ knowledge ############ \\n{knowledge} \\n############ thoughts ############ \\n{thought} \\n\\n\\n')\n",
    "            except Exception as e:\n",
    "                # print(e)\n",
    "                print(f'AI: \\n{content}\\n\\n\\n')\n",
    "        if message[0] == 'human':\n",
    "            print(f'YOU: \\n{content}\\n\\n\\n')\n",
    "\n",
    "\n",
    "def get_history(conversation_id):\n",
    "    response = requests.get(f\"{BASE_URL}/conversation_history\", params={\"conversation_id\": conversation_id})\n",
    "    if response.status_code == 200:\n",
    "        return response.json()\n",
    "    else:\n",
    "        raise Exception(\"Failed to retrieve conversation history\")\n",
    "    \n",
    "\n",
    "def verify(conversation_id):\n",
    "    response = requests.post(f\"{BASE_URL}/verify\", params={\"conversation_id\": conversation_id})\n",
    "    if response.status_code == 200:\n",
    "        # return response.json()\n",
    "        return \"knowledgebase updated\"\n",
    "    else:\n",
    "        raise Exception(\"Failed to add knowledge\")\n",
    "\n",
    "\n",
    "# def get_history(conversation_id):\n",
    "    \n",
    "# conversation_id=None\n",
    "\n",
    "def chat(connection_id):\n",
    "    global conversation_id\n",
    "    \n",
    "    if not conversation_id == None:\n",
    "        history = get_history(conversation_id)\n",
    "        display_messages(history)\n",
    "    else:\n",
    "        # global conv_id\n",
    "        conversation_id = start_conversation(connection_id)\n",
    "        # conv_id = conversation_id\n",
    "\n",
    "    while True:\n",
    "        user_input = input(\"You: \")\n",
    "        display_messages([('human', user_input)])\n",
    "        if user_input.lower() == 'exit'or user_input== '':\n",
    "            break\n",
    "        response = send_message(conversation_id, user_input)\n",
    "        print('done!\\n')\n",
    "        display_messages([('ai', response)])\n",
    "    \n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Start a new chat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "YOU: \n",
      "which artist has the highest APOT\n",
      "\n",
      "\n",
      "\n",
      "processing.....\n",
      "\n",
      "done!\n",
      "\n",
      "AI: \n",
      "\"It seems that there is no direct column named \\\"APOT\\\" in the available tables. However, \\\"APOT\\\" might refer to a calculated value based on sales or popularity of tracks associated with artists.\\n\\nTo find the artist with the highest sales (which could be interpreted as APOT), I can calculate the total sales for each artist based on the `InvoiceLine` and `Track` tables, and then join that with the `Artist` table.\\n\\nWould you like me to proceed with this approach?\"\n",
      "\n",
      "\n",
      "\n",
      "YOU: \n",
      "\n",
      "\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "conversation_id = None\n",
    "\n",
    "chat(connection_id='1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# global variable conversation_id will change after calling chat\n",
    "this_id = conversation_id\n",
    "conversation_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Resume existing chat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "YOU: \n",
      "which artist has the highest APOT\n",
      "\n",
      "\n",
      "\n",
      "AI: \n",
      "It seems that there is no direct column named \"APOT\" in the available tables. However, \"APOT\" might refer to a calculated value based on sales or popularity of tracks associated with artists.\n",
      "\n",
      "To find the artist with the highest sales or popularity, we would typically need to aggregate sales data from the `InvoiceLine` table, which contains information about the quantity and price of tracks sold, and join it with the `Track` and `Artist` tables to get the artist information.\n",
      "\n",
      "Would you like me to proceed with this approach to find the artist with the highest sales?\n",
      "\n",
      "\n",
      "\n",
      "YOU: \n",
      "APOT stands for average price of tracks\n",
      "\n",
      "\n",
      "\n",
      "AI: \n",
      "############ SQL ############ \n",
      "SELECT a.ArtistId, a.Name AS ArtistName, AVG(t.UnitPrice) AS AveragePrice\n",
      "FROM Artist a\n",
      "JOIN Album al ON a.ArtistId = al.ArtistId\n",
      "JOIN Track t ON al.AlbumId = t.AlbumId\n",
      "GROUP BY a.ArtistId\n",
      "ORDER BY AveragePrice DESC\n",
      "LIMIT 1; \n",
      "############ knowledge ############ \n",
      "#knowledge about database\n",
      "* The Artist table contains information about artists.\n",
      "* The Album table contains information about albums, including their associated artists.\n",
      "* The Track table contains information about tracks, including their unit price.\n",
      "#knowledge from the user\n",
      "* APOT stands for average price of tracks. \n",
      "############ thoughts ############ \n",
      "* step1: I selected the ArtistId and ArtistName from the Artist table.\n",
      "* step2: I joined the Album table to get the albums associated with each artist.\n",
      "* step3: I joined the Track table to access the unit price of each track.\n",
      "* step4: I calculated the average price of tracks for each artist using AVG(t.UnitPrice).\n",
      "* step5: I grouped the results by ArtistId to get the average price for each artist.\n",
      "* step6: I ordered the results in descending order based on the average price and limited the result to the top artist. \n",
      "\n",
      "\n",
      "\n",
      "YOU: \n",
      "show me the data of 2021 only\n",
      "\n",
      "\n",
      "\n",
      "AI: \n",
      "To filter the data for the year 2021, we need to know which specific table and column you are referring to. Typically, date-related information might be found in tables like `Invoice`, `Track`, or `Album`, depending on what data you want to see.\n",
      "\n",
      "Could you please specify which table or type of data you would like to see for the year 2021? For example, are you interested in sales data, track releases, or something else?\n",
      "\n",
      "\n",
      "\n",
      "YOU: \n",
      "look in Invoice\n",
      "\n",
      "\n",
      "\n",
      "AI: \n",
      "############ SQL ############ \n",
      "SELECT *\n",
      "FROM Invoice\n",
      "WHERE strftime('%Y', InvoiceDate) = '2021'; \n",
      "############ knowledge ############ \n",
      "#knowledge about database\n",
      "* The Invoice table contains information about invoices, including the date of the invoice in the InvoiceDate column.\n",
      "#knowledge from the user\n",
      "* The user wants to see data from the Invoice table for the year 2021. \n",
      "############ thoughts ############ \n",
      "* step1: I selected all columns from the Invoice table.\n",
      "* step2: I filtered the results using the InvoiceDate column to include only those records where the year is 2021. \n",
      "\n",
      "\n",
      "\n",
      "YOU: \n",
      "in fact i mean the artist of highest apot in 2021\n",
      "\n",
      "\n",
      "\n",
      "processing.....\n",
      "\n",
      "done!\n",
      "\n",
      "AI: \n",
      "############ SQL ############ \n",
      "SELECT a.ArtistId, a.Name AS ArtistName, AVG(t.UnitPrice) AS AveragePrice\n",
      "FROM Artist a\n",
      "JOIN Album al ON a.ArtistId = al.ArtistId\n",
      "JOIN Track t ON al.AlbumId = t.AlbumId\n",
      "JOIN InvoiceLine il ON t.TrackId = il.TrackId\n",
      "JOIN Invoice i ON il.InvoiceId = i.InvoiceId\n",
      "WHERE strftime('%Y', i.InvoiceDate) = '2021'\n",
      "GROUP BY a.ArtistId\n",
      "ORDER BY AveragePrice DESC\n",
      "LIMIT 1; \n",
      "############ knowledge ############ \n",
      "#knowledge about database\n",
      "* The Artist table contains information about artists.\n",
      "* The Album table contains information about albums, including their associated artists.\n",
      "* The Track table contains information about tracks, including their unit price.\n",
      "* The InvoiceLine table contains information about the quantity and price of tracks sold.\n",
      "* The Invoice table contains information about invoices, including the date of the invoice in the InvoiceDate column.\n",
      "#knowledge from the user\n",
      "* APOT stands for average price of tracks.\n",
      "* The user wants to find the artist with the highest average price of tracks in the year 2021. \n",
      "############ thoughts ############ \n",
      "* step1: I selected the ArtistId and ArtistName from the Artist table.\n",
      "* step2: I joined the Album table to get the albums associated with each artist.\n",
      "* step3: I joined the Track table to access the unit price of each track.\n",
      "* step4: I joined the InvoiceLine table to link tracks to their sales.\n",
      "* step5: I joined the Invoice table to filter the data for the year 2021.\n",
      "* step6: I calculated the average price of tracks for each artist using AVG(t.UnitPrice).\n",
      "* step7: I grouped the results by ArtistId to get the average price for each artist.\n",
      "* step8: I ordered the results in descending order based on the average price and limited the result to the top artist. \n",
      "\n",
      "\n",
      "\n",
      "YOU: \n",
      "\n",
      "\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Just call chat() directly to resume the existing chat\n",
    "chat(connection_id='1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conversation_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# verification"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adding long term memory to the agent"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* ### if no long term memory. start a new chat, the agent forgets what it learned from past conversation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "YOU: \n",
      "which artist has the highest APOT\n",
      "\n",
      "\n",
      "\n",
      "processing.....\n",
      "\n",
      "done!\n",
      "\n",
      "AI: \n",
      "\"It seems that there is no direct column named \\\"APOT\\\" in the available tables. However, \\\"APOT\\\" might refer to a calculated value based on sales or popularity of tracks associated with artists.\\n\\nTo find the artist with the highest sales (which could be interpreted as APOT), I can calculate the total sales for each artist based on the `InvoiceLine` and `Track` tables, and then join that with the `Artist` table.\\n\\nWould you like me to proceed with this approach?\"\n",
      "\n",
      "\n",
      "\n",
      "YOU: \n",
      "\n",
      "\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "conversation_id = None\n",
    "\n",
    "chat(connection_id='1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* ### verify() function add the knowledge learned from conversation to the knowledgebase to form long term memory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'knowledgebase updated'"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conversation_id = this_id\n",
    "verify(conversation_id=conversation_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* ### Then ask similar questions in new conversations. The agent can understand what APOT is and where to find date info at the first round of conversation without needing user to explain."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "YOU: \n",
      "which artist has the highest APOT in year 2021\n",
      "\n",
      "\n",
      "\n",
      "processing.....\n",
      "\n",
      "Function 'send_message' executed in 71.3734 seconds\n",
      "done!\n",
      "\n",
      "AI: \n",
      "############ SQL ############ \n",
      "SELECT a.ArtistId, a.Name AS ArtistName, AVG(t.UnitPrice) AS AveragePrice\n",
      "FROM Artist a\n",
      "JOIN Album al ON a.ArtistId = al.ArtistId\n",
      "JOIN Track t ON al.AlbumId = t.AlbumId\n",
      "JOIN InvoiceLine il ON t.TrackId = il.TrackId\n",
      "JOIN Invoice i ON il.InvoiceId = i.InvoiceId\n",
      "WHERE strftime('%Y', i.InvoiceDate) = '2021'\n",
      "GROUP BY a.ArtistId\n",
      "ORDER BY AveragePrice DESC\n",
      "LIMIT 1; \n",
      "############ knowledge ############ \n",
      "#knowledge about database\n",
      "* The Artist table contains information about artists, including ArtistId and Name.\n",
      "* The Album table contains information about albums, including AlbumId and ArtistId.\n",
      "* The Track table contains information about tracks, including TrackId, AlbumId, and UnitPrice.\n",
      "* The Invoice table contains information about invoices, including InvoiceId and InvoiceDate.\n",
      "* The InvoiceLine table contains information about the quantity and price of tracks sold, including InvoiceId, TrackId, and UnitPrice.\n",
      "#knowledge from the user\n",
      "* The user wants to find the artist with the highest average price of tracks (APOT) specifically for the year 2021. \n",
      "############ thoughts ############ \n",
      "* step1: I selected the ArtistId and ArtistName from the Artist table.\n",
      "* step2: I joined the Album table to get the albums associated with each artist.\n",
      "* step3: I joined the Track table to access the unit price of each track.\n",
      "* step4: I joined the InvoiceLine table to link tracks to their sales.\n",
      "* step5: I joined the Invoice table to filter the data for the year 2021.\n",
      "* step6: I calculated the average price of tracks for each artist using AVG(t.UnitPrice).\n",
      "* step7: I grouped the results by ArtistId to get the average price for each artist.\n",
      "* step8: I ordered the results in descending order based on the average price and limited the result to the top artist. \n",
      "\n",
      "\n",
      "\n",
      "YOU: \n",
      "\n",
      "\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "conversation_id = None\n",
    "\n",
    "chat(connection_id='1')\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "xuance",
   "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.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}