data_insertion.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3035bb91",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This file creates dataframe from json dataset files and inserts data into mysql server.\n",
    "# This was the first method using mysql connector which is not very efficient so it takes considerable time to run"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c8a10a54",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: pandas in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (1.5.2)\n",
      "Requirement already satisfied: numpy>=1.20.3 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from pandas) (1.23.5)\n",
      "Requirement already satisfied: pytz>=2020.1 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from pandas) (2022.6)\n",
      "Requirement already satisfied: python-dateutil>=2.8.1 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from pandas) (2.8.2)\n",
      "Requirement already satisfied: six>=1.5 in /Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/site-packages (from python-dateutil>=2.8.1->pandas) (1.15.0)\n",
      "\u001b[33mWARNING: You are using pip version 21.2.4; however, version 22.3.1 is available.\n",
      "You should consider upgrading via the '/Library/Developer/CommandLineTools/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n",
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: SQLAlchemy in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (1.4.44)\n",
      "\u001b[33mWARNING: You are using pip version 21.2.4; however, version 22.3.1 is available.\n",
      "You should consider upgrading via the '/Library/Developer/CommandLineTools/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n",
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: PyMySQL in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (1.0.2)\n",
      "\u001b[33mWARNING: You are using pip version 21.2.4; however, version 22.3.1 is available.\n",
      "You should consider upgrading via the '/Library/Developer/CommandLineTools/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!pip3 install pandas\n",
    "!pip3 install SQLAlchemy\n",
    "!pip3 install PyMySQL\n",
    "import pymysql\n",
    "from urllib.parse import quote \n",
    "import mysql.connector\n",
    "import sqlalchemy\n",
    "from sqlalchemy.engine import create_engine\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "15a6657e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<mysql.connector.connection_cext.CMySQLConnection object at 0x11f812f40>\n"
     ]
    }
   ],
   "source": [
    "#connect to mysql servel\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",\n",
    "  user=\"root\",\n",
    "  password=\"Parvashah@123\",\n",
    "  database=\"yelpdb\"\n",
    ")\n",
    "\n",
    "print(mydb)\n",
    "\n",
    "mycursor = mydb.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0e7823c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create business table\n",
    "mycursor.execute(\"DROP TABLE IF EXISTS business\")\n",
    "mycursor.execute(\"CREATE TABLE business (business_id VARCHAR(255) PRIMARY KEY, name VARCHAR(255), address VARCHAR(255), city VARCHAR(255), state TEXT(2), postal_code INT(5), stars DECIMAL(10,1), review_count INT, is_open BOOL)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "482669b9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "150346 was inserted.\n"
     ]
    }
   ],
   "source": [
    "# Insert data into business table from json file\n",
    "\n",
    "# dataframe creation\n",
    "df_b = pd.read_json('yelp_dataset/yelp_academic_dataset_business.json', lines=True)\n",
    "# delete unwanted columns\n",
    "del df_b['attributes'], df_b['hours'], df_b['categories'], df_b['latitude'], df_b['longitude']\n",
    "\n",
    "sql = \"INSERT IGNORE INTO business (business_id, name, address, city, state, postal_code, stars, review_count, is_open) VALUES (%s, %s, %s, %s, %s, %s,%s, %s, %s)\"\n",
    "val = list(df_b.itertuples(index=False,name=None))\n",
    "\n",
    "mycursor.executemany(sql, val)\n",
    "\n",
    "mydb.commit()\n",
    "\n",
    "print(mycursor.rowcount, \"was inserted.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "2faa362f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create user table\n",
    "mycursor.execute(\"DROP TABLE IF EXISTS user\")\n",
    "mycursor.execute(\"CREATE TABLE user (user_id VARCHAR(255) PRIMARY KEY, name VARCHAR(255), review_count INT, average_stars FLOAT, yelping_since DATETIME)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "b37c7a4a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1987897 was inserted.\n"
     ]
    }
   ],
   "source": [
    "chunks = pd.read_json('yelp_dataset/yelp_academic_dataset_user.json', lines=True, chunksize = 10000)\n",
    "users = pd.DataFrame()\n",
    "for chunk in chunks:\n",
    "    users = pd.concat([users, chunk])\n",
    "\n",
    "del users['useful'], users['funny'], users['cool'], users['elite'], users['friends'], users['fans'], users['compliment_hot'], users['compliment_more'], users['compliment_profile'], users['compliment_cute'], users['compliment_list'], users['compliment_note'], users['compliment_plain'], users['compliment_cool'], users['compliment_funny'], users['compliment_writer'], users['compliment_photos']\n",
    "val = list(users.itertuples(index=False,name=None))\n",
    "sql = \"INSERT IGNORE INTO user (user_id, name, review_count, average_stars, yelping_since) VALUES (%s, %s, %s, %s, %s)\"\n",
    "\n",
    "mycursor.executemany(sql, val)\n",
    "\n",
    "mydb.commit()\n",
    "\n",
    "print(mycursor.rowcount, \"was inserted.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a673beb8",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'mycursor' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[1], line 2\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[39m# create review table\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m mycursor\u001b[39m.\u001b[39mexecute(\u001b[39m\"\u001b[39m\u001b[39mDROP TABLE IF EXISTS review\u001b[39m\u001b[39m\"\u001b[39m)\n\u001b[1;32m      3\u001b[0m mycursor\u001b[39m.\u001b[39mexecute(\u001b[39m\"\u001b[39m\u001b[39mCREATE TABLE review (review_id VARCHAR(255) PRIMARY KEY, user_id VARCHAR(255), business_id VARCHAR(255), stars INT(1), text TEXT(65535), date DATETIME, CONSTRAINT user_id_fk FOREIGN KEY (user_id) REFERENCES user (user_id) ON UPDATE CASCADE ON DELETE CASCADE,CONSTRAINT business_id_fk FOREIGN KEY (business_id) REFERENCES business (business_id) ON UPDATE CASCADE ON DELETE CASCADE)\u001b[39m\u001b[39m\"\u001b[39m)\n",
      "\u001b[0;31mNameError\u001b[0m: name 'mycursor' is not defined"
     ]
    }
   ],
   "source": [
    "# create review table\n",
    "mycursor.execute(\"DROP TABLE IF EXISTS review\")\n",
    "mycursor.execute(\"CREATE TABLE review (review_id VARCHAR(255) PRIMARY KEY, user_id VARCHAR(255), business_id VARCHAR(255), stars INT(1), text TEXT(65535), date DATETIME, CONSTRAINT user_id_fk FOREIGN KEY (user_id) REFERENCES user (user_id) ON UPDATE CASCADE ON DELETE CASCADE,CONSTRAINT business_id_fk FOREIGN KEY (business_id) REFERENCES business (business_id) ON UPDATE CASCADE ON DELETE CASCADE)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "56c4a54e",
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[36], line 6\u001b[0m\n\u001b[1;32m      4\u001b[0m reviews \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mDataFrame()\n\u001b[1;32m      5\u001b[0m \u001b[39mfor\u001b[39;00m chunk \u001b[39min\u001b[39;00m chunks:\n\u001b[0;32m----> 6\u001b[0m     reviews \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39;49mconcat([reviews, chunk])\n\u001b[1;32m      7\u001b[0m \u001b[39m# delete unwanted columns from dataframe\u001b[39;00m\n\u001b[1;32m      8\u001b[0m \u001b[39mdel\u001b[39;00m reviews[\u001b[39m'\u001b[39m\u001b[39museful\u001b[39m\u001b[39m'\u001b[39m], reviews[\u001b[39m'\u001b[39m\u001b[39mfunny\u001b[39m\u001b[39m'\u001b[39m], reviews[\u001b[39m'\u001b[39m\u001b[39mcool\u001b[39m\u001b[39m'\u001b[39m]\n",
      "File \u001b[0;32m~/Library/Python/3.9/lib/python/site-packages/pandas/util/_decorators.py:331\u001b[0m, in \u001b[0;36mdeprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    325\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mlen\u001b[39m(args) \u001b[39m>\u001b[39m num_allow_args:\n\u001b[1;32m    326\u001b[0m     warnings\u001b[39m.\u001b[39mwarn(\n\u001b[1;32m    327\u001b[0m         msg\u001b[39m.\u001b[39mformat(arguments\u001b[39m=\u001b[39m_format_argument_list(allow_args)),\n\u001b[1;32m    328\u001b[0m         \u001b[39mFutureWarning\u001b[39;00m,\n\u001b[1;32m    329\u001b[0m         stacklevel\u001b[39m=\u001b[39mfind_stack_level(),\n\u001b[1;32m    330\u001b[0m     )\n\u001b[0;32m--> 331\u001b[0m \u001b[39mreturn\u001b[39;00m func(\u001b[39m*\u001b[39;49margs, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49mkwargs)\n",
      "File \u001b[0;32m~/Library/Python/3.9/lib/python/site-packages/pandas/core/reshape/concat.py:381\u001b[0m, in \u001b[0;36mconcat\u001b[0;34m(objs, axis, join, ignore_index, keys, levels, names, verify_integrity, sort, copy)\u001b[0m\n\u001b[1;32m    159\u001b[0m \u001b[39m\"\"\"\u001b[39;00m\n\u001b[1;32m    160\u001b[0m \u001b[39mConcatenate pandas objects along a particular axis.\u001b[39;00m\n\u001b[1;32m    161\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    366\u001b[0m \u001b[39m1   3   4\u001b[39;00m\n\u001b[1;32m    367\u001b[0m \u001b[39m\"\"\"\u001b[39;00m\n\u001b[1;32m    368\u001b[0m op \u001b[39m=\u001b[39m _Concatenator(\n\u001b[1;32m    369\u001b[0m     objs,\n\u001b[1;32m    370\u001b[0m     axis\u001b[39m=\u001b[39maxis,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    378\u001b[0m     sort\u001b[39m=\u001b[39msort,\n\u001b[1;32m    379\u001b[0m )\n\u001b[0;32m--> 381\u001b[0m \u001b[39mreturn\u001b[39;00m op\u001b[39m.\u001b[39;49mget_result()\n",
      "File \u001b[0;32m~/Library/Python/3.9/lib/python/site-packages/pandas/core/reshape/concat.py:616\u001b[0m, in \u001b[0;36m_Concatenator.get_result\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    612\u001b[0m             indexers[ax] \u001b[39m=\u001b[39m obj_labels\u001b[39m.\u001b[39mget_indexer(new_labels)\n\u001b[1;32m    614\u001b[0m     mgrs_indexers\u001b[39m.\u001b[39mappend((obj\u001b[39m.\u001b[39m_mgr, indexers))\n\u001b[0;32m--> 616\u001b[0m new_data \u001b[39m=\u001b[39m concatenate_managers(\n\u001b[1;32m    617\u001b[0m     mgrs_indexers, \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mnew_axes, concat_axis\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mbm_axis, copy\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mcopy\n\u001b[1;32m    618\u001b[0m )\n\u001b[1;32m    619\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mcopy:\n\u001b[1;32m    620\u001b[0m     new_data\u001b[39m.\u001b[39m_consolidate_inplace()\n",
      "File \u001b[0;32m~/Library/Python/3.9/lib/python/site-packages/pandas/core/internals/concat.py:233\u001b[0m, in \u001b[0;36mconcatenate_managers\u001b[0;34m(mgrs_indexers, axes, concat_axis, copy)\u001b[0m\n\u001b[1;32m    231\u001b[0m     fastpath \u001b[39m=\u001b[39m blk\u001b[39m.\u001b[39mvalues\u001b[39m.\u001b[39mdtype \u001b[39m==\u001b[39m values\u001b[39m.\u001b[39mdtype\n\u001b[1;32m    232\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[0;32m--> 233\u001b[0m     values \u001b[39m=\u001b[39m _concatenate_join_units(join_units, concat_axis, copy\u001b[39m=\u001b[39;49mcopy)\n\u001b[1;32m    234\u001b[0m     fastpath \u001b[39m=\u001b[39m \u001b[39mFalse\u001b[39;00m\n\u001b[1;32m    236\u001b[0m \u001b[39mif\u001b[39;00m fastpath:\n",
      "File \u001b[0;32m~/Library/Python/3.9/lib/python/site-packages/pandas/core/internals/concat.py:577\u001b[0m, in \u001b[0;36m_concatenate_join_units\u001b[0;34m(join_units, concat_axis, copy)\u001b[0m\n\u001b[1;32m    574\u001b[0m     concat_values \u001b[39m=\u001b[39m ensure_block_shape(concat_values, \u001b[39m2\u001b[39m)\n\u001b[1;32m    576\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[0;32m--> 577\u001b[0m     concat_values \u001b[39m=\u001b[39m concat_compat(to_concat, axis\u001b[39m=\u001b[39;49mconcat_axis)\n\u001b[1;32m    579\u001b[0m \u001b[39mreturn\u001b[39;00m concat_values\n",
      "File \u001b[0;32m~/Library/Python/3.9/lib/python/site-packages/pandas/core/dtypes/concat.py:151\u001b[0m, in \u001b[0;36mconcat_compat\u001b[0;34m(to_concat, axis, ea_compat_axis)\u001b[0m\n\u001b[1;32m    148\u001b[0m             to_concat \u001b[39m=\u001b[39m [x\u001b[39m.\u001b[39mastype(\u001b[39m\"\u001b[39m\u001b[39mobject\u001b[39m\u001b[39m\"\u001b[39m) \u001b[39mfor\u001b[39;00m x \u001b[39min\u001b[39;00m to_concat]\n\u001b[1;32m    149\u001b[0m             kinds \u001b[39m=\u001b[39m {\u001b[39m\"\u001b[39m\u001b[39mo\u001b[39m\u001b[39m\"\u001b[39m}\n\u001b[0;32m--> 151\u001b[0m result \u001b[39m=\u001b[39m np\u001b[39m.\u001b[39;49mconcatenate(to_concat, axis\u001b[39m=\u001b[39;49maxis)\n\u001b[1;32m    152\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39m\"\u001b[39m\u001b[39mb\u001b[39m\u001b[39m\"\u001b[39m \u001b[39min\u001b[39;00m kinds \u001b[39mand\u001b[39;00m result\u001b[39m.\u001b[39mdtype\u001b[39m.\u001b[39mkind \u001b[39min\u001b[39;00m [\u001b[39m\"\u001b[39m\u001b[39mi\u001b[39m\u001b[39m\"\u001b[39m, \u001b[39m\"\u001b[39m\u001b[39mu\u001b[39m\u001b[39m\"\u001b[39m, \u001b[39m\"\u001b[39m\u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m]:\n\u001b[1;32m    153\u001b[0m     \u001b[39m# GH#39817\u001b[39;00m\n\u001b[1;32m    154\u001b[0m     warnings\u001b[39m.\u001b[39mwarn(\n\u001b[1;32m    155\u001b[0m         \u001b[39m\"\u001b[39m\u001b[39mBehavior when concatenating bool-dtype and numeric-dtype arrays is \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m    156\u001b[0m         \u001b[39m\"\u001b[39m\u001b[39mdeprecated; in a future version these will cast to object dtype \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    160\u001b[0m         stacklevel\u001b[39m=\u001b[39mfind_stack_level(),\n\u001b[1;32m    161\u001b[0m     )\n",
      "File \u001b[0;32m<__array_function__ internals>:180\u001b[0m, in \u001b[0;36mconcatenate\u001b[0;34m(*args, **kwargs)\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "# Insert data into review table from json file\n",
    "# dataframe creation is done in chunks since it is a large file which cannot be read at once.\n",
    "chunks = pd.read_json('yelp_dataset/yelp_academic_dataset_review.json', lines=True, chunksize = 10000)\n",
    "reviews = pd.DataFrame()\n",
    "for chunk in chunks:\n",
    "    reviews = pd.concat([reviews, chunk])\n",
    "# delete unwanted columns from dataframe\n",
    "del reviews['useful'], reviews['funny'], reviews['cool']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "a0c0db40",
   "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>review_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>business_id</th>\n",
       "      <th>stars</th>\n",
       "      <th>useful</th>\n",
       "      <th>funny</th>\n",
       "      <th>cool</th>\n",
       "      <th>text</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>KU_O5udG6zpxOg-VcAEodg</td>\n",
       "      <td>mh_-eMZ6K5RLWhZyISBhwA</td>\n",
       "      <td>XQfwVwDr-v0ZS3_CbbE5Xw</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>If you decide to eat here, just be aware it is...</td>\n",
       "      <td>2018-07-07 22:09:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BiTunyQ73aT9WBnpR9DZGw</td>\n",
       "      <td>OyoGAe7OKpv6SyGZT5g77Q</td>\n",
       "      <td>7ATYjTIgM3jUlt4UM3IypQ</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>I've taken a lot of spin classes over the year...</td>\n",
       "      <td>2012-01-03 15:28:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>saUsX_uimxRlCVr67Z4Jig</td>\n",
       "      <td>8g_iMtfSiwikVnbP2etR0A</td>\n",
       "      <td>YjUWPpI6HXG530lwP-fb2A</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Family diner. Had the buffet. Eclectic assortm...</td>\n",
       "      <td>2014-02-05 20:30:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AqPFMleE6RsU23_auESxiA</td>\n",
       "      <td>_7bHUi9Uuf5__HHc_Q8guQ</td>\n",
       "      <td>kxX2SOes4o-D3ZQBkiMRfA</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Wow!  Yummy, different,  delicious.   Our favo...</td>\n",
       "      <td>2015-01-04 00:01:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sx8TMOWLNuJBWer-0pcmoA</td>\n",
       "      <td>bcjbaE6dDog4jkNY91ncLQ</td>\n",
       "      <td>e4Vwtrqf-wpJfwesgvdgxQ</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Cute interior and owner (?) gave us tour of up...</td>\n",
       "      <td>2017-01-14 20:54:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5089995</th>\n",
       "      <td>1sb3k7AVkPuHRh4NOLpICg</td>\n",
       "      <td>RVXG9BjSqAEvPAzUAsY_8g</td>\n",
       "      <td>mpeLyxsDTm5RqZkFT--SdQ</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>The table was dirty when we sat down. The drin...</td>\n",
       "      <td>2017-08-13 03:32:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5089996</th>\n",
       "      <td>dQ22aY7R_TLhltogeAY3ZQ</td>\n",
       "      <td>ZDc-keECogZyw0U2x6xYpw</td>\n",
       "      <td>SjiRaZoWaDpkCCpq5SsrMw</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>We stopped in for lunch and sat at the bar wit...</td>\n",
       "      <td>2014-07-21 17:53:09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5089997</th>\n",
       "      <td>UEcL3XitFeg-wtZE5E_6Tg</td>\n",
       "      <td>vGifaZpcscr0WOOp2ctadw</td>\n",
       "      <td>_6woRce_3sGy5zDrpbM4Vg</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Went here to get a basic service on my car.  S...</td>\n",
       "      <td>2016-06-27 19:27:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5089998</th>\n",
       "      <td>i6keVbnvkO5MZoCdAdR_8w</td>\n",
       "      <td>qTMK2qr6ngof4fe29qyooA</td>\n",
       "      <td>9U5nEjCeHJ1yr8pEYkKB1A</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>I'm not a fan of the Gargoyle, plain and simpl...</td>\n",
       "      <td>2011-02-08 21:27:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5089999</th>\n",
       "      <td>Bhc9pFDRlxtmuq3b4lcxYA</td>\n",
       "      <td>Mkeq3sxV-CUktete8bqa1w</td>\n",
       "      <td>SjiRaZoWaDpkCCpq5SsrMw</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>This place was the first place i went to to ea...</td>\n",
       "      <td>2018-06-26 15:41:07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5090000 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      review_id                 user_id  \\\n",
       "0        KU_O5udG6zpxOg-VcAEodg  mh_-eMZ6K5RLWhZyISBhwA   \n",
       "1        BiTunyQ73aT9WBnpR9DZGw  OyoGAe7OKpv6SyGZT5g77Q   \n",
       "2        saUsX_uimxRlCVr67Z4Jig  8g_iMtfSiwikVnbP2etR0A   \n",
       "3        AqPFMleE6RsU23_auESxiA  _7bHUi9Uuf5__HHc_Q8guQ   \n",
       "4        Sx8TMOWLNuJBWer-0pcmoA  bcjbaE6dDog4jkNY91ncLQ   \n",
       "...                         ...                     ...   \n",
       "5089995  1sb3k7AVkPuHRh4NOLpICg  RVXG9BjSqAEvPAzUAsY_8g   \n",
       "5089996  dQ22aY7R_TLhltogeAY3ZQ  ZDc-keECogZyw0U2x6xYpw   \n",
       "5089997  UEcL3XitFeg-wtZE5E_6Tg  vGifaZpcscr0WOOp2ctadw   \n",
       "5089998  i6keVbnvkO5MZoCdAdR_8w  qTMK2qr6ngof4fe29qyooA   \n",
       "5089999  Bhc9pFDRlxtmuq3b4lcxYA  Mkeq3sxV-CUktete8bqa1w   \n",
       "\n",
       "                    business_id  stars  useful  funny  cool  \\\n",
       "0        XQfwVwDr-v0ZS3_CbbE5Xw      3       0      0     0   \n",
       "1        7ATYjTIgM3jUlt4UM3IypQ      5       1      0     1   \n",
       "2        YjUWPpI6HXG530lwP-fb2A      3       0      0     0   \n",
       "3        kxX2SOes4o-D3ZQBkiMRfA      5       1      0     1   \n",
       "4        e4Vwtrqf-wpJfwesgvdgxQ      4       1      0     1   \n",
       "...                         ...    ...     ...    ...   ...   \n",
       "5089995  mpeLyxsDTm5RqZkFT--SdQ      1       0      0     0   \n",
       "5089996  SjiRaZoWaDpkCCpq5SsrMw      5       2      1     2   \n",
       "5089997  _6woRce_3sGy5zDrpbM4Vg      5       0      0     0   \n",
       "5089998  9U5nEjCeHJ1yr8pEYkKB1A      2       2      4     1   \n",
       "5089999  SjiRaZoWaDpkCCpq5SsrMw      5       2      0     2   \n",
       "\n",
       "                                                      text                date  \n",
       "0        If you decide to eat here, just be aware it is... 2018-07-07 22:09:11  \n",
       "1        I've taken a lot of spin classes over the year... 2012-01-03 15:28:18  \n",
       "2        Family diner. Had the buffet. Eclectic assortm... 2014-02-05 20:30:30  \n",
       "3        Wow!  Yummy, different,  delicious.   Our favo... 2015-01-04 00:01:03  \n",
       "4        Cute interior and owner (?) gave us tour of up... 2017-01-14 20:54:15  \n",
       "...                                                    ...                 ...  \n",
       "5089995  The table was dirty when we sat down. The drin... 2017-08-13 03:32:30  \n",
       "5089996  We stopped in for lunch and sat at the bar wit... 2014-07-21 17:53:09  \n",
       "5089997  Went here to get a basic service on my car.  S... 2016-06-27 19:27:51  \n",
       "5089998  I'm not a fan of the Gargoyle, plain and simpl... 2011-02-08 21:27:51  \n",
       "5089999  This place was the first place i went to to ea... 2018-06-26 15:41:07  \n",
       "\n",
       "[5090000 rows x 9 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Insert the dataframe in to sql\n",
    "val = list(reviews.itertuples(index=False,name=None))\n",
    "sql = \"INSERT IGNORE INTO review (review_id, user_id, business_id, stars, text, date) VALUES (%s, %s, %s, %s, %s, %s)\"\n",
    "\n",
    "mycursor.executemany(sql, val)\n",
    "\n",
    "mydb.commit()\n",
    "\n",
    "print(mycursor.rowcount, \"was inserted.\")\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.6 64-bit",
   "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.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}