9.0.1_pythorn2mysql_template.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "requirements\n",
    "\n",
    "pip install sqlalchemy\n",
    "\n",
    "pip install pymysql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To familiar with the SQL syntax, check:\n",
    "\n",
    "[w3school sql tutorial](https://www.w3schools.com/sql/)\n",
    "\n",
    "You can code SQL and run for results on site"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "password=# ask me for the password, wipe this value clean after you are connected\n",
    "from sqlalchemy import create_engine as ce\n",
    "# username ray\n",
    "# hostname localhost(in other cases, ip or domain)\n",
    "# database name \"alex\", I ran sql: create database alex, and created this database\n",
    "engine=ce(\"mysql+pymysql://alex:%s@localhost/alex?charset=utf8\"%(password))\n",
    "conn=engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to the mysql base use "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This part tells how to import a csv file to the mysql database.\n",
    "\n",
    "If you are not interested in the process, jump ahead to the next green title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlalchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the csv file to pandas dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[20 year game dataset](https://www.kaggle.com/egrinstein/20-years-of-games)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "gamedf=pd.read_csv(\"ign.csv\",encoding=\"utf8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cols=list(gamedf.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define each column's type in mysql database, \n",
    "# just like you defined variable's type in other language\n",
    "schema_dict=dict({\n",
    "    cols[1]:sqlalchemy.types.String(length=30),\n",
    "    cols[2]:sqlalchemy.types.String(length=200,\n",
    "                                    convert_unicode=\"force\",\n",
    "                                    unicode_error=\"unicode_error\",\n",
    "                                   ),\n",
    "    cols[3]:sqlalchemy.types.String(length=255),\n",
    "    cols[4]:sqlalchemy.types.String(length=50),\n",
    "    cols[5]:sqlalchemy.types.Float(precision=1,asdecimal=True),\n",
    "    cols[6]:sqlalchemy.types.String(length=50),\n",
    "    cols[7]:sqlalchemy.types.String(length=10),\n",
    "    cols[8]:sqlalchemy.types.Integer(),\n",
    "    cols[9]:sqlalchemy.types.Integer(),\n",
    "    cols[10]:sqlalchemy.types.Integer(),\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use df.to_sql() to save the dataframe to MySQL database\n",
    "\n",
    "Just like df.to_csv(), "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "gamedf[cols[1:]].to_sql(\"games\",#name of the sql table\n",
    "              conn,\n",
    "              if_exists=\"replace\", # over write the original data in database\n",
    "              dtype=schema_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Learn SQL Here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mysql connection started\n"
     ]
    }
   ],
   "source": [
    "class mysql():\n",
    "    \"\"\"\n",
    "    Here I created a class,\n",
    "    to help you focus on sql(not python)\n",
    "    input the SQL sentence, \n",
    "    the function will return a dataframe compiled from mysql database\n",
    "    \"\"\"\n",
    "    def __init__(self,conn):\n",
    "        self.conn=conn\n",
    "        print(\"mysql connection started\")\n",
    "    \n",
    "    def sql(self,sqlline):\n",
    "        rs=self.conn.execute(sqlline) # sql result\n",
    "        print(\"SQL line %s result:\"%sqlline)\n",
    "        return pd.DataFrame(list(rs),columns=rs.keys()) # compile sql result into pandas dataframe\n",
    "\n",
    "my=mysql(conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL line SELECT * FROM alex.games LIMIT 2 result:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>score_phrase</th>\n",
       "      <th>title</th>\n",
       "      <th>url</th>\n",
       "      <th>platform</th>\n",
       "      <th>score</th>\n",
       "      <th>genre</th>\n",
       "      <th>editors_choice</th>\n",
       "      <th>release_year</th>\n",
       "      <th>release_month</th>\n",
       "      <th>release_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Amazing</td>\n",
       "      <td>LittleBigPlanet PS Vita</td>\n",
       "      <td>/games/littlebigplanet-vita/vita-98907</td>\n",
       "      <td>PlayStation Vita</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Platformer</td>\n",
       "      <td>Y</td>\n",
       "      <td>2012</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Amazing</td>\n",
       "      <td>LittleBigPlanet PS Vita -- Marvel Super Hero E...</td>\n",
       "      <td>/games/littlebigplanet-ps-vita-marvel-super-he...</td>\n",
       "      <td>PlayStation Vita</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Platformer</td>\n",
       "      <td>Y</td>\n",
       "      <td>2012</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index score_phrase                                              title  \\\n",
       "0      0      Amazing                            LittleBigPlanet PS Vita   \n",
       "1      1      Amazing  LittleBigPlanet PS Vita -- Marvel Super Hero E...   \n",
       "\n",
       "                                                 url          platform  score  \\\n",
       "0             /games/littlebigplanet-vita/vita-98907  PlayStation Vita    9.0   \n",
       "1  /games/littlebigplanet-ps-vita-marvel-super-he...  PlayStation Vita    9.0   \n",
       "\n",
       "        genre editors_choice  release_year  release_month  release_day  \n",
       "0  Platformer              Y          2012              9           12  \n",
       "1  Platformer              Y          2012              9           12  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my.sql(\"SELECT * FROM alex.games LIMIT 2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL line SELECT title,platform,score FROM alex.games WHERE genre='shooter' LIMIT 10 result:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>platform</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Symphony</td>\n",
       "      <td>PC</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tom Clancy's Ghost Recon Phantoms</td>\n",
       "      <td>PC</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Counter-Strike: Global Offensive</td>\n",
       "      <td>Macintosh</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Counter-Strike: Global Offensive</td>\n",
       "      <td>PC</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Counter-Strike: Global Offensive</td>\n",
       "      <td>PlayStation 3</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Counter-Strike: Global Offensive</td>\n",
       "      <td>Xbox 360</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Medal of Honor Warfighter</td>\n",
       "      <td>PC</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Medal of Honor Warfighter</td>\n",
       "      <td>PlayStation 3</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Medal of Honor Warfighter</td>\n",
       "      <td>Xbox 360</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Transformers: Fall of Cybertron</td>\n",
       "      <td>Xbox 360</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               title       platform  score\n",
       "0                           Symphony             PC    7.0\n",
       "1  Tom Clancy's Ghost Recon Phantoms             PC    7.5\n",
       "2   Counter-Strike: Global Offensive      Macintosh    8.0\n",
       "3   Counter-Strike: Global Offensive             PC    8.0\n",
       "4   Counter-Strike: Global Offensive  PlayStation 3    8.0\n",
       "5   Counter-Strike: Global Offensive       Xbox 360    8.0\n",
       "6          Medal of Honor Warfighter             PC    4.0\n",
       "7          Medal of Honor Warfighter  PlayStation 3    4.0\n",
       "8          Medal of Honor Warfighter       Xbox 360    4.0\n",
       "9    Transformers: Fall of Cybertron       Xbox 360    8.5"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my.sql(\"SELECT title,platform,score FROM alex.games WHERE genre='shooter' LIMIT 10\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now your turn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "my.sql()"
   ]
  }
 ],
 "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}