IPL2023_Using_ChatGPT.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4fc7b3b5-4233-4d87-9982-3838ce52e593",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.environ[\"OPENAI_API_KEY\"] = \"Add your ChatGPT api key\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2688ac1b-1aec-44b0-9c37-3668ccd7f7d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nimport pandas as pd\\nimport sqlite3\\n\\neach_match_records_df[\\'date\\'] = pd.to_datetime(each_match_records_df[\\'date\\'], format=\\'%d-%m-%Y\\')\\n\\neach_match_records_df = pd.read_csv(\\'each_match_records.csv\\')\\neach_ball_records_df = pd.read_csv(\\'each_ball_records.csv\\')\\n\\n# Create a connection to your SQLite database\\nconn = sqlite3.connect(\"ipl_2023.db\") \\n\\n# Push the first DataFrame into the database\\neach_match_records_df.to_sql(\"each_match_records\", conn, if_exists=\"replace\", index=False)\\n\\n# Push the second DataFrame into the database\\neach_ball_records_df.to_sql(\"each_ball_records\", conn, if_exists=\"replace\", index=False)\\n\\n# Close the database connection\\nconn.close()\\n'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## One time code to create sql lite db using csv files \n",
    "'''\n",
    "import pandas as pd\n",
    "import sqlite3\n",
    "\n",
    "each_match_records_df['date'] = pd.to_datetime(each_match_records_df['date'], format='%d-%m-%Y')\n",
    "\n",
    "each_match_records_df = pd.read_csv('each_match_records.csv')\n",
    "each_ball_records_df = pd.read_csv('each_ball_records.csv')\n",
    "\n",
    "# Create a connection to your SQLite database\n",
    "conn = sqlite3.connect(\"ipl_2023.db\") \n",
    "\n",
    "# Push the first DataFrame into the database\n",
    "each_match_records_df.to_sql(\"each_match_records\", conn, if_exists=\"replace\", index=False)\n",
    "\n",
    "# Push the second DataFrame into the database\n",
    "each_ball_records_df.to_sql(\"each_ball_records\", conn, if_exists=\"replace\", index=False)\n",
    "\n",
    "# Close the database connection\n",
    "conn.close()\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1371bfb5-0e89-44e8-b738-5a75ffdfe677",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain_experimental.sql import SQLDatabaseChain\n",
    "from langchain.sql_database import SQLDatabase\n",
    "from langchain.llms.openai import OpenAI\n",
    "from langchain.chat_models import ChatOpenAI\n",
    "from langchain.prompts.prompt import PromptTemplate\n",
    "\n",
    "llm = ChatOpenAI(temperature=0, model_name=\"gpt-3.5-turbo\", verbose=True)\n",
    "\n",
    "DEFAULT_TABLES = [\n",
    "    'each_match_records',\n",
    "    'each_ball_records'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "17cda20c-5ab2-4545-a7bd-a28858042a04",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_prompt():\n",
    "    _DEFAULT_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",
    "    Use the following format:\n",
    "\n",
    "    Question: \"Question here\"\n",
    "    SQLQuery: \"SQL Query to run\"\n",
    "    SQLResult: \"Result of the SQLQuery\"\n",
    "\n",
    "    Answer: \"\"\n",
    "\n",
    "    Only use the following tables:\n",
    "\n",
    "    {table_info}\n",
    "\n",
    "    Question: {input}\"\"\"\n",
    "\n",
    "    PROMPT = PromptTemplate(\n",
    "        input_variables=[\"input\", \"table_info\", \"dialect\"], template=_DEFAULT_TEMPLATE\n",
    "    )\n",
    "    return PROMPT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d9373672-ef7b-4c06-8313-576854d12b39",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_db():\n",
    "    db = SQLDatabase.from_uri(\"sqlite:///ipl_2023.db\",\n",
    "                                  include_tables = DEFAULT_TABLES,\n",
    "                                  sample_rows_in_table_info=2)\n",
    "    return db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8bf2f5c3-a28d-459c-b72f-a86365ff0926",
   "metadata": {},
   "outputs": [],
   "source": [
    "def answer_question(query):\n",
    "    PROMPT = get_prompt()\n",
    "    db = get_db()\n",
    "    try:\n",
    "        db_chain = SQLDatabaseChain.from_llm(llm, db, \n",
    "                                             prompt=PROMPT, \n",
    "                                             verbose=True, \n",
    "                                             return_intermediate_steps=True,\n",
    "                                            #use_query_checker=True\n",
    "                                             )\n",
    "        result = db_chain(query)\n",
    "        sql_cmd = None\n",
    "        for step in result['intermediate_steps']:\n",
    "            if 'sql_cmd' in step:\n",
    "                sql_cmd = step['sql_cmd']\n",
    "                break\n",
    "\n",
    "        final_op = \"{}\\nSQL Command: < {} >\".format(result['result'], sql_cmd)\n",
    "        print(final_op)  \n",
    "        return result['result']\n",
    "    except Exception as e:\n",
    "        return f\"An error occurred: {str(e)}\"   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7188f7bd-7757-498b-ad4c-0a4b6fb09027",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "who won the final match\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT winner\n",
      "FROM each_match_records\n",
      "WHERE match_type = 'Final'\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[('Chennai Super Kings',)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mChennai Super Kings\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "Chennai Super Kings\n",
      "SQL Command: < SELECT winner\n",
      "FROM each_match_records\n",
      "WHERE match_type = 'Final' >\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Chennai Super Kings'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "answer_question(\"who won the final match\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04b5957b-1e24-4fa5-afb9-77037af1bc0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "answer_question(\"which player won most number of man of the match award\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "779aea10-2890-4e8a-8726-cb8364eeef4c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
      "who scored maximum runs\n",
      "SQLQuery:\u001b[32;1m\u001b[1;3mSELECT batter, SUM(score) AS total_runs\n",
      "FROM each_ball_records\n",
      "GROUP BY batter\n",
      "ORDER BY total_runs DESC\n",
      "LIMIT 1\u001b[0m\n",
      "SQLResult: \u001b[33;1m\u001b[1;3m[('Shubman Gill', 916)]\u001b[0m\n",
      "Answer:\u001b[32;1m\u001b[1;3mShubman Gill scored the maximum runs with a total of 916.\u001b[0m\n",
      "\u001b[1m> Finished chain.\u001b[0m\n",
      "Shubman Gill scored the maximum runs with a total of 916.\n",
      "SQL Command: < SELECT batter, SUM(score) AS total_runs\n",
      "FROM each_ball_records\n",
      "GROUP BY batter\n",
      "ORDER BY total_runs DESC\n",
      "LIMIT 1 >\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Shubman Gill scored the maximum runs with a total of 916.'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "answer_question(\"who scored maximum runs\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15c7137a-33a3-4f1c-afda-4cefcb266486",
   "metadata": {},
   "outputs": [],
   "source": [
    "answer_question(\"which player took most number of wickets\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84c7f186-2f37-451c-9ab0-57b267708205",
   "metadata": {},
   "outputs": [],
   "source": [
    "answer_question(\"Hello Bot\") #error traping "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86ef5cdd-e274-44e1-ba96-0fcfd9839e8d",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}