sql-query-generation-using-the-open-ai-api-1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9aad5153-7470-4b09-9722-66216f873d5d",
   "metadata": {},
   "source": [
    "# SQL query generation using the OPEN AI API (1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86bc2813-763c-49f2-9f5d-e1f87a20556f",
   "metadata": {
    "id": "86bc2813-763c-49f2-9f5d-e1f87a20556f"
   },
   "source": [
    "In This notebook we are going to test if using just the name of the table, and a shord definition of its contect we can use a Model like GTP3.5-Turbo to select which tables are necessary to create a SQL Order to answer the user petition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "7fbbdf5b-4d7f-4496-8f40-9d15ea46d023",
   "metadata": {
    "id": "7fbbdf5b-4d7f-4496-8f40-9d15ea46d023"
   },
   "outputs": [],
   "source": [
    "from openai import OpenAI\n",
    "import os\n",
    "from dotenv import load_dotenv, find_dotenv\n",
    "_ = load_dotenv(find_dotenv())\n",
    "\n",
    "OPENAI_API_KEY  = os.getenv('OPENAI_API_KEY')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e480bbfb-9a80-4ea6-b792-067e63ae3148",
   "metadata": {
    "id": "e480bbfb-9a80-4ea6-b792-067e63ae3148"
   },
   "outputs": [],
   "source": [
    "#Functio to call the model.\n",
    "def return_OAI(user_message):\n",
    "    client = OpenAI(\n",
    "    # This is the default and can be omitted\n",
    "    api_key=OPENAI_API_KEY,\n",
    ")\n",
    "    context = []\n",
    "    context.append({'role':'system', \"content\": user_message})\n",
    "\n",
    "    response = client.chat.completions.create(\n",
    "            model=\"gpt-3.5-turbo\",\n",
    "            messages=context,\n",
    "            temperature=0,\n",
    "        )\n",
    "\n",
    "    return (response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6d5d2bdc-d822-4ed8-815e-b3f223730f15",
   "metadata": {
    "id": "6d5d2bdc-d822-4ed8-815e-b3f223730f15",
    "outputId": "61068bf0-41e3-40d9-b453-b76da5b0f086"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       table                                         definition\n",
      "0  employees                      Employee information, name...\n",
      "1     salary                       Salary details for each year\n",
      "2    studies  Educational studies, name of the institution, ...\n"
     ]
    }
   ],
   "source": [
    "#Definition of the tables.\n",
    "import pandas as pd\n",
    "\n",
    "# Table and definitions sample\n",
    "data = {'table': ['employees', 'salary', 'studies'],\n",
    "        'definition': ['Employee information, name...',\n",
    "                       'Salary details for each year',\n",
    "                       'Educational studies, name of the institution, type of studies, level']}\n",
    "df = pd.DataFrame(data)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "009e681c-d95d-4c9c-b044-5bfd76e3ad95",
   "metadata": {
    "id": "009e681c-d95d-4c9c-b044-5bfd76e3ad95"
   },
   "outputs": [],
   "source": [
    "text_tables = '\\n'.join([f\"{row['table']}: {row['definition']}\" for index, row in df.iterrows()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "fe03ac25-8d02-4cd1-99a1-be4220c6fd2d",
   "metadata": {
    "id": "fe03ac25-8d02-4cd1-99a1-be4220c6fd2d",
    "outputId": "c1f3aab1-5f26-48fe-fcf9-3780120f5aad"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "employees: Employee information, name...\n",
      "salary: Salary details for each year\n",
      "studies: Educational studies, name of the institution, type of studies, level\n"
     ]
    }
   ],
   "source": [
    "print(text_tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c7e275ae-f20d-4134-b9b6-d8677dfb544c",
   "metadata": {
    "id": "c7e275ae-f20d-4134-b9b6-d8677dfb544c"
   },
   "outputs": [],
   "source": [
    "prompt_question_tables = \"\"\"\n",
    "Given the following tables and their content definitions,\n",
    "###Tables\n",
    "{tables}\n",
    "\n",
    "Tell me which tables would be necessary to query with SQL to address the user's question below.\n",
    "Return the table names in a json format.\n",
    "###User Questyion:\n",
    "{question}\n",
    "\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b1cb5957-2df2-4e5e-9e6a-ace955c9817e",
   "metadata": {
    "id": "b1cb5957-2df2-4e5e-9e6a-ace955c9817e"
   },
   "outputs": [],
   "source": [
    "#Creating the prompt, with the user questions and the tables definitions.\n",
    "pqt1 = prompt_question_tables.format(tables=text_tables, question=\"Return The name of the best paid employee\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "10d30f2b-6c23-4fd6-8038-840fba784cce",
   "metadata": {
    "id": "10d30f2b-6c23-4fd6-8038-840fba784cce",
    "outputId": "9924022c-7b2b-4ec8-e2c2-75bc1c745151",
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "```json\n",
      "{\n",
      "    \"tables\": [\"employees\", \"salary\"]\n",
      "}\n",
      "```\n"
     ]
    }
   ],
   "source": [
    "print(return_OAI(pqt1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "57e07083-be8f-4cd0-95bd-c4b909422c6b",
   "metadata": {
    "id": "57e07083-be8f-4cd0-95bd-c4b909422c6b"
   },
   "outputs": [],
   "source": [
    "pqt3 = prompt_question_tables.format(tables=text_tables,\n",
    "                                     question=\"Return the Education Institution with a higher average salary\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "a0eeb79e-caf1-4f48-9897-168d95d2ae37",
   "metadata": {
    "id": "a0eeb79e-caf1-4f48-9897-168d95d2ae37",
    "outputId": "81d77115-9cad-4284-a228-5368bb9aa6fb"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "```json\n",
      "{\n",
      "    \"tables\": {\n",
      "        \"employees\": \"Employee information\",\n",
      "        \"salary\": \"Salary details for each year\",\n",
      "        \"studies\": \"Educational studies\"\n",
      "    }\n",
      "}\n",
      "```\n"
     ]
    }
   ],
   "source": [
    "print(return_OAI(pqt3))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "321bb9a2-4937-4e9a-a31b-7049cb8f5aa3",
   "metadata": {
    "id": "321bb9a2-4937-4e9a-a31b-7049cb8f5aa3"
   },
   "source": [
    "# Conclusions\n",
    "It is clear that GPT-3.5-Turbo is a model entirely capable of deciding which tables should be used in creating an SQL query.\n",
    "\n",
    "In a more complex system, we may need to refine the definition and conduct several tests before finalizing them.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1269aaf2-e203-44da-b3cb-80bf538d67a3",
   "metadata": {
    "id": "1269aaf2-e203-44da-b3cb-80bf538d67a3"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "provenance": []
  },
  "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}