Python_to_mysql_configFiles.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python to MySql: with ( `mysql.connector` )\n",
    "\n",
    "`-----------------------------`\n",
    "\n",
    "**<font size=5>`Mr Fugu Data Science`</font>**\n",
    "\n",
    "# (◕‿◕✿)\n",
    "\n",
    "[Github](https://github.com/MrFuguDataScience) | [Youtube](https://www.youtube.com/channel/UCbni-TDI-Ub8VlGaP8HLTNw?view_as=subscriber)\n",
    "\n",
    "# Purpose & Outcome:\n",
    "\n",
    "+ Install `mysql.connector`\n",
    "    + Create `Config.ini & .py files` for user credentials\n",
    "+ `Connect to MySQL from Python`\n",
    "+ Run SImple Query & Store as Data Frame\n",
    "\n",
    "\n",
    "`--------------------------------------`\n",
    "\n",
    "# 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`\n",
    "\n",
    "[mysql connection python docs](https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "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, \n",
    "# that stored user credentials"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Config files creation:\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1<sup>st</sup>**: Create `config_.ini` file\n",
    "\n",
    "`[mysql]\n",
    "host = localhost\n",
    "database = your_database_name_here\n",
    "user = user_name_here\n",
    "password = your_password_here`\n",
    "\n",
    "\n",
    "`----------------------------------`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "**2<sup>nd</sup>**: Create file to read parameters: `python_to_mysql_config.py`\n",
    "\n",
    "` #!usr/bin/python`  (Change this line if using venv)\n",
    "\n",
    "`from configparser import ConfigParser`\n",
    "\n",
    "`def read_db_config(filename='config_.ini', section='mysql'):`\n",
    "\n",
    "`    \"\"\" Read database configuration file and return a dictionary object`\n",
    "    `:param filename: name of the configuration file\n",
    "    :param section: section of database configuration\n",
    "    :return: a dictionary of database parameters\n",
    "    \"\"\"`\n",
    "    \n",
    "    # create parser and read ini configuration file\n",
    "    \n",
    "     parser = ConfigParser()\n",
    "     parser.read(filename)\n",
    "     # get section, default to mysql\n",
    "    \n",
    "     db = {}  \n",
    "    if parser.has_section(section):\n",
    "        items = parser.items(section)\n",
    "        for item in items:\n",
    "            db[item[0]] = item[1]\n",
    "    else:\n",
    "        raise Exception('{0} not found in the {1} file'.format(section, filename))\n",
    "    return db "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Connection:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup Connection:\n",
    "\n",
    "db_config = read_db_config() # user credentials\n",
    "\n",
    "conn = MySQLConnection(**read_db_config()) # establish connection\n",
    "\n",
    "mycursor = conn.cursor() # cursor, allows us to do queriers,send/retrieve data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Starting MySql from command Line:\n",
    "\n",
    "*I have Mac, Not sure aboout Windows*: `mysql.server start`\n",
    "\n",
    "*Shutdown server*: `mysqld`\n",
    "\n",
    "\n",
    "*login as root*: `mysql -uroot -p`\n",
    "\n",
    "\n",
    "*Checking all Users*: ` SELECT User FROM mysql.user;`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('7929-3849-5090-8440',\n",
       "  'wls@mlatgw.crw',\n",
       "  'Zackarie',\n",
       "  'el-Sami',\n",
       "  7956729362.0,\n",
       "  1),\n",
       " ('7606-6195-2000-3181',\n",
       "  'bawclqrmd@hofcrm.bgt',\n",
       "  'Andrea',\n",
       "  'Luna Gonzalez',\n",
       "  3172817425.0,\n",
       "  2)]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Basic Query: Proof of Concept\n",
    "\n",
    "mycursor.execute(\"SELECT * from user_o limit 2\")\n",
    "mycursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mycursor.execute('ALTER TABLE user_o DROP X')\n",
    "# mycursor.fetchone()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>credit_card</th>\n",
       "      <th>email</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>primary_phone_number</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7929-3849-5090-8440</td>\n",
       "      <td>wls@mlatgw.crw</td>\n",
       "      <td>Zackarie</td>\n",
       "      <td>el-Sami</td>\n",
       "      <td>7.956729e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7606-6195-2000-3181</td>\n",
       "      <td>bawclqrmd@hofcrm.bgt</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>Luna Gonzalez</td>\n",
       "      <td>3.172817e+09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           credit_card                 email first_name      last_name  \\\n",
       "0  7929-3849-5090-8440        wls@mlatgw.crw   Zackarie        el-Sami   \n",
       "1  7606-6195-2000-3181  bawclqrmd@hofcrm.bgt     Andrea  Luna Gonzalez   \n",
       "\n",
       "   primary_phone_number  id  \n",
       "0          7.956729e+09   1  \n",
       "1          3.172817e+09   2  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query:\n",
    "mycursor.execute(\"SELECT * from user_o limit 2\")\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)\n",
    "# mm\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>credit_card</th>\n",
       "      <th>email</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>primary_phone_number</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7929-3849-5090-8440</td>\n",
       "      <td>wls@mlatgw.crw</td>\n",
       "      <td>Zackarie</td>\n",
       "      <td>el-Sami</td>\n",
       "      <td>7956729362</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7606-6195-2000-3181</td>\n",
       "      <td>bawclqrmd@hofcrm.bgt</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>Luna Gonzalez</td>\n",
       "      <td>3172817425</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           credit_card                 email first_name      last_name  \\\n",
       "0  7929-3849-5090-8440        wls@mlatgw.crw   Zackarie        el-Sami   \n",
       "1  7606-6195-2000-3181  bawclqrmd@hofcrm.bgt     Andrea  Luna Gonzalez   \n",
       "\n",
       "   primary_phone_number  id  \n",
       "0            7956729362   1  \n",
       "1            3172817425   2  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mydf=pd.DataFrame(mm,columns=mycursor.column_names)\n",
    "\n",
    "# Change Primary Phone to Int instead of float\n",
    "mydf=mydf.astype({'primary_phone_number': 'int64'})\n",
    "\n",
    "mydf\n",
    "\n",
    "# conn.close()  # close connection when done, un-highlight this~"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`-----------------------------------`\n",
    "\n",
    "# Alternate Connection: If you are NOT concerned about credentials\n",
    "\n",
    "`cnx = mysql.connector.connect(user='bambam', password='password',\n",
    "                              host='127.0.0.1',\n",
    "                              database='cowboy_doodah')\n",
    "cnx.close()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Citations & Help\n",
    "\n",
    "# ◔̯◔\n",
    "\n",
    "\n",
    "https://pypi.org/project/mongo-pickle/\n",
    "\n",
    "https://www.bogotobogo.com/python/python_serialization_pickle_json.php\n",
    "\n",
    "https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html\n",
    "\n",
    "https://medium.com/@devontem/solved-cant-connect-to-local-mysql-server-through-socket-tmp-mysql-sock-2-f52c9c546f7\n",
    "\n",
    "https://stackoverflow.com/questions/26344795/cannot-connect-to-mysql-workbench-on-mac-cant-connect-to-mysql-server-on-127 (workbench issues)\n",
    "\n",
    "https://www.youtube.com/watch?v=DCgRF4KOYIY (mysql workbench issues)\n",
    "\n",
    "https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-commands.html (mysql shell commands)\n",
    "\n",
    "https://www.fastwebhost.in/blog/mysql-list-users-how-to-list-mysql-user-accounts-via-command-line/#:~:text=mysql%3E%20SELECT%20User%20FROM%20mysql,and%20lists%20only%20user%20names.&text=If%20you%20want%20to%20list,information%20and%20all%20users%20data\n",
    "\n",
    "https://www.datacamp.com/community/tutorials/mysql-python\n",
    "\n",
    "https://www.mysqltutorial.org/python-connecting-mysql-databases/ (config file creation)\n",
    "\n",
    "https://dev.mysql.com/doc/refman/8.0/en/option-files.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
}