GPT3_testing.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd"
      ],
      "metadata": {
        "id": "IUJ-uY_JGJNo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install datasets"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "V8EKS4fOofeu",
        "outputId": "45dae885-63cb-4a2f-ba29-cf03e8c64bc7"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting datasets\n",
            "  Downloading datasets-2.15.0-py3-none-any.whl (521 kB)\n",
            "\u001b[?25l     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m0.0/521.2 kB\u001b[0m \u001b[31m?\u001b[0m eta \u001b[36m-:--:--\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━━━━━━━\u001b[0m\u001b[91m╸\u001b[0m\u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m194.6/521.2 kB\u001b[0m \u001b[31m5.6 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[90m╺\u001b[0m \u001b[32m512.0/521.2 kB\u001b[0m \u001b[31m8.3 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m521.2/521.2 kB\u001b[0m \u001b[31m7.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hRequirement already satisfied: numpy>=1.17 in /usr/local/lib/python3.10/dist-packages (from datasets) (1.23.5)\n",
            "Requirement already satisfied: pyarrow>=8.0.0 in /usr/local/lib/python3.10/dist-packages (from datasets) (9.0.0)\n",
            "Collecting pyarrow-hotfix (from datasets)\n",
            "  Downloading pyarrow_hotfix-0.6-py3-none-any.whl (7.9 kB)\n",
            "Collecting dill<0.3.8,>=0.3.0 (from datasets)\n",
            "  Downloading dill-0.3.7-py3-none-any.whl (115 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m115.3/115.3 kB\u001b[0m \u001b[31m11.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hRequirement already satisfied: pandas in /usr/local/lib/python3.10/dist-packages (from datasets) (1.5.3)\n",
            "Requirement already satisfied: requests>=2.19.0 in /usr/local/lib/python3.10/dist-packages (from datasets) (2.31.0)\n",
            "Requirement already satisfied: tqdm>=4.62.1 in /usr/local/lib/python3.10/dist-packages (from datasets) (4.66.1)\n",
            "Requirement already satisfied: xxhash in /usr/local/lib/python3.10/dist-packages (from datasets) (3.4.1)\n",
            "Collecting multiprocess (from datasets)\n",
            "  Downloading multiprocess-0.70.15-py310-none-any.whl (134 kB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m134.8/134.8 kB\u001b[0m \u001b[31m9.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25hRequirement already satisfied: fsspec[http]<=2023.10.0,>=2023.1.0 in /usr/local/lib/python3.10/dist-packages (from datasets) (2023.6.0)\n",
            "Requirement already satisfied: aiohttp in /usr/local/lib/python3.10/dist-packages (from datasets) (3.9.1)\n",
            "Requirement already satisfied: huggingface-hub>=0.18.0 in /usr/local/lib/python3.10/dist-packages (from datasets) (0.19.4)\n",
            "Requirement already satisfied: packaging in /usr/local/lib/python3.10/dist-packages (from datasets) (23.2)\n",
            "Requirement already satisfied: pyyaml>=5.1 in /usr/local/lib/python3.10/dist-packages (from datasets) (6.0.1)\n",
            "Requirement already satisfied: attrs>=17.3.0 in /usr/local/lib/python3.10/dist-packages (from aiohttp->datasets) (23.1.0)\n",
            "Requirement already satisfied: multidict<7.0,>=4.5 in /usr/local/lib/python3.10/dist-packages (from aiohttp->datasets) (6.0.4)\n",
            "Requirement already satisfied: yarl<2.0,>=1.0 in /usr/local/lib/python3.10/dist-packages (from aiohttp->datasets) (1.9.3)\n",
            "Requirement already satisfied: frozenlist>=1.1.1 in /usr/local/lib/python3.10/dist-packages (from aiohttp->datasets) (1.4.0)\n",
            "Requirement already satisfied: aiosignal>=1.1.2 in /usr/local/lib/python3.10/dist-packages (from aiohttp->datasets) (1.3.1)\n",
            "Requirement already satisfied: async-timeout<5.0,>=4.0 in /usr/local/lib/python3.10/dist-packages (from aiohttp->datasets) (4.0.3)\n",
            "Requirement already satisfied: filelock in /usr/local/lib/python3.10/dist-packages (from huggingface-hub>=0.18.0->datasets) (3.13.1)\n",
            "Requirement already satisfied: typing-extensions>=3.7.4.3 in /usr/local/lib/python3.10/dist-packages (from huggingface-hub>=0.18.0->datasets) (4.5.0)\n",
            "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests>=2.19.0->datasets) (3.3.2)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests>=2.19.0->datasets) (3.6)\n",
            "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests>=2.19.0->datasets) (2.0.7)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests>=2.19.0->datasets) (2023.11.17)\n",
            "Requirement already satisfied: python-dateutil>=2.8.1 in /usr/local/lib/python3.10/dist-packages (from pandas->datasets) (2.8.2)\n",
            "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.10/dist-packages (from pandas->datasets) (2023.3.post1)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil>=2.8.1->pandas->datasets) (1.16.0)\n",
            "Installing collected packages: pyarrow-hotfix, dill, multiprocess, datasets\n",
            "Successfully installed datasets-2.15.0 dill-0.3.7 multiprocess-0.70.15 pyarrow-hotfix-0.6\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install openai==0.28"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5v7Rl2YT8rg9",
        "outputId": "119558ba-6aee-4b1e-8960-f8f9c051801d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: openai==0.28 in /usr/local/lib/python3.10/dist-packages (0.28.0)\n",
            "Requirement already satisfied: requests>=2.20 in /usr/local/lib/python3.10/dist-packages (from openai==0.28) (2.31.0)\n",
            "Requirement already satisfied: tqdm in /usr/local/lib/python3.10/dist-packages (from openai==0.28) (4.66.1)\n",
            "Requirement already satisfied: aiohttp in /usr/local/lib/python3.10/dist-packages (from openai==0.28) (3.9.1)\n",
            "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai==0.28) (3.3.2)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai==0.28) (3.6)\n",
            "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai==0.28) (2.0.7)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.10/dist-packages (from requests>=2.20->openai==0.28) (2023.11.17)\n",
            "Requirement already satisfied: attrs>=17.3.0 in /usr/local/lib/python3.10/dist-packages (from aiohttp->openai==0.28) (23.1.0)\n",
            "Requirement already satisfied: multidict<7.0,>=4.5 in /usr/local/lib/python3.10/dist-packages (from aiohttp->openai==0.28) (6.0.4)\n",
            "Requirement already satisfied: yarl<2.0,>=1.0 in /usr/local/lib/python3.10/dist-packages (from aiohttp->openai==0.28) (1.9.3)\n",
            "Requirement already satisfied: frozenlist>=1.1.1 in /usr/local/lib/python3.10/dist-packages (from aiohttp->openai==0.28) (1.4.0)\n",
            "Requirement already satisfied: aiosignal>=1.1.2 in /usr/local/lib/python3.10/dist-packages (from aiohttp->openai==0.28) (1.3.1)\n",
            "Requirement already satisfied: async-timeout<5.0,>=4.0 in /usr/local/lib/python3.10/dist-packages (from aiohttp->openai==0.28) (4.0.3)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df = pd.read_json('/content/tables.json')"
      ],
      "metadata": {
        "id": "rtpf5SPgGGSz"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 982
        },
        "id": "cnnu5Xi6KXjo",
        "outputId": "46723243-0251-446f-a0c2-c67576244e35"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                          column_names  \\\n",
              "0    [[-1, *], [0, perpetrator id], [0, people id],...   \n",
              "1    [[-1, *], [0, building], [0, room number], [0,...   \n",
              "2    [[-1, *], [0, id], [0, city], [0, country], [0...   \n",
              "3    [[-1, *], [0, institution id], [0, name], [0, ...   \n",
              "4    [[-1, *], [0, body builder id], [0, people id]...   \n",
              "..                                                 ...   \n",
              "161  [[-1, *], [0, employee ssn], [0, project numbe...   \n",
              "162  [[-1, *], [0, workshop id], [0, date], [0, ven...   \n",
              "163  [[-1, *], [0, item id], [0, title], [1, a id],...   \n",
              "164  [[-1, *], [0, party id], [0, party theme], [0,...   \n",
              "165  [[-1, *], [0, attribute id], [0, attribute nam...   \n",
              "\n",
              "                                 column_names_original  \\\n",
              "0    [[-1, *], [0, Perpetrator_ID], [0, People_ID],...   \n",
              "1    [[-1, *], [0, building], [0, room_number], [0,...   \n",
              "2    [[-1, *], [0, id], [0, City], [0, Country], [0...   \n",
              "3    [[-1, *], [0, instID], [0, name], [0, country]...   \n",
              "4    [[-1, *], [0, Body_Builder_ID], [0, People_ID]...   \n",
              "..                                                 ...   \n",
              "161  [[-1, *], [0, Essn], [0, Pno], [0, Hours], [1,...   \n",
              "162  [[-1, *], [0, Workshop_ID], [0, Date], [0, Ven...   \n",
              "163  [[-1, *], [0, i_id], [0, title], [1, a_id], [1...   \n",
              "164  [[-1, *], [0, Party_ID], [0, Party_Theme], [0,...   \n",
              "165  [[-1, *], [0, attribute_id], [0, attribute_nam...   \n",
              "\n",
              "                                          column_types            db_id  \\\n",
              "0    [text, number, number, text, number, text, tex...      perpetrator   \n",
              "1    [text, text, text, number, text, text, number,...        college_2   \n",
              "2    [text, number, text, text, text, text, text, n...   flight_company   \n",
              "3    [text, number, text, text, number, text, text,...           icfp_1   \n",
              "4    [text, number, number, number, number, number,...     body_builder   \n",
              "..                                                 ...              ...   \n",
              "161  [text, number, number, number, text, text, tex...        company_1   \n",
              "162  [text, number, text, text, text, number, numbe...   workshop_paper   \n",
              "163  [text, number, text, number, number, number, n...       epinions_1   \n",
              "164  [text, number, text, text, text, text, number,...       party_host   \n",
              "165  [text, number, text, text, number, text, text,...  product_catalog   \n",
              "\n",
              "                                          foreign_keys  \\\n",
              "0                                             [[2, 9]]   \n",
              "1    [[9, 4], [13, 4], [19, 1], [20, 2], [15, 7], [...   \n",
              "2                                   [[20, 7], [19, 1]]   \n",
              "3                           [[11, 7], [10, 1], [9, 4]]   \n",
              "4                                             [[2, 6]]   \n",
              "..                                                 ...   \n",
              "161                                                 []   \n",
              "162                                  [[10, 1], [9, 5]]   \n",
              "163                 [[5, 1], [4, 8], [11, 8], [10, 8]]   \n",
              "164                                 [[11, 1], [12, 7]]   \n",
              "165              [[10, 4], [13, 9], [27, 9], [26, 12]]   \n",
              "\n",
              "                                  primary_keys  \\\n",
              "0                                       [1, 9]   \n",
              "1    [1, 4, 7, 11, 15, 22, 27, 31, 37, 39, 45]   \n",
              "2                                   [1, 7, 13]   \n",
              "3                                 [1, 4, 7, 9]   \n",
              "4                                       [1, 6]   \n",
              "..                                         ...   \n",
              "161                     [1, 7, 15, 19, 22, 27]   \n",
              "162                                  [1, 5, 9]   \n",
              "163                                  [1, 3, 8]   \n",
              "164                                 [1, 7, 11]   \n",
              "165                              [1, 4, 9, 12]   \n",
              "\n",
              "                                           table_names  \\\n",
              "0                                [perpetrator, people]   \n",
              "1    [classroom, department, course, instructor, se...   \n",
              "2                   [airport, operate company, flight]   \n",
              "3     [institution, authors, papers, authorship count]   \n",
              "4                               [body builder, people]   \n",
              "..                                                 ...   \n",
              "161  [works on, employee, department, project, depe...   \n",
              "162                 [workshop, submission, acceptance]   \n",
              "163                    [item, review, useracct, trust]   \n",
              "164                          [party, host, party host]   \n",
              "165  [attribute definitions, catalogs, catalog stru...   \n",
              "\n",
              "                                  table_names_original  \n",
              "0                                [perpetrator, people]  \n",
              "1    [classroom, department, course, instructor, se...  \n",
              "2                   [airport, operate_company, flight]  \n",
              "3                  [Inst, Authors, Papers, Authorship]  \n",
              "4                               [body_builder, people]  \n",
              "..                                                 ...  \n",
              "161  [works_on, employee, department, project, depe...  \n",
              "162                 [workshop, submission, Acceptance]  \n",
              "163                    [item, review, useracct, trust]  \n",
              "164                          [party, host, party_host]  \n",
              "165  [Attribute_Definitions, Catalogs, Catalog_Stru...  \n",
              "\n",
              "[166 rows x 8 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-fa27f330-47a2-4941-9984-81db0444cf38\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>column_names</th>\n",
              "      <th>column_names_original</th>\n",
              "      <th>column_types</th>\n",
              "      <th>db_id</th>\n",
              "      <th>foreign_keys</th>\n",
              "      <th>primary_keys</th>\n",
              "      <th>table_names</th>\n",
              "      <th>table_names_original</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>[[-1, *], [0, perpetrator id], [0, people id],...</td>\n",
              "      <td>[[-1, *], [0, Perpetrator_ID], [0, People_ID],...</td>\n",
              "      <td>[text, number, number, text, number, text, tex...</td>\n",
              "      <td>perpetrator</td>\n",
              "      <td>[[2, 9]]</td>\n",
              "      <td>[1, 9]</td>\n",
              "      <td>[perpetrator, people]</td>\n",
              "      <td>[perpetrator, people]</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>[[-1, *], [0, building], [0, room number], [0,...</td>\n",
              "      <td>[[-1, *], [0, building], [0, room_number], [0,...</td>\n",
              "      <td>[text, text, text, number, text, text, number,...</td>\n",
              "      <td>college_2</td>\n",
              "      <td>[[9, 4], [13, 4], [19, 1], [20, 2], [15, 7], [...</td>\n",
              "      <td>[1, 4, 7, 11, 15, 22, 27, 31, 37, 39, 45]</td>\n",
              "      <td>[classroom, department, course, instructor, se...</td>\n",
              "      <td>[classroom, department, course, instructor, se...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>[[-1, *], [0, id], [0, city], [0, country], [0...</td>\n",
              "      <td>[[-1, *], [0, id], [0, City], [0, Country], [0...</td>\n",
              "      <td>[text, number, text, text, text, text, text, n...</td>\n",
              "      <td>flight_company</td>\n",
              "      <td>[[20, 7], [19, 1]]</td>\n",
              "      <td>[1, 7, 13]</td>\n",
              "      <td>[airport, operate company, flight]</td>\n",
              "      <td>[airport, operate_company, flight]</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>[[-1, *], [0, institution id], [0, name], [0, ...</td>\n",
              "      <td>[[-1, *], [0, instID], [0, name], [0, country]...</td>\n",
              "      <td>[text, number, text, text, number, text, text,...</td>\n",
              "      <td>icfp_1</td>\n",
              "      <td>[[11, 7], [10, 1], [9, 4]]</td>\n",
              "      <td>[1, 4, 7, 9]</td>\n",
              "      <td>[institution, authors, papers, authorship count]</td>\n",
              "      <td>[Inst, Authors, Papers, Authorship]</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>[[-1, *], [0, body builder id], [0, people id]...</td>\n",
              "      <td>[[-1, *], [0, Body_Builder_ID], [0, People_ID]...</td>\n",
              "      <td>[text, number, number, number, number, number,...</td>\n",
              "      <td>body_builder</td>\n",
              "      <td>[[2, 6]]</td>\n",
              "      <td>[1, 6]</td>\n",
              "      <td>[body builder, people]</td>\n",
              "      <td>[body_builder, people]</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>161</th>\n",
              "      <td>[[-1, *], [0, employee ssn], [0, project numbe...</td>\n",
              "      <td>[[-1, *], [0, Essn], [0, Pno], [0, Hours], [1,...</td>\n",
              "      <td>[text, number, number, number, text, text, tex...</td>\n",
              "      <td>company_1</td>\n",
              "      <td>[]</td>\n",
              "      <td>[1, 7, 15, 19, 22, 27]</td>\n",
              "      <td>[works on, employee, department, project, depe...</td>\n",
              "      <td>[works_on, employee, department, project, depe...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>162</th>\n",
              "      <td>[[-1, *], [0, workshop id], [0, date], [0, ven...</td>\n",
              "      <td>[[-1, *], [0, Workshop_ID], [0, Date], [0, Ven...</td>\n",
              "      <td>[text, number, text, text, text, number, numbe...</td>\n",
              "      <td>workshop_paper</td>\n",
              "      <td>[[10, 1], [9, 5]]</td>\n",
              "      <td>[1, 5, 9]</td>\n",
              "      <td>[workshop, submission, acceptance]</td>\n",
              "      <td>[workshop, submission, Acceptance]</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>163</th>\n",
              "      <td>[[-1, *], [0, item id], [0, title], [1, a id],...</td>\n",
              "      <td>[[-1, *], [0, i_id], [0, title], [1, a_id], [1...</td>\n",
              "      <td>[text, number, text, number, number, number, n...</td>\n",
              "      <td>epinions_1</td>\n",
              "      <td>[[5, 1], [4, 8], [11, 8], [10, 8]]</td>\n",
              "      <td>[1, 3, 8]</td>\n",
              "      <td>[item, review, useracct, trust]</td>\n",
              "      <td>[item, review, useracct, trust]</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>164</th>\n",
              "      <td>[[-1, *], [0, party id], [0, party theme], [0,...</td>\n",
              "      <td>[[-1, *], [0, Party_ID], [0, Party_Theme], [0,...</td>\n",
              "      <td>[text, number, text, text, text, text, number,...</td>\n",
              "      <td>party_host</td>\n",
              "      <td>[[11, 1], [12, 7]]</td>\n",
              "      <td>[1, 7, 11]</td>\n",
              "      <td>[party, host, party host]</td>\n",
              "      <td>[party, host, party_host]</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>165</th>\n",
              "      <td>[[-1, *], [0, attribute id], [0, attribute nam...</td>\n",
              "      <td>[[-1, *], [0, attribute_id], [0, attribute_nam...</td>\n",
              "      <td>[text, number, text, text, number, text, text,...</td>\n",
              "      <td>product_catalog</td>\n",
              "      <td>[[10, 4], [13, 9], [27, 9], [26, 12]]</td>\n",
              "      <td>[1, 4, 9, 12]</td>\n",
              "      <td>[attribute definitions, catalogs, catalog stru...</td>\n",
              "      <td>[Attribute_Definitions, Catalogs, Catalog_Stru...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>166 rows × 8 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-fa27f330-47a2-4941-9984-81db0444cf38')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-fa27f330-47a2-4941-9984-81db0444cf38 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-fa27f330-47a2-4941-9984-81db0444cf38');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-d191c3a0-5054-4380-af0e-c58bb926ae23\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-d191c3a0-5054-4380-af0e-c58bb926ae23')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-d191c3a0-5054-4380-af0e-c58bb926ae23 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df[df['db_id']=='flight_company']"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 153
        },
        "id": "qKKjjVJyIum0",
        "outputId": "76ab7a7c-ebcb-4c8e-f3b6-61586278ce10"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                        column_names  \\\n",
              "2  [[-1, *], [0, id], [0, city], [0, country], [0...   \n",
              "\n",
              "                               column_names_original  \\\n",
              "2  [[-1, *], [0, id], [0, City], [0, Country], [0...   \n",
              "\n",
              "                                        column_types           db_id  \\\n",
              "2  [text, number, text, text, text, text, text, n...  flight_company   \n",
              "\n",
              "         foreign_keys primary_keys                         table_names  \\\n",
              "2  [[20, 7], [19, 1]]   [1, 7, 13]  [airport, operate company, flight]   \n",
              "\n",
              "                 table_names_original  \n",
              "2  [airport, operate_company, flight]  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-1eb8fef5-9f5b-444b-b44b-d58eb896f417\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>column_names</th>\n",
              "      <th>column_names_original</th>\n",
              "      <th>column_types</th>\n",
              "      <th>db_id</th>\n",
              "      <th>foreign_keys</th>\n",
              "      <th>primary_keys</th>\n",
              "      <th>table_names</th>\n",
              "      <th>table_names_original</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>[[-1, *], [0, id], [0, city], [0, country], [0...</td>\n",
              "      <td>[[-1, *], [0, id], [0, City], [0, Country], [0...</td>\n",
              "      <td>[text, number, text, text, text, text, text, n...</td>\n",
              "      <td>flight_company</td>\n",
              "      <td>[[20, 7], [19, 1]]</td>\n",
              "      <td>[1, 7, 13]</td>\n",
              "      <td>[airport, operate company, flight]</td>\n",
              "      <td>[airport, operate_company, flight]</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1eb8fef5-9f5b-444b-b44b-d58eb896f417')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-1eb8fef5-9f5b-444b-b44b-d58eb896f417 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-1eb8fef5-9f5b-444b-b44b-d58eb896f417');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import json\n",
        "with open('/content/tables.json', 'r') as file:\n",
        "    tables = json.load(file)"
      ],
      "metadata": {
        "id": "dgROfR16LzkU"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "for example in data['validation']:\n",
        "  #schema_info = df[df['db_id']==example['db_id']]['column_names']\n",
        "  schema_info = next((item for item in tables if item.get('db_id') == example['db_id']), None)\n",
        "  print(str(schema_info))\n",
        "  break"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bVpSx_IqI9se",
        "outputId": "67a22dd9-1666-4c72-94d1-c58c7de9091e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "{'column_names': [[-1, '*'], [0, 'stadium id'], [0, 'location'], [0, 'name'], [0, 'capacity'], [0, 'highest'], [0, 'lowest'], [0, 'average'], [1, 'singer id'], [1, 'name'], [1, 'country'], [1, 'song name'], [1, 'song release year'], [1, 'age'], [1, 'is male'], [2, 'concert id'], [2, 'concert name'], [2, 'theme'], [2, 'stadium id'], [2, 'year'], [3, 'concert id'], [3, 'singer id']], 'column_names_original': [[-1, '*'], [0, 'Stadium_ID'], [0, 'Location'], [0, 'Name'], [0, 'Capacity'], [0, 'Highest'], [0, 'Lowest'], [0, 'Average'], [1, 'Singer_ID'], [1, 'Name'], [1, 'Country'], [1, 'Song_Name'], [1, 'Song_release_year'], [1, 'Age'], [1, 'Is_male'], [2, 'concert_ID'], [2, 'concert_Name'], [2, 'Theme'], [2, 'Stadium_ID'], [2, 'Year'], [3, 'concert_ID'], [3, 'Singer_ID']], 'column_types': ['text', 'number', 'text', 'text', 'number', 'number', 'number', 'number', 'number', 'text', 'text', 'text', 'text', 'number', 'others', 'number', 'text', 'text', 'text', 'text', 'number', 'text'], 'db_id': 'concert_singer', 'foreign_keys': [[18, 1], [21, 8], [20, 15]], 'primary_keys': [1, 8, 15, 20], 'table_names': ['stadium', 'singer', 'concert', 'singer in concert'], 'table_names_original': ['stadium', 'singer', 'concert', 'singer_in_concert']}\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import openai\n",
        "from datasets import load_dataset\n",
        "import sqlparse\n",
        "\n",
        "# Replace with your actual OpenAI API key\n",
        "api_key = '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'\n",
        "\n",
        "# Initialize the OpenAI API client\n",
        "openai.api_key = api_key\n",
        "\n",
        "# Load the Spider dataset\n",
        "data = load_dataset(\"spider\")\n",
        "\n",
        "em_count = 0\n",
        "count = 0\n",
        "# Iterate through the dataset\n",
        "for example in data['validation']:  # You can use 'train' or 'test' as well\n",
        "    count+=1\n",
        "    if count>200:\n",
        "      break\n",
        "    question = example['question']\n",
        "    ground_truth_query = example['query']\n",
        "    schema_info = next((item for item in tables if item.get('db_id') == example['db_id']), None)\n",
        "    keys = ['column_names_original', 'table_names_original', 'db_id']\n",
        "\n",
        "    for key in keys:\n",
        "        schema_info.pop(key, None)\n",
        "\n",
        "    # Generate SQL query using the OpenAI GPT API\n",
        "    response = openai.Completion.create(\n",
        "        engine=\"text-davinci-003\",\n",
        "        prompt= \"Write an sql code for this query: \" + question + \"Here's the schema information for the database: \" + str(schema_info),\n",
        "        max_tokens=50,\n",
        "        api_key=api_key\n",
        "    )\n",
        "    print(\"Write an sql code for this query: \" + question + \" \\nHere's the schema information for the database: \" + str(schema_info))\n",
        "    generated_query = response.choices[0].text.strip()\n",
        "\n",
        "\n",
        "    # Check EM (Exact Match)\n",
        "    if generated_query.lower() == ground_truth_query.lower():\n",
        "        em_count += 1\n",
        "    # else:\n",
        "    #print(\"GENERATED\",generated_query)\n",
        "    #print(\"ACTUAL   \", ground_truth_query)\n",
        "    #print(\"===============\")\n",
        "\n",
        "# Calculate EM percentage\n",
        "total_examples = len(data['validation'])  # You can use 'train' or 'test' as well\n",
        "em_percentage = (em_count / count) * 100\n",
        "\n",
        "print(f\"Exact Match (EM) Percentage: {em_percentage}%\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YqsYjKyVY7TK",
        "outputId": "3abd83d9-97c2-4bf2-87ab-cb97730d3979"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Exact Match (EM) Percentage: 67.3%\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "HBPAZRzyY_yM"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}