assignment for interview (1).ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlrd # Reading an excel file using Python \n",
    "\n",
    "\n",
    "# Give the location of the file \n",
    "loc = (\"/home/rajaneesh/Downloads/beginner_assignment01.xlsx\") \n",
    "\n",
    "# To open Workbook \n",
    "wb = xlrd.open_workbook(loc) \n",
    "sheet = wb.sheet_by_index(0) \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<mysql.connector.connection_cext.CMySQLConnection object at 0x7fd02ca88910>\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "pymysql.install_as_MySQLdb()                                #library to access MySQLdb\n",
    "import MySQLdb\n",
    "import mysql.connector\n",
    "db_connection = mysql.connector.connect(                    #connecting to mysql-server\n",
    "  host=\"localhost\",\n",
    "  user=\"root\",\n",
    "  passwd=\"Rajini@123\",\n",
    "  auth_plugin=\"mysql_native_password\"\n",
    ")\n",
    "print(db_connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = db_connection.cursor()                            #cursor to execute mysql queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Product Name\n",
      "Model Name\n",
      "Product Serial No\n",
      "Group Associated\n",
      "product MRP (rs.)\n"
     ]
    }
   ],
   "source": [
    "# For reading columns\n",
    "sheet.cell_value(0, 0) \n",
    "  \n",
    "for i in range(sheet.ncols): \n",
    "    print(sheet.cell_value(0, i)) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Product Name\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 1\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 2\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 3\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 4\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n",
      "Demo Product 5\n"
     ]
    }
   ],
   "source": [
    "# For readind rows\n",
    "sheet.cell_value(0, 0) \n",
    "  \n",
    "for i in range(sheet.nrows): \n",
    "    print(sheet.cell_value(i, 0)) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"CREATE DATABASE assignment1\") #assignment1 is the name of the database which needs to be created"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Assignment1',)\n",
      "('Assugnment1',)\n",
      "('Exceldb',)\n",
      "('Myssignment',)\n",
      "('PythonMysql',)\n",
      "('assignment',)\n",
      "('assignment1',)\n",
      "('information_schema',)\n",
      "('mysql',)\n",
      "('performance_schema',)\n",
      "('pyMysql',)\n",
      "('pythonmysql',)\n",
      "('sys',)\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"SHOW DATABASES\")\n",
    "\n",
    "for x in cursor:\n",
    "  print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Product Name       Model Name  Product Serial No Group Associated  \\\n",
      "0    Demo Product 1  Product Model 1     98765987659801          Group 1   \n",
      "1    Demo Product 1  Product Model 1     98765987659802          Group 2   \n",
      "2    Demo Product 1  Product Model 1     98765987659803          Group 3   \n",
      "3    Demo Product 1  Product Model 1     98765987659804          Group 4   \n",
      "4    Demo Product 1  Product Model 1     98765987659805          Group 5   \n",
      "..              ...              ...                ...              ...   \n",
      "105  Demo Product 5  Product Model 5     98765987659418         Group 18   \n",
      "106  Demo Product 5  Product Model 5     98765987659419         Group 19   \n",
      "107  Demo Product 5  Product Model 5     98765987659420         Group 20   \n",
      "108  Demo Product 5  Product Model 5     98765987659421         Group 21   \n",
      "109  Demo Product 5  Product Model 5     98765987659422         Group 22   \n",
      "\n",
      "     product MRP (rs.)  \n",
      "0                 1001  \n",
      "1                 1002  \n",
      "2                 1003  \n",
      "3                 1004  \n",
      "4                 1005  \n",
      "..                 ...  \n",
      "105              11018  \n",
      "106              11019  \n",
      "107              11020  \n",
      "108              11021  \n",
      "109              11022  \n",
      "\n",
      "[110 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from pandas.io import sql\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "df = pd.read_excel('/home/rajaneesh/Downloads/beginner_assignment01.xlsx')\n",
    "print(df)\n",
    "\n",
    "engine = create_engine('mysql://root:Rajini@123@localhost/assignment1')\n",
    "with engine.connect() as conn, conn.begin():\n",
    "    df.to_sql('Table1', conn, if_exists='replace')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}