Python_MySQL_Hiccups.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Python to MySQL: Hiccups you will encounter`\n",
    "\n",
    "# <font color=red>Mr Fugu Data Science</font>\n",
    "\n",
    "# (◕‿◕✿)\n",
    "\n",
    "# `Outcome:`\n",
    "\n",
    "+ `Problems with Date-Time`\n",
    "+ `Handling NaN`\n",
    "+ `Schema Considerations`\n",
    "+ `Dot Notation Naming`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd # dataframe stuff\n",
    "# import json # \n",
    "from collections import defaultdict # dictionary where values are a list\n",
    "import numpy as np # formatting data with Nan\n",
    "from datetime import date\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Install: mysql.connector`\n",
    "\n",
    "`conda install -c anaconda mysql-connector-python`\n",
    "\n",
    "`pip install mysql-connector`\n",
    "\n",
    "or `pip3 install mysql-connector`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup MySQL for us to send data and create a new DB\n",
    "\n",
    "import mysql.connector as mysql # establish connection to mysql \n",
    "\n",
    "from mysql.connector import MySQLConnection # using Class for function call\n",
    "\n",
    "import pandas as pd  # Create Dataframe\n",
    "\n",
    "from python_to_myql_config import read_db_config # calling config files, (you need to make this) \n",
    "# stored user credentials I have, or use the block of code below\n",
    "\n",
    "import csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Turn on mysql server for this to work: in Mac terminal`\n",
    "\n",
    "\n",
    "**`mysql.server start`**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `If you want user` <font color=red>credential</font> `setup` <font color=red>go here</font>`:`\n",
    "\n",
    "https://github.com/MrFuguDataScience/SendDataFrom-R-Python-to-SQL-PSQL-Mongo/blob/master/Python_to_mysql_configFiles.ipynb\n",
    "\n",
    "**IF YOU DO NOT HAVE CREDENTIALS OR IT DOESN'T MATTER TO YOU TRY THIS:**\n",
    "\n",
    "`\n",
    "cnx = mysql.connector.connect(user='bambam', password='password',\n",
    "                              host='127.0.0.1',\n",
    "                              database='cowboy_doodah')\n",
    "cnx.close()\n",
    "`\n",
    "\n",
    "+ Remember: replace, user, password and database with your information!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_config = read_db_config() # user credentials are mine, you will need to change this line\n",
    "\n",
    "conn = MySQLConnection(**read_db_config()) # establish connection\n",
    "\n",
    "mycursor = conn.cursor() # cursor, allows us to do queries,send/retrieve data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('project_practice',)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show What DB we are using:\n",
    "\n",
    "# show what database we are currently using:\n",
    "mycursor.execute(\"SELECT DATABASE()\")\n",
    "mycursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('secure_file_priv', '')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Turn this on, if you have issues sending data\n",
    "\n",
    "mycursor.execute('SET GLOBAL local_infile=1')\n",
    "\n",
    "# This checks if you have issues with exporting later due to security:\n",
    "mycursor.execute('SHOW VARIABLES LIKE \"secure_file_priv\"')\n",
    "mycursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('information_schema',),\n",
       " ('JA_w_help',),\n",
       " ('mysql',),\n",
       " ('performance_schema',),\n",
       " ('project_practice',),\n",
       " ('sys',)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check Db Using:\n",
    "mycursor.execute(\"SHOW DATABASES\")\n",
    "mycursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('cpu_purchase',),\n",
       " ('dupl_transactions_copy',),\n",
       " ('Julie_Sales_dta',),\n",
       " ('Julie_Sales_dta_26',),\n",
       " ('Sales_Transactions',),\n",
       " ('user_info',),\n",
       " ('user_o',),\n",
       " ('users_inf',),\n",
       " ('users_info',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check Tables:\n",
    "\n",
    "mycursor.execute(\"SHOW TABLES\")\n",
    "mycursor.fetchall()\n",
    "\n",
    "# mycursor.execute(\"DROP TABLE dot_notation_table_02\")\n",
    "# mycursor.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Ex.1) Evaluating Dates/Time Formatting MySQL`\n",
    "\n",
    "+ This is an example of Timestamp data but we need to format it before using it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>createDate_date</th>\n",
       "      <th>dateScanned_date</th>\n",
       "      <th>finishedDate_date</th>\n",
       "      <th>modifyDate_date</th>\n",
       "      <th>pointsAwardedDate_date</th>\n",
       "      <th>purchaseDate_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1609687531000</td>\n",
       "      <td>1609687531000</td>\n",
       "      <td>1.609688e+12</td>\n",
       "      <td>1609687536000</td>\n",
       "      <td>1.609688e+12</td>\n",
       "      <td>1.609632e+12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1609687483000</td>\n",
       "      <td>1609687483000</td>\n",
       "      <td>1.609687e+12</td>\n",
       "      <td>1609687488000</td>\n",
       "      <td>1.609687e+12</td>\n",
       "      <td>1.609601e+12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1609687483000</td>\n",
       "      <td>1609687483000</td>\n",
       "      <td>1.609687e+12</td>\n",
       "      <td>1609687488000</td>\n",
       "      <td>1.609687e+12</td>\n",
       "      <td>1.609601e+12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1609687537000</td>\n",
       "      <td>1609687537000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1609687542000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.609632e+12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1609687534000</td>\n",
       "      <td>1609687534000</td>\n",
       "      <td>1.609688e+12</td>\n",
       "      <td>1609687539000</td>\n",
       "      <td>1.609688e+12</td>\n",
       "      <td>1.609632e+12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   createDate_date  dateScanned_date  finishedDate_date  modifyDate_date  \\\n",
       "0    1609687531000     1609687531000       1.609688e+12    1609687536000   \n",
       "1    1609687483000     1609687483000       1.609687e+12    1609687488000   \n",
       "2    1609687483000     1609687483000       1.609687e+12    1609687488000   \n",
       "3    1609687537000     1609687537000                NaN    1609687542000   \n",
       "4    1609687534000     1609687534000       1.609688e+12    1609687539000   \n",
       "\n",
       "   pointsAwardedDate_date  purchaseDate_date  \n",
       "0            1.609688e+12       1.609632e+12  \n",
       "1            1.609687e+12       1.609601e+12  \n",
       "2            1.609687e+12       1.609601e+12  \n",
       "3                     NaN       1.609632e+12  \n",
       "4            1.609688e+12       1.609632e+12  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Date-Time csv:\n",
    "dates_df=pd.read_csv('date_time_ex.csv')\n",
    "dates_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>createDate_date</th>\n",
       "      <th>dateScanned_date</th>\n",
       "      <th>finishedDate_date</th>\n",
       "      <th>modifyDate_date</th>\n",
       "      <th>pointsAwardedDate_date</th>\n",
       "      <th>purchaseDate_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  createDate_date dateScanned_date finishedDate_date modifyDate_date  \\\n",
       "0      2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "1      2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "2      2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "3      2021-01-03       2021-01-03               NaN      2021-01-03   \n",
       "4      2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "\n",
       "  pointsAwardedDate_date purchaseDate_date  \n",
       "0             2021-01-03        2021-01-03  \n",
       "1             2021-01-03        2021-01-02  \n",
       "2             2021-01-03        2021-01-02  \n",
       "3                    NaN        2021-01-03  \n",
       "4             2021-01-03        2021-01-03  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating proper format for dates\n",
    "divide_by_thous_dts=dates_df.div(1000, axis=0)\n",
    "\n",
    "# doing this across all date columns\n",
    "convert_to_dts=divide_by_thous_dts.apply(pd.to_datetime, errors='coerce',\n",
    "                                         utc=True,unit='s')\n",
    "\n",
    "# If you need date with time unblock the , %H:%M:%S\")) portion of code and it will \n",
    "# work BUT, CHANGE SCHEMA to DATETIME instead of DATE!\n",
    "\n",
    "format_dt_df=convert_to_dts.apply(lambda x: x.dt.strftime(\"%Y-%m-%d\"))\n",
    "                                        \n",
    "#                                        , %H:%M:%S\"))\n",
    "format_dt_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Here is a potential issue:` \n",
    "+ **`['finishedDate_date','dateScanned_date','modifyDate_date']`** you have `NaN` for dates \n",
    "and empty string is not working\n",
    "+ *let's investigate all the dates first to make sure that this is not a fluke and needs further testing*\n",
    "\n",
    "\n",
    "`What is the problem with NaN values for MySQL import/Insert?`\n",
    "\n",
    "\n",
    "# `Python Nan is not the same as MySQL Null...`\n",
    "+ You need to get creative and think about how you want to handle this!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "createDate_date              0\n",
       "dateScanned_date             0\n",
       "finishedDate_date         1411\n",
       "modifyDate_date              0\n",
       "pointsAwardedDate_date    1301\n",
       "purchaseDate_date          458\n",
       "dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "format_dt_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create Schema:\n",
    "\n",
    "sql_01 =\"\"\"\n",
    "CREATE TABLE date_table_Ex01(createDate_date DATE,\n",
    "dateScanned_date DATE, finishedDate_date DATE, modifyDate_date DATE,\n",
    "pointsAwardedDate_date DATE, purchaseDate_date DATE)\"\"\"\n",
    "\n",
    "# or DATETIME depending on your needs, but change formatting above first"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Send Data to MySQL from Python `"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating table as per requirement, sql is the schema from above we created\n",
    "\n",
    "mycursor.execute(sql_01)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "1054 (42S22): Unknown column 'nan' in 'field list'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-20-6be064b4faef>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0mdateScanned_date\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfinishedDate_date\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmodifyDate_date\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m pointsAwardedDate_date, purchaseDate_date ) VALUES(%s,%s,%s,%s,%s,%s)\n\u001b[0;32m----> 5\u001b[0;31m \"\"\",list(format_dt_df.values))\n\u001b[0m\u001b[1;32m      6\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/cursor.py\u001b[0m in \u001b[0;36mexecutemany\u001b[0;34m(self, operation, seq_params)\u001b[0m\n\u001b[1;32m    668\u001b[0m             \u001b[0mstmt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_batch_insert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0moperation\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mseq_params\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    669\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mstmt\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 670\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    671\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    672\u001b[0m         \u001b[0mrowcnt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/cursor.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, params, multi)\u001b[0m\n\u001b[1;32m    567\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    568\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 569\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcmd_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    570\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mInterfaceError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    571\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_have_next_result\u001b[0m\u001b[0;34m:\u001b[0m  \u001b[0;31m# pylint: disable=W0212\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/connection.py\u001b[0m in \u001b[0;36mcmd_query\u001b[0;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[1;32m    588\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbytes\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    589\u001b[0m             \u001b[0mquery\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'utf-8'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 590\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_send_cmd\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mServerCmd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mQUERY\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    591\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    592\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_have_next_result\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/connection.py\u001b[0m in \u001b[0;36m_handle_result\u001b[0;34m(self, packet)\u001b[0m\n\u001b[1;32m    476\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_eof\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpacket\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    477\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0mpacket\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m4\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m255\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 478\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpacket\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    479\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    480\u001b[0m         \u001b[0;31m# We have a text result set\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: 1054 (42S22): Unknown column 'nan' in 'field list'"
     ]
    }
   ],
   "source": [
    "mycursor.executemany(\"\"\"\n",
    "INSERT INTO date_table_Ex01(createDate_date,\n",
    "dateScanned_date, finishedDate_date, modifyDate_date,\n",
    "pointsAwardedDate_date, purchaseDate_date ) VALUES(%s,%s,%s,%s,%s,%s)\n",
    "\"\"\",list(format_dt_df.values))\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Now What do we do?`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>createDate_date</th>\n",
       "      <th>dateScanned_date</th>\n",
       "      <th>finishedDate_date</th>\n",
       "      <th>modifyDate_date</th>\n",
       "      <th>pointsAwardedDate_date</th>\n",
       "      <th>purchaseDate_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7376</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7377</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7378</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2020-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2020-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7380</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7381 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     createDate_date dateScanned_date finishedDate_date modifyDate_date  \\\n",
       "0         2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "1         2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "2         2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "3         2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "4         2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "...              ...              ...               ...             ...   \n",
       "7376      2021-03-01       2021-03-01        2021-03-01      2021-03-01   \n",
       "7377      2021-03-01       2021-03-01        2021-03-01      2021-03-01   \n",
       "7378      2021-03-01       2021-03-01        2021-03-01      2021-03-01   \n",
       "7379      2021-03-01       2021-03-01        2021-03-01      2021-03-01   \n",
       "7380      2021-03-01       2021-03-01        2021-03-01      2021-03-01   \n",
       "\n",
       "     pointsAwardedDate_date purchaseDate_date  \n",
       "0                2021-01-03        2021-01-03  \n",
       "1                2021-01-03        2021-01-02  \n",
       "2                2021-01-03        2021-01-02  \n",
       "3                2021-01-03        2021-01-03  \n",
       "4                2021-01-03        2021-01-03  \n",
       "...                     ...               ...  \n",
       "7376             2021-03-01        2021-03-01  \n",
       "7377             2021-03-01        2021-03-01  \n",
       "7378             2021-03-01        2020-08-17  \n",
       "7379             2021-03-01        2020-08-17  \n",
       "7380             2021-03-01        2021-03-01  \n",
       "\n",
       "[7381 rows x 6 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Send Data: W/O Converting Dates Correctly (2 ways to do this)\n",
    "\n",
    "# 1) \n",
    "\n",
    "\"\"\"\n",
    "We have the option to grab data from one consistant column and use that as our \n",
    "source to work from to fill\n",
    "NaN values. In this case I was considering using ['createDate_date']\n",
    "\"\"\"\n",
    "\n",
    "# deep copy: has own index and copy of data vs shallow copy which shares! \n",
    "# If I don't do this I will overwrite\n",
    "format_updated_df=format_dt_df.copy()\n",
    "\n",
    "# Fill Nan with CreateDate_date Column for each row where corresponding Column has Nan\n",
    "format_updated_df.apply(lambda x: x.fillna(format_updated_df.createDate_date,inplace=True))\n",
    "\n",
    "# No more NaN but how do I know it did everything correct? Further investigation below\n",
    "format_updated_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id    col                   \n",
       "3     finishedDate_date         True\n",
       "      pointsAwardedDate_date    True\n",
       "14    finishedDate_date         True\n",
       "      pointsAwardedDate_date    True\n",
       "15    finishedDate_date         True\n",
       "                                ... \n",
       "7379  finishedDate_date         True\n",
       "      pointsAwardedDate_date    True\n",
       "7380  finishedDate_date         True\n",
       "      pointsAwardedDate_date    True\n",
       "      purchaseDate_date         True\n",
       "Length: 3170, dtype: bool"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's Check and see if its correct: USE Comparisons (Stckoverflow adapted, citation below)\n",
    "\n",
    "ne_stacked=(format_dt_df != format_updated_df).stack()\n",
    "\n",
    "changed = ne_stacked[ne_stacked]\n",
    "\n",
    "changed.index.names = ['id', 'col']\n",
    "\n",
    "changed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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></th>\n",
       "      <th>from</th>\n",
       "      <th>to</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th>col</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>finishedDate_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pointsAwardedDate_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">14</th>\n",
       "      <th>finishedDate_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pointsAwardedDate_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <th>finishedDate_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">7379</th>\n",
       "      <th>finishedDate_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pointsAwardedDate_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">7380</th>\n",
       "      <th>finishedDate_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pointsAwardedDate_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>purchaseDate_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-03-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3170 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            from          to\n",
       "id   col                                    \n",
       "3    finishedDate_date       NaN  2021-01-03\n",
       "     pointsAwardedDate_date  NaN  2021-01-03\n",
       "14   finishedDate_date       NaN  2021-01-03\n",
       "     pointsAwardedDate_date  NaN  2021-01-03\n",
       "15   finishedDate_date       NaN  2021-01-03\n",
       "...                          ...         ...\n",
       "7379 finishedDate_date       NaN  2021-03-01\n",
       "     pointsAwardedDate_date  NaN  2021-03-01\n",
       "7380 finishedDate_date       NaN  2021-03-01\n",
       "     pointsAwardedDate_date  NaN  2021-03-01\n",
       "     purchaseDate_date       NaN  2021-03-01\n",
       "\n",
       "[3170 rows x 2 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# stackoverflow adapted, citation below\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "df1=format_dt_df\n",
    "df2=format_updated_df\n",
    "\n",
    "difference_locations = np.where(df1 != df2)\n",
    "\n",
    "changed_from = df1.values[difference_locations]\n",
    "\n",
    "changed_to = df2.values[difference_locations]\n",
    "\n",
    "pd.DataFrame({'from': changed_from, 'to': changed_to}, index=changed.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>createDate_date</th>\n",
       "      <th>dateScanned_date</th>\n",
       "      <th>finishedDate_date</th>\n",
       "      <th>modifyDate_date</th>\n",
       "      <th>pointsAwardedDate_date</th>\n",
       "      <th>purchaseDate_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  createDate_date dateScanned_date finishedDate_date modifyDate_date  \\\n",
       "0      2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "1      2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "2      2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "3      2021-01-03       2021-01-03        1900-01-01      2021-01-03   \n",
       "4      2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "\n",
       "  pointsAwardedDate_date purchaseDate_date  \n",
       "0             2021-01-03        2021-01-03  \n",
       "1             2021-01-03        2021-01-02  \n",
       "2             2021-01-03        2021-01-02  \n",
       "3             1900-01-01        2021-01-03  \n",
       "4             2021-01-03        2021-01-03  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2)\n",
    "\n",
    "\"\"\"\n",
    "This was a dummy date so MySQL would take import and you can query this to \n",
    "find your Nan values easy\n",
    "if you want I would think about replacing with CreateDate for consistency. \n",
    "This will depend on what you are\n",
    "needing as an end result or personal preference.\n",
    "\"\"\"\n",
    "new_date_nan_changed=format_dt_df.replace(np.nan,'1900-01-01',regex=True)\n",
    "new_date_nan_changed.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\nSend Dates: With Correct formatting for schema:\\n\\nCreating table as per requirement, sql is the schema from above we created\\n\\nmycursor.execute(sql_01) This doesn't need to be updated since format \\nwas correct except for our Nan we fixed\\n\\n\""
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Send Dates: With Correct formatting for schema:\n",
    "\n",
    "Creating table as per requirement, sql is the schema from above we created\n",
    "\n",
    "mycursor.execute(sql_01) This doesn't need to be updated since format \n",
    "was correct except for our Nan we fixed\n",
    "\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Send of batch of rows via DF to MySQL\n",
    "\n",
    "mycursor.executemany(\"\"\"\n",
    "INSERT INTO date_table_Ex01(createDate_date,\n",
    "dateScanned_date, finishedDate_date, modifyDate_date,\n",
    "pointsAwardedDate_date, purchaseDate_date ) VALUES(%s,%s,%s,%s,%s,%s)\n",
    "\"\"\",list(new_date_nan_changed.values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>createDate_date</th>\n",
       "      <th>dateScanned_date</th>\n",
       "      <th>finishedDate_date</th>\n",
       "      <th>modifyDate_date</th>\n",
       "      <th>pointsAwardedDate_date</th>\n",
       "      <th>purchaseDate_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2021-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>2021-01-03</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7376</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7377</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7378</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2020-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2020-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7380</th>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>1900-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7381 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     createDate_date dateScanned_date finishedDate_date modifyDate_date  \\\n",
       "0         2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "1         2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "2         2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "3         2021-01-03       2021-01-03        1900-01-01      2021-01-03   \n",
       "4         2021-01-03       2021-01-03        2021-01-03      2021-01-03   \n",
       "...              ...              ...               ...             ...   \n",
       "7376      2021-03-01       2021-03-01        1900-01-01      2021-03-01   \n",
       "7377      2021-03-01       2021-03-01        1900-01-01      2021-03-01   \n",
       "7378      2021-03-01       2021-03-01        1900-01-01      2021-03-01   \n",
       "7379      2021-03-01       2021-03-01        1900-01-01      2021-03-01   \n",
       "7380      2021-03-01       2021-03-01        1900-01-01      2021-03-01   \n",
       "\n",
       "     pointsAwardedDate_date purchaseDate_date  \n",
       "0                2021-01-03        2021-01-03  \n",
       "1                2021-01-03        2021-01-02  \n",
       "2                2021-01-03        2021-01-02  \n",
       "3                1900-01-01        2021-01-03  \n",
       "4                2021-01-03        2021-01-03  \n",
       "...                     ...               ...  \n",
       "7376             1900-01-01        1900-01-01  \n",
       "7377             1900-01-01        1900-01-01  \n",
       "7378             1900-01-01        2020-08-17  \n",
       "7379             1900-01-01        2020-08-17  \n",
       "7380             1900-01-01        1900-01-01  \n",
       "\n",
       "[7381 rows x 6 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mycursor.execute(\"SELECT * from date_table_Ex01\")\n",
    "\n",
    "# Get all results and store them \n",
    "query_to_df=mycursor.fetchall()\n",
    "# mycursor.fetchall()\n",
    "# Retain columns names, and store as DF: these data are now imported \n",
    "# back from MySQL btw from line above\n",
    "\n",
    "pd.DataFrame(query_to_df,columns=mycursor.column_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Ex.2) Other Errors That May Occur:`\n",
    "\n",
    "+ `Dot Notation Naming`\n",
    "+ `_id.oid`\n",
    "+ `NaN in general`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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.oid</th>\n",
       "      <th>bonusPointsEarned</th>\n",
       "      <th>rewardsReceiptItemList_pointsEarned</th>\n",
       "      <th>needsFetchReview</th>\n",
       "      <th>competitiveProduct</th>\n",
       "      <th>deleted</th>\n",
       "      <th>userFlaggedQuantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ff1e1eb0a720f0523000575</td>\n",
       "      <td>500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5ff1e1f10a720f052300057a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5ff1e1ee0a7214ada100056f</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    _id.oid  bonusPointsEarned  \\\n",
       "0  5ff1e1eb0a720f0523000575              500.0   \n",
       "1  5ff1e1bb0a720f052300056b              150.0   \n",
       "2  5ff1e1bb0a720f052300056b              150.0   \n",
       "3  5ff1e1f10a720f052300057a                5.0   \n",
       "4  5ff1e1ee0a7214ada100056f                5.0   \n",
       "\n",
       "   rewardsReceiptItemList_pointsEarned needsFetchReview competitiveProduct  \\\n",
       "0                                  NaN            False                NaN   \n",
       "1                                  NaN              NaN                NaN   \n",
       "2                                  NaN             True                NaN   \n",
       "3                                  NaN            False                NaN   \n",
       "4                                  NaN            False                NaN   \n",
       "\n",
       "  deleted  userFlaggedQuantity  \n",
       "0     NaN                  5.0  \n",
       "1     NaN                  NaN  \n",
       "2     NaN                  1.0  \n",
       "3     NaN                  3.0  \n",
       "4     NaN                  4.0  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Data subset csv\n",
    "df_parse=pd.read_csv('mysql_play_data.csv')\n",
    "\n",
    "# Subset for ease of the example and schema later\n",
    "ex_df_subset=df_parse.loc[:,['_id.oid','bonusPointsEarned',\n",
    "'rewardsReceiptItemList_pointsEarned',\n",
    "\"needsFetchReview\",'competitiveProduct','deleted','userFlaggedQuantity']]\n",
    "\n",
    "ex_df_subset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "_id.oid                                   0\n",
       "bonusPointsEarned                      1401\n",
       "rewardsReceiptItemList_pointsEarned    6454\n",
       "needsFetchReview                       6568\n",
       "competitiveProduct                     6736\n",
       "deleted                                7372\n",
       "userFlaggedQuantity                    7082\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This is allowing me to take a note of our empty rows by column and assess how to handle later\n",
    "ex_df_subset.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "_id.oid                                 object\n",
       "bonusPointsEarned                      float64\n",
       "rewardsReceiptItemList_pointsEarned    float64\n",
       "needsFetchReview                        object\n",
       "competitiveProduct                      object\n",
       "deleted                                 object\n",
       "userFlaggedQuantity                    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Preparing myself for the schema, thinking about our variables\n",
    "ex_df_subset.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['_id.oid', 'bonus.PointsEarned', 'rewards.ReceiptItemList_pointsEarned',\n",
       "       'needs.FetchReview', 'competitive.Product', 'deleted',\n",
       "       'user.FlaggedQuantity'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Articifically change column names so we can get the point for the example!\n",
    "ex_df_subset.columns=['_id.oid', 'bonus.PointsEarned', \n",
    "                      'rewards.ReceiptItemList_pointsEarned',\n",
    "       'needs.FetchReview', 'competitive.Product', 'deleted',\n",
    "       'user.FlaggedQuantity']\n",
    "\n",
    "ex_df_subset.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Let's see what happens if you try to send this to MySQL`\n",
    "\n",
    "+ First create a schema and table to send these data\n",
    "+ Investigate the error we get if any"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create Schema and Table:\n",
    "\n",
    "sql_02a =\"\"\"\n",
    "CREATE TABLE dot_notation_table_01( _id.oid VARCHAR(255), bonus.PointsEarned FLOAT, \n",
    "rewards.ReceiptItemList_pointsEarned FLOAT, needs.FetchReview BOOL , \n",
    "competitive.Product TEXT, deleted BOOL, user.FlaggedQuantity INT )\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.oid VARCHAR(255), bonus.PointsEarned FLOAT, \nrewards.ReceiptItemList_pointsEarn' at line 1",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-52-5b688abbfc4d>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;31m# Creating table as per requirement, sql is the schema from above we created\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m \u001b[0mmycursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql_02a\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/cursor.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, params, multi)\u001b[0m\n\u001b[1;32m    567\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    568\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 569\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcmd_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    570\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mInterfaceError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    571\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_have_next_result\u001b[0m\u001b[0;34m:\u001b[0m  \u001b[0;31m# pylint: disable=W0212\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/connection.py\u001b[0m in \u001b[0;36mcmd_query\u001b[0;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[1;32m    588\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbytes\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    589\u001b[0m             \u001b[0mquery\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'utf-8'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 590\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_send_cmd\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mServerCmd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mQUERY\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    591\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    592\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_have_next_result\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/connection.py\u001b[0m in \u001b[0;36m_handle_result\u001b[0;34m(self, packet)\u001b[0m\n\u001b[1;32m    476\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_eof\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpacket\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    477\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0mpacket\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m4\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m255\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 478\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpacket\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    479\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    480\u001b[0m         \u001b[0;31m# We have a text result set\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.oid VARCHAR(255), bonus.PointsEarned FLOAT, \nrewards.ReceiptItemList_pointsEarn' at line 1"
     ]
    }
   ],
   "source": [
    "# Send this off to MySQL\n",
    "\n",
    "# Creating table as per requirement, sql is the schema from above we created\n",
    "\n",
    "mycursor.execute(sql_02a)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `2A.) Dot Notation from data, explain & fix formatting`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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_oid</th>\n",
       "      <th>bonus_PointsEarned</th>\n",
       "      <th>rewards_ReceiptItemList_pointsEarned</th>\n",
       "      <th>needs_FetchReview</th>\n",
       "      <th>competitive_Product</th>\n",
       "      <th>deleted</th>\n",
       "      <th>user_FlaggedQuantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ff1e1eb0a720f0523000575</td>\n",
       "      <td>500.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5ff1e1f10a720f052300057a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5ff1e1ee0a7214ada100056f</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7376</th>\n",
       "      <td>603d0b710a720fde1000042a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7377</th>\n",
       "      <td>603cf5290a720fde10000413</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7378</th>\n",
       "      <td>603ce7100a7217c72c000405</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379</th>\n",
       "      <td>603ce7100a7217c72c000405</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7380</th>\n",
       "      <td>603c4fea0a7217c72c000389</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7381 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       _id_oid  bonus_PointsEarned  \\\n",
       "0     5ff1e1eb0a720f0523000575               500.0   \n",
       "1     5ff1e1bb0a720f052300056b               150.0   \n",
       "2     5ff1e1bb0a720f052300056b               150.0   \n",
       "3     5ff1e1f10a720f052300057a                 5.0   \n",
       "4     5ff1e1ee0a7214ada100056f                 5.0   \n",
       "...                        ...                 ...   \n",
       "7376  603d0b710a720fde1000042a                 NaN   \n",
       "7377  603cf5290a720fde10000413                 NaN   \n",
       "7378  603ce7100a7217c72c000405                25.0   \n",
       "7379  603ce7100a7217c72c000405                25.0   \n",
       "7380  603c4fea0a7217c72c000389                 NaN   \n",
       "\n",
       "      rewards_ReceiptItemList_pointsEarned needs_FetchReview  \\\n",
       "0                                      NaN             False   \n",
       "1                                      NaN               NaN   \n",
       "2                                      NaN              True   \n",
       "3                                      NaN             False   \n",
       "4                                      NaN             False   \n",
       "...                                    ...               ...   \n",
       "7376                                   NaN               NaN   \n",
       "7377                                   NaN               NaN   \n",
       "7378                                   NaN               NaN   \n",
       "7379                                   NaN               NaN   \n",
       "7380                                   NaN               NaN   \n",
       "\n",
       "     competitive_Product deleted  user_FlaggedQuantity  \n",
       "0                    NaN     NaN                   5.0  \n",
       "1                    NaN     NaN                   NaN  \n",
       "2                    NaN     NaN                   1.0  \n",
       "3                    NaN     NaN                   3.0  \n",
       "4                    NaN     NaN                   4.0  \n",
       "...                  ...     ...                   ...  \n",
       "7376                 NaN     NaN                   NaN  \n",
       "7377                 NaN     NaN                   NaN  \n",
       "7378                 NaN     NaN                   NaN  \n",
       "7379                 NaN     NaN                   NaN  \n",
       "7380                 NaN     NaN                   NaN  \n",
       "\n",
       "[7381 rows x 7 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This is MySQL problem for importing so I changed dot notation of columns\n",
    "ex_df_subset.columns=ex_df_subset.columns.str.replace('.','_')\n",
    "\n",
    "ex_df_subset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_02b =\"\"\"\n",
    "CREATE TABLE dot_notation_table_02( _id_oid VARCHAR(255), bonus_PointsEarned FLOAT, \n",
    "rewards_ReceiptItemList_pointsEarned FLOAT,\n",
    "needs_FetchReview BOOL, competitive_Product TEXT, deleted BOOL, user_FlaggedQuantity INT )\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating table as per requirement, sql is the schema from above we created\n",
    "\n",
    "mycursor.execute(sql_02b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "1054 (42S22): Unknown column 'nan' in 'field list'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-37-29e1676ecf1a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0mrewards_ReceiptItemList_pointsEarned\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mneeds_FetchReview\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m competitive_Product, deleted, user_FlaggedQuantity ) VALUES(%s,%s,%s,%s,%s,%s,%s)\n\u001b[0;32m----> 5\u001b[0;31m \"\"\",list(ex_df_subset.values))\n\u001b[0m",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/cursor.py\u001b[0m in \u001b[0;36mexecutemany\u001b[0;34m(self, operation, seq_params)\u001b[0m\n\u001b[1;32m    668\u001b[0m             \u001b[0mstmt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_batch_insert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0moperation\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mseq_params\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    669\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mstmt\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 670\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    671\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    672\u001b[0m         \u001b[0mrowcnt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/cursor.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, params, multi)\u001b[0m\n\u001b[1;32m    567\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    568\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 569\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcmd_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    570\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mInterfaceError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    571\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_have_next_result\u001b[0m\u001b[0;34m:\u001b[0m  \u001b[0;31m# pylint: disable=W0212\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/connection.py\u001b[0m in \u001b[0;36mcmd_query\u001b[0;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[1;32m    588\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbytes\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    589\u001b[0m             \u001b[0mquery\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'utf-8'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 590\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_send_cmd\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mServerCmd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mQUERY\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    591\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    592\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_have_next_result\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/connection.py\u001b[0m in \u001b[0;36m_handle_result\u001b[0;34m(self, packet)\u001b[0m\n\u001b[1;32m    476\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_eof\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpacket\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    477\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0mpacket\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m4\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m255\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 478\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpacket\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    479\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    480\u001b[0m         \u001b[0;31m# We have a text result set\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: 1054 (42S22): Unknown column 'nan' in 'field list'"
     ]
    }
   ],
   "source": [
    "mycursor.executemany(\"\"\"\n",
    "INSERT INTO dot_notation_table_02(_id_oid, bonus_PointsEarned, \n",
    "rewards_ReceiptItemList_pointsEarned, needs_FetchReview, \n",
    "competitive_Product, deleted, user_FlaggedQuantity ) VALUES(%s,%s,%s,%s,%s,%s,%s)\n",
    "\"\"\",list(ex_df_subset.values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "_id_oid                                    0\n",
       "bonus_PointsEarned                      1401\n",
       "rewards_ReceiptItemList_pointsEarned    6454\n",
       "needs_FetchReview                       6568\n",
       "competitive_Product                     6736\n",
       "deleted                                 7372\n",
       "user_FlaggedQuantity                    7082\n",
       "dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We need to take care of NaN values but, we should do some investigation first\n",
    "\n",
    "ex_df_subset.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{False, True, 'Unknown'}"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ex_df_subset['bonus_PointsEarned']=ex_df_subset['bonus_PointsEarned'].fillna(0.0)\n",
    "ex_df_subset['bonus_PointsEarned']=pd.to_numeric(ex_df_subset['bonus_PointsEarned'])\n",
    "# set(ex_df_subset['bonus_PointsEarned'])\n",
    "\n",
    "ex_df_subset['rewards_ReceiptItemList_pointsEarned']=ex_df_subset['rewards_ReceiptItemList_pointsEarned'].fillna(0.0)\n",
    "\n",
    "ex_df_subset['rewards_ReceiptItemList_pointsEarned']=pd.to_numeric(ex_df_subset['rewards_ReceiptItemList_pointsEarned'])\n",
    "\n",
    "# 88% NaN\n",
    "set(ex_df_subset['needs_FetchReview'])\n",
    "ex_df_subset['needs_FetchReview'].isnull().sum()/len(ex_df_subset['needs_FetchReview'])\n",
    "# Replace NaN with False\n",
    "ex_df_subset[\"needs_FetchReview\"].fillna(False,inplace=True)\n",
    "\n",
    "\n",
    "# Checking to see proportion of data NaN to decide how to handle these\n",
    "ex_df_subset['competitive_Product'].isnull().sum()/len(ex_df_subset['competitive_Product'])\n",
    "\n",
    "ex_df_subset['competitive_Product']=ex_df_subset['competitive_Product'].fillna(\"Unknown\")\n",
    "\n",
    "# set(ex_df_subset['competitive_Product'])\n",
    "\n",
    "# set(ex_df_subset['deleted'])\n",
    "#Bool values here, I replaced NaN with False as a judgement call and easier to import\n",
    "ex_df_subset[\"deleted\"].fillna(False,inplace=True)\n",
    "\n",
    "# # ex_df_subset['userFlaggedQuantity']\n",
    "ex_df_subset['user_FlaggedQuantity']=ex_df_subset['user_FlaggedQuantity'].fillna(0)\n",
    "\n",
    "ex_df_subset['user_FlaggedQuantity']=pd.to_numeric(ex_df_subset['user_FlaggedQuantity'],\n",
    "                                                   downcast='signed')\n",
    "# # ex_df_subset['userFlaggedQuantity']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.executemany(\"\"\"\n",
    "INSERT INTO dot_notation_table_02(_id_oid, bonus_PointsEarned, \n",
    "rewards_ReceiptItemList_pointsEarned, needs_FetchReview, \n",
    "competitive_Product, deleted, user_FlaggedQuantity ) VALUES(%s,%s,%s,%s,%s,%s,%s)\n",
    "\"\"\",list(ex_df_subset.values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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_oid</th>\n",
       "      <th>bonus_PointsEarned</th>\n",
       "      <th>rewards_ReceiptItemList_pointsEarned</th>\n",
       "      <th>needs_FetchReview</th>\n",
       "      <th>competitive_Product</th>\n",
       "      <th>deleted</th>\n",
       "      <th>user_FlaggedQuantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ff1e1eb0a720f0523000575</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5ff1e1f10a720f052300057a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5ff1e1ee0a7214ada100056f</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>4</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7376</th>\n",
       "      <td>603d0b710a720fde1000042a</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7377</th>\n",
       "      <td>603cf5290a720fde10000413</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7378</th>\n",
       "      <td>603ce7100a7217c72c000405</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379</th>\n",
       "      <td>603ce7100a7217c72c000405</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7380</th>\n",
       "      <td>603c4fea0a7217c72c000389</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7381 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       _id_oid  bonus_PointsEarned  \\\n",
       "0     5ff1e1eb0a720f0523000575               500.0   \n",
       "1     5ff1e1bb0a720f052300056b               150.0   \n",
       "2     5ff1e1bb0a720f052300056b               150.0   \n",
       "3     5ff1e1f10a720f052300057a                 5.0   \n",
       "4     5ff1e1ee0a7214ada100056f                 5.0   \n",
       "...                        ...                 ...   \n",
       "7376  603d0b710a720fde1000042a                 0.0   \n",
       "7377  603cf5290a720fde10000413                 0.0   \n",
       "7378  603ce7100a7217c72c000405                25.0   \n",
       "7379  603ce7100a7217c72c000405                25.0   \n",
       "7380  603c4fea0a7217c72c000389                 0.0   \n",
       "\n",
       "      rewards_ReceiptItemList_pointsEarned  needs_FetchReview  \\\n",
       "0                                      0.0                  0   \n",
       "1                                      0.0                  0   \n",
       "2                                      0.0                  1   \n",
       "3                                      0.0                  0   \n",
       "4                                      0.0                  0   \n",
       "...                                    ...                ...   \n",
       "7376                                   0.0                  0   \n",
       "7377                                   0.0                  0   \n",
       "7378                                   0.0                  0   \n",
       "7379                                   0.0                  0   \n",
       "7380                                   0.0                  0   \n",
       "\n",
       "     competitive_Product  deleted  user_FlaggedQuantity  \n",
       "0                Unknown        0                     5  \n",
       "1                Unknown        0                     0  \n",
       "2                Unknown        0                     1  \n",
       "3                Unknown        0                     3  \n",
       "4                Unknown        0                     4  \n",
       "...                  ...      ...                   ...  \n",
       "7376             Unknown        0                     0  \n",
       "7377             Unknown        0                     0  \n",
       "7378             Unknown        0                     0  \n",
       "7379             Unknown        0                     0  \n",
       "7380             Unknown        0                     0  \n",
       "\n",
       "[7381 rows x 7 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mycursor.execute(\"SELECT * from dot_notation_table_02\")\n",
    "\n",
    "# Get all results and store them \n",
    "df_formatted=mycursor.fetchall()\n",
    "\n",
    "# Retain columns names, and store as DF:\n",
    "pd.DataFrame(df_formatted,columns=mycursor.column_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `2B.) _id.OID issues explained`\n",
    "\n",
    "+ Why is this an issue anyway?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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.oid</th>\n",
       "      <th>bonus_PointsEarned</th>\n",
       "      <th>rewards_ReceiptItemList_pointsEarned</th>\n",
       "      <th>needs_FetchReview</th>\n",
       "      <th>competitive_Product</th>\n",
       "      <th>deleted</th>\n",
       "      <th>user_FlaggedQuantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ff1e1eb0a720f0523000575</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5ff1e1f10a720f052300057a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>False</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5ff1e1ee0a7214ada100056f</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>False</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    _id.oid  bonus_PointsEarned  \\\n",
       "0  5ff1e1eb0a720f0523000575               500.0   \n",
       "1  5ff1e1bb0a720f052300056b               150.0   \n",
       "2  5ff1e1bb0a720f052300056b               150.0   \n",
       "3  5ff1e1f10a720f052300057a                 5.0   \n",
       "4  5ff1e1ee0a7214ada100056f                 5.0   \n",
       "\n",
       "   rewards_ReceiptItemList_pointsEarned  needs_FetchReview  \\\n",
       "0                                   0.0              False   \n",
       "1                                   0.0              False   \n",
       "2                                   0.0               True   \n",
       "3                                   0.0              False   \n",
       "4                                   0.0              False   \n",
       "\n",
       "  competitive_Product  deleted  user_FlaggedQuantity  \n",
       "0             Unknown    False                     5  \n",
       "1             Unknown    False                     0  \n",
       "2             Unknown    False                     1  \n",
       "3             Unknown    False                     3  \n",
       "4             Unknown    False                     4  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# I artifically reset this so we can play and see what happens!\n",
    "ex_df_subset=ex_df_subset.rename(columns={'_id_oid':'_id.oid'})\n",
    "ex_df_subset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_02b_01 =\"\"\"\n",
    "CREATE TABLE dot_notation_table_oidFix( _id.oid VARCHAR(255), bonus_PointsEarned FLOAT, \n",
    "rewards_ReceiptItemList_pointsEarned FLOAT,\n",
    "needs_FetchReview BOOL, competitive_Product TEXT, deleted BOOL, user_FlaggedQuantity INT )\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.oid VARCHAR(255), bonus_PointsEarned FLOAT, \nrewards_ReceiptItemList_pointsEarn' at line 1",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-45-a8b908063e85>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mmycursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql_02b_01\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/cursor.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, params, multi)\u001b[0m\n\u001b[1;32m    567\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    568\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 569\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcmd_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    570\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mInterfaceError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    571\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_have_next_result\u001b[0m\u001b[0;34m:\u001b[0m  \u001b[0;31m# pylint: disable=W0212\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/connection.py\u001b[0m in \u001b[0;36mcmd_query\u001b[0;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[1;32m    588\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbytes\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    589\u001b[0m             \u001b[0mquery\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'utf-8'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 590\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_send_cmd\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mServerCmd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mQUERY\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    591\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    592\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_have_next_result\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/mysql/connector/connection.py\u001b[0m in \u001b[0;36m_handle_result\u001b[0;34m(self, packet)\u001b[0m\n\u001b[1;32m    476\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_eof\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpacket\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    477\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0mpacket\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m4\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m255\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 478\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpacket\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    479\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    480\u001b[0m         \u001b[0;31m# We have a text result set\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.oid VARCHAR(255), bonus_PointsEarned FLOAT, \nrewards_ReceiptItemList_pointsEarn' at line 1"
     ]
    }
   ],
   "source": [
    "mycursor.execute(sql_02b_01)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# OID issue and convert:\n",
    "\n",
    "# Changed this name _id.oid because of convention naming systems for DB and I don't want confusion/errors\n",
    "ex_df_subset=ex_df_subset.rename(columns={ex_df_subset.columns[0]:\"OID\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_02b =\"\"\"\n",
    "CREATE TABLE dot_notation_table_oidFix( OID VARCHAR(255), bonus_PointsEarned FLOAT, \n",
    "rewards_ReceiptItemList_pointsEarned FLOAT,\n",
    "needs_FetchReview BOOL, competitive_Product TEXT, deleted BOOL, user_FlaggedQuantity INT )\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating table as per requirement, sql is the schema from above we created\n",
    "\n",
    "mycursor.execute(sql_02b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.executemany(\"\"\"\n",
    "INSERT INTO dot_notation_table_oidFix( OID, bonus_PointsEarned, \n",
    "rewards_ReceiptItemList_pointsEarned, needs_FetchReview, \n",
    "competitive_Product, deleted, user_FlaggedQuantity ) VALUES(%s,%s,%s,%s,%s,%s,%s)\n",
    "\"\"\",list(ex_df_subset.values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>OID</th>\n",
       "      <th>bonus_PointsEarned</th>\n",
       "      <th>rewards_ReceiptItemList_pointsEarned</th>\n",
       "      <th>needs_FetchReview</th>\n",
       "      <th>competitive_Product</th>\n",
       "      <th>deleted</th>\n",
       "      <th>user_FlaggedQuantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ff1e1eb0a720f0523000575</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5ff1e1f10a720f052300057a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5ff1e1ee0a7214ada100056f</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>4</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7376</th>\n",
       "      <td>603d0b710a720fde1000042a</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7377</th>\n",
       "      <td>603cf5290a720fde10000413</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7378</th>\n",
       "      <td>603ce7100a7217c72c000405</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379</th>\n",
       "      <td>603ce7100a7217c72c000405</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7380</th>\n",
       "      <td>603c4fea0a7217c72c000389</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7381 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           OID  bonus_PointsEarned  \\\n",
       "0     5ff1e1eb0a720f0523000575               500.0   \n",
       "1     5ff1e1bb0a720f052300056b               150.0   \n",
       "2     5ff1e1bb0a720f052300056b               150.0   \n",
       "3     5ff1e1f10a720f052300057a                 5.0   \n",
       "4     5ff1e1ee0a7214ada100056f                 5.0   \n",
       "...                        ...                 ...   \n",
       "7376  603d0b710a720fde1000042a                 0.0   \n",
       "7377  603cf5290a720fde10000413                 0.0   \n",
       "7378  603ce7100a7217c72c000405                25.0   \n",
       "7379  603ce7100a7217c72c000405                25.0   \n",
       "7380  603c4fea0a7217c72c000389                 0.0   \n",
       "\n",
       "      rewards_ReceiptItemList_pointsEarned  needs_FetchReview  \\\n",
       "0                                      0.0                  0   \n",
       "1                                      0.0                  0   \n",
       "2                                      0.0                  1   \n",
       "3                                      0.0                  0   \n",
       "4                                      0.0                  0   \n",
       "...                                    ...                ...   \n",
       "7376                                   0.0                  0   \n",
       "7377                                   0.0                  0   \n",
       "7378                                   0.0                  0   \n",
       "7379                                   0.0                  0   \n",
       "7380                                   0.0                  0   \n",
       "\n",
       "     competitive_Product  deleted  user_FlaggedQuantity  \n",
       "0                Unknown        0                     5  \n",
       "1                Unknown        0                     0  \n",
       "2                Unknown        0                     1  \n",
       "3                Unknown        0                     3  \n",
       "4                Unknown        0                     4  \n",
       "...                  ...      ...                   ...  \n",
       "7376             Unknown        0                     0  \n",
       "7377             Unknown        0                     0  \n",
       "7378             Unknown        0                     0  \n",
       "7379             Unknown        0                     0  \n",
       "7380             Unknown        0                     0  \n",
       "\n",
       "[7381 rows x 7 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mycursor.execute(\"SELECT * from dot_notation_table_oidFix\")\n",
    "\n",
    "# Get all results and store them \n",
    "mm=mycursor.fetchall()\n",
    "\n",
    "# Retain columns names, and store as DF:\n",
    "pd.DataFrame(mm,columns=mycursor.column_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Like, Share & <font color=red>SUB</font>scribe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Citations & Help:`\n",
    "\n",
    "# ◔̯◔\n",
    "\n",
    "https://stackoverflow.com/questions/24942538/sql-wont-insert-null-values-with-bulk-insert\n",
    "\n",
    "https://stackoverflow.com/questions/45756557/inserting-nan-value-into-mysql-database\n",
    "\n",
    "https://www.tutorialspoint.com/can-mysql-automatically-convert-empty-strings-to-null\n",
    "\n",
    "https://stackoverflow.com/questions/41027882/convert-nan-values-to-null-in-mysql-columns-with-python-code\n",
    "\n",
    "https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html\n",
    "\n",
    "https://www.tutorialspoint.com/can-mysql-automatically-convert-empty-strings-to-null\n",
    "\n",
    "https://www.programiz.com/python-programming/datetime/strftime\n",
    "\n",
    "https://stackoverflow.com/questions/66477410/how-can-i-insert-id-oid-5ec796a7868d607e00f4b18d-and-time-15901\n",
    "\n",
    "https://stackoverflow.com/questions/59817317/how-to-convert-multiple-columns-datatype-format-to-another-datatype-formats-at-a\n",
    "\n",
    "https://stackoverflow.com/questions/29177498/python-pandas-replace-nan-in-one-column-with-value-from-corresponding-row-of-sec\n",
    "\n",
    "https://stackoverflow.com/questions/28901683/pandas-get-rows-which-are-not-in-other-dataframe\n",
    "\n",
    "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.copy.html\n",
    "\n",
    "https://stackoverflow.com/questions/17095101/compare-two-dataframes-and-output-their-differences-side-by-side\n",
    "\n",
    "https://www.pythonfixing.com/2022/04/fixed-how-to-properly-insert-pandas-nat.html"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}