poc.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data\n",
    "\n",
    "Loading and appending the Chicago Crime dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import duckdb\n",
    "import openai\n",
    "import time \n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set the path of the CSV files:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['chicago_crime_2022.csv', 'chicago_crime_2023.csv', 'chicago_crime_2021.csv']\n"
     ]
    }
   ],
   "source": [
    "path = \"./data_raw\"\n",
    "\n",
    "files = [x for x in os.listdir(path = path) if \".csv\" in x]\n",
    "\n",
    "print(files)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>ID</th>\n",
       "      <th>Case Number</th>\n",
       "      <th>Date</th>\n",
       "      <th>Block</th>\n",
       "      <th>IUCR</th>\n",
       "      <th>Primary Type</th>\n",
       "      <th>Description</th>\n",
       "      <th>Location Description</th>\n",
       "      <th>Arrest</th>\n",
       "      <th>Domestic</th>\n",
       "      <th>...</th>\n",
       "      <th>Ward</th>\n",
       "      <th>Community Area</th>\n",
       "      <th>FBI Code</th>\n",
       "      <th>X Coordinate</th>\n",
       "      <th>Y Coordinate</th>\n",
       "      <th>Year</th>\n",
       "      <th>Updated On</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12589893</td>\n",
       "      <td>JF109865</td>\n",
       "      <td>01/11/2022 03:00:00 PM</td>\n",
       "      <td>087XX S KINGSTON AVE</td>\n",
       "      <td>1565</td>\n",
       "      <td>SEX OFFENSE</td>\n",
       "      <td>INDECENT SOLICITATION OF A CHILD</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>46</td>\n",
       "      <td>17</td>\n",
       "      <td>1194660.0</td>\n",
       "      <td>1847481.0</td>\n",
       "      <td>2022</td>\n",
       "      <td>09/14/2023 03:41:59 PM</td>\n",
       "      <td>41.736409</td>\n",
       "      <td>-87.562410</td>\n",
       "      <td>(41.736409029, -87.562410309)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12592454</td>\n",
       "      <td>JF113025</td>\n",
       "      <td>01/14/2022 03:55:00 PM</td>\n",
       "      <td>067XX S MORGAN ST</td>\n",
       "      <td>2826</td>\n",
       "      <td>OTHER OFFENSE</td>\n",
       "      <td>HARASSMENT BY ELECTRONIC MEANS</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>16.0</td>\n",
       "      <td>68</td>\n",
       "      <td>26</td>\n",
       "      <td>1170805.0</td>\n",
       "      <td>1860170.0</td>\n",
       "      <td>2022</td>\n",
       "      <td>09/14/2023 03:41:59 PM</td>\n",
       "      <td>41.771782</td>\n",
       "      <td>-87.649437</td>\n",
       "      <td>(41.771782439, -87.649436929)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12601676</td>\n",
       "      <td>JF124024</td>\n",
       "      <td>01/13/2022 04:00:00 PM</td>\n",
       "      <td>031XX W AUGUSTA BLVD</td>\n",
       "      <td>1752</td>\n",
       "      <td>OFFENSE INVOLVING CHILDREN</td>\n",
       "      <td>AGGRAVATED CRIMINAL SEXUAL ABUSE BY FAMILY MEMBER</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>36.0</td>\n",
       "      <td>23</td>\n",
       "      <td>17</td>\n",
       "      <td>1155171.0</td>\n",
       "      <td>1906486.0</td>\n",
       "      <td>2022</td>\n",
       "      <td>09/14/2023 03:41:59 PM</td>\n",
       "      <td>41.899206</td>\n",
       "      <td>-87.705506</td>\n",
       "      <td>(41.899206068, -87.705505587)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12785595</td>\n",
       "      <td>JF346553</td>\n",
       "      <td>08/05/2022 09:00:00 PM</td>\n",
       "      <td>072XX S UNIVERSITY AVE</td>\n",
       "      <td>1544</td>\n",
       "      <td>SEX OFFENSE</td>\n",
       "      <td>SEXUAL EXPLOITATION OF A CHILD</td>\n",
       "      <td>APARTMENT</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>69</td>\n",
       "      <td>17</td>\n",
       "      <td>1185135.0</td>\n",
       "      <td>1857211.0</td>\n",
       "      <td>2022</td>\n",
       "      <td>09/14/2023 03:41:59 PM</td>\n",
       "      <td>41.763338</td>\n",
       "      <td>-87.597001</td>\n",
       "      <td>(41.763337967, -87.597001131)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12808281</td>\n",
       "      <td>JF373517</td>\n",
       "      <td>08/14/2022 02:00:00 PM</td>\n",
       "      <td>055XX W ARDMORE AVE</td>\n",
       "      <td>1562</td>\n",
       "      <td>SEX OFFENSE</td>\n",
       "      <td>AGGRAVATED CRIMINAL SEXUAL ABUSE</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>39.0</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>1138383.0</td>\n",
       "      <td>1937953.0</td>\n",
       "      <td>2022</td>\n",
       "      <td>09/14/2023 03:41:59 PM</td>\n",
       "      <td>41.985875</td>\n",
       "      <td>-87.766404</td>\n",
       "      <td>(41.985875279, -87.766403857)</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",
       "      <td>...</td>\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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>648826</th>\n",
       "      <td>26461</td>\n",
       "      <td>JE455267</td>\n",
       "      <td>11/24/2021 12:51:00 AM</td>\n",
       "      <td>107XX S LANGLEY AVE</td>\n",
       "      <td>0110</td>\n",
       "      <td>HOMICIDE</td>\n",
       "      <td>FIRST DEGREE MURDER</td>\n",
       "      <td>VACANT LOT</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>9.0</td>\n",
       "      <td>50</td>\n",
       "      <td>01A</td>\n",
       "      <td>1182822.0</td>\n",
       "      <td>1833730.0</td>\n",
       "      <td>2021</td>\n",
       "      <td>09/19/2022 03:41:05 PM</td>\n",
       "      <td>41.698957</td>\n",
       "      <td>-87.606206</td>\n",
       "      <td>(41.698957409, -87.606205674)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>648827</th>\n",
       "      <td>26041</td>\n",
       "      <td>JE281927</td>\n",
       "      <td>06/28/2021 01:12:00 AM</td>\n",
       "      <td>117XX S LAFLIN ST</td>\n",
       "      <td>0110</td>\n",
       "      <td>HOMICIDE</td>\n",
       "      <td>FIRST DEGREE MURDER</td>\n",
       "      <td>AUTO</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>34.0</td>\n",
       "      <td>53</td>\n",
       "      <td>01A</td>\n",
       "      <td>1168442.0</td>\n",
       "      <td>1826982.0</td>\n",
       "      <td>2021</td>\n",
       "      <td>09/01/2022 03:42:17 PM</td>\n",
       "      <td>41.680761</td>\n",
       "      <td>-87.659052</td>\n",
       "      <td>(41.680760863, -87.659051873)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>648828</th>\n",
       "      <td>26238</td>\n",
       "      <td>JE353715</td>\n",
       "      <td>08/29/2021 03:07:00 AM</td>\n",
       "      <td>010XX N LAWNDALE AVE</td>\n",
       "      <td>0110</td>\n",
       "      <td>HOMICIDE</td>\n",
       "      <td>FIRST DEGREE MURDER</td>\n",
       "      <td>STREET</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>27.0</td>\n",
       "      <td>23</td>\n",
       "      <td>01A</td>\n",
       "      <td>1151525.0</td>\n",
       "      <td>1906643.0</td>\n",
       "      <td>2021</td>\n",
       "      <td>09/19/2022 03:41:05 PM</td>\n",
       "      <td>41.899709</td>\n",
       "      <td>-87.718893</td>\n",
       "      <td>(41.899709327, -87.718893208)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>648829</th>\n",
       "      <td>26479</td>\n",
       "      <td>JE465230</td>\n",
       "      <td>12/03/2021 08:37:00 PM</td>\n",
       "      <td>000XX W 78TH PL</td>\n",
       "      <td>0110</td>\n",
       "      <td>HOMICIDE</td>\n",
       "      <td>FIRST DEGREE MURDER</td>\n",
       "      <td>PORCH</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>69</td>\n",
       "      <td>01A</td>\n",
       "      <td>1177156.0</td>\n",
       "      <td>1852951.0</td>\n",
       "      <td>2021</td>\n",
       "      <td>09/01/2022 03:42:17 PM</td>\n",
       "      <td>41.751832</td>\n",
       "      <td>-87.626374</td>\n",
       "      <td>(41.751831742, -87.626373808)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>648830</th>\n",
       "      <td>11138622</td>\n",
       "      <td>JA495186</td>\n",
       "      <td>05/21/2021 12:01:00 AM</td>\n",
       "      <td>019XX N PULASKI RD</td>\n",
       "      <td>1752</td>\n",
       "      <td>OFFENSE INVOLVING CHILDREN</td>\n",
       "      <td>AGGRAVATED CRIMINAL SEXUAL ABUSE BY FAMILY MEMBER</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>35.0</td>\n",
       "      <td>20</td>\n",
       "      <td>17</td>\n",
       "      <td>1149406.0</td>\n",
       "      <td>1912491.0</td>\n",
       "      <td>2021</td>\n",
       "      <td>09/07/2021 03:41:02 PM</td>\n",
       "      <td>41.915798</td>\n",
       "      <td>-87.726524</td>\n",
       "      <td>(41.915798196, -87.726524412)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>648831 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              ID Case Number                    Date                   Block   \n",
       "0       12589893    JF109865  01/11/2022 03:00:00 PM    087XX S KINGSTON AVE  \\\n",
       "1       12592454    JF113025  01/14/2022 03:55:00 PM       067XX S MORGAN ST   \n",
       "2       12601676    JF124024  01/13/2022 04:00:00 PM    031XX W AUGUSTA BLVD   \n",
       "3       12785595    JF346553  08/05/2022 09:00:00 PM  072XX S UNIVERSITY AVE   \n",
       "4       12808281    JF373517  08/14/2022 02:00:00 PM     055XX W ARDMORE AVE   \n",
       "...          ...         ...                     ...                     ...   \n",
       "648826     26461    JE455267  11/24/2021 12:51:00 AM     107XX S LANGLEY AVE   \n",
       "648827     26041    JE281927  06/28/2021 01:12:00 AM       117XX S LAFLIN ST   \n",
       "648828     26238    JE353715  08/29/2021 03:07:00 AM    010XX N LAWNDALE AVE   \n",
       "648829     26479    JE465230  12/03/2021 08:37:00 PM         000XX W 78TH PL   \n",
       "648830  11138622    JA495186  05/21/2021 12:01:00 AM      019XX N PULASKI RD   \n",
       "\n",
       "        IUCR                Primary Type   \n",
       "0       1565                 SEX OFFENSE  \\\n",
       "1       2826               OTHER OFFENSE   \n",
       "2       1752  OFFENSE INVOLVING CHILDREN   \n",
       "3       1544                 SEX OFFENSE   \n",
       "4       1562                 SEX OFFENSE   \n",
       "...      ...                         ...   \n",
       "648826  0110                    HOMICIDE   \n",
       "648827  0110                    HOMICIDE   \n",
       "648828  0110                    HOMICIDE   \n",
       "648829  0110                    HOMICIDE   \n",
       "648830  1752  OFFENSE INVOLVING CHILDREN   \n",
       "\n",
       "                                              Description   \n",
       "0                        INDECENT SOLICITATION OF A CHILD  \\\n",
       "1                          HARASSMENT BY ELECTRONIC MEANS   \n",
       "2       AGGRAVATED CRIMINAL SEXUAL ABUSE BY FAMILY MEMBER   \n",
       "3                          SEXUAL EXPLOITATION OF A CHILD   \n",
       "4                        AGGRAVATED CRIMINAL SEXUAL ABUSE   \n",
       "...                                                   ...   \n",
       "648826                                FIRST DEGREE MURDER   \n",
       "648827                                FIRST DEGREE MURDER   \n",
       "648828                                FIRST DEGREE MURDER   \n",
       "648829                                FIRST DEGREE MURDER   \n",
       "648830  AGGRAVATED CRIMINAL SEXUAL ABUSE BY FAMILY MEMBER   \n",
       "\n",
       "       Location Description  Arrest  Domestic  ...  Ward  Community Area   \n",
       "0                 RESIDENCE   False      True  ...   7.0              46  \\\n",
       "1                 RESIDENCE   False      True  ...  16.0              68   \n",
       "2                 RESIDENCE   False      True  ...  36.0              23   \n",
       "3                 APARTMENT    True     False  ...   5.0              69   \n",
       "4                 RESIDENCE   False     False  ...  39.0              11   \n",
       "...                     ...     ...       ...  ...   ...             ...   \n",
       "648826           VACANT LOT   False     False  ...   9.0              50   \n",
       "648827                 AUTO   False     False  ...  34.0              53   \n",
       "648828               STREET   False     False  ...  27.0              23   \n",
       "648829                PORCH    True     False  ...   6.0              69   \n",
       "648830            RESIDENCE   False      True  ...  35.0              20   \n",
       "\n",
       "        FBI Code  X Coordinate Y Coordinate  Year              Updated On   \n",
       "0             17     1194660.0    1847481.0  2022  09/14/2023 03:41:59 PM  \\\n",
       "1             26     1170805.0    1860170.0  2022  09/14/2023 03:41:59 PM   \n",
       "2             17     1155171.0    1906486.0  2022  09/14/2023 03:41:59 PM   \n",
       "3             17     1185135.0    1857211.0  2022  09/14/2023 03:41:59 PM   \n",
       "4             17     1138383.0    1937953.0  2022  09/14/2023 03:41:59 PM   \n",
       "...          ...           ...          ...   ...                     ...   \n",
       "648826       01A     1182822.0    1833730.0  2021  09/19/2022 03:41:05 PM   \n",
       "648827       01A     1168442.0    1826982.0  2021  09/01/2022 03:42:17 PM   \n",
       "648828       01A     1151525.0    1906643.0  2021  09/19/2022 03:41:05 PM   \n",
       "648829       01A     1177156.0    1852951.0  2021  09/01/2022 03:42:17 PM   \n",
       "648830        17     1149406.0    1912491.0  2021  09/07/2021 03:41:02 PM   \n",
       "\n",
       "         Latitude  Longitude                       Location  \n",
       "0       41.736409 -87.562410  (41.736409029, -87.562410309)  \n",
       "1       41.771782 -87.649437  (41.771782439, -87.649436929)  \n",
       "2       41.899206 -87.705506  (41.899206068, -87.705505587)  \n",
       "3       41.763338 -87.597001  (41.763337967, -87.597001131)  \n",
       "4       41.985875 -87.766404  (41.985875279, -87.766403857)  \n",
       "...           ...        ...                            ...  \n",
       "648826  41.698957 -87.606206  (41.698957409, -87.606205674)  \n",
       "648827  41.680761 -87.659052  (41.680760863, -87.659051873)  \n",
       "648828  41.899709 -87.718893  (41.899709327, -87.718893208)  \n",
       "648829  41.751832 -87.626374  (41.751831742, -87.626373808)  \n",
       "648830  41.915798 -87.726524  (41.915798196, -87.726524412)  \n",
       "\n",
       "[648831 rows x 22 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago_crime = pd.concat((pd.read_csv(path +\"/\" + f) for f in files), ignore_index=True)\n",
    "\n",
    "chicago_crime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Source: https://platform.openai.com/examples/default-sql-translate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "prompt_template = \"\"\"\n",
    "\n",
    "Given the following SQL table, your job is to write queries given a user’s request. \\n\n",
    "\n",
    "CREATE TABLE {} ({}) \\n\n",
    "\n",
    "Write a SQL query that returns - {}\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sql_prompt_generator(table_name, col_names, query):\n",
    "    prompt = prompt_template.format(table_name, col_names, query)\n",
    "    return prompt\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "Given the following SQL table, your job is to write queries given a user’s request. \n",
      "\n",
      "\n",
      "CREATE TABLE chicago_crime ('ID', 'Case Number', 'Date', 'Block', 'IUCR', 'Primary Type', 'Description', 'Location Description', 'Arrest', 'Domestic', 'Beat', 'District', 'Ward', 'Community Area', 'FBI Code', 'X Coordinate', 'Y Coordinate', 'Year', 'Updated On', 'Latitude', 'Longitude', 'Location') \n",
      "\n",
      "\n",
      "Write a SQL query that returns - How many cases are from the year 2023?\n",
      "\n"
     ]
    }
   ],
   "source": [
    "table = \"chicago_crime\"\n",
    "col_names = str(list(chicago_crime.columns)).replace('[', '').replace(']', '')\n",
    "query = \"How many cases are from the year 2023?\"\n",
    "\n",
    "p = sql_prompt_generator(table_name = table, col_names = col_names, query = query)\n",
    "\n",
    "print(p)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "openai.api_key = os.getenv('OPENAI_KEY')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>id</th>\n",
       "      <th>object</th>\n",
       "      <th>created</th>\n",
       "      <th>owned_by</th>\n",
       "      <th>permission</th>\n",
       "      <th>root</th>\n",
       "      <th>parent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>text-search-babbage-doc-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172509</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-s9n5HnzbtVn7kNc5TIZWiCFS', ...</td>\n",
       "      <td>text-search-babbage-doc-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gpt-3.5-turbo-16k-0613</td>\n",
       "      <td>model</td>\n",
       "      <td>1685474247</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-6TvrqdxTZPTKF0DrBbCYP3sh', ...</td>\n",
       "      <td>gpt-3.5-turbo-16k-0613</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>curie-search-query</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172509</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-8aqdyZaKtD3MD831mGbqh1MD', ...</td>\n",
       "      <td>curie-search-query</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>gpt-3.5-turbo-16k</td>\n",
       "      <td>model</td>\n",
       "      <td>1683758102</td>\n",
       "      <td>openai-internal</td>\n",
       "      <td>[{'id': 'modelperm-UTqphsmnPe8SNK52msfOjBhP', ...</td>\n",
       "      <td>gpt-3.5-turbo-16k</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>text-search-babbage-query-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172509</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-hXsRH2IK0hXmWxmLRiNTp70t', ...</td>\n",
       "      <td>text-search-babbage-query-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>babbage</td>\n",
       "      <td>model</td>\n",
       "      <td>1649358449</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-h574xGeqWyBeFDDKaoVTC4CO', ...</td>\n",
       "      <td>babbage</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>babbage-search-query</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172509</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-1zMLcaRlTvYAdpmvvixnTWlF', ...</td>\n",
       "      <td>babbage-search-query</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>text-babbage-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1649364043</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-YABzYWjC1kS6M2BnI6Fr9vuS', ...</td>\n",
       "      <td>text-babbage-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>text-similarity-davinci-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172505</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-C6TT4mQR3bJQEzEuiZlhKM5u', ...</td>\n",
       "      <td>text-similarity-davinci-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>davinci</td>\n",
       "      <td>model</td>\n",
       "      <td>1649359874</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-RcfCH2MkO5NP9C7wx6kdYnIT', ...</td>\n",
       "      <td>davinci</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>davinci-similarity</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172509</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-OtRWxI0nRtN9q8mHI3OOk0GT', ...</td>\n",
       "      <td>davinci-similarity</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>code-davinci-edit-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1649880484</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-T8Ie7SvlPyvtsDvPlfC8DftZ', ...</td>\n",
       "      <td>code-davinci-edit-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>curie-similarity</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172510</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-9PfIlYDKOt24EV6fnCiunZGA', ...</td>\n",
       "      <td>curie-similarity</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>babbage-search-document</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172510</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-qJ0Iu5XcwrdsmSOn9ewphBNF', ...</td>\n",
       "      <td>babbage-search-document</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>curie-instruct-beta</td>\n",
       "      <td>model</td>\n",
       "      <td>1649364042</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-rTxpdy2DwwUp38frYQFsj5OC', ...</td>\n",
       "      <td>curie-instruct-beta</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>text-search-ada-doc-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172507</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-tdL3cX2rMgQuyfQwqNGcQOp3', ...</td>\n",
       "      <td>text-search-ada-doc-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>davinci-instruct-beta</td>\n",
       "      <td>model</td>\n",
       "      <td>1649364042</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-SKEW42qz05X43sqLXFwhbAox', ...</td>\n",
       "      <td>davinci-instruct-beta</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>gpt-4</td>\n",
       "      <td>model</td>\n",
       "      <td>1687882411</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-OO7KOdfYKac98TMlgT8IGLB0', ...</td>\n",
       "      <td>gpt-4</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>text-similarity-babbage-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172505</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-yNsfFUtfAoDhJHRofgD2Kjgd', ...</td>\n",
       "      <td>text-similarity-babbage-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>text-search-davinci-doc-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172505</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-I0hY9ySAeJrE3qBF47roClh9', ...</td>\n",
       "      <td>text-search-davinci-doc-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>gpt-4-0314</td>\n",
       "      <td>model</td>\n",
       "      <td>1687882410</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-HBZvtylKdNFeA3CNLyWmo0au', ...</td>\n",
       "      <td>gpt-4-0314</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>babbage-similarity</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172505</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-8Inp4vh9P5Mh0llHmSX8Va3b', ...</td>\n",
       "      <td>babbage-similarity</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>davinci-search-query</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172505</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-ZNL7KVorrZk5pvj7v3cG3lUz', ...</td>\n",
       "      <td>davinci-search-query</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>text-similarity-curie-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172507</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-J2U414W9ZcjxYqf13tZdvwI5', ...</td>\n",
       "      <td>text-similarity-curie-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>text-davinci-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1649364042</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-bwpDudS41UaZskP1jKfAHKMG', ...</td>\n",
       "      <td>text-davinci-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>text-search-davinci-query-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172505</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-lEI8RY9UBNhXYJRKub8U4Sc1', ...</td>\n",
       "      <td>text-search-davinci-query-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>ada-search-document</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172507</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-CSgBXBOV02tB05EbOK36apFu', ...</td>\n",
       "      <td>ada-search-document</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>ada-code-search-code</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172505</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-NFpO3yF5OZ9bfwls2MSUn5cR', ...</td>\n",
       "      <td>ada-code-search-code</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>babbage-002</td>\n",
       "      <td>model</td>\n",
       "      <td>1692634615</td>\n",
       "      <td>system</td>\n",
       "      <td>[{'id': 'modelperm-tC8uL3ohDEVSzUvcgZCv33gV', ...</td>\n",
       "      <td>babbage-002</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>davinci-002</td>\n",
       "      <td>model</td>\n",
       "      <td>1692634301</td>\n",
       "      <td>system</td>\n",
       "      <td>[{'id': 'modelperm-9a6yBfznsbObxHVWBWRJctsx', ...</td>\n",
       "      <td>davinci-002</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>davinci-search-document</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172509</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-Bbb0MBYeWROhvx9NkaDbZg3R', ...</td>\n",
       "      <td>davinci-search-document</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>curie-search-document</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172508</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-xkoHDxWoBlLQj4HM4iVwWj9C', ...</td>\n",
       "      <td>curie-search-document</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>babbage-code-search-code</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172509</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-5Dv3knU1yoRtpY5TFzSHySiM', ...</td>\n",
       "      <td>babbage-code-search-code</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>text-search-ada-query-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172505</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-2XH9mphBxBD5uKyXKn8o77sE', ...</td>\n",
       "      <td>text-search-ada-query-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>code-search-ada-text-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172507</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-MQOFciXDRywliXymhKJT5rUy', ...</td>\n",
       "      <td>code-search-ada-text-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>babbage-code-search-text</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172509</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-DE4WMTkago3EHJ0n1p2urLkw', ...</td>\n",
       "      <td>babbage-code-search-text</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>code-search-babbage-code-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172507</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-xuS8vuaSaa5jIQSmyP0S35Qd', ...</td>\n",
       "      <td>code-search-babbage-code-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>ada-search-query</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172505</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-kSLKiCrDbosTMvZnIt5wuEqE', ...</td>\n",
       "      <td>ada-search-query</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>ada-code-search-text</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172510</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-Ya8bDcPgbFbqFR0K44jdPN64', ...</td>\n",
       "      <td>ada-code-search-text</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>text-search-curie-query-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172509</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-7Y7YYZkPezf96wy8j5bphjjg', ...</td>\n",
       "      <td>text-search-curie-query-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>text-davinci-002</td>\n",
       "      <td>model</td>\n",
       "      <td>1649880484</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-eKMXF4rV1slbvrpgmDbTm3wD', ...</td>\n",
       "      <td>text-davinci-002</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>text-embedding-ada-002</td>\n",
       "      <td>model</td>\n",
       "      <td>1671217299</td>\n",
       "      <td>openai-internal</td>\n",
       "      <td>[{'id': 'modelperm-iHRhxPwYPqj6AJE3PFzga9rD', ...</td>\n",
       "      <td>text-embedding-ada-002</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>text-davinci-edit-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1649809179</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-bwEWUtGiBcdX0p1D1ayafH8w', ...</td>\n",
       "      <td>text-davinci-edit-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>code-search-babbage-text-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172507</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-JkzgCs0oPZaC1uCfO0n0ulYX', ...</td>\n",
       "      <td>code-search-babbage-text-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>gpt-3.5-turbo-instruct-0914</td>\n",
       "      <td>model</td>\n",
       "      <td>1694122472</td>\n",
       "      <td>system</td>\n",
       "      <td>[{'id': 'modelperm-kMX0MSKsmmMHNQaGtK0ID5Nr', ...</td>\n",
       "      <td>gpt-3.5-turbo-instruct-0914</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>ada</td>\n",
       "      <td>model</td>\n",
       "      <td>1649357491</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-0JnkYlxDvw3kMS1mht3cmQKR', ...</td>\n",
       "      <td>ada</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>text-ada-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1649364042</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-jRuB7xBCdj159SqaDmpPgeWO', ...</td>\n",
       "      <td>text-ada-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>ada-similarity</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172507</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-z4NZ5sCV2GsxBeNqzcNVhEvH', ...</td>\n",
       "      <td>ada-similarity</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>code-search-ada-code-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172507</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-9k7jVGlzTgB52nza3Po7ENUZ', ...</td>\n",
       "      <td>code-search-ada-code-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>text-similarity-ada-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172505</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-2l92GeLtllKOhDdWb6RzzfEN', ...</td>\n",
       "      <td>text-similarity-ada-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>gpt-3.5-turbo-0301</td>\n",
       "      <td>model</td>\n",
       "      <td>1677649963</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-I4IcSJFYZl2fIK0DPSBkgK3d', ...</td>\n",
       "      <td>gpt-3.5-turbo-0301</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>gpt-3.5-turbo-instruct</td>\n",
       "      <td>model</td>\n",
       "      <td>1692901427</td>\n",
       "      <td>system</td>\n",
       "      <td>[{'id': 'modelperm-UK4BVstQKYTKaTj3zbCsKCGx', ...</td>\n",
       "      <td>gpt-3.5-turbo-instruct</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>gpt-3.5-turbo-0613</td>\n",
       "      <td>model</td>\n",
       "      <td>1686587434</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-adJzC6xjJvkl3yGMI6bP35gK', ...</td>\n",
       "      <td>gpt-3.5-turbo-0613</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>gpt-3.5-turbo</td>\n",
       "      <td>model</td>\n",
       "      <td>1677610602</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-1kwP3lPiCrHc9qOYigLjXOH6', ...</td>\n",
       "      <td>gpt-3.5-turbo</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>text-search-curie-doc-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1651172509</td>\n",
       "      <td>openai-dev</td>\n",
       "      <td>[{'id': 'modelperm-gMoCj5Vfel451hGbAp0kfKMH', ...</td>\n",
       "      <td>text-search-curie-doc-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>whisper-1</td>\n",
       "      <td>model</td>\n",
       "      <td>1677532384</td>\n",
       "      <td>openai-internal</td>\n",
       "      <td>[{'id': 'modelperm-Gcy5Sou5w0KPpqxlW6BihGxk', ...</td>\n",
       "      <td>whisper-1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>text-davinci-003</td>\n",
       "      <td>model</td>\n",
       "      <td>1669599635</td>\n",
       "      <td>openai-internal</td>\n",
       "      <td>[{'id': 'modelperm-OLYlW6I3e7VURS85ytehLxKj', ...</td>\n",
       "      <td>text-davinci-003</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>text-curie-001</td>\n",
       "      <td>model</td>\n",
       "      <td>1649364043</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-3D7myCVeMXS00PJdPESttrwj', ...</td>\n",
       "      <td>text-curie-001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>curie</td>\n",
       "      <td>model</td>\n",
       "      <td>1649359874</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-FwPnxb7Y7YHn9eMwTNgQyiJ4', ...</td>\n",
       "      <td>curie</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>gpt-4-0613</td>\n",
       "      <td>model</td>\n",
       "      <td>1686588896</td>\n",
       "      <td>openai</td>\n",
       "      <td>[{'id': 'modelperm-VV6bd8m0ChGEoYt7lIwWwRbv', ...</td>\n",
       "      <td>gpt-4-0613</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               id object     created         owned_by   \n",
       "0     text-search-babbage-doc-001  model  1651172509       openai-dev  \\\n",
       "1          gpt-3.5-turbo-16k-0613  model  1685474247           openai   \n",
       "2              curie-search-query  model  1651172509       openai-dev   \n",
       "3               gpt-3.5-turbo-16k  model  1683758102  openai-internal   \n",
       "4   text-search-babbage-query-001  model  1651172509       openai-dev   \n",
       "5                         babbage  model  1649358449           openai   \n",
       "6            babbage-search-query  model  1651172509       openai-dev   \n",
       "7                text-babbage-001  model  1649364043           openai   \n",
       "8     text-similarity-davinci-001  model  1651172505       openai-dev   \n",
       "9                         davinci  model  1649359874           openai   \n",
       "10             davinci-similarity  model  1651172509       openai-dev   \n",
       "11          code-davinci-edit-001  model  1649880484           openai   \n",
       "12               curie-similarity  model  1651172510       openai-dev   \n",
       "13        babbage-search-document  model  1651172510       openai-dev   \n",
       "14            curie-instruct-beta  model  1649364042           openai   \n",
       "15        text-search-ada-doc-001  model  1651172507       openai-dev   \n",
       "16          davinci-instruct-beta  model  1649364042           openai   \n",
       "17                          gpt-4  model  1687882411           openai   \n",
       "18    text-similarity-babbage-001  model  1651172505       openai-dev   \n",
       "19    text-search-davinci-doc-001  model  1651172505       openai-dev   \n",
       "20                     gpt-4-0314  model  1687882410           openai   \n",
       "21             babbage-similarity  model  1651172505       openai-dev   \n",
       "22           davinci-search-query  model  1651172505       openai-dev   \n",
       "23      text-similarity-curie-001  model  1651172507       openai-dev   \n",
       "24               text-davinci-001  model  1649364042           openai   \n",
       "25  text-search-davinci-query-001  model  1651172505       openai-dev   \n",
       "26            ada-search-document  model  1651172507       openai-dev   \n",
       "27           ada-code-search-code  model  1651172505       openai-dev   \n",
       "28                    babbage-002  model  1692634615           system   \n",
       "29                    davinci-002  model  1692634301           system   \n",
       "30        davinci-search-document  model  1651172509       openai-dev   \n",
       "31          curie-search-document  model  1651172508       openai-dev   \n",
       "32       babbage-code-search-code  model  1651172509       openai-dev   \n",
       "33      text-search-ada-query-001  model  1651172505       openai-dev   \n",
       "34       code-search-ada-text-001  model  1651172507       openai-dev   \n",
       "35       babbage-code-search-text  model  1651172509       openai-dev   \n",
       "36   code-search-babbage-code-001  model  1651172507       openai-dev   \n",
       "37               ada-search-query  model  1651172505       openai-dev   \n",
       "38           ada-code-search-text  model  1651172510       openai-dev   \n",
       "39    text-search-curie-query-001  model  1651172509       openai-dev   \n",
       "40               text-davinci-002  model  1649880484           openai   \n",
       "41         text-embedding-ada-002  model  1671217299  openai-internal   \n",
       "42          text-davinci-edit-001  model  1649809179           openai   \n",
       "43   code-search-babbage-text-001  model  1651172507       openai-dev   \n",
       "44    gpt-3.5-turbo-instruct-0914  model  1694122472           system   \n",
       "45                            ada  model  1649357491           openai   \n",
       "46                   text-ada-001  model  1649364042           openai   \n",
       "47                 ada-similarity  model  1651172507       openai-dev   \n",
       "48       code-search-ada-code-001  model  1651172507       openai-dev   \n",
       "49        text-similarity-ada-001  model  1651172505       openai-dev   \n",
       "50             gpt-3.5-turbo-0301  model  1677649963           openai   \n",
       "51         gpt-3.5-turbo-instruct  model  1692901427           system   \n",
       "52             gpt-3.5-turbo-0613  model  1686587434           openai   \n",
       "53                  gpt-3.5-turbo  model  1677610602           openai   \n",
       "54      text-search-curie-doc-001  model  1651172509       openai-dev   \n",
       "55                      whisper-1  model  1677532384  openai-internal   \n",
       "56               text-davinci-003  model  1669599635  openai-internal   \n",
       "57                 text-curie-001  model  1649364043           openai   \n",
       "58                          curie  model  1649359874           openai   \n",
       "59                     gpt-4-0613  model  1686588896           openai   \n",
       "\n",
       "                                           permission   \n",
       "0   [{'id': 'modelperm-s9n5HnzbtVn7kNc5TIZWiCFS', ...  \\\n",
       "1   [{'id': 'modelperm-6TvrqdxTZPTKF0DrBbCYP3sh', ...   \n",
       "2   [{'id': 'modelperm-8aqdyZaKtD3MD831mGbqh1MD', ...   \n",
       "3   [{'id': 'modelperm-UTqphsmnPe8SNK52msfOjBhP', ...   \n",
       "4   [{'id': 'modelperm-hXsRH2IK0hXmWxmLRiNTp70t', ...   \n",
       "5   [{'id': 'modelperm-h574xGeqWyBeFDDKaoVTC4CO', ...   \n",
       "6   [{'id': 'modelperm-1zMLcaRlTvYAdpmvvixnTWlF', ...   \n",
       "7   [{'id': 'modelperm-YABzYWjC1kS6M2BnI6Fr9vuS', ...   \n",
       "8   [{'id': 'modelperm-C6TT4mQR3bJQEzEuiZlhKM5u', ...   \n",
       "9   [{'id': 'modelperm-RcfCH2MkO5NP9C7wx6kdYnIT', ...   \n",
       "10  [{'id': 'modelperm-OtRWxI0nRtN9q8mHI3OOk0GT', ...   \n",
       "11  [{'id': 'modelperm-T8Ie7SvlPyvtsDvPlfC8DftZ', ...   \n",
       "12  [{'id': 'modelperm-9PfIlYDKOt24EV6fnCiunZGA', ...   \n",
       "13  [{'id': 'modelperm-qJ0Iu5XcwrdsmSOn9ewphBNF', ...   \n",
       "14  [{'id': 'modelperm-rTxpdy2DwwUp38frYQFsj5OC', ...   \n",
       "15  [{'id': 'modelperm-tdL3cX2rMgQuyfQwqNGcQOp3', ...   \n",
       "16  [{'id': 'modelperm-SKEW42qz05X43sqLXFwhbAox', ...   \n",
       "17  [{'id': 'modelperm-OO7KOdfYKac98TMlgT8IGLB0', ...   \n",
       "18  [{'id': 'modelperm-yNsfFUtfAoDhJHRofgD2Kjgd', ...   \n",
       "19  [{'id': 'modelperm-I0hY9ySAeJrE3qBF47roClh9', ...   \n",
       "20  [{'id': 'modelperm-HBZvtylKdNFeA3CNLyWmo0au', ...   \n",
       "21  [{'id': 'modelperm-8Inp4vh9P5Mh0llHmSX8Va3b', ...   \n",
       "22  [{'id': 'modelperm-ZNL7KVorrZk5pvj7v3cG3lUz', ...   \n",
       "23  [{'id': 'modelperm-J2U414W9ZcjxYqf13tZdvwI5', ...   \n",
       "24  [{'id': 'modelperm-bwpDudS41UaZskP1jKfAHKMG', ...   \n",
       "25  [{'id': 'modelperm-lEI8RY9UBNhXYJRKub8U4Sc1', ...   \n",
       "26  [{'id': 'modelperm-CSgBXBOV02tB05EbOK36apFu', ...   \n",
       "27  [{'id': 'modelperm-NFpO3yF5OZ9bfwls2MSUn5cR', ...   \n",
       "28  [{'id': 'modelperm-tC8uL3ohDEVSzUvcgZCv33gV', ...   \n",
       "29  [{'id': 'modelperm-9a6yBfznsbObxHVWBWRJctsx', ...   \n",
       "30  [{'id': 'modelperm-Bbb0MBYeWROhvx9NkaDbZg3R', ...   \n",
       "31  [{'id': 'modelperm-xkoHDxWoBlLQj4HM4iVwWj9C', ...   \n",
       "32  [{'id': 'modelperm-5Dv3knU1yoRtpY5TFzSHySiM', ...   \n",
       "33  [{'id': 'modelperm-2XH9mphBxBD5uKyXKn8o77sE', ...   \n",
       "34  [{'id': 'modelperm-MQOFciXDRywliXymhKJT5rUy', ...   \n",
       "35  [{'id': 'modelperm-DE4WMTkago3EHJ0n1p2urLkw', ...   \n",
       "36  [{'id': 'modelperm-xuS8vuaSaa5jIQSmyP0S35Qd', ...   \n",
       "37  [{'id': 'modelperm-kSLKiCrDbosTMvZnIt5wuEqE', ...   \n",
       "38  [{'id': 'modelperm-Ya8bDcPgbFbqFR0K44jdPN64', ...   \n",
       "39  [{'id': 'modelperm-7Y7YYZkPezf96wy8j5bphjjg', ...   \n",
       "40  [{'id': 'modelperm-eKMXF4rV1slbvrpgmDbTm3wD', ...   \n",
       "41  [{'id': 'modelperm-iHRhxPwYPqj6AJE3PFzga9rD', ...   \n",
       "42  [{'id': 'modelperm-bwEWUtGiBcdX0p1D1ayafH8w', ...   \n",
       "43  [{'id': 'modelperm-JkzgCs0oPZaC1uCfO0n0ulYX', ...   \n",
       "44  [{'id': 'modelperm-kMX0MSKsmmMHNQaGtK0ID5Nr', ...   \n",
       "45  [{'id': 'modelperm-0JnkYlxDvw3kMS1mht3cmQKR', ...   \n",
       "46  [{'id': 'modelperm-jRuB7xBCdj159SqaDmpPgeWO', ...   \n",
       "47  [{'id': 'modelperm-z4NZ5sCV2GsxBeNqzcNVhEvH', ...   \n",
       "48  [{'id': 'modelperm-9k7jVGlzTgB52nza3Po7ENUZ', ...   \n",
       "49  [{'id': 'modelperm-2l92GeLtllKOhDdWb6RzzfEN', ...   \n",
       "50  [{'id': 'modelperm-I4IcSJFYZl2fIK0DPSBkgK3d', ...   \n",
       "51  [{'id': 'modelperm-UK4BVstQKYTKaTj3zbCsKCGx', ...   \n",
       "52  [{'id': 'modelperm-adJzC6xjJvkl3yGMI6bP35gK', ...   \n",
       "53  [{'id': 'modelperm-1kwP3lPiCrHc9qOYigLjXOH6', ...   \n",
       "54  [{'id': 'modelperm-gMoCj5Vfel451hGbAp0kfKMH', ...   \n",
       "55  [{'id': 'modelperm-Gcy5Sou5w0KPpqxlW6BihGxk', ...   \n",
       "56  [{'id': 'modelperm-OLYlW6I3e7VURS85ytehLxKj', ...   \n",
       "57  [{'id': 'modelperm-3D7myCVeMXS00PJdPESttrwj', ...   \n",
       "58  [{'id': 'modelperm-FwPnxb7Y7YHn9eMwTNgQyiJ4', ...   \n",
       "59  [{'id': 'modelperm-VV6bd8m0ChGEoYt7lIwWwRbv', ...   \n",
       "\n",
       "                             root parent  \n",
       "0     text-search-babbage-doc-001   None  \n",
       "1          gpt-3.5-turbo-16k-0613   None  \n",
       "2              curie-search-query   None  \n",
       "3               gpt-3.5-turbo-16k   None  \n",
       "4   text-search-babbage-query-001   None  \n",
       "5                         babbage   None  \n",
       "6            babbage-search-query   None  \n",
       "7                text-babbage-001   None  \n",
       "8     text-similarity-davinci-001   None  \n",
       "9                         davinci   None  \n",
       "10             davinci-similarity   None  \n",
       "11          code-davinci-edit-001   None  \n",
       "12               curie-similarity   None  \n",
       "13        babbage-search-document   None  \n",
       "14            curie-instruct-beta   None  \n",
       "15        text-search-ada-doc-001   None  \n",
       "16          davinci-instruct-beta   None  \n",
       "17                          gpt-4   None  \n",
       "18    text-similarity-babbage-001   None  \n",
       "19    text-search-davinci-doc-001   None  \n",
       "20                     gpt-4-0314   None  \n",
       "21             babbage-similarity   None  \n",
       "22           davinci-search-query   None  \n",
       "23      text-similarity-curie-001   None  \n",
       "24               text-davinci-001   None  \n",
       "25  text-search-davinci-query-001   None  \n",
       "26            ada-search-document   None  \n",
       "27           ada-code-search-code   None  \n",
       "28                    babbage-002   None  \n",
       "29                    davinci-002   None  \n",
       "30        davinci-search-document   None  \n",
       "31          curie-search-document   None  \n",
       "32       babbage-code-search-code   None  \n",
       "33      text-search-ada-query-001   None  \n",
       "34       code-search-ada-text-001   None  \n",
       "35       babbage-code-search-text   None  \n",
       "36   code-search-babbage-code-001   None  \n",
       "37               ada-search-query   None  \n",
       "38           ada-code-search-text   None  \n",
       "39    text-search-curie-query-001   None  \n",
       "40               text-davinci-002   None  \n",
       "41         text-embedding-ada-002   None  \n",
       "42          text-davinci-edit-001   None  \n",
       "43   code-search-babbage-text-001   None  \n",
       "44    gpt-3.5-turbo-instruct-0914   None  \n",
       "45                            ada   None  \n",
       "46                   text-ada-001   None  \n",
       "47                 ada-similarity   None  \n",
       "48       code-search-ada-code-001   None  \n",
       "49        text-similarity-ada-001   None  \n",
       "50             gpt-3.5-turbo-0301   None  \n",
       "51         gpt-3.5-turbo-instruct   None  \n",
       "52             gpt-3.5-turbo-0613   None  \n",
       "53                  gpt-3.5-turbo   None  \n",
       "54      text-search-curie-doc-001   None  \n",
       "55                      whisper-1   None  \n",
       "56               text-davinci-003   None  \n",
       "57                 text-curie-001   None  \n",
       "58                          curie   None  \n",
       "59                     gpt-4-0613   None  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(openai.Model.list()[\"data\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "response = openai.Completion.create(engine = \"text-davinci-003\",\n",
    "                                     prompt= p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT COUNT(*) FROM chicago_crime WHERE Year = 2023\n",
      "┌──────────────┐\n",
      "│ count_star() │\n",
      "│    int64     │\n",
      "├──────────────┤\n",
      "│       200932 │\n",
      "└──────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "query = response[\"choices\"][0][\"text\"]\n",
    "print(query)\n",
    "duckdb.sql(query).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"warning\": \"This model version is deprecated. Migrate before January 4, 2024 to avoid disruption of service. Learn more https://platform.openai.com/docs/deprecations\",\n",
      "  \"id\": \"cmpl-8BrE4ukZyon9KZQqcajEPMKnusH26\",\n",
      "  \"object\": \"text_completion\",\n",
      "  \"created\": 1697836900,\n",
      "  \"model\": \"text-davinci-003\",\n",
      "  \"choices\": [\n",
      "    {\n",
      "      \"text\": \"\\nSELECT COUNT(*) FROM chicago_crime WHERE year = 2023\",\n",
      "      \"index\": 0,\n",
      "      \"logprobs\": null,\n",
      "      \"finish_reason\": \"length\"\n",
      "    },\n",
      "    {\n",
      "      \"text\": \"\\nSELECT COUNT(*) \\nFROM chicago_crime \\n\",\n",
      "      \"index\": 1,\n",
      "      \"logprobs\": null,\n",
      "      \"finish_reason\": \"length\"\n",
      "    }\n",
      "  ],\n",
      "  \"usage\": {\n",
      "    \"prompt_tokens\": 133,\n",
      "    \"completion_tokens\": 32,\n",
      "    \"total_tokens\": 165\n",
      "  }\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "response = openai.Completion.create(engine = \"text-davinci-003\",\n",
    "                                     prompt= p,\n",
    "                                     n = 2)\n",
    "print(response)\n",
    "query = response[\"choices\"][0][\"text\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"warning\": \"This model version is deprecated. Migrate before January 4, 2024 to avoid disruption of service. Learn more https://platform.openai.com/docs/deprecations\",\n",
      "  \"id\": \"cmpl-8BrFhvTP7BT3kJoyGbe9lZJUUb5TW\",\n",
      "  \"object\": \"text_completion\",\n",
      "  \"created\": 1697837001,\n",
      "  \"model\": \"text-davinci-003\",\n",
      "  \"choices\": [\n",
      "    {\n",
      "      \"text\": \"\\nSELECT COUNT(*) FROM chicago_crime WHERE Year = 2023\",\n",
      "      \"index\": 0,\n",
      "      \"logprobs\": null,\n",
      "      \"finish_reason\": \"length\"\n",
      "    },\n",
      "    {\n",
      "      \"text\": \"\\nSELECT COUNT(*) FROM chicago_crime WHERE Year = 2023\",\n",
      "      \"index\": 1,\n",
      "      \"logprobs\": null,\n",
      "      \"finish_reason\": \"length\"\n",
      "    }\n",
      "  ],\n",
      "  \"usage\": {\n",
      "    \"prompt_tokens\": 133,\n",
      "    \"completion_tokens\": 32,\n",
      "    \"total_tokens\": 165\n",
      "  }\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "response = openai.Completion.create(engine = \"text-davinci-003\",\n",
    "                                     prompt= p,\n",
    "                                     n = 2,\n",
    "                                     temperature = 0)\n",
    "print(response)\n",
    "query = response[\"choices\"][0][\"text\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"warning\": \"This model version is deprecated. Migrate before January 4, 2024 to avoid disruption of service. Learn more https://platform.openai.com/docs/deprecations\",\n",
      "  \"id\": \"cmpl-8BrGQ7MspIZXBNs9NOdCeyYHHZ4Dg\",\n",
      "  \"object\": \"text_completion\",\n",
      "  \"created\": 1697837046,\n",
      "  \"model\": \"text-davinci-003\",\n",
      "  \"choices\": [\n",
      "    {\n",
      "      \"text\": \"\\nSELECT COUNT('ID') \\nFROM `insert tableing data\",\n",
      "      \"index\": 0,\n",
      "      \"logprobs\": null,\n",
      "      \"finish_reason\": \"length\"\n",
      "    },\n",
      "    {\n",
      "      \"text\": \"\\nSELECT COUNT(*) AS ' Tom Many Cases'\\nFROM ch\",\n",
      "      \"index\": 1,\n",
      "      \"logprobs\": null,\n",
      "      \"finish_reason\": \"length\"\n",
      "    }\n",
      "  ],\n",
      "  \"usage\": {\n",
      "    \"prompt_tokens\": 133,\n",
      "    \"completion_tokens\": 32,\n",
      "    \"total_tokens\": 165\n",
      "  }\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "response = openai.Completion.create(engine = \"text-davinci-003\",\n",
    "                                     prompt= p,\n",
    "                                     n = 2,\n",
    "                                     temperature = 2)\n",
    "print(response)\n",
    "query = response[\"choices\"][0][\"text\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def lang2sql(api_key, table_name, col_names, query, engine = \"text-davinci-003\", n = 1, temperature = 0):\n",
    "    prompt = sql_prompt_generator(table_name = table_name, col_names = col_names, query = query)\n",
    "    \n",
    "    openai.api_key = api_key\n",
    "\n",
    "    response = openai.Completion.create(engine = engine,\n",
    "                                        prompt= prompt,\n",
    "                                        n = n,\n",
    "                                        temperature = temperature)\n",
    "    return response\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_name = \"chicago_crime\"\n",
    "col_names = str(list(chicago_crime.columns)).replace('[', '').replace(']', '')\n",
    "api_key = os.getenv('OPENAI_KEY')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"How many cases are from the year 2023?\"\n",
    "\n",
    "response = lang2sql(api_key = api_key, table_name = table_name, col_names= col_names, query = query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"warning\": \"This model version is deprecated. Migrate before January 4, 2024 to avoid disruption of service. Learn more https://platform.openai.com/docs/deprecations\",\n",
      "  \"id\": \"cmpl-8BrPcluYY1YPMG1hfwCeof3ukaa9G\",\n",
      "  \"object\": \"text_completion\",\n",
      "  \"created\": 1697837616,\n",
      "  \"model\": \"text-davinci-003\",\n",
      "  \"choices\": [\n",
      "    {\n",
      "      \"text\": \"\\nSELECT COUNT(*) FROM chicago_crime WHERE Year = 2023\",\n",
      "      \"index\": 0,\n",
      "      \"logprobs\": null,\n",
      "      \"finish_reason\": \"length\"\n",
      "    }\n",
      "  ],\n",
      "  \"usage\": {\n",
      "    \"prompt_tokens\": 133,\n",
      "    \"completion_tokens\": 16,\n",
      "    \"total_tokens\": 149\n",
      "  }\n",
      "}\n",
      "\n",
      "SELECT COUNT(*) FROM chicago_crime WHERE Year = 2023\n",
      "┌──────────────┐\n",
      "│ count_star() │\n",
      "│    int64     │\n",
      "├──────────────┤\n",
      "│       200932 │\n",
      "└──────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(response)\n",
    "sql_query = response[\"choices\"][0][\"text\"]\n",
    "print(sql_query)\n",
    "\n",
    "duckdb.sql(sql_query).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT COUNT(*) \n",
      "FROM chicago_crime \n",
      "\n"
     ]
    }
   ],
   "source": [
    "query = \"How many cases ended up with arrest?\"\n",
    "\n",
    "response = lang2sql(api_key = api_key, table_name = table_name, col_names= col_names, query = query)\n",
    "sql_query = response[\"choices\"][0][\"text\"]\n",
    "print(sql_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐\n",
       "│     column_name      │ column_type │  null   │   key   │ default │  extra  │\n",
       "│       varchar        │   varchar   │ varchar │ varchar │ varchar │ varchar │\n",
       "├──────────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤\n",
       "│ ID                   │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Case Number          │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Date                 │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Block                │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ IUCR                 │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Primary Type         │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Description          │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Location Description │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Arrest               │ BOOLEAN     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Domestic             │ BOOLEAN     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Beat                 │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ District             │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Ward                 │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Community Area       │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ FBI Code             │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ X Coordinate         │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Y Coordinate         │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Year                 │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Updated On           │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Latitude             │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Longitude            │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "│ Location             │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n",
       "├──────────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤\n",
       "│ 22 rows                                                          6 columns │\n",
       "└────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tbl_describe = duckdb.sql(\"DESCRIBE SELECT * FROM chicago_crime;\")\n",
    "tbl_describe\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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_name</th>\n",
       "      <th>column_type</th>\n",
       "      <th>column_joint</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>ID BIGINT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Case Number</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Case Number VARCHAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Date</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Date VARCHAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Block</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Block VARCHAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>IUCR</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>IUCR VARCHAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Primary Type</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Primary Type VARCHAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Description</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Description VARCHAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Location Description</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Location Description VARCHAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Arrest</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>Arrest BOOLEAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Domestic</td>\n",
       "      <td>BOOLEAN</td>\n",
       "      <td>Domestic BOOLEAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Beat</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>Beat BIGINT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>District</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>District BIGINT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Ward</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>Ward DOUBLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Community Area</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>Community Area BIGINT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>FBI Code</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>FBI Code VARCHAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>X Coordinate</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>X Coordinate DOUBLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Y Coordinate</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>Y Coordinate DOUBLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Year</td>\n",
       "      <td>BIGINT</td>\n",
       "      <td>Year BIGINT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Updated On</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Updated On VARCHAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Latitude</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>Latitude DOUBLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Longitude</td>\n",
       "      <td>DOUBLE</td>\n",
       "      <td>Longitude DOUBLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Location</td>\n",
       "      <td>VARCHAR</td>\n",
       "      <td>Location VARCHAR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             column_name column_type                  column_joint\n",
       "0                     ID      BIGINT                     ID BIGINT\n",
       "1            Case Number     VARCHAR           Case Number VARCHAR\n",
       "2                   Date     VARCHAR                  Date VARCHAR\n",
       "3                  Block     VARCHAR                 Block VARCHAR\n",
       "4                   IUCR     VARCHAR                  IUCR VARCHAR\n",
       "5           Primary Type     VARCHAR          Primary Type VARCHAR\n",
       "6            Description     VARCHAR           Description VARCHAR\n",
       "7   Location Description     VARCHAR  Location Description VARCHAR\n",
       "8                 Arrest     BOOLEAN                Arrest BOOLEAN\n",
       "9               Domestic     BOOLEAN              Domestic BOOLEAN\n",
       "10                  Beat      BIGINT                   Beat BIGINT\n",
       "11              District      BIGINT               District BIGINT\n",
       "12                  Ward      DOUBLE                   Ward DOUBLE\n",
       "13        Community Area      BIGINT         Community Area BIGINT\n",
       "14              FBI Code     VARCHAR              FBI Code VARCHAR\n",
       "15          X Coordinate      DOUBLE           X Coordinate DOUBLE\n",
       "16          Y Coordinate      DOUBLE           Y Coordinate DOUBLE\n",
       "17                  Year      BIGINT                   Year BIGINT\n",
       "18            Updated On     VARCHAR            Updated On VARCHAR\n",
       "19              Latitude      DOUBLE               Latitude DOUBLE\n",
       "20             Longitude      DOUBLE              Longitude DOUBLE\n",
       "21              Location     VARCHAR              Location VARCHAR"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col_attr = tbl_describe.df()[[\"column_name\", \"column_type\"]]\n",
    "\n",
    "col_attr[\"column_joint\"] = col_attr[\"column_name\"] + \" \" +  col_attr[\"column_type\"]\n",
    "col_attr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "Given the following SQL table, your job is to write queries given a user’s request. \n",
      "\n",
      "\n",
      "CREATE TABLE chicago_crime (ID BIGINT, Case Number VARCHAR, Date VARCHAR, Block VARCHAR, IUCR VARCHAR, Primary Type VARCHAR, Description VARCHAR, Location Description VARCHAR, Arrest BOOLEAN, Domestic BOOLEAN, Beat BIGINT, District BIGINT, Ward DOUBLE, Community Area BIGINT, FBI Code VARCHAR, X Coordinate DOUBLE, Y Coordinate DOUBLE, Year BIGINT, Updated On VARCHAR, Latitude DOUBLE, Longitude DOUBLE, Location VARCHAR) \n",
      "\n",
      "\n",
      "Write a SQL query that returns - How many cases ended up with arrest?\n",
      "\n"
     ]
    }
   ],
   "source": [
    "col_names = str(list(col_attr[\"column_joint\"].values)).replace('[', '').replace(']', '').replace('\\'', '')\n",
    "query = \"How many cases ended up with arrest?\"\n",
    "prompt = sql_prompt_generator(table_name = table_name, col_names = col_names, query = query)\n",
    "print(prompt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"warning\": \"This model version is deprecated. Migrate before January 4, 2024 to avoid disruption of service. Learn more https://platform.openai.com/docs/deprecations\",\n",
      "  \"id\": \"cmpl-8BrTEXXWPtIzzLOYcjOwV9FAT5Wcw\",\n",
      "  \"object\": \"text_completion\",\n",
      "  \"created\": 1697837840,\n",
      "  \"model\": \"text-davinci-003\",\n",
      "  \"choices\": [\n",
      "    {\n",
      "      \"text\": \"\\nSELECT COUNT(*) \\nFROM chicago_crime \\n\",\n",
      "      \"index\": 0,\n",
      "      \"logprobs\": null,\n",
      "      \"finish_reason\": \"length\"\n",
      "    }\n",
      "  ],\n",
      "  \"usage\": {\n",
      "    \"prompt_tokens\": 172,\n",
      "    \"completion_tokens\": 16,\n",
      "    \"total_tokens\": 188\n",
      "  }\n",
      "}\n",
      "\n",
      "SELECT COUNT(*) \n",
      "FROM chicago_crime \n",
      "\n"
     ]
    }
   ],
   "source": [
    "\n",
    "response = lang2sql(api_key = api_key, table_name = table_name, col_names= col_names, query = query)\n",
    "print(response)\n",
    "sql_query = response[\"choices\"][0][\"text\"]\n",
    "print(sql_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "system = \"\"\"\n",
    "Given the following SQL table, your job is to write queries given a user’s request. \n",
    "\n",
    "\n",
    "CREATE TABLE chicago_crime (ID BIGINT, Case Number VARCHAR, Date VARCHAR, Block VARCHAR, IUCR VARCHAR, Primary Type VARCHAR, Description VARCHAR, Location Description VARCHAR, Arrest BOOLEAN, Domestic BOOLEAN, Beat BIGINT, District BIGINT, Ward DOUBLE, Community Area BIGINT, FBI Code VARCHAR, X Coordinate DOUBLE, Y Coordinate DOUBLE, Year BIGINT, Updated On VARCHAR, Latitude DOUBLE, Longitude DOUBLE, Location VARCHAR) \n",
    "\"\"\"\n",
    "\n",
    "prompt = \"Write a SQL query that returns - How many cases ended up with arrest?\"\n",
    "\n",
    "response = openai.ChatCompletion.create(\n",
    "  model=\"gpt-3.5-turbo\",\n",
    "  messages=[\n",
    "    {\n",
    "      \"role\": \"system\",\n",
    "      \"content\": system\n",
    "    },\n",
    "    {\n",
    "      \"role\": \"user\",\n",
    "      \"content\": prompt\n",
    "    }\n",
    "  ],\n",
    "  temperature=1,\n",
    "  max_tokens=256,\n",
    "  top_p=1,\n",
    "  frequency_penalty=0,\n",
    "  presence_penalty=0\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"id\": \"chatcmpl-8C2XhXlX8XHb9P9olwbNmh7WmwRYY\",\n",
      "  \"object\": \"chat.completion\",\n",
      "  \"created\": 1697880401,\n",
      "  \"model\": \"gpt-3.5-turbo-0613\",\n",
      "  \"choices\": [\n",
      "    {\n",
      "      \"index\": 0,\n",
      "      \"message\": {\n",
      "        \"role\": \"assistant\",\n",
      "        \"content\": \"SELECT COUNT(*) AS arrest_cases\\nFROM chicago_crime\\nWHERE Arrest = true;\"\n",
      "      },\n",
      "      \"finish_reason\": \"stop\"\n",
      "    }\n",
      "  ],\n",
      "  \"usage\": {\n",
      "    \"prompt_tokens\": 134,\n",
      "    \"completion_tokens\": 17,\n",
      "    \"total_tokens\": 151\n",
      "  }\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "print(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT COUNT(*) AS arrest_cases\n",
      "FROM chicago_crime\n",
      "WHERE Arrest = true;\n"
     ]
    }
   ],
   "source": [
    "sql_query = response[\"choices\"][0][\"message\"][\"content\"]\n",
    "print(sql_query)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌──────────────┐\n",
      "│ arrest_cases │\n",
      "│    int64     │\n",
      "├──────────────┤\n",
      "│        77635 │\n",
      "└──────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "duckdb.sql(sql_query).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_message(table_name, query):\n",
    "    system_template = \"\"\"\n",
    "\n",
    "    Given the following SQL table, your job is to write queries given a user’s request. \\n\n",
    "\n",
    "    CREATE TABLE {} ({}) \\n\n",
    "    \"\"\"\n",
    "\n",
    "    user_template = \"Write a SQL query that returns - {}\"\n",
    "    \n",
    "    tbl_describe = duckdb.sql(\"DESCRIBE SELECT * FROM \" + table_name +  \";\")\n",
    "    col_attr = tbl_describe.df()[[\"column_name\", \"column_type\"]]\n",
    "    col_attr[\"column_joint\"] = col_attr[\"column_name\"] + \" \" +  col_attr[\"column_type\"]\n",
    "    col_names = str(list(col_attr[\"column_joint\"].values)).replace('[', '').replace(']', '').replace('\\'', '')\n",
    "\n",
    "    system = system_template.format(table_name, col_names)\n",
    "    user = user_template.format(query)\n",
    "\n",
    "    message = [\n",
    "    {\n",
    "      \"role\": \"system\",\n",
    "      \"content\": system\n",
    "    },\n",
    "    {\n",
    "      \"role\": \"user\",\n",
    "      \"content\": user\n",
    "    }\n",
    "    ]\n",
    "    \n",
    "    return message\n",
    "\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'role': 'system',\n",
       "  'content': '\\n\\n    Given the following SQL table, your job is to write queries given a user’s request. \\n\\n\\n    CREATE TABLE chicago_crime (ID BIGINT, Case Number VARCHAR, Date VARCHAR, Block VARCHAR, IUCR VARCHAR, Primary Type VARCHAR, Description VARCHAR, Location Description VARCHAR, Arrest BOOLEAN, Domestic BOOLEAN, Beat BIGINT, District BIGINT, Ward DOUBLE, Community Area BIGINT, FBI Code VARCHAR, X Coordinate DOUBLE, Y Coordinate DOUBLE, Year BIGINT, Updated On VARCHAR, Latitude DOUBLE, Longitude DOUBLE, Location VARCHAR) \\n\\n    '},\n",
       " {'role': 'user',\n",
       "  'content': 'Write a SQL query that returns - How many cases ended up with arrest?'}]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"How many cases ended up with arrest?\"\n",
    "create_message(table_name = \"chicago_crime\", query = query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "def lang2sql(api_key, table_name, query, model = \"gpt-3.5-turbo\", temperature = 0, max_tokens = 256, top_p = 1, frequency_penalty = 0,presence_penalty= 0):\n",
    "    \n",
    "    openai.api_key = api_key\n",
    "\n",
    "    message = create_message(table_name = table_name, query = query)\n",
    "    response = openai.ChatCompletion.create(\n",
    "        model = model,\n",
    "        messages = message,\n",
    "        temperature = temperature,\n",
    "        max_tokens = max_tokens,\n",
    "        top_p = top_p,\n",
    "        frequency_penalty = frequency_penalty,\n",
    "        presence_penalty = presence_penalty)\n",
    "\n",
    "    return response\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"id\": \"chatcmpl-8CLQiEzeApOrRGwMF6OyqZAxwVM6R\",\n",
      "  \"object\": \"chat.completion\",\n",
      "  \"created\": 1697953004,\n",
      "  \"model\": \"gpt-3.5-turbo-0613\",\n",
      "  \"choices\": [\n",
      "    {\n",
      "      \"index\": 0,\n",
      "      \"message\": {\n",
      "        \"role\": \"assistant\",\n",
      "        \"content\": \"SELECT COUNT(*) FROM chicago_crime WHERE Arrest = true;\"\n",
      "      },\n",
      "      \"finish_reason\": \"stop\"\n",
      "    }\n",
      "  ],\n",
      "  \"usage\": {\n",
      "    \"prompt_tokens\": 137,\n",
      "    \"completion_tokens\": 12,\n",
      "    \"total_tokens\": 149\n",
      "  }\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "query = \"How many cases ended up with arrest?\"\n",
    "response = lang2sql(api_key = api_key, table_name = \"chicago_crime\", query = query)\n",
    "\n",
    "print(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"id\": \"chatcmpl-8CLVMxh7XmzIEl0HjZmjYEXeBHteM\",\n",
      "  \"object\": \"chat.completion\",\n",
      "  \"created\": 1697953292,\n",
      "  \"model\": \"gpt-3.5-turbo-0613\",\n",
      "  \"choices\": [\n",
      "    {\n",
      "      \"index\": 0,\n",
      "      \"message\": {\n",
      "        \"role\": \"assistant\",\n",
      "        \"content\": \"SELECT COUNT(*) FROM chicago_crime WHERE Arrest = TRUE AND Year BETWEEN 2022 AND 2023;\"\n",
      "      },\n",
      "      \"finish_reason\": \"stop\"\n",
      "    }\n",
      "  ],\n",
      "  \"usage\": {\n",
      "    \"prompt_tokens\": 145,\n",
      "    \"completion_tokens\": 22,\n",
      "    \"total_tokens\": 167\n",
      "  }\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "query = \"How many cases ended up with arrest between 2022 and 2023?\"\n",
    "response = lang2sql(api_key = api_key, table_name = \"chicago_crime\", query = query)\n",
    "print(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT COUNT(*) FROM chicago_crime WHERE Arrest = TRUE AND Year BETWEEN 2022 AND 2023;\n",
      "┌──────────────┐\n",
      "│ count_star() │\n",
      "│    int64     │\n",
      "├──────────────┤\n",
      "│        51265 │\n",
      "└──────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql_query = response[\"choices\"][0][\"message\"][\"content\"]\n",
    "print(sql_query)\n",
    "duckdb.sql(sql_query).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"id\": \"chatcmpl-8CM4cl5Rk1fBFz2DZF8BVISG7KNeT\",\n",
      "  \"object\": \"chat.completion\",\n",
      "  \"created\": 1697955478,\n",
      "  \"model\": \"gpt-3.5-turbo-0613\",\n",
      "  \"choices\": [\n",
      "    {\n",
      "      \"index\": 0,\n",
      "      \"message\": {\n",
      "        \"role\": \"assistant\",\n",
      "        \"content\": \"SELECT Primary Type, COUNT(*) as TotalCases\\nFROM chicago_crime\\nGROUP BY Primary Type\"\n",
      "      },\n",
      "      \"finish_reason\": \"stop\"\n",
      "    }\n",
      "  ],\n",
      "  \"usage\": {\n",
      "    \"prompt_tokens\": 137,\n",
      "    \"completion_tokens\": 19,\n",
      "    \"total_tokens\": 156\n",
      "  }\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "query = \"Summarize the cases by primary type\"\n",
    "response = lang2sql(api_key = api_key, table_name = \"chicago_crime\", query = query)\n",
    "print(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT Primary Type, COUNT(*) as TotalCases\n",
      "FROM chicago_crime\n",
      "GROUP BY Primary Type\n"
     ]
    }
   ],
   "source": [
    "sql_query = response[\"choices\"][0][\"message\"][\"content\"]\n",
    "print(sql_query)\n",
    "# duckdb.sql(sql_query).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌───────────────────────────────────┬────────────┐\n",
       "│           Primary Type            │ TotalCases │\n",
       "│              varchar              │   int64    │\n",
       "├───────────────────────────────────┼────────────┤\n",
       "│ ASSAULT                           │      58685 │\n",
       "│ BURGLARY                          │      19898 │\n",
       "│ CRIMINAL DAMAGE                   │      75611 │\n",
       "│ NARCOTICS                         │      13931 │\n",
       "│ DECEPTIVE PRACTICE                │      46377 │\n",
       "│ OTHER OFFENSE                     │      40396 │\n",
       "│ INTIMIDATION                      │        499 │\n",
       "│ SEX OFFENSE                       │       3360 │\n",
       "│ CONCEALED CARRY LICENSE VIOLATION │        510 │\n",
       "│ LIQUOR LAW VIOLATION              │        530 │\n",
       "│          ·                        │         ·  │\n",
       "│          ·                        │         ·  │\n",
       "│          ·                        │         ·  │\n",
       "│ MOTOR VEHICLE THEFT               │      54934 │\n",
       "│ WEAPONS VIOLATION                 │      24672 │\n",
       "│ INTERFERENCE WITH PUBLIC OFFICER  │       1161 │\n",
       "│ OBSCENITY                         │        127 │\n",
       "│ THEFT                             │     139021 │\n",
       "│ ARSON                             │       1344 │\n",
       "│ KIDNAPPING                        │        319 │\n",
       "│ HUMAN TRAFFICKING                 │         32 │\n",
       "│ OTHER NARCOTIC VIOLATION          │         11 │\n",
       "│ GAMBLING                          │         33 │\n",
       "├───────────────────────────────────┴────────────┤\n",
       "│ 31 rows (20 shown)                   2 columns │\n",
       "└────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT \"Primary Type\", COUNT(*) as TotalCases\n",
    "FROM chicago_crime\n",
    "GROUP BY \"Primary Type\";\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "duckdb.sql(sql_query)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "lang2sql",
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}