2_Connect_With_MySQL_DataBase.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "66293ca2",
   "metadata": {},
   "source": [
    "# Connect with MySQL database\n",
    "### Read Clean_Data.csv file "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9316cb4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv(\"Clean_Data.csv\",index_col = [0])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afe32158",
   "metadata": {},
   "source": [
    "### Rename the columns name in dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "51869f5b",
   "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>Name</th>\n",
       "      <th>Current_Price</th>\n",
       "      <th>P2E_Ratio</th>\n",
       "      <th>Div_Yld</th>\n",
       "      <th>ROCE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S.No.</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>Reliance Industr</td>\n",
       "      <td>2550.25</td>\n",
       "      <td>25.87</td>\n",
       "      <td>0.31</td>\n",
       "      <td>10.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.0</th>\n",
       "      <td>TCS</td>\n",
       "      <td>3302.25</td>\n",
       "      <td>28.68</td>\n",
       "      <td>1.45</td>\n",
       "      <td>59.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.0</th>\n",
       "      <td>HDFC Bank</td>\n",
       "      <td>1701.40</td>\n",
       "      <td>20.67</td>\n",
       "      <td>1.12</td>\n",
       "      <td>6.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4.0</th>\n",
       "      <td>ICICI Bank</td>\n",
       "      <td>934.60</td>\n",
       "      <td>19.21</td>\n",
       "      <td>0.86</td>\n",
       "      <td>6.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5.0</th>\n",
       "      <td>Hind. Unilever</td>\n",
       "      <td>2678.15</td>\n",
       "      <td>61.88</td>\n",
       "      <td>1.46</td>\n",
       "      <td>26.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1246.0</th>\n",
       "      <td>Silver Touch</td>\n",
       "      <td>399.65</td>\n",
       "      <td>52.19</td>\n",
       "      <td>0.25</td>\n",
       "      <td>15.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1247.0</th>\n",
       "      <td>Arfin India</td>\n",
       "      <td>31.88</td>\n",
       "      <td>49.09</td>\n",
       "      <td>0.00</td>\n",
       "      <td>13.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1248.0</th>\n",
       "      <td>Urja Global</td>\n",
       "      <td>9.60</td>\n",
       "      <td>329.70</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1249.0</th>\n",
       "      <td>N R Agarwal Inds</td>\n",
       "      <td>296.25</td>\n",
       "      <td>5.08</td>\n",
       "      <td>0.00</td>\n",
       "      <td>21.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1250.0</th>\n",
       "      <td>Donear Inds.</td>\n",
       "      <td>96.95</td>\n",
       "      <td>13.91</td>\n",
       "      <td>0.21</td>\n",
       "      <td>15.69</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1250 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Name  Current_Price  P2E_Ratio  Div_Yld   ROCE\n",
       "S.No.                                                             \n",
       "1.0     Reliance Industr        2550.25      25.87     0.31  10.14\n",
       "2.0                  TCS        3302.25      28.68     1.45  59.11\n",
       "3.0            HDFC Bank        1701.40      20.67     1.12   6.85\n",
       "4.0           ICICI Bank         934.60      19.21     0.86   6.82\n",
       "5.0       Hind. Unilever        2678.15      61.88     1.46  26.61\n",
       "...                  ...            ...        ...      ...    ...\n",
       "1246.0      Silver Touch         399.65      52.19     0.25  15.33\n",
       "1247.0       Arfin India          31.88      49.09     0.00  13.03\n",
       "1248.0       Urja Global           9.60     329.70     0.00   1.63\n",
       "1249.0  N R Agarwal Inds         296.25       5.08     0.00  21.39\n",
       "1250.0      Donear Inds.          96.95      13.91     0.21  15.69\n",
       "\n",
       "[1250 rows x 5 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rename(columns={\"CMP Rs.\":\"Current_Price\",\"P/E\":\"P2E_Ratio\",\"Div Yld %\":\"Div_Yld\",\"ROCE %\":\"ROCE\"},inplace=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6652aaf5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 1250 entries, 1.0 to 1250.0\n",
      "Data columns (total 5 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   Name           1250 non-null   object \n",
      " 1   Current_Price  1250 non-null   float64\n",
      " 2   P2E_Ratio      1249 non-null   float64\n",
      " 3   Div_Yld        1244 non-null   float64\n",
      " 4   ROCE           1246 non-null   float64\n",
      "dtypes: float64(4), object(1)\n",
      "memory usage: 58.6+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "33bfceb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# pip install sqlalchemy\n",
    "# pip install pymysql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db63be3a",
   "metadata": {},
   "source": [
    "### Import required Libraries "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d92fb2bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "import pymysql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a3da36c",
   "metadata": {},
   "source": [
    "### Create SQLAlchemy engine to connect to MySQL Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "52eca487",
   "metadata": {},
   "outputs": [],
   "source": [
    "# engine = create_engine(\"mysql+pymysql://{user}:{pw}@{host}/{db}\".format(host=hostname, db=dbname, user=uname, pw=pwd))\n",
    "\n",
    "engine = sqlalchemy.create_engine(\"mysql+pymysql://root:@localhost:3306/fundamental_stocks\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c468338d",
   "metadata": {},
   "source": [
    "### Export dataframe data to MySQL database as form of table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "06e1d15f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1250"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert dataframe to sql table\n",
    "\n",
    "df.to_sql('stocks', engine, index=False,if_exists=\"replace\") # fail, replace, append"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "507ed0a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''SELECT * FROM fundamental_stocks.stocks where ROCE>20 and Div_Yld>2.75 order by ROCE desc limit 30 ;'''"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a3ffd54",
   "metadata": {},
   "source": [
    "### Execute MySQL query \n",
    "With pd.read_sql() function, we execute the 'select' sql query on table 'stocks' to get desired output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "0509a43a",
   "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>Name</th>\n",
       "      <th>Current_Price</th>\n",
       "      <th>P2E_Ratio</th>\n",
       "      <th>Div_Yld</th>\n",
       "      <th>ROCE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Coal India</td>\n",
       "      <td>231.00</td>\n",
       "      <td>5.06</td>\n",
       "      <td>8.77</td>\n",
       "      <td>71.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Castrol India</td>\n",
       "      <td>120.80</td>\n",
       "      <td>15.14</td>\n",
       "      <td>5.38</td>\n",
       "      <td>60.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Swaraj Engines</td>\n",
       "      <td>2131.40</td>\n",
       "      <td>19.38</td>\n",
       "      <td>4.32</td>\n",
       "      <td>55.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Hindustan Zinc</td>\n",
       "      <td>307.35</td>\n",
       "      <td>12.34</td>\n",
       "      <td>24.56</td>\n",
       "      <td>50.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sanofi India</td>\n",
       "      <td>6912.30</td>\n",
       "      <td>28.92</td>\n",
       "      <td>2.81</td>\n",
       "      <td>41.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ITC</td>\n",
       "      <td>451.60</td>\n",
       "      <td>29.33</td>\n",
       "      <td>2.82</td>\n",
       "      <td>39.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VST Industries</td>\n",
       "      <td>3460.60</td>\n",
       "      <td>16.34</td>\n",
       "      <td>4.33</td>\n",
       "      <td>38.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MSTC</td>\n",
       "      <td>347.00</td>\n",
       "      <td>10.10</td>\n",
       "      <td>3.72</td>\n",
       "      <td>37.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Accelya Solution</td>\n",
       "      <td>1323.95</td>\n",
       "      <td>17.76</td>\n",
       "      <td>4.68</td>\n",
       "      <td>37.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>GHCL</td>\n",
       "      <td>515.25</td>\n",
       "      <td>4.53</td>\n",
       "      <td>3.40</td>\n",
       "      <td>35.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Oracle Fin.Serv.</td>\n",
       "      <td>3858.65</td>\n",
       "      <td>18.48</td>\n",
       "      <td>5.83</td>\n",
       "      <td>35.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Aditya AMC</td>\n",
       "      <td>372.35</td>\n",
       "      <td>17.98</td>\n",
       "      <td>3.08</td>\n",
       "      <td>33.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Rites</td>\n",
       "      <td>371.95</td>\n",
       "      <td>16.49</td>\n",
       "      <td>4.57</td>\n",
       "      <td>30.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Styrenix Perfor.</td>\n",
       "      <td>1070.35</td>\n",
       "      <td>10.29</td>\n",
       "      <td>9.81</td>\n",
       "      <td>30.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Glenmark Life</td>\n",
       "      <td>569.15</td>\n",
       "      <td>14.93</td>\n",
       "      <td>3.69</td>\n",
       "      <td>29.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>HCL Technologies</td>\n",
       "      <td>1187.95</td>\n",
       "      <td>21.72</td>\n",
       "      <td>4.04</td>\n",
       "      <td>28.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Oil India</td>\n",
       "      <td>244.95</td>\n",
       "      <td>3.04</td>\n",
       "      <td>5.92</td>\n",
       "      <td>26.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Nippon Life Ind.</td>\n",
       "      <td>288.15</td>\n",
       "      <td>24.83</td>\n",
       "      <td>3.99</td>\n",
       "      <td>26.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Bajaj Auto</td>\n",
       "      <td>4691.55</td>\n",
       "      <td>21.91</td>\n",
       "      <td>2.98</td>\n",
       "      <td>26.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Sun TV Network</td>\n",
       "      <td>439.25</td>\n",
       "      <td>10.14</td>\n",
       "      <td>3.41</td>\n",
       "      <td>26.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Allsec Tech.</td>\n",
       "      <td>543.20</td>\n",
       "      <td>16.94</td>\n",
       "      <td>8.28</td>\n",
       "      <td>25.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Banco Products</td>\n",
       "      <td>301.35</td>\n",
       "      <td>9.15</td>\n",
       "      <td>6.64</td>\n",
       "      <td>25.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Redington</td>\n",
       "      <td>186.00</td>\n",
       "      <td>10.44</td>\n",
       "      <td>3.87</td>\n",
       "      <td>24.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Vedanta</td>\n",
       "      <td>277.95</td>\n",
       "      <td>9.68</td>\n",
       "      <td>36.52</td>\n",
       "      <td>23.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Hero Motocorp</td>\n",
       "      <td>2910.10</td>\n",
       "      <td>20.77</td>\n",
       "      <td>3.44</td>\n",
       "      <td>23.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>IIFL Securities</td>\n",
       "      <td>61.05</td>\n",
       "      <td>7.47</td>\n",
       "      <td>4.91</td>\n",
       "      <td>22.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Sh. Digvijay Cem</td>\n",
       "      <td>78.90</td>\n",
       "      <td>19.88</td>\n",
       "      <td>5.07</td>\n",
       "      <td>22.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Tech Mahindra</td>\n",
       "      <td>1130.85</td>\n",
       "      <td>22.82</td>\n",
       "      <td>2.83</td>\n",
       "      <td>22.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Nirlon</td>\n",
       "      <td>399.40</td>\n",
       "      <td>22.79</td>\n",
       "      <td>6.51</td>\n",
       "      <td>22.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Bajaj Consumer</td>\n",
       "      <td>193.80</td>\n",
       "      <td>20.13</td>\n",
       "      <td>4.13</td>\n",
       "      <td>21.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Name  Current_Price  P2E_Ratio  Div_Yld   ROCE\n",
       "0         Coal India         231.00       5.06     8.77  71.48\n",
       "1      Castrol India         120.80      15.14     5.38  60.76\n",
       "2     Swaraj Engines        2131.40      19.38     4.32  55.41\n",
       "3     Hindustan Zinc         307.35      12.34    24.56  50.46\n",
       "4       Sanofi India        6912.30      28.92     2.81  41.36\n",
       "5                ITC         451.60      29.33     2.82  39.10\n",
       "6     VST Industries        3460.60      16.34     4.33  38.06\n",
       "7               MSTC         347.00      10.10     3.72  37.95\n",
       "8   Accelya Solution        1323.95      17.76     4.68  37.70\n",
       "9               GHCL         515.25       4.53     3.40  35.53\n",
       "10  Oracle Fin.Serv.        3858.65      18.48     5.83  35.11\n",
       "11        Aditya AMC         372.35      17.98     3.08  33.13\n",
       "12             Rites         371.95      16.49     4.57  30.47\n",
       "13  Styrenix Perfor.        1070.35      10.29     9.81  30.42\n",
       "14     Glenmark Life         569.15      14.93     3.69  29.86\n",
       "15  HCL Technologies        1187.95      21.72     4.04  28.66\n",
       "16         Oil India         244.95       3.04     5.92  26.86\n",
       "17  Nippon Life Ind.         288.15      24.83     3.99  26.64\n",
       "18        Bajaj Auto        4691.55      21.91     2.98  26.51\n",
       "19    Sun TV Network         439.25      10.14     3.41  26.02\n",
       "20      Allsec Tech.         543.20      16.94     8.28  25.93\n",
       "21    Banco Products         301.35       9.15     6.64  25.71\n",
       "22         Redington         186.00      10.44     3.87  24.97\n",
       "23           Vedanta         277.95       9.68    36.52  23.76\n",
       "24     Hero Motocorp        2910.10      20.77     3.44  23.57\n",
       "25   IIFL Securities          61.05       7.47     4.91  22.97\n",
       "26  Sh. Digvijay Cem          78.90      19.88     5.07  22.69\n",
       "27     Tech Mahindra        1130.85      22.82     2.83  22.49\n",
       "28            Nirlon         399.40      22.79     6.51  22.37\n",
       "29    Bajaj Consumer         193.80      20.13     4.13  21.11"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pdf = pd.read_sql(query,engine)\n",
    "pdf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b37815b",
   "metadata": {},
   "source": [
    "### Export Final Resultant data from \"pdf\" dataframe to a \"Final_Data.csv\" file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "413b957f",
   "metadata": {},
   "outputs": [],
   "source": [
    "pdf.to_csv(\"Final_Result.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bda5cf59",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}