mysql_jupyternotebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "867f2d7c",
   "metadata": {},
   "source": [
    "I'm connecting to a local sql database and non local sql databases later on"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bcf70814",
   "metadata": {},
   "outputs": [],
   "source": [
    "#connect to one of my SQL databases"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9d31cb8",
   "metadata": {},
   "source": [
    "[stackoverflow](https://stackoverflow.com/questions/50973191/connect-to-mysql-db-from-jupyter-notebook)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3e33cd57",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pip install PyMySQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e6ab556",
   "metadata": {},
   "source": [
    "The steps to make the 'acme' database base below comes from [here](https://www.youtube.com/watch?v=9ylj9NR0Lcg). \n",
    "I am accessing this local database as an example to work with SQL inside Jupyter Notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7c51a5a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#establishing a connection with the local database\n",
    "#keep in mind there are many other ways to establish the connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "acfc1791",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   id first_name last_name             email password       location  \\\n",
      "0   1       John       Doe    john@gmail.com   123456  Massachusetts   \n",
      "1   2       Fred     Smith  freddy@gmail.com   123456       New York   \n",
      "2   3       Sara    Watson    sara@gmail.com   123456       New York   \n",
      "3   4       Will   Jackson    will@yahoo.com   123456   Rhode Island   \n",
      "4   5      Paula   Johnson   paula@yahoo.com   123456  Massachusetts   \n",
      "\n",
      "          dept  is_admin       register_date  age  \n",
      "0  development         1 2022-01-12 23:42:40   20  \n",
      "1       design         0 2022-01-12 23:43:05   23  \n",
      "2       design         0 2022-01-12 23:43:05   34  \n",
      "3  development         1 2022-01-12 23:43:05   29  \n",
      "4        sales         0 2022-01-12 23:43:05   40  \n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "import pandas as pd\n",
    "conn=pymysql.connect(host='localhost',port=int(3306),user='deco',passwd='123456',db='acme')\n",
    "df=pd.read_sql_query(\"SELECT * FROM users \",conn)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8b7a3cbd",
   "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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>email</th>\n",
       "      <th>password</th>\n",
       "      <th>location</th>\n",
       "      <th>dept</th>\n",
       "      <th>is_admin</th>\n",
       "      <th>register_date</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>Doe</td>\n",
       "      <td>john@gmail.com</td>\n",
       "      <td>123456</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>development</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-12 23:42:40</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Fred</td>\n",
       "      <td>Smith</td>\n",
       "      <td>freddy@gmail.com</td>\n",
       "      <td>123456</td>\n",
       "      <td>New York</td>\n",
       "      <td>design</td>\n",
       "      <td>0</td>\n",
       "      <td>2022-01-12 23:43:05</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Sara</td>\n",
       "      <td>Watson</td>\n",
       "      <td>sara@gmail.com</td>\n",
       "      <td>123456</td>\n",
       "      <td>New York</td>\n",
       "      <td>design</td>\n",
       "      <td>0</td>\n",
       "      <td>2022-01-12 23:43:05</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Will</td>\n",
       "      <td>Jackson</td>\n",
       "      <td>will@yahoo.com</td>\n",
       "      <td>123456</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>development</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-12 23:43:05</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Paula</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>paula@yahoo.com</td>\n",
       "      <td>123456</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>sales</td>\n",
       "      <td>0</td>\n",
       "      <td>2022-01-12 23:43:05</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id first_name last_name             email password       location  \\\n",
       "0   1       John       Doe    john@gmail.com   123456  Massachusetts   \n",
       "1   2       Fred     Smith  freddy@gmail.com   123456       New York   \n",
       "2   3       Sara    Watson    sara@gmail.com   123456       New York   \n",
       "3   4       Will   Jackson    will@yahoo.com   123456   Rhode Island   \n",
       "4   5      Paula   Johnson   paula@yahoo.com   123456  Massachusetts   \n",
       "\n",
       "          dept  is_admin       register_date  age  \n",
       "0  development         1 2022-01-12 23:42:40   20  \n",
       "1       design         0 2022-01-12 23:43:05   23  \n",
       "2       design         0 2022-01-12 23:43:05   34  \n",
       "3  development         1 2022-01-12 23:43:05   29  \n",
       "4        sales         0 2022-01-12 23:43:05   40  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "53063b36",
   "metadata": {},
   "outputs": [],
   "source": [
    "#a nice dataframe which anyone can do anything using all commands available in pandas to make graphs, data viz, etc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7042024a",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close() #this command is to close the connection "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "15758cc3",
   "metadata": {},
   "outputs": [
    {
     "ename": "Error",
     "evalue": "Already closed",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mError\u001b[0m                                     Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-8-be6fcf0f92ee>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;31m#run again to see if it did close the connection, and it did\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36mclose\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    402\u001b[0m         \"\"\"\n\u001b[0;32m    403\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_closed\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 404\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"Already closed\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    405\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_closed\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    406\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_sock\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mError\u001b[0m: Already closed"
     ]
    }
   ],
   "source": [
    "conn.close() #run again to see if it did close the connection, and it did "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "edcac25b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#but what if I want to open a connection with the data and use only sql commands inside jupyter the whole time?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b10d9dcc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "58f294d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql sqlite:///acme.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "4270b48b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>email</th>\n",
       "        <th>password</th>\n",
       "        <th>location</th>\n",
       "        <th>dept</th>\n",
       "        <th>is_admin</th>\n",
       "        <th>register_date</th>\n",
       "        <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>John</td>\n",
       "        <td>Doe</td>\n",
       "        <td>john@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>development</td>\n",
       "        <td>1</td>\n",
       "        <td>2022-01-12 23:42:40</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>Fred</td>\n",
       "        <td>Smith</td>\n",
       "        <td>freddy@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>New York</td>\n",
       "        <td>design</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "        <td>Sara</td>\n",
       "        <td>Watson</td>\n",
       "        <td>sara@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>New York</td>\n",
       "        <td>design</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>4</td>\n",
       "        <td>Will</td>\n",
       "        <td>Jackson</td>\n",
       "        <td>will@yahoo.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Rhode Island</td>\n",
       "        <td>development</td>\n",
       "        <td>1</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>5</td>\n",
       "        <td>Paula</td>\n",
       "        <td>Johnson</td>\n",
       "        <td>paula@yahoo.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>sales</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1, 'John', 'Doe', 'john@gmail.com', '123456', 'Massachusetts', 'development', 1, '2022-01-12 23:42:40', 20),\n",
       " (1, 2, 'Fred', 'Smith', 'freddy@gmail.com', '123456', 'New York', 'design', 0, '2022-01-12 23:43:05', 23),\n",
       " (2, 3, 'Sara', 'Watson', 'sara@gmail.com', '123456', 'New York', 'design', 0, '2022-01-12 23:43:05', 34),\n",
       " (3, 4, 'Will', 'Jackson', 'will@yahoo.com', '123456', 'Rhode Island', 'development', 1, '2022-01-12 23:43:05', 29),\n",
       " (4, 5, 'Paula', 'Johnson', 'paula@yahoo.com', '123456', 'Massachusetts', 'sales', 0, '2022-01-12 23:43:05', 40)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT * FROM users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "331bcfa4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#nice, now we can use sql commands the whole time if we want to"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f807d0e",
   "metadata": {},
   "source": [
    "some of sql steps comes from [here](https://www.youtube.com/watch?v=9ylj9NR0Lcg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "55b6a0ca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COUNT(id)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5,)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(id) FROM users;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ef2dc565",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John</td>\n",
       "        <td>Doe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fred</td>\n",
       "        <td>Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sara</td>\n",
       "        <td>Watson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Will</td>\n",
       "        <td>Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paula</td>\n",
       "        <td>Johnson</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('John', 'Doe'),\n",
       " ('Fred', 'Smith'),\n",
       " ('Sara', 'Watson'),\n",
       " ('Will', 'Jackson'),\n",
       " ('Paula', 'Johnson')]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT first_name, last_name FROM users;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "aef21100",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>email</th>\n",
       "        <th>password</th>\n",
       "        <th>location</th>\n",
       "        <th>dept</th>\n",
       "        <th>is_admin</th>\n",
       "        <th>register_date</th>\n",
       "        <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>John</td>\n",
       "        <td>Doe</td>\n",
       "        <td>john@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>development</td>\n",
       "        <td>1</td>\n",
       "        <td>2022-01-12 23:42:40</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>5</td>\n",
       "        <td>Paula</td>\n",
       "        <td>Johnson</td>\n",
       "        <td>paula@yahoo.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>sales</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1, 'John', 'Doe', 'john@gmail.com', '123456', 'Massachusetts', 'development', 1, '2022-01-12 23:42:40', 20),\n",
       " (4, 5, 'Paula', 'Johnson', 'paula@yahoo.com', '123456', 'Massachusetts', 'sales', 0, '2022-01-12 23:43:05', 40)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM users WHERE location='Massachusetts';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "39b08780",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>email</th>\n",
       "        <th>password</th>\n",
       "        <th>location</th>\n",
       "        <th>dept</th>\n",
       "        <th>is_admin</th>\n",
       "        <th>register_date</th>\n",
       "        <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>5</td>\n",
       "        <td>Paula</td>\n",
       "        <td>Johnson</td>\n",
       "        <td>paula@yahoo.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>sales</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(4, 5, 'Paula', 'Johnson', 'paula@yahoo.com', '123456', 'Massachusetts', 'sales', 0, '2022-01-12 23:43:05', 40)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM users WHERE location='Massachusetts' AND dept='sales';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "0354202c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>email</th>\n",
       "        <th>password</th>\n",
       "        <th>location</th>\n",
       "        <th>dept</th>\n",
       "        <th>is_admin</th>\n",
       "        <th>register_date</th>\n",
       "        <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>John</td>\n",
       "        <td>Doe</td>\n",
       "        <td>john@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>development</td>\n",
       "        <td>1</td>\n",
       "        <td>2022-01-12 23:42:40</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>4</td>\n",
       "        <td>Will</td>\n",
       "        <td>Jackson</td>\n",
       "        <td>will@yahoo.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Rhode Island</td>\n",
       "        <td>development</td>\n",
       "        <td>1</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>29</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1, 'John', 'Doe', 'john@gmail.com', '123456', 'Massachusetts', 'development', 1, '2022-01-12 23:42:40', 20),\n",
       " (3, 4, 'Will', 'Jackson', 'will@yahoo.com', '123456', 'Rhode Island', 'development', 1, '2022-01-12 23:43:05', 29)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM users WHERE is_admin>0;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "1a7ff99c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>email</th>\n",
       "        <th>password</th>\n",
       "        <th>location</th>\n",
       "        <th>dept</th>\n",
       "        <th>is_admin</th>\n",
       "        <th>register_date</th>\n",
       "        <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>John</td>\n",
       "        <td>Doe</td>\n",
       "        <td>john@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>development</td>\n",
       "        <td>1</td>\n",
       "        <td>2022-01-12 23:42:40</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>Fred</td>\n",
       "        <td>Smith</td>\n",
       "        <td>freddy@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>New York</td>\n",
       "        <td>design</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "        <td>Sara</td>\n",
       "        <td>Watson</td>\n",
       "        <td>sara@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>New York</td>\n",
       "        <td>design</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>4</td>\n",
       "        <td>Will</td>\n",
       "        <td>Jackson</td>\n",
       "        <td>will@yahoo.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Rhode Island</td>\n",
       "        <td>development</td>\n",
       "        <td>1</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>5</td>\n",
       "        <td>Paula</td>\n",
       "        <td>Johnson</td>\n",
       "        <td>paula@yahoo.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>sales</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1, 'John', 'Doe', 'john@gmail.com', '123456', 'Massachusetts', 'development', 1, '2022-01-12 23:42:40', 20),\n",
       " (1, 2, 'Fred', 'Smith', 'freddy@gmail.com', '123456', 'New York', 'design', 0, '2022-01-12 23:43:05', 23),\n",
       " (2, 3, 'Sara', 'Watson', 'sara@gmail.com', '123456', 'New York', 'design', 0, '2022-01-12 23:43:05', 34),\n",
       " (3, 4, 'Will', 'Jackson', 'will@yahoo.com', '123456', 'Rhode Island', 'development', 1, '2022-01-12 23:43:05', 29),\n",
       " (4, 5, 'Paula', 'Johnson', 'paula@yahoo.com', '123456', 'Massachusetts', 'sales', 0, '2022-01-12 23:43:05', 40)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT * FROM users;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "631bf0d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>email</th>\n",
       "        <th>password</th>\n",
       "        <th>location</th>\n",
       "        <th>dept</th>\n",
       "        <th>is_admin</th>\n",
       "        <th>register_date</th>\n",
       "        <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>John</td>\n",
       "        <td>Doe</td>\n",
       "        <td>john@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>development</td>\n",
       "        <td>1</td>\n",
       "        <td>2022-01-12 23:42:40</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>4</td>\n",
       "        <td>Will</td>\n",
       "        <td>Jackson</td>\n",
       "        <td>will@yahoo.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Rhode Island</td>\n",
       "        <td>development</td>\n",
       "        <td>1</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>5</td>\n",
       "        <td>Paula</td>\n",
       "        <td>Johnson</td>\n",
       "        <td>paula@yahoo.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>sales</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>Fred</td>\n",
       "        <td>Smith</td>\n",
       "        <td>freddy@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>New York</td>\n",
       "        <td>design</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "        <td>Sara</td>\n",
       "        <td>Watson</td>\n",
       "        <td>sara@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>New York</td>\n",
       "        <td>design</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>34</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1, 'John', 'Doe', 'john@gmail.com', '123456', 'Massachusetts', 'development', 1, '2022-01-12 23:42:40', 20),\n",
       " (3, 4, 'Will', 'Jackson', 'will@yahoo.com', '123456', 'Rhode Island', 'development', 1, '2022-01-12 23:43:05', 29),\n",
       " (4, 5, 'Paula', 'Johnson', 'paula@yahoo.com', '123456', 'Massachusetts', 'sales', 0, '2022-01-12 23:43:05', 40),\n",
       " (1, 2, 'Fred', 'Smith', 'freddy@gmail.com', '123456', 'New York', 'design', 0, '2022-01-12 23:43:05', 23),\n",
       " (2, 3, 'Sara', 'Watson', 'sara@gmail.com', '123456', 'New York', 'design', 0, '2022-01-12 23:43:05', 34)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM users ORDER BY last_name ASC; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "1b30c59a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>email</th>\n",
       "        <th>password</th>\n",
       "        <th>location</th>\n",
       "        <th>dept</th>\n",
       "        <th>is_admin</th>\n",
       "        <th>register_date</th>\n",
       "        <th>age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "        <td>Sara</td>\n",
       "        <td>Watson</td>\n",
       "        <td>sara@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>New York</td>\n",
       "        <td>design</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>Fred</td>\n",
       "        <td>Smith</td>\n",
       "        <td>freddy@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>New York</td>\n",
       "        <td>design</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>5</td>\n",
       "        <td>Paula</td>\n",
       "        <td>Johnson</td>\n",
       "        <td>paula@yahoo.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>sales</td>\n",
       "        <td>0</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>4</td>\n",
       "        <td>Will</td>\n",
       "        <td>Jackson</td>\n",
       "        <td>will@yahoo.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Rhode Island</td>\n",
       "        <td>development</td>\n",
       "        <td>1</td>\n",
       "        <td>2022-01-12 23:43:05</td>\n",
       "        <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>John</td>\n",
       "        <td>Doe</td>\n",
       "        <td>john@gmail.com</td>\n",
       "        <td>123456</td>\n",
       "        <td>Massachusetts</td>\n",
       "        <td>development</td>\n",
       "        <td>1</td>\n",
       "        <td>2022-01-12 23:42:40</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 3, 'Sara', 'Watson', 'sara@gmail.com', '123456', 'New York', 'design', 0, '2022-01-12 23:43:05', 34),\n",
       " (1, 2, 'Fred', 'Smith', 'freddy@gmail.com', '123456', 'New York', 'design', 0, '2022-01-12 23:43:05', 23),\n",
       " (4, 5, 'Paula', 'Johnson', 'paula@yahoo.com', '123456', 'Massachusetts', 'sales', 0, '2022-01-12 23:43:05', 40),\n",
       " (3, 4, 'Will', 'Jackson', 'will@yahoo.com', '123456', 'Rhode Island', 'development', 1, '2022-01-12 23:43:05', 29),\n",
       " (0, 1, 'John', 'Doe', 'john@gmail.com', '123456', 'Massachusetts', 'development', 1, '2022-01-12 23:42:40', 20)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM users ORDER BY last_name DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "c4575383",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\nThis is mysql command below, then the code cell below the line below is the equivalent in SQLite\\n\\nSELECT CONCAT(first_name, '  ',last_name) AS 'Name', dept FROM users;\\n\""
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "This is mysql command below, then the code cell below the line below is the equivalent in SQLite\n",
    "\n",
    "SELECT CONCAT(first_name, '  ',last_name) AS 'Name', dept FROM users;\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "815410bd",
   "metadata": {},
   "source": [
    "In fact here are the [differences](https://stackoverflow.com/questions/3568779/string-concatenation-does-not-work-in-sqlite)\n",
    "\n",
    "SQLite                      ||  \n",
    "\n",
    "Oracle                      CONCAT(string1, string2) or ||\n",
    "\n",
    "MySQL                       CONCAT(string1, string2, string3...) or || if PIPES_AS_CONCAT enabled\n",
    "\n",
    "Postgres                    CONCAT(string1, string2, string3...) or ||\n",
    "\n",
    "Microsoft SQL Server 2012+  CONCAT(string1, string2, string3...) or + \n",
    "\n",
    "Microsoft Access   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "101c6dbd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fred Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sara Watson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Doe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Will Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paula Johnson</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Fred Smith',),\n",
       " ('Sara Watson',),\n",
       " ('John Doe',),\n",
       " ('Will Jackson',),\n",
       " ('Paula Johnson',)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    first_name || ' ' || last_name AS 'Name'\n",
    "FROM\n",
    "    users\n",
    "ORDER BY\n",
    "    dept;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "e4712604",
   "metadata": {},
   "outputs": [],
   "source": [
    "#the connection made is using sqlite syntax, if someone wants to create all commands with mysql syntax and not sqlite syntax,\n",
    "#there are some options, but not as nice visually as the one above, at least not that I know of. Keep in mind there are \n",
    "#many other commands that could be presented, even graphs, but I wanted to keep these code together showing ways to connect \n",
    "#to an existing database and creating a database inside jupyter notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9cc1526c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\nimport mysql.connector as mdb\\ncon = mdb.connect(user='deco', password ='123456', database='acme')                      \\n\\n\\nthis was not working right so I used the option below \\n\""
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "import mysql.connector as mdb\n",
    "con = mdb.connect(user='deco', password ='123456', database='acme')                      \n",
    "\n",
    "\n",
    "this was not working right so I used the option below \n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "40bba53f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pip install pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "4ba88cec",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql as MySQLdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "4ac2bb74",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = MySQLdb.connect(user='deco', password ='123456', database='acme')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "4c3a727a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "1c50734f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymysql.cursors.Cursor at 0x1c5634ee7f0>"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "70a23d55",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute('SELECT * FROM users;') #there are 5 rows in the table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bce789cb",
   "metadata": {},
   "source": [
    "There are other options such as [SQLALchemy](https://www.sqlalchemy.org/), and others. \n",
    "It's also nice to work inside [MySQL Workbench](https://www.mysql.com/products/workbench/).\n",
    "But if someone wants to work with sql inside jupyter notebook, I'd stick with the option using %%sql, it makes me feel like \n",
    "I am inside MySQL Workbench seeing the tables, although \n",
    "from time to time, the syntax might be a bit different between sqlite and mysql such as || for CONCAT,\n",
    "but most of the time the syntax is the same."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f16f4ad1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "20699be5",
   "metadata": {},
   "outputs": [],
   "source": [
    "#What if I want to create some data from scratch and use SQL commands inside jupyter notebook without an existing database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c844fab3",
   "metadata": {},
   "source": [
    "This [helped](https://www.youtube.com/watch?v=sDY_fKe_JVw)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7c7a7b82",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pip install ipython-sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "2b9564d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "f686479f",
   "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>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Juan</td>\n",
       "      <td>23</td>\n",
       "      <td>Miami</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Victoria</td>\n",
       "      <td>34</td>\n",
       "      <td>Buenos Aires</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>43</td>\n",
       "      <td>Santiago</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       name  age          city\n",
       "0      Juan   23         Miami\n",
       "1  Victoria   34  Buenos Aires\n",
       "2      Mary   43      Santiago"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df= pd.DataFrame({'name': ['Juan', 'Victoria', 'Mary'], \n",
    "                  'age': [23,34,43], \n",
    "                  'city': ['Miami', 'Buenos Aires','Santiago']})\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "1c6567ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "#connect the sqlite3 library creates a connection with the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "87be01b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "cnn = sqlite3.connect('exampledatabase.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "531c4cbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql('newtablecreated1',cnn) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "d01fee70",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "91754ec1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "31d03170",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql sqlite:///exampledatabase.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "a15f5272",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   sqlite:///acme.db\n",
      " * sqlite:///exampledatabase.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>name</th>\n",
       "        <th>age</th>\n",
       "        <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>Juan</td>\n",
       "        <td>23</td>\n",
       "        <td>Miami</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Victoria</td>\n",
       "        <td>34</td>\n",
       "        <td>Buenos Aires</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Mary</td>\n",
       "        <td>43</td>\n",
       "        <td>Santiago</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 'Juan', 23, 'Miami'),\n",
       " (1, 'Victoria', 34, 'Buenos Aires'),\n",
       " (2, 'Mary', 43, 'Santiago')]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT * FROM newtablecreated1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "69a5445a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#again, now we can use sql commands the whole time if we want to "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "db4041d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#all sql commands can be applied here again if you want"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ee6cf8d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "fb5f5bfa",
   "metadata": {},
   "outputs": [],
   "source": [
    "#if you wish, keep in mind that the 'COMMIT;' command will save changes made and permanently record these changes in the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b056788",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "298ab856",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Below are examples from two medium articles, there are some errors in some cells below, I kept it here for future use"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af084fb3",
   "metadata": {},
   "source": [
    "Medium [article](https://towardsdatascience.com/how-to-run-sql-queries-from-a-jupyter-notebook-aaa18e59e7bc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "5d2a2d49",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "68ea27c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql sqlite://"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "5cb9ef7f",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'engine' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-51-4f86dedc1c77>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'connecting with engine '\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0mstr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'engine' is not defined"
     ]
    }
   ],
   "source": [
    "print('connecting with engine ' + str(engine))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "182ccad3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "   sqlite:///exampledatabase.db\n",
      "Done.\n",
      "6 rows affected.\n"
     ]
    },
    {
     "ename": "ResourceClosedError",
     "evalue": "This result object does not return rows. It has been closed automatically.",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mResourceClosedError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-52-e6580886e96a>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mget_ipython\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrun_cell_magic\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'sql'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m''\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"CREATE TABLE sales\\n(\\n    key       varchar(6),\\n    ts        timestamp,\\n    product   integer,\\n    completed boolean,\\n    price     float\\n);\\nINSERT INTO sales\\nVALUES ('sale_1', '2019-11-08 00:00', 0, TRUE, 1.1),\\n       ('sale_2', '2019-11-08 01:00', 0, FALSE, 1.2),\\n       ('sale_3', '2019-11-08 01:00', 0, TRUE, 1.3),\\n       ('sale_4', '2019-11-08 01:00', 1, FALSE, 1.4),\\n       ('sale_5', '2019-11-08 02:00', 1, TRUE, 1.5),\\n       ('sale_6', '2019-11-08 02:00', 1, TRUE, 1.5);\\n\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py\u001b[0m in \u001b[0;36mrun_cell_magic\u001b[1;34m(self, magic_name, line, cell)\u001b[0m\n\u001b[0;32m   2397\u001b[0m             \u001b[1;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mbuiltin_trap\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2398\u001b[0m                 \u001b[0margs\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mmagic_arg_s\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcell\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2399\u001b[1;33m                 \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2400\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2401\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\decorator.py\u001b[0m in \u001b[0;36mfun\u001b[1;34m(*args, **kw)\u001b[0m\n\u001b[0;32m    229\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mkwsyntax\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    230\u001b[0m                 \u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkw\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfix\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkw\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msig\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 231\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mcaller\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfunc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mextras\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    232\u001b[0m     \u001b[0mfun\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__name__\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__name__\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    233\u001b[0m     \u001b[0mfun\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__doc__\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__doc__\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\IPython\\core\\magic.py\u001b[0m in \u001b[0;36m<lambda>\u001b[1;34m(f, *a, **k)\u001b[0m\n\u001b[0;32m    185\u001b[0m     \u001b[1;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    186\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 187\u001b[1;33m         \u001b[0mcall\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    188\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    189\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\decorator.py\u001b[0m in \u001b[0;36mfun\u001b[1;34m(*args, **kw)\u001b[0m\n\u001b[0;32m    229\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mkwsyntax\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    230\u001b[0m                 \u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkw\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfix\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkw\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msig\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 231\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mcaller\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfunc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mextras\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    232\u001b[0m     \u001b[0mfun\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__name__\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__name__\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    233\u001b[0m     \u001b[0mfun\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__doc__\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__doc__\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\IPython\\core\\magic.py\u001b[0m in \u001b[0;36m<lambda>\u001b[1;34m(f, *a, **k)\u001b[0m\n\u001b[0;32m    185\u001b[0m     \u001b[1;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    186\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 187\u001b[1;33m         \u001b[0mcall\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    188\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    189\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sql\\magic.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, line, cell, local_ns)\u001b[0m\n\u001b[0;32m    215\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    216\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 217\u001b[1;33m             \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparsed\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"sql\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0muser_ns\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    218\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    219\u001b[0m             if (\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sql\\run.py\u001b[0m in \u001b[0;36mrun\u001b[1;34m(conn, sql, config, user_namespace)\u001b[0m\n\u001b[0;32m    369\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mresult\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mconfig\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfeedback\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    370\u001b[0m                 \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0minterpret_rowcount\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrowcount\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 371\u001b[1;33m         \u001b[0mresultset\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mResultSet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconfig\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    372\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mconfig\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mautopandas\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    373\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mresultset\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sql\\run.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, sqlaproxy, sql, config)\u001b[0m\n\u001b[0;32m    105\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    106\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__init__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msqlaproxy\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconfig\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 107\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mkeys\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msqlaproxy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mkeys\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    108\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msql\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    109\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconfig\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconfig\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\result.py\u001b[0m in \u001b[0;36mkeys\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    705\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    706\u001b[0m         \"\"\"\n\u001b[1;32m--> 707\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_metadata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mkeys\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    708\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    709\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\cursor.py\u001b[0m in \u001b[0;36mkeys\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1199\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mproperty\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1200\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mkeys\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1201\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_we_dont_return_rows\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1202\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1203\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\cursor.py\u001b[0m in \u001b[0;36m_we_dont_return_rows\u001b[1;34m(self, err)\u001b[0m\n\u001b[0;32m   1176\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1177\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_we_dont_return_rows\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1178\u001b[1;33m         util.raise_(\n\u001b[0m\u001b[0;32m   1179\u001b[0m             exc.ResourceClosedError(\n\u001b[0;32m   1180\u001b[0m                 \u001b[1;34m\"This result object does not return rows. \"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\util\\compat.py\u001b[0m in \u001b[0;36mraise_\u001b[1;34m(***failed resolving arguments***)\u001b[0m\n\u001b[0;32m    209\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    210\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 211\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    212\u001b[0m         \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    213\u001b[0m             \u001b[1;31m# credit to\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mResourceClosedError\u001b[0m: This result object does not return rows. It has been closed automatically."
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE sales\n",
    "(\n",
    "    key       varchar(6),\n",
    "    ts        timestamp,\n",
    "    product   integer,\n",
    "    completed boolean,\n",
    "    price     float\n",
    ");\n",
    "INSERT INTO sales\n",
    "VALUES ('sale_1', '2019-11-08 00:00', 0, TRUE, 1.1),\n",
    "       ('sale_2', '2019-11-08 01:00', 0, FALSE, 1.2),\n",
    "       ('sale_3', '2019-11-08 01:00', 0, TRUE, 1.3),\n",
    "       ('sale_4', '2019-11-08 01:00', 1, FALSE, 1.4),\n",
    "       ('sale_5', '2019-11-08 02:00', 1, TRUE, 1.5),\n",
    "       ('sale_6', '2019-11-08 02:00', 1, TRUE, 1.5);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "8cfd60c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "   sqlite:///exampledatabase.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>key</th>\n",
       "        <th>ts</th>\n",
       "        <th>product</th>\n",
       "        <th>completed</th>\n",
       "        <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sale_1</td>\n",
       "        <td>2019-11-08 00:00</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sale_2</td>\n",
       "        <td>2019-11-08 01:00</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sale_3</td>\n",
       "        <td>2019-11-08 01:00</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sale_4</td>\n",
       "        <td>2019-11-08 01:00</td>\n",
       "        <td>1</td>\n",
       "        <td>0</td>\n",
       "        <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sale_5</td>\n",
       "        <td>2019-11-08 02:00</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sale_6</td>\n",
       "        <td>2019-11-08 02:00</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1.5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('sale_1', '2019-11-08 00:00', 0, 1, 1.1),\n",
       " ('sale_2', '2019-11-08 01:00', 0, 0, 1.2),\n",
       " ('sale_3', '2019-11-08 01:00', 0, 1, 1.3),\n",
       " ('sale_4', '2019-11-08 01:00', 1, 0, 1.4),\n",
       " ('sale_5', '2019-11-08 02:00', 1, 1, 1.5),\n",
       " ('sale_6', '2019-11-08 02:00', 1, 1, 1.5)]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from sales;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "960443c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = _"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "4cf342b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sql.run.ResultSet"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(result)\n",
    "# sql.run.ResultSet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "d74726c1",
   "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>key</th>\n",
       "      <th>ts</th>\n",
       "      <th>product</th>\n",
       "      <th>completed</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sale_1</td>\n",
       "      <td>2019-11-08 00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sale_2</td>\n",
       "      <td>2019-11-08 01:00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sale_3</td>\n",
       "      <td>2019-11-08 01:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sale_4</td>\n",
       "      <td>2019-11-08 01:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sale_5</td>\n",
       "      <td>2019-11-08 02:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>sale_6</td>\n",
       "      <td>2019-11-08 02:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      key                ts  product  completed  price\n",
       "0  sale_1  2019-11-08 00:00        0          1    1.1\n",
       "1  sale_2  2019-11-08 01:00        0          0    1.2\n",
       "2  sale_3  2019-11-08 01:00        0          1    1.3\n",
       "3  sale_4  2019-11-08 01:00        1          0    1.4\n",
       "4  sale_5  2019-11-08 02:00        1          1    1.5\n",
       "5  sale_6  2019-11-08 02:00        1          1    1.5"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = result.DataFrame()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "ed77118d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXoAAAD4CAYAAADiry33AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAAAWKUlEQVR4nO3dfYxc1X3G8e/TxZbILnXaOAypDfG2sZS4GBCe2LREZVZq0IISuaiWYst15DTWijROmwqiuP0DpEaVEkVUUYDEWaWWFQmYPwoGCzu8KGXrpJTKNnJYnOBoayx1vQgLjJYsQSVLf/1jrstkmZc7bzv24flII8/cc87M75y5fvbu3XlRRGBmZun6rX4XYGZmveWgNzNLnIPezCxxDnozs8Q56M3MEndRvwuoZfny5bFq1aq2xr7xxhsMDg52t6AucF2tcV2tcV2tSbGuo0ePvhIRH6zZGBHn3WXdunXRrqeeeqrtsb3kulrjulrjulqTYl3AkaiTqT51Y2aWOAe9mVniHPRmZolz0JuZJc5Bb2aWOAe9mVnimga9pMslPSXp55KOS/qbGn0k6duSpiQ9J+naqrZRSSeytl3dnoCZmTWW54h+HrgtIj4GXAd8UdKaBX1uAlZnlzHguwCSBoB7s/Y1wJYaY83MrIeaBn1EvBQRz2bXfwn8HFixoNtG4AfZ6/afAd4v6UPAemAqIk5GxFtAOetrZmaLRNHCF49IWgUcAq6MiNertj8KfD0ifpLd/hHwVWAVMBoRO7Lt24ANEbGzxn2PUfltgEKhsK5cLrc1obm5OYaGhtoa20uuqzWuqzWuqzXN6po8PbuI1bxjeNlA2+s1MjJyNCKKtdpyf9aNpCHgQeDL1SF/rrnGkGiw/d0bI8aBcYBisRilUilvab9hYmKCdsf2kutqjetqjetqTbO6tu86sHjFVNk7OtiT9coV9JKWUAn5+yLioRpdpoHLq26vBGaApXW2m5nZIsnzqhsB/wz8PCL+qU63/cBns1ffXAfMRsRLwGFgtaRhSUuBzVlfMzNbJHmO6K8HtgGTko5l2/4euAIgInYDB4GbgSngV8DnsrZ5STuBx4EBYE9EHO/mBMzMrLGmQZ/9gbXWufbqPgF8sU7bQSo/CMzMrA/8zlgzs8Q56M3MEuegNzNLnIPezCxxDnozs8Q56M3MEuegNzNLnIPezCxxDnozs8Q56M3MEuegNzNLnIPezCxxDnozs8Q56M3MEuegNzNLnIPezCxxTb94RNIe4FPAmYi4skb7V4CtVff3MeCDEXFW0ingl8DbwHy9byg3M7PeyXNEvxcYrdcYEd+MiGsi4hrg74B/i4izVV1GsnaHvJlZHzQN+og4BJxt1i+zBXigo4rMzKyrunaOXtL7qBz5P1i1OYAnJB2VNNatxzIzs/xU+V7vJp2kVcCjtc7RV/X5DPAXEfHpqm2/FxEzki4FngS+lP2GUGv8GDAGUCgU1pXL5ZYmcs7c3BxDQ0Ntje0l19Ua19Ua19WaZnVNnp5dxGreMbxsoO31GhkZOVrvFHnTP8a2YDMLTttExEz27xlJ+4D1QM2gj4hxYBygWCxGqVRqq4iJiQnaHdtLrqs1rqs1rqs1zeravuvA4hVTZe/oYE/WqyunbiQtA24AHqnaNijpknPXgRuB57vxeGZmll+el1c+AJSA5ZKmgTuBJQARsTvrdgvwRES8UTW0AOyTdO5x7o+Ix7pXupmZ5dE06CNiS44+e6m8DLN620ng6nYLMzOz7vA7Y83MEuegNzNLnIPezCxxDnozs8Q56M3MEuegNzNLnIPezCxxDnozs8Q56M3MEuegNzNLnIPezCxxDnozs8Q56M3MEuegNzNLnIPezCxxDnozs8Q56M3MEtc06CXtkXRGUs3ve5VUkjQr6Vh2uaOqbVTSCUlTknZ1s3AzM8snzxH9XmC0SZ8fR8Q12eUfACQNAPcCNwFrgC2S1nRSrJmZta5p0EfEIeBsG/e9HpiKiJMR8RZQBja2cT9mZtYBRUTzTtIq4NGIuLJGWwl4EJgGZoDbI+K4pE3AaETsyPptAzZExM46jzEGjAEUCoV15XK5nfkwNzfH0NBQW2N7yXW1xnW1xnW1plldk6dnF7GadwwvG2h7vUZGRo5GRLFW20UdVVXxLPDhiJiTdDPwMLAaUI2+dX+qRMQ4MA5QLBajVCq1VczExATtju0l19Ua19Ua19WaZnVt33Vg8Yqpsnd0sCfr1fGrbiLi9YiYy64fBJZIWk7lCP/yqq4rqRzxm5nZIuo46CVdJknZ9fXZfb4KHAZWSxqWtBTYDOzv9PHMzKw1TU/dSHoAKAHLJU0DdwJLACJiN7AJ+IKkeeBNYHNUTvzPS9oJPA4MAHsi4nhPZmFmZnU1DfqI2NKk/R7gnjptB4GD7ZVmZmbd4HfGmpklzkFvZpY4B72ZWeIc9GZmiXPQm5klzkFvZpY4B72ZWeIc9GZmiXPQm5klzkFvZpY4B72ZWeIc9GZmiXPQm5klzkFvZpY4B72ZWeIc9GZmiXPQm5klrmnQS9oj6Yyk5+u0b5X0XHZ5WtLVVW2nJE1KOibpSDcLNzOzfPIc0e8FRhu0vwjcEBFXAV8Dxhe0j0TENRFRbK9EMzPrRJ7vjD0kaVWD9qerbj4DrOxCXWZm1iWKiOadKkH/aERc2aTf7cBHI2JHdvtF4DUggO9FxMKj/eqxY8AYQKFQWFcul/PO4TfMzc0xNDTU1thecl2tcV2tcV2taVbX5OnZRazmHcPLBtper5GRkaP1zpw0PaLPS9II8HngE1Wbr4+IGUmXAk9KeiEiDtUan/0QGAcoFotRKpXaqmNiYoJ2x/aS62qN62qN62pNs7q27zqweMVU2Ts62JP16sqrbiRdBXwf2BgRr57bHhEz2b9ngH3A+m48npmZ5ddx0Eu6AngI2BYRv6jaPijpknPXgRuBmq/cMTOz3ml66kbSA0AJWC5pGrgTWAIQEbuBO4APAN+RBDCfnScqAPuybRcB90fEYz2Yg5mZNZDnVTdbmrTvAHbU2H4SuPrdI8zMbDH5nbFmZolz0JuZJc5Bb2aWOAe9mVniHPRmZolz0JuZJc5Bb2aWOAe9mVniHPRmZolz0JuZJc5Bb2aWOAe9mVniHPRmZolz0JuZJc5Bb2aWOAe9mVniHPRmZolrGvSS9kg6I6nm972q4tuSpiQ9J+naqrZRSSeytl3dLNzMzPLJc0S/Fxht0H4TsDq7jAHfBZA0ANybta8Btkha00mxZmbWuqZBHxGHgLMNumwEfhAVzwDvl/QhYD0wFREnI+ItoJz1NTOzRaSIaN5JWgU8GhFX1mh7FPh6RPwku/0j4KvAKmA0+/JwJG0DNkTEzjqPMUblNwIKhcK6crncznw4c3aWl99sa2hH1q5Y1rB9bm6OoaGhnjz25OnZtscWLqbt9Wo25070cr064f2rNRfq/tXJnDsxvGyg7edxZGTkaEQUa7Vd1FFVFaqxLRpsrykixoFxgGKxGKVSqa1i7r7vEe6a7Ma0WnNqa6lh+8TEBO3OqZntuw60Pfa2tfNtr1ezOXeil+vVCe9frblQ969O5tyJvaODPXkeu7HHTgOXV91eCcwAS+tsNzOzRdSNl1fuBz6bvfrmOmA2Il4CDgOrJQ1LWgpszvqamdkianpEL+kBoAQslzQN3AksAYiI3cBB4GZgCvgV8LmsbV7STuBxYADYExHHezAHMzNroGnQR8SWJu0BfLFO20EqPwjMzKxP/M5YM7PEOejNzBLnoDczS5yD3swscQ56M7PEOejNzBLnoDczS5yD3swscQ56M7PEOejNzBLnoDczS5yD3swscQ56M7PEOejNzBLnoDczS5yD3swscQ56M7PE5Qp6SaOSTkiakrSrRvtXJB3LLs9LelvS72ZtpyRNZm1Huj0BMzNrLM93xg4A9wKfBKaBw5L2R8TPzvWJiG8C38z6fxr424g4W3U3IxHxSlcrNzOzXPIc0a8HpiLiZES8BZSBjQ36bwEe6EZxZmbWOVW+27tBB2kTMBoRO7Lb24ANEbGzRt/3UTnq/8i5I3pJLwKvAQF8LyLG6zzOGDAGUCgU1pXL5bYmdObsLC+/2dbQjqxdsaxh+9zcHENDQz157MnTs22PLVxM2+vVbM6d6OV6dcL7V2su1P2rkzl3YnjZQNvP48jIyNGIKNZqa3rqBlCNbfV+Onwa+PcFp22uj4gZSZcCT0p6ISIOvesOKz8AxgGKxWKUSqUcpb3b3fc9wl2TeabVXae2lhq2T0xM0O6cmtm+60DbY29bO9/2ejWbcyd6uV6d8P7Vmgt1/+pkzp3YOzrYk+cxz6mbaeDyqtsrgZk6fTez4LRNRMxk/54B9lE5FWRmZoskT9AfBlZLGpa0lEqY71/YSdIy4Abgkaptg5IuOXcduBF4vhuFm5lZPk1/p4qIeUk7gceBAWBPRByXdGvWvjvregvwRES8UTW8AOyTdO6x7o+Ix7o5ATMzayzXybOIOAgcXLBt94Lbe4G9C7adBK7uqEIzM+uI3xlrZpY4B72ZWeIc9GZmiXPQm5klzkFvZpY4B72ZWeIc9GZmiXPQm5klzkFvZpY4B72ZWeIc9GZmiXPQm5klzkFvZpY4B72ZWeIc9GZmiXPQm5klzkFvZpa4XEEvaVTSCUlTknbVaC9JmpV0LLvckXesmZn1VtOvEpQ0ANwLfBKYBg5L2h8RP1vQ9ccR8ak2x5qZWY/kOaJfD0xFxMmIeAsoAxtz3n8nY83MrAsUEY07SJuA0YjYkd3eBmyIiJ1VfUrAg1SO2meA2yPieJ6xVfcxBowBFAqFdeVyua0JnTk7y8tvtjW0I2tXLGvYPjc3x9DQUE8ee/L0bNtjCxfT9no1m3MnerlenfD+1ZoLdf/qZM6dGF420PbzODIycjQiirXamp66AVRj28KfDs8CH46IOUk3Aw8Dq3OOrWyMGAfGAYrFYpRKpRylvdvd9z3CXZN5ptVdp7aWGrZPTEzQ7pya2b7rQNtjb1s73/Z6NZtzJ3q5Xp3w/tWaC3X/6mTOndg7OtiT5zHPqZtp4PKq2yupHLX/v4h4PSLmsusHgSWSlucZa2ZmvZUn6A8DqyUNS1oKbAb2V3eQdJkkZdfXZ/f7ap6xZmbWW01/p4qIeUk7gceBAWBPdv791qx9N7AJ+IKkeeBNYHNUTv7XHNujuZiZWQ25Tp5lp2MOLti2u+r6PcA9eceamdni8TtjzcwS56A3M0ucg97MLHEOejOzxDnozcwS56A3M0ucg97MLHEOejOzxDnozcwS56A3M0ucg97MLHEOejOzxDnozcwS56A3M0ucg97MLHEOejOzxDnozcwSlyvoJY1KOiFpStKuGu1bJT2XXZ6WdHVV2ylJk5KOSTrSzeLNzKy5pl8lKGkAuBf4JDANHJa0PyJ+VtXtReCGiHhN0k3AOLChqn0kIl7pYt1mZpZTniP69cBURJyMiLeAMrCxukNEPB0Rr2U3nwFWdrdMMzNrlyKicQdpEzAaETuy29uADRGxs07/24GPVvV/EXgNCOB7ETFeZ9wYMAZQKBTWlcvltiZ05uwsL7/Z1tCOrF2xrGH73NwcQ0NDPXnsydOzbY8tXEzb69Vszp3o5Xp1wvtXay7U/auTOXdieNlA28/jyMjI0Ygo1mpreuoGUI1tNX86SBoBPg98omrz9RExI+lS4ElJL0TEoXfdYeUHwDhAsViMUqmUo7R3u/u+R7hrMs+0uuvU1lLD9omJCdqdUzPbdx1oe+xta+fbXq9mc+5EL9erE96/WnOh7l+dzLkTe0cHe/I85jl1Mw1cXnV7JTCzsJOkq4DvAxsj4tVz2yNiJvv3DLCPyqkgMzNbJHmC/jCwWtKwpKXAZmB/dQdJVwAPAdsi4hdV2wclXXLuOnAj8Hy3ijczs+aa/k4VEfOSdgKPAwPAnog4LunWrH03cAfwAeA7kgDms3NFBWBftu0i4P6IeKwnMzEzs5pynTyLiIPAwQXbdldd3wHsqDHuJHD1wu1mZrZ4/M5YM7PEOejNzBLnoDczS5yD3swscQ56M7PEOejNzBLnoDczS5yD3swscQ56M7PEOejNzBLnoDczS5yD3swscQ56M7PEOejNzBLnoDczS5yD3swscQ56M7PE5Qp6SaOSTkiakrSrRrskfTtrf07StXnHmplZbzUNekkDwL3ATcAaYIukNQu63QSszi5jwHdbGGtmZj2U54h+PTAVEScj4i2gDGxc0Gcj8IOoeAZ4v6QP5RxrZmY9lOfLwVcA/111exrYkKPPipxjAZA0RuW3AYA5SSdy1FbLcuCVNse2Td9o2qUvdTXz1x3UlWPOnTgv1wvvXy3x/tWakW90VNeH6zXkCXrV2BY5++QZW9kYMQ6M56inIUlHIqLY6f10m+tqjetqjetqzXutrjxBPw1cXnV7JTCTs8/SHGPNzKyH8pyjPwysljQsaSmwGdi/oM9+4LPZq2+uA2Yj4qWcY83MrIeaHtFHxLykncDjwACwJyKOS7o1a98NHARuBqaAXwGfazS2JzN5R8enf3rEdbXGdbXGdbXmPVWXImqeMjczs0T4nbFmZolz0JuZJe6CCXpJeySdkfR8nfaPSvoPSf8j6fYFbT37GIYO6zolaVLSMUlHFrmurdnHVTwn6WlJV1e19XO9GtXVz/XamNV0TNIRSZ+oauvnejWqq2/rVdXv45LelrSpalvf1qtJXf3cv0qSZrPHPibpjqq2ztcrIi6IC/AnwLXA83XaLwU+DvwjcHvV9gHgv4Dfp/Jyz58Ca/pdV9Z2Cljep/X6Y+B3sus3Af95nqxXzbrOg/Ua4p2/aV0FvHCerFfNuvq9XlVr869UXqyx6XxYr3p19Xu9gBLwaJ1aO16vC+aIPiIOAWcbtJ+JiMPArxc09fRjGDqoq6dy1PV0RLyW3XyGynscoP/rVa+unspR11xk//OAQd5541+/16teXT3VrK7Ml4AHgTNV2/q6Xg3q6qmcddXSlfW6YIK+A/U+nuF8EMATko6q8hEQ/fJ54IfZ9fNpvarrgj6vl6RbJL0AHAD+Mtvc9/WqUxf0cb0krQBuAXYvaOrrejWoC/r///GPJP1U0g8l/WG2rSvrleedsRe63B/D0AfXR8SMpEuBJyW9kP3kXzSSRqgE6rlzu+fFetWoC/q8XhGxD9gn6U+ArwF/ynmwXnXqgv6u17eAr0bE29JvLFG/1+tb1K4L+rtezwIfjog5STcDD1P5NOCurNd74Yg+z0c49EVEzGT/ngH2Ufk1bdFIugr4PrAxIl7NNvd9verU1ff1qqrjEPAHkpZzHqxXnbr6vV5FoCzpFLAJ+I6kP6P/61Wvrr6uV0S8HhFz2fWDwJJu7l/vhaA/Lz+GQdKgpEvOXQduBBq+UqDLj38F8BCwLSJ+UdXU1/WqV9d5sF4fUXYIqMoX6ywFXqX/61Wzrn6vV0QMR8SqiFgF/AvwVxHxMH1er3p19Xu9JF1W9Tyup5LNXdu/LphTN5IeoPKX6eWSpoE7gSVQ+RgGSZcBR4DfBv5X0pep/HX6dfXwYxjarYvKx6Tuy57bi4D7I+KxxaoLuAP4AJUjGoD5iChGjz+2ot26gAL9Xa8/p/J5Tr8G3gQ+k/0RtN/rVbMuSf1er5rOg/2rnn6v1ybgC5LmqTyPm7u5f/kjEMzMEvdeOHVjZvae5qA3M0ucg97MLHEOejOzxDnozcwS56A3M0ucg97MLHH/B1mChF3V1O4hAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df.price.hist();"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40c23794",
   "metadata": {},
   "source": [
    "Medium [article](https://towardsdatascience.com/heres-how-to-run-sql-in-jupyter-notebooks-f26eb90f3259)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "9347f7e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "9008966a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(oracle://hr:***@localhost:1521/orcl)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlalchemy.create_engine('oracle://hr:hr@localhost:1521/orcl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "531847e6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "8f09b7b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "1999d591",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(cx_Oracle.DatabaseError) DPI-1047: Cannot locate a 64-bit Oracle Client library: \"The specified module could not be found\". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help\n",
      "(Background on this error at: http://sqlalche.me/e/14/4xp6)\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys(['sqlite:///acme.db', 'sqlite:///exampledatabase.db'])\n"
     ]
    }
   ],
   "source": [
    "%sql oracle://hr:hr@localhost:1521/orcl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "f7f39dca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///acme.db\n",
      "   sqlite:///exampledatabase.db\n",
      "(sqlite3.OperationalError) no such table: employees\n",
      "[SQL: SELECT * FROM employees \n",
      "WHERE ROWNUM <= 5]\n",
      "(Background on this error at: http://sqlalche.me/e/14/e3q8)\n"
     ]
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "SELECT * FROM employees \n",
    "WHERE ROWNUM <= 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a53cc399",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}