{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "63a6161b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain import OpenAI, SQLDatabase, SQLDatabaseChain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "24f017da",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = SQLDatabase.from_uri(\"sqlite:///../../../notebooks/Chinook.db\")\n",
    "llm = OpenAI(temperature=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3e980729",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_chain = SQLDatabaseChain(llm=llm, database=db, verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "f8b4e54f",
   "metadata": {},
   "outputs": [],
   "source": [
    "questions = [\n",
    "    {\n",
    "        \"question\": \"How many employees are there?\",\n",
    "        \"answer\": \"8\"\n",
    "    },\n",
    "    {\n",
    "        \"question\": \"What are some example tracks by composer Johann Sebastian Bach?\",\n",
    "        \"answer\": \"'Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace', 'Aria Mit 30 Veränderungen, BWV 988 'Goldberg Variations': Aria', and 'Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude'\"\n",
    "    },\n",
    "    {\n",
    "        \"question\": \"What are some example tracks by Bach?\",\n",
    "        \"answer\": \"'Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace', 'Aria Mit 30 Veränderungen, BWV 988 'Goldberg Variations': Aria', and 'Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude'\"\n",
    "    },\n",
    "    {\n",
    "        \"question\": \"How many employees are also customers?\",\n",
    "        \"answer\": \"None\"\n",
    "    },\n",
    "    {\n",
    "        \"question\": \"Where is Mark Telus from?\",\n",
    "        \"answer\": \"Edmonton, Canada\"\n",
    "    },\n",
    "    {\n",
    "        \"question\": \"What is the most common genre of songs?\",\n",
    "        \"answer\": \"Rock\"\n",
    "    },\n",
    "    {\n",
    "        \"question\": \"What is the most common media type?\",\n",
    "        \"answer\": \"MPEG audio file\"\n",
    "    },\n",
    "    {\n",
    "        \"question\": \"What is the most common media type?\",\n",
    "        \"answer\": \"Purchased AAC audio file\"\n",
    "    },\n",
    "    {\n",
    "        \"question\": \"How many more Protected AAC audio files are there than Protected MPEG-4 video file?\",\n",
    "        \"answer\": \"23\"\n",
    "    },\n",
    "    {\n",
    "        \"question\": \"How many albums are there\",\n",
    "        \"answer\": \"347\"\n",
    "    }\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "5896eda7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(questions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "21dc41ac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[(1, 3034), (2, 237), (3, 214), (4, 7), (5, 11)]'"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.run(\"\"\"SELECT\n",
    "  MediaTypeID,\n",
    "  COUNT(*) AS `num`\n",
    "FROM\n",
    "  Track\n",
    "GROUP BY\n",
    "  MediaTypeID\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "115cd3da",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.get_table_info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "659c8d20",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[(347,)]'"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.run(\"select count(*) from album\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b99a505",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}