Day 23 - Database SQL Connectivity.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Student Detain\n",
      "Student Detain\n",
      "Sufficient attendance\n"
     ]
    }
   ],
   "source": [
    "import xlrd\n",
    "address=(\"C:/Users/Lenovo/Relearn/file.xlsx\")\n",
    "a=xlrd.open_workbook(address)\n",
    "sheet=a.sheet_by_index(0)\n",
    "sheet.cell_value(0,0)\n",
    "\n",
    "for i in range(sheet.nrows):\n",
    "    a=sheet.cell_value(i,1)\n",
    "    if i >0:\n",
    "        if a<75.0:\n",
    "            print (\"Student Detain\")\n",
    "        else:\n",
    "            print(\"Sufficient attendance\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter password: ········\n",
      "Email\n",
      "pupneja.yashu@gmail.com\n",
      "balpreetkaur5626@gmail.com\n",
      "pupnejaanshima@gmail.com\n",
      "Sufficient attendance\n"
     ]
    }
   ],
   "source": [
    "import xlrd\n",
    "import smtplib\n",
    "import getpass\n",
    "\n",
    "address=(\"C:/Users/Lenovo/Relearn/file.xlsx\")\n",
    "\n",
    "a=xlrd.open_workbook(address)\n",
    "sheet=a.sheet_by_index(0)\n",
    "sheet.cell_value(0,0)\n",
    "server=smtplib.SMTP(\"smtp.gmail.com:587\")\n",
    "server.ehlo()\n",
    "server.starttls()\n",
    "password=getpass.getpass(\"Enter password: \")\n",
    "server.login('pupneja.yashu@gmail.com',password)\n",
    "\n",
    "for i in range(sheet.nrows):\n",
    "    at=sheet.cell_value(i,1)\n",
    "    a=sheet.cell_value(i,2)\n",
    "    print(a)\n",
    "    if i>0:\n",
    "        if float(at)<75.0:\n",
    "            from_address='pupneja.yashu@gmail.com'\n",
    "            to_address=sheet.cell_value(i,2)\n",
    "            subject=\"Alert !! Meeting\"\n",
    "            body=\"Dear Parent, Your ward is having shortage of attendance.Please meet the principal.\"\n",
    "            msg=(\"Subject: \"+subject+\"\\n\\n\"+body)\n",
    "            server.sendmail(from_address,to_address,msg)\n",
    "        else:\n",
    "            print(\"Sufficient attendance\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "\n",
    "mydb=mysql.connector.connect(\n",
    "host=\"localhost\",\n",
    "user=\"root\",\n",
    "passwd=\"\")\n",
    "\n",
    "mycursor=mydb.cursor()\n",
    "mycursor.execute(\"CREATE DATABASE PROJECT\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "\n",
    "mydb=mysql.connector.connect(\n",
    "host=\"localhost\",\n",
    "user=\"root\",\n",
    "passwd=\"\",\n",
    "database=\"PROJECT\")\n",
    "\n",
    "mycursor=mydb.cursor()\n",
    "mycursor.execute(\"CREATE TABLE PYTHON(name VARCHAR(255),address VARCHAR(255))\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('python',)\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "mydb=mysql.connector.connect(\n",
    "host=\"localhost\",\n",
    "user=\"root\",\n",
    "passwd=\"\",\n",
    "database=\"PROJECT\")\n",
    "\n",
    "mycursor=mydb.cursor()\n",
    "mycursor.execute(\"SHOW TABLES\")\n",
    "\n",
    "for x in mycursor:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 record inserted.\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "mydb=mysql.connector.connect(\n",
    "host=\"localhost\",\n",
    "user=\"root\",\n",
    "passwd=\"\",\n",
    "database=\"PROJECT\")\n",
    "\n",
    "mycursor=mydb.cursor()\n",
    "sql=\"INSERT INTO PYTHON (name,address) VALUES (%s, %s)\"\n",
    "val = (\"John\", \"Highway 21\")\n",
    "\n",
    "mycursor.execute(sql, val)\n",
    "\n",
    "mydb.commit()\n",
    "\n",
    "print(mycursor.rowcount, \"record inserted.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 record inserted.\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "mydb=mysql.connector.connect(\n",
    "host=\"localhost\",\n",
    "user=\"root\",\n",
    "passwd=\"\",\n",
    "database=\"PROJECT\")\n",
    "\n",
    "mycursor=mydb.cursor()\n",
    "sql=\"INSERT INTO PYTHON (name,address) VALUES (%s, %s)\"\n",
    "val = [\n",
    "  ('Peter', 'Lowstreet 4'),\n",
    "  ('Amy', 'Apple st 652'),\n",
    "  ('Hannah', 'Mountain 21'),\n",
    "  ('Michael', 'Valley 345'),\n",
    "  ('Sandy', 'Ocean blvd 2'),\n",
    "  ('Betty', 'Green Grass 1'),\n",
    "  ('Richard', 'Sky st 331'),\n",
    "  ('Susan', 'One way 98'),\n",
    "  ('Vicky', 'Yellow Garden 2'),\n",
    "  ('Ben', 'Park Lane 38'),\n",
    "  ('William', 'Central st 954'),\n",
    "  ('Chuck', 'Main Road 989'),\n",
    "  ('Viola', 'Sideway 1633')\n",
    "]\n",
    "\n",
    "mycursor.executemany(sql, val)\n",
    "\n",
    "mydb.commit()\n",
    "\n",
    "print(mycursor.rowcount, \"record inserted.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('John', 'Highway 21')\n",
      "('Peter', 'Lowstreet 4')\n",
      "('Amy', 'Apple st 652')\n",
      "('Hannah', 'Mountain 21')\n",
      "('Michael', 'Valley 345')\n",
      "('Sandy', 'Ocean blvd 2')\n",
      "('Betty', 'Green Grass 1')\n",
      "('Richard', 'Sky st 331')\n",
      "('Susan', 'One way 98')\n",
      "('Vicky', 'Yellow Garden 2')\n",
      "('Ben', 'Park Lane 38')\n",
      "('William', 'Central st 954')\n",
      "('Chuck', 'Main Road 989')\n",
      "('Viola', 'Sideway 1633')\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "mydb=mysql.connector.connect(\n",
    "host=\"localhost\",\n",
    "user=\"root\",\n",
    "passwd=\"\",\n",
    "database=\"PROJECT\")\n",
    "mycursor = mydb.cursor()\n",
    "\n",
    "mycursor.execute(\"SELECT * FROM python\")\n",
    "\n",
    "myresult = mycursor.fetchall()\n",
    "\n",
    "for x in myresult:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://www.w3schools.com/python/python_mysql_limit.asp"
   ]
  },
  {
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}