{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Installing and using the Python connector for MySQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On Unix systems, you can 'pip install mysql-connector'.  Depending on how you installed MySQL you may need to do that as root."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "The code below assumes you've loaded the empoyee database into MySQL.\n",
    "\n",
    "https://dev.mysql.com/doc/employee/en/employees-installation.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connect to the database, which requires credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "cnx = mysql.connector.connect(user='oates', password='mysqlpassword',\n",
    "                              host='localhost',\n",
    "                              database='employees')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's run a query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Awdeh, Satosi was hired on 18 May 1988\n",
      "Azuma, Mona was hired on 02 Mar 1990\n",
      "Aingworth, Eben was hired on 19 Dec 1990\n",
      "Auria, Yucel was hired on 14 Mar 1991\n",
      "Azumi, Douadi was hired on 10 Oct 1995\n",
      "Aloisi, Nigel was hired on 02 Nov 1985\n",
      "Ananiadou, Aleksandar was hired on 11 Jan 1988\n",
      "Awdeh, Vidya was hired on 16 Oct 1985\n",
      "Anandan, Girolamo was hired on 11 Oct 1992\n",
      "Alpin, Yuping was hired on 10 May 1994\n",
      "Auria, Marko was hired on 04 Jun 1992\n",
      "Avouris, Mihalis was hired on 12 Dec 1992\n",
      "Alencar, Aksel was hired on 24 Oct 1990\n",
      "Apsitis, Christfried was hired on 16 Jan 1986\n",
      "Angelopoulos, Maris was hired on 28 Aug 1987\n",
      "Aumann, Leucio was hired on 19 Jun 1991\n",
      "Akaboshi, Hongzue was hired on 24 Dec 1989\n",
      "Alpin, Yinlin was hired on 30 May 1990\n",
      "Ananiadou, Munehiko was hired on 05 Jun 1985\n",
      "Anick, Zhiwei was hired on 08 Nov 1992\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor = cnx.cursor()\n",
    "query = (\"SELECT first_name, last_name, hire_date FROM employees \"\n",
    "         \"WHERE last_name LIKE 'A%' LIMIT 20\")\n",
    "\n",
    "cursor.execute(query)\n",
    "\n",
    "for (first_name, last_name, hire_date) in cursor:\n",
    "  print(\"{}, {} was hired on {:%d %b %Y}\".format(\n",
    "    last_name, first_name, hire_date))\n",
    "\n",
    "cursor.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns in result = ('emp_no', 'salary', 'from_date', 'to_date')\n",
      "Query = SELECT * FROM salaries LIMIT 20\n",
      "(10001, 60117, datetime.date(1986, 6, 26), datetime.date(1987, 6, 26))\n",
      "(10001, 62102, datetime.date(1987, 6, 26), datetime.date(1988, 6, 25))\n",
      "(10001, 66074, datetime.date(1988, 6, 25), datetime.date(1989, 6, 25))\n",
      "(10001, 66596, datetime.date(1989, 6, 25), datetime.date(1990, 6, 25))\n",
      "(10001, 66961, datetime.date(1990, 6, 25), datetime.date(1991, 6, 25))\n",
      "(10001, 71046, datetime.date(1991, 6, 25), datetime.date(1992, 6, 24))\n",
      "(10001, 74333, datetime.date(1992, 6, 24), datetime.date(1993, 6, 24))\n",
      "(10001, 75286, datetime.date(1993, 6, 24), datetime.date(1994, 6, 24))\n",
      "(10001, 75994, datetime.date(1994, 6, 24), datetime.date(1995, 6, 24))\n",
      "(10001, 76884, datetime.date(1995, 6, 24), datetime.date(1996, 6, 23))\n",
      "(10001, 80013, datetime.date(1996, 6, 23), datetime.date(1997, 6, 23))\n",
      "(10001, 81025, datetime.date(1997, 6, 23), datetime.date(1998, 6, 23))\n",
      "(10001, 81097, datetime.date(1998, 6, 23), datetime.date(1999, 6, 23))\n",
      "(10001, 84917, datetime.date(1999, 6, 23), datetime.date(2000, 6, 22))\n",
      "(10001, 85112, datetime.date(2000, 6, 22), datetime.date(2001, 6, 22))\n",
      "(10001, 85097, datetime.date(2001, 6, 22), datetime.date(2002, 6, 22))\n",
      "(10001, 88958, datetime.date(2002, 6, 22), datetime.date(9999, 1, 1))\n",
      "(10002, 65828, datetime.date(1996, 8, 3), datetime.date(1997, 8, 3))\n",
      "(10002, 65909, datetime.date(1997, 8, 3), datetime.date(1998, 8, 3))\n",
      "(10002, 67534, datetime.date(1998, 8, 3), datetime.date(1999, 8, 3))\n",
      "Number of rows = 20\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor = cnx.cursor()\n",
    "query = (\"SELECT * FROM salaries LIMIT 20\")\n",
    "\n",
    "cursor.execute(query)\n",
    "\n",
    "print('Columns in result = ' + str(cursor.column_names))\n",
    "print('Query = ' + cursor.statement)\n",
    "row = cursor.fetchone()\n",
    "while row is not None:\n",
    "  print(row)\n",
    "  row = cursor.fetchone()\n",
    "\n",
    "print('Number of rows = ' + str(cursor.rowcount))\n",
    "\n",
    "cursor.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "ename": "InternalError",
     "evalue": "Unread result found",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mInternalError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-7-6b448690e545>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0;31m# cursor.fetchall()\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m \u001b[0mquery2\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0;34m\"SELECT * FROM employees LIMIT 20\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 8\u001b[0;31m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery2\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      9\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/anaconda3/lib/python3.5/site-packages/mysql/connector/cursor_cext.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, params, multi)\u001b[0m\n\u001b[1;32m    230\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_cnx\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    231\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mProgrammingError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Cursor is not connected\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 232\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_cnx\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhandle_unread_result\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    233\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    234\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[0;32m/usr/local/anaconda3/lib/python3.5/site-packages/mysql/connector/connection_cext.py\u001b[0m in \u001b[0;36mhandle_unread_result\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    612\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconsume_results\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    613\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munread_result\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 614\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mInternalError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Unread result found\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mInternalError\u001b[0m: Unread result found"
     ]
    }
   ],
   "source": [
    "cursor = cnx.cursor()\n",
    "query1 = (\"SELECT * FROM salaries LIMIT 20\")\n",
    "cursor.execute(query1)\n",
    "\n",
    "# What happens if I comment this out?\n",
    "# cursor.fetchall()\n",
    "query2 = (\"SELECT * FROM employees LIMIT 20\")\n",
    "cursor.execute(query2)\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(10001, 60117, datetime.date(1986, 6, 26), datetime.date(1987, 6, 26)),\n",
       " (10001, 62102, datetime.date(1987, 6, 26), datetime.date(1988, 6, 25)),\n",
       " (10001, 66074, datetime.date(1988, 6, 25), datetime.date(1989, 6, 25)),\n",
       " (10001, 66596, datetime.date(1989, 6, 25), datetime.date(1990, 6, 25)),\n",
       " (10001, 66961, datetime.date(1990, 6, 25), datetime.date(1991, 6, 25)),\n",
       " (10001, 71046, datetime.date(1991, 6, 25), datetime.date(1992, 6, 24)),\n",
       " (10001, 74333, datetime.date(1992, 6, 24), datetime.date(1993, 6, 24)),\n",
       " (10001, 75286, datetime.date(1993, 6, 24), datetime.date(1994, 6, 24)),\n",
       " (10001, 75994, datetime.date(1994, 6, 24), datetime.date(1995, 6, 24)),\n",
       " (10001, 76884, datetime.date(1995, 6, 24), datetime.date(1996, 6, 23)),\n",
       " (10001, 80013, datetime.date(1996, 6, 23), datetime.date(1997, 6, 23)),\n",
       " (10001, 81025, datetime.date(1997, 6, 23), datetime.date(1998, 6, 23)),\n",
       " (10001, 81097, datetime.date(1998, 6, 23), datetime.date(1999, 6, 23)),\n",
       " (10001, 84917, datetime.date(1999, 6, 23), datetime.date(2000, 6, 22)),\n",
       " (10001, 85112, datetime.date(2000, 6, 22), datetime.date(2001, 6, 22)),\n",
       " (10001, 85097, datetime.date(2001, 6, 22), datetime.date(2002, 6, 22)),\n",
       " (10001, 88958, datetime.date(2002, 6, 22), datetime.date(9999, 1, 1)),\n",
       " (10002, 65828, datetime.date(1996, 8, 3), datetime.date(1997, 8, 3)),\n",
       " (10002, 65909, datetime.date(1997, 8, 3), datetime.date(1998, 8, 3)),\n",
       " (10002, 67534, datetime.date(1998, 8, 3), datetime.date(1999, 8, 3))]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('current_dept_emp',),\n",
       " ('departments',),\n",
       " ('dept_emp',),\n",
       " ('dept_emp_latest_date',),\n",
       " ('dept_manager',),\n",
       " ('employees',),\n",
       " ('salaries',),\n",
       " ('titles',)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor = cnx.cursor()\n",
    "query1 = (\"SHOW TABLES\")\n",
    "cursor.execute(query1)\n",
    "cursor.fetchall()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('dept_no', 'char(4)', 'NO', 'PRI', None, ''),\n",
       " ('dept_name', 'varchar(40)', 'NO', 'UNI', None, '')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor = cnx.cursor()\n",
    "query1 = (\"DESCRIBE departments\")\n",
    "cursor.execute(query1)\n",
    "cursor.fetchall()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda env:anaconda3]",
   "language": "python",
   "name": "conda-env-anaconda3-py"
  },
  "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.5.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}