Connecting Database to Jupyter Notebook.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "eb9c47cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from config import db_password\n",
    "import pyodbc\n",
    "import pandas as pd\n",
    "\n",
    "# Python SQL toolkit and Object Relational Mapper\n",
    "import sqlalchemy as db\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from sqlalchemy.orm import Session\n",
    "from sqlalchemy import create_engine, func"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f2db8215",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = db.create_engine('postgresql://postgres:nodebt@127.0.0.1:5432/Online_Shoppers_Intention')\n",
    "connection = engine.connect()\n",
    "metadata = db.MetaData()\n",
    "customer_id = db.Table('online_shoppers', metadata, autoload=True, autoload_with=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2f532873",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['administrative', 'administrative_duration', 'informational', 'informational_duration', 'productrelated', 'productrelated_duration', 'bouncerates', 'exitrates', 'pagevalues', 'specialday', 'month_name', 'operatingsystems', 'browser', 'region', 'traffictype', 'visitortype', 'weekend', 'revenue', 'visit_id']\n"
     ]
    }
   ],
   "source": [
    "# Print the column names\n",
    "print(customer_id.columns.keys())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "642546c9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['online_shoppers']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# reflect an existing database into a new model\n",
    "Base = automap_base()\n",
    "# reflect the tables\n",
    "Base.prepare(engine, reflect=True)\n",
    "# print classes to confirm f\n",
    "\n",
    "Base.classes.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e52d231e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create our session (link) from Python to the DB\n",
    "session = Session(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4e0aac02",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a connection to the SQL database\n",
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7515329b",
   "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>administrative</th>\n",
       "      <th>administrative_duration</th>\n",
       "      <th>informational</th>\n",
       "      <th>informational_duration</th>\n",
       "      <th>productrelated</th>\n",
       "      <th>productrelated_duration</th>\n",
       "      <th>bouncerates</th>\n",
       "      <th>exitrates</th>\n",
       "      <th>pagevalues</th>\n",
       "      <th>specialday</th>\n",
       "      <th>month_name</th>\n",
       "      <th>operatingsystems</th>\n",
       "      <th>browser</th>\n",
       "      <th>region</th>\n",
       "      <th>traffictype</th>\n",
       "      <th>visitortype</th>\n",
       "      <th>weekend</th>\n",
       "      <th>revenue</th>\n",
       "      <th>visit_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Feb</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Returning_Visitor</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Feb</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Returning_Visitor</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Feb</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>Returning_Visitor</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2.666667</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Feb</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>Returning_Visitor</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>627.500000</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Feb</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>Returning_Visitor</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   administrative  administrative_duration  informational  \\\n",
       "0               0                      0.0              0   \n",
       "1               0                      0.0              0   \n",
       "2               0                      0.0              0   \n",
       "3               0                      0.0              0   \n",
       "4               0                      0.0              0   \n",
       "\n",
       "   informational_duration  productrelated  productrelated_duration  \\\n",
       "0                     0.0               1                 0.000000   \n",
       "1                     0.0               2                64.000000   \n",
       "2                     0.0               1                 0.000000   \n",
       "3                     0.0               2                 2.666667   \n",
       "4                     0.0              10               627.500000   \n",
       "\n",
       "   bouncerates  exitrates  pagevalues  specialday month_name  \\\n",
       "0         0.20       0.20         0.0         0.0        Feb   \n",
       "1         0.00       0.10         0.0         0.0        Feb   \n",
       "2         0.20       0.20         0.0         0.0        Feb   \n",
       "3         0.05       0.14         0.0         0.0        Feb   \n",
       "4         0.02       0.05         0.0         0.0        Feb   \n",
       "\n",
       "   operatingsystems  browser  region  traffictype        visitortype  weekend  \\\n",
       "0                 1        1       1            1  Returning_Visitor    False   \n",
       "1                 2        2       1            2  Returning_Visitor    False   \n",
       "2                 4        1       9            3  Returning_Visitor    False   \n",
       "3                 3        2       2            4  Returning_Visitor    False   \n",
       "4                 3        3       1            4  Returning_Visitor     True   \n",
       "\n",
       "   revenue  visit_id  \n",
       "0    False         1  \n",
       "1    False         2  \n",
       "2    False         3  \n",
       "3    False         4  \n",
       "4    False         5  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query All Records in the the Database\n",
    "visitor_type_df = pd.read_sql(\"SELECT * FROM online_shoppers\", conn)\n",
    "visitor_type_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "85916f25",
   "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>visitortype</th>\n",
       "      <th>month_name</th>\n",
       "      <th>bouncerates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Returning_Visitor</td>\n",
       "      <td>Feb</td>\n",
       "      <td>0.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Returning_Visitor</td>\n",
       "      <td>Feb</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Returning_Visitor</td>\n",
       "      <td>Feb</td>\n",
       "      <td>0.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Returning_Visitor</td>\n",
       "      <td>Feb</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Returning_Visitor</td>\n",
       "      <td>Feb</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         visitortype month_name  bouncerates\n",
       "0  Returning_Visitor        Feb         0.20\n",
       "1  Returning_Visitor        Feb         0.00\n",
       "2  Returning_Visitor        Feb         0.20\n",
       "3  Returning_Visitor        Feb         0.05\n",
       "4  Returning_Visitor        Feb         0.02"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visitor_type_df = pd.read_sql(\"SELECT visitortype, month_name,bouncerates FROM online_shoppers\", conn)\n",
    "visitor_type_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "65bb8a09",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 12330 entries, 0 to 12329\n",
      "Data columns (total 3 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   visitortype  12330 non-null  object \n",
      " 1   month_name   12330 non-null  object \n",
      " 2   bouncerates  12330 non-null  float64\n",
      "dtypes: float64(1), object(2)\n",
      "memory usage: 289.1+ KB\n"
     ]
    }
   ],
   "source": [
    "visitor_type_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "073f92b5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Returning_Visitor    10551\n",
       "New_Visitor           1694\n",
       "Other                   85\n",
       "Name: visitortype, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visitor_type_df[\"visitortype\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "1632b5bd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "May     3364\n",
       "Nov     2998\n",
       "Mar     1907\n",
       "Dec     1727\n",
       "Oct      549\n",
       "Sep      448\n",
       "Aug      433\n",
       "Jul      432\n",
       "June     288\n",
       "Feb      184\n",
       "Name: month_name, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# explore the value counts with visitor typw with certain month \n",
    "visitor_type_df[\"month_name\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "baf3fda7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    12330.000000\n",
       "mean         0.022191\n",
       "std          0.048488\n",
       "min          0.000000\n",
       "25%          0.000000\n",
       "50%          0.003112\n",
       "75%          0.016813\n",
       "max          0.200000\n",
       "Name: bouncerates, dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# explore the value counts with visitor typw with certain month \n",
    "visitor_type_df[\"bouncerates\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b350754",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "288c6f27",
   "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}