mainFileCopy.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\KIIT\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\gradio\\inputs.py:27: UserWarning: Usage of gradio.inputs is deprecated, and will not be supported in the future, please import your component from gradio.components\n",
      "  warnings.warn(\n",
      "c:\\Users\\KIIT\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\gradio\\inputs.py:30: UserWarning: `optional` parameter is deprecated, and it has no effect\n",
      "  super().__init__(\n",
      "c:\\Users\\KIIT\\AppData\\Local\\Programs\\Python\\Python310\\lib\\site-packages\\gradio\\inputs.py:30: UserWarning: `numeric` parameter is deprecated, and it has no effect\n",
      "  super().__init__(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "IMPORTANT: You are using gradio version 3.31.0, however version 4.29.0 is available, please upgrade.\n",
      "--------\n",
      "Running on local URL:  http://127.0.0.1:7860\n",
      "\n",
      "To create a public link, set `share=True` in `launch()`.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><iframe src=\"http://127.0.0.1:7860/\" width=\"100%\" height=\"500\" allow=\"autoplay; camera; microphone; clipboard-read; clipboard-write;\" frameborder=\"0\" allowfullscreen></iframe></div>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The Expanded Prompt is:  Can you provide me with the top 5 customers based on their total invoice amount?\n",
      "Query being used is:  SELECT c.CustomerId, c.FirstName, c.LastName, SUM(i.Total) AS TotalAmount\n",
      "FROM customer c\n",
      "JOIN invoice i ON c.CustomerId = i.CustomerId\n",
      "GROUP BY c.CustomerId\n",
      "ORDER BY TotalAmount DESC\n",
      "LIMIT 5;\n",
      "Generated answer is:  [(6, 'Helena', 'Holý', Decimal('49.62')), (26, 'Richard', 'Cunningham', Decimal('47.62')), (57, 'Luis', 'Rojas', Decimal('46.62')), (45, 'Ladislav', 'Kovács', Decimal('45.62')), (46, 'Hugh', \"O'Reilly\", Decimal('45.62'))] EOL\n",
      "Error creating DataFrame: SQL result is not in the expected format (list of tuples).\n",
      "Query being used is:  SELECT c.CustomerId, c.FirstName, c.LastName, SUM(i.Total) AS TotalAmount\n",
      "FROM customer c\n",
      "JOIN invoice i ON c.CustomerId = i.CustomerId\n",
      "GROUP BY c.CustomerId\n",
      "ORDER BY TotalAmount DESC\n",
      "LIMIT 5;\n",
      "Generated answer is:  [(6, 'Helena', 'Holý', Decimal('49.62')), (26, 'Richard', 'Cunningham', Decimal('47.62')), (57, 'Luis', 'Rojas', Decimal('46.62')), (45, 'Ladislav', 'Kovács', Decimal('45.62')), (46, 'Hugh', \"O'Reilly\", Decimal('45.62'))] EOL\n",
      "Keyboard interruption in main thread... closing server.\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from decimal import Decimal\n",
    "import gradio as gr\n",
    "from langchain_community.utilities import SQLDatabase\n",
    "from langchain_core.prompts import ChatPromptTemplate\n",
    "from langchain_core.output_parsers import StrOutputParser\n",
    "from langchain_core.runnables import RunnablePassthrough\n",
    "from langchain_openai import ChatOpenAI \n",
    "import os\n",
    "\n",
    "# Set the OpenAI API key\n",
    "os.environ['OPENAI_API_KEY'] = ''\n",
    "\n",
    "# Database URI\n",
    "mysql_uri = 'mysql+mysqlconnector://root:1234@localhost:3306/chinook'\n",
    "db = SQLDatabase.from_uri(mysql_uri)\n",
    "\n",
    "# Functions\n",
    "def get_schema(_):\n",
    "    schema = db.get_table_info()\n",
    "    return schema\n",
    "\n",
    "def expand_prompt(user_question):\n",
    "    llm = ChatOpenAI(model='gpt-3.5-turbo')\n",
    "    expand_template = \"\"\"In context of the table schema below, expand the prompt don't give me SQL Code, but expand it to be more SQL Friendly, and more detailed. Don't just write the SQL, give me a prompt to be given to an LLM. ONLY give me the prompt, and nothing else.\n",
    "    {schema}\n",
    "\n",
    "    Question: {question}\"\"\"\n",
    "    ex_prompt = ChatPromptTemplate.from_template(expand_template)\n",
    "    expand_chain = (\n",
    "        RunnablePassthrough.assign(schema=get_schema)\n",
    "        | ex_prompt\n",
    "        | llm\n",
    "        | StrOutputParser()\n",
    "    )\n",
    "    return expand_chain.invoke({\"question\": user_question})\n",
    "\n",
    "def run_query(query):\n",
    "    print(\"Query being used is:- \", query)\n",
    "    print(\"Generated answer is:- \", db.run(query), \"EOL\")\n",
    "    return db.run(query)\n",
    "\n",
    "def generate_response(user_question):\n",
    "    # Expand the user question\n",
    "    expanded_prompt = expand_prompt(user_question)\n",
    "    print(\"The Expanded Prompt is:- \", expanded_prompt)\n",
    "\n",
    "    # Create the SQL chain\n",
    "    llm = ChatOpenAI(model='gpt-3.5-turbo')\n",
    "    sql_template = \"\"\"Based on the table schema below, write an SQL query keeping in mind the foreign keys that would answer the user's question. ONLY generate the SQL Query and NOTHING else:\n",
    "    {schema}\n",
    "\n",
    "    Question: {question}\n",
    "    SQL Query:\"\"\"\n",
    "    sql_prompt = ChatPromptTemplate.from_template(sql_template)\n",
    "    sql_chain = (\n",
    "        RunnablePassthrough.assign(schema=get_schema)\n",
    "        | sql_prompt\n",
    "        | llm.bind(stop=[\"\\nSQLResult:\"])\n",
    "        | StrOutputParser()\n",
    "    )\n",
    "\n",
    "    # Generate the SQL query\n",
    "    sql_query = sql_chain.invoke({\"question\": expanded_prompt})\n",
    "\n",
    "    # Run the SQL query\n",
    "    sql_result = run_query(sql_query)\n",
    "\n",
    "    data = sql_result\n",
    "    \n",
    "    num_columns = len(data[0])\n",
    "\n",
    "    column_names = [f'Column{i+1}' for i in range(num_columns)]\n",
    "    df = pd.DataFrame(data, column_names)\n",
    "    print(df)\n",
    "    # Generate the final response\n",
    "    response_template = \"\"\"Write a natural language response which asks the user to refer to the table based on their question, and find the answer. Also summarize from the table the answer to the point as much as possible and write it as 'Summary:-':\n",
    "    {schema}\n",
    "\n",
    "    Question: {question}\n",
    "    SQL Query: {query}\n",
    "    SQL Response: {response}\"\"\"\n",
    "    prompt_response = ChatPromptTemplate.from_template(response_template)\n",
    "    full_chain = (\n",
    "        RunnablePassthrough.assign(query=sql_chain).assign(\n",
    "            schema=get_schema,\n",
    "            response=lambda vars: run_query(vars[\"query\"]),\n",
    "        )\n",
    "        | prompt_response\n",
    "        | llm\n",
    "    )\n",
    "\n",
    "    # Generate the response\n",
    "    final_response = full_chain.invoke({\"question\": expanded_prompt})\n",
    "    return final_response.content\n",
    "\n",
    "# Gradio Interface\n",
    "def gradio_interface(user_question):\n",
    "    return generate_response(user_question)\n",
    "\n",
    "interface = gr.Interface(\n",
    "    fn=gradio_interface,\n",
    "    inputs=gr.inputs.Textbox(lines=2, placeholder=\"Enter your question here...\"),\n",
    "    outputs=\"text\",\n",
    "    title=\"SQL Query Generator\",\n",
    "    description=\"Ask a question and get a detailed SQL query and response based on the Chinook database schema.\"\n",
    ")\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    interface.launch(debug=True)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}