Database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql as sql "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "hostname = \"localhost\"\n",
    "port = 3306\n",
    "username = \"root\"\n",
    "password = \"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sql.connect(\n",
    "    host=hostname, \n",
    "    port=port,\n",
    "    user=username,\n",
    "    password=password,\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"SHOW DATABASES;\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "output = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(('bank',), ('demo',), ('employees',), ('information_schema',), ('infytq',), ('mysql',), ('mystudent',), ('performance_schema',), ('phpmyadmin',), ('sakila',), ('student',), ('test',), ('world',))\n"
     ]
    }
   ],
   "source": [
    "print(output)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "bank\n",
      "demo\n",
      "employees\n",
      "information_schema\n",
      "infytq\n",
      "mysql\n",
      "mystudent\n",
      "performance_schema\n",
      "phpmyadmin\n",
      "sakila\n",
      "student\n",
      "test\n",
      "world\n"
     ]
    }
   ],
   "source": [
    "for dbname in output:\n",
    "    print(*dbname)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"CREATE DATABASE Blog;\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_databases(cursor):\n",
    "    cursor.execute(\"SHOW DATABASES;\")\n",
    "    for sn, dbname in enumerate(cursor.fetchall()):\n",
    "        print(f\"{sn+1:>5} . {dbname}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    1 . ('bank',)\n",
      "    2 . ('blog',)\n",
      "    3 . ('demo',)\n",
      "    4 . ('employees',)\n",
      "    5 . ('information_schema',)\n",
      "    6 . ('infytq',)\n",
      "    7 . ('mysql',)\n",
      "    8 . ('mystudent',)\n",
      "    9 . ('performance_schema',)\n",
      "   10 . ('phpmyadmin',)\n",
      "   11 . ('sakila',)\n",
      "   12 . ('student',)\n",
      "   13 . ('test',)\n",
      "   14 . ('world',)\n"
     ]
    }
   ],
   "source": [
    "get_databases(cursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "# local\n",
    "# remote\n",
    "query = [\"CREATE USER 'blog'@'localhost' IDENTIFIED BY 'blog';\", \n",
    "         \"CREATE USER 'blog'@'%' IDENTIFIED BY 'blog';\"\n",
    "        ]\n",
    "for q in query:\n",
    "    print(cursor.execute(q))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_user(cursor):\n",
    "    query = \"SELECT user, host FROM mysql.user\"\n",
    "    print(\"total users: \", cursor.execute(query))\n",
    "    result = cursor.fetchall()\n",
    "    print(f\"{'user':>30} {'hostname':>30}\")\n",
    "    for user, host in result:\n",
    "        print(f\"{user:>30} {host:>30}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total users:  10\n",
      "                          user                       hostname\n",
      "                          blog                              %\n",
      "                          root                      127.0.0.1\n",
      "                          root                            ::1\n",
      "                          bank                      localhost\n",
      "                          blog                      localhost\n",
      "                           pma                      localhost\n",
      "                          root                      localhost\n",
      "                        sachin                      localhost\n",
      "                            st                      localhost\n",
      "                       student                      localhost\n"
     ]
    }
   ],
   "source": [
    "get_user(cursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"GRANT SELECT,COUNT ON  Blog.users TO 'blog'@'localhost';\"\n",
    "query = \"REVOKE SELECT,COUNT ON  Blog.users TO 'blog'@'localhost';\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"GRANT ALL PRIVILEGES ON Blog.* TO 'blog'@'localhost';\"\n",
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"GRANT ALL PRIVILEGES ON Blog.* TO 'blog'@'%';\"\n",
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### We have to Create Tables "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_all_tables(cursor):\n",
    "    query = f\"SHOW TABLES\"\n",
    "    print(\"Total Tables: \", cursor.execute(query))\n",
    "    result = cursor.fetchall()\n",
    "    for table in result:\n",
    "        print(*table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sql.connect(host='localhost', port=3306, user='blog', password='blog', database='blog')\n",
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Tables:  0\n"
     ]
    }
   ],
   "source": [
    "get_all_tables(cursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"CREATE TABLE users(username VARCHAR(30) PRIMARY KEY, email  VARCHAR(30),\\\n",
    "password VARCHAR(30), first_name VARCHAR(30), last_name VARCHAR(30), ph_no VARCHAR(30))\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# DROP -> structure, schema + Data\n",
    "# TRUNCATE -> DATA\n",
    "\n",
    "query = \"DROP TABLE users\"\n",
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Tables:  0\n"
     ]
    }
   ],
   "source": [
    "get_all_tables(cursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"CREATE TABLE users(username VARCHAR(30) PRIMARY KEY, email  VARCHAR(30) NOT NULL,\\\n",
    "password VARCHAR(30) NOT NULL, first_name VARCHAR(30) NOT NULL, last_name VARCHAR(30) NOT NULL,\\\n",
    "ph_no VARCHAR(30), sex ENUM('MALE', 'FEMALE', 'OTHER'))\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Tables:  1\n",
      "users\n"
     ]
    }
   ],
   "source": [
    "get_all_tables(cursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q = \"DESCRIBE users;\"\n",
    "# SCHEMA\n",
    "cursor.execute(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "__________________________________________________________________________________________\n",
      "|     Column Name |                      Data Type | NULL Value | key   |    OTHER | \n",
      "__________________________________________________________________________________________\n",
      "|        username |                    varchar(30) |         NO |   PRI |     None |  \n",
      "__________________________________________________________________________________________\n",
      "|           email |                    varchar(30) |         NO |       |     None |  \n",
      "__________________________________________________________________________________________\n",
      "|        password |                    varchar(30) |         NO |       |     None |  \n",
      "__________________________________________________________________________________________\n",
      "|      first_name |                    varchar(30) |         NO |       |     None |  \n",
      "__________________________________________________________________________________________\n",
      "|       last_name |                    varchar(30) |         NO |       |     None |  \n",
      "__________________________________________________________________________________________\n",
      "|           ph_no |                    varchar(30) |        YES |       |     None |  \n",
      "__________________________________________________________________________________________\n",
      "|             sex |  enum('MALE','FEMALE','OTHER') |        YES |       |     None |  \n",
      "__________________________________________________________________________________________\n"
     ]
    }
   ],
   "source": [
    "print(\"__\"*45)\n",
    "print(f\"| {'Column Name':>15} | {'Data Type':>30} | {'NULL Value':>4} | {'key':5} | {'OTHER':>8} | \")\n",
    "for row in result:\n",
    "    print(\"__\"*45)\n",
    "    row =list(row)\n",
    "    if row[-2] == None:\n",
    "        row[-2] = 'None'\n",
    "    print(\"| {:>15} | {:>30} | {:>10} | {:>5} | {:>8} | {} \".format(*row))\n",
    "print(\"__\"*45)\n",
    "# column name, data type, nullable, KEYS, EXTRA "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"DELETE FROM users WHERE username='sachin';\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"INSERT INTO users (username, email, password, first_name, last_name, ph_no, sex) values\\\n",
    "('sachin', 'sachin@grras.com', 'redhat', 'sachin', 'yadav', '9782131159', 'MALE');\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"INSERT INTO users (username, email, password, first_name, last_name, ph_no, sex) values\\\n",
    "('kushal', 'kushal@grras.com', 'Asimov', 'kushal', 'samota', '1234567890', 'MALE');\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"SELECT * FROM users;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "kushal\tkushal@grras.com\tAsimov\tkushal\tsamota\t1234567890\tMALE\n"
     ]
    }
   ],
   "source": [
    "for row in cursor.fetchall():\n",
    "    print(*row, sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "kushal\tkushal@grras.com\t1234567890\n",
      "sachin\tsachin@grras.com\t9782131159\n",
      "simran\tsimran.grover@grras.com\t1234567890\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"SELECT username, email, ph_no FROM users;\")\n",
    "for row in cursor.fetchall():\n",
    "    print(*row, sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter name: simran\n",
      "No such user exists\n"
     ]
    }
   ],
   "source": [
    "name = input(\"Enter name: \").strip().lower()\n",
    "if cursor.execute(f\"SELECT * FROM users WHERE username = '{name}';\"):\n",
    "    print(*cursor.fetchone(), sep='\\t')\n",
    "else:\n",
    "    print(\"No such user exists\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter name: sachin\n",
      "sachin\tsachin@grras.com\tredhat\tsachin\tyadav\t9782131159\tMALE\n"
     ]
    }
   ],
   "source": [
    "name = input(\"Enter name: \").strip().lower()\n",
    "if cursor.execute(f\"SELECT * FROM users WHERE username = '{name}';\"):\n",
    "    print(*cursor.fetchone(), sep='\\t')\n",
    "else:\n",
    "    print(\"No such user exists\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter name: kushal\n",
      "kushal\tkushal@grras.com\tAsimov\tkushal\tsamota\t1234567890\tMALE\n"
     ]
    }
   ],
   "source": [
    "name = input(\"Enter name: \").strip().lower()\n",
    "if cursor.execute(f\"SELECT * FROM users WHERE username = '{name}';\"):\n",
    "    print(*cursor.fetchone(), sep='\\t')\n",
    "else:\n",
    "    print(\"No such user exists\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    UPDATE, DELETE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assignment: \n",
    "    \n",
    "    Blog \n",
    "        \n",
    "        username foreign key, heading, date, content, city, topic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Project\n",
    "\n",
    "    app.py\n",
    "\n",
    "    templates\n",
    "\n",
    "        index.html\n",
    "        \n",
    "    static\n",
    "    \n",
    "        css\n",
    "        \n",
    "        images\n",
    "        \n",
    "        js"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = pymysql.connect(\n",
    "                            host='localhost',\n",
    "                            port=3306,\n",
    "                            user='blog',\n",
    "                            password='blog',\n",
    "                            database='blog'\n",
    "                            )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"SHOW TABLES;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(('blogs',), ('users',))"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"DESC users;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(('username', 'varchar(30)', 'NO', 'PRI', None, ''),\n",
       " ('email', 'varchar(30)', 'NO', '', None, ''),\n",
       " ('password', 'varchar(30)', 'NO', '', None, ''),\n",
       " ('first_name', 'varchar(30)', 'NO', '', None, ''),\n",
       " ('last_name', 'varchar(30)', 'NO', '', None, ''),\n",
       " ('ph_no', 'varchar(30)', 'YES', '', None, ''),\n",
       " ('sex', \"enum('MALE','FEMALE','OTHER')\", 'YES', '', None, ''))"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"SELECT * FROM users;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(('kushal',\n",
       "  'kushal@grras.com',\n",
       "  'Asimov',\n",
       "  'kushal',\n",
       "  'samota',\n",
       "  '1234567890',\n",
       "  'MALE'),\n",
       " ('sachin',\n",
       "  'sachin@grras.com',\n",
       "  'redhat',\n",
       "  'sachin',\n",
       "  'yadav',\n",
       "  '9782131159',\n",
       "  'MALE'))"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"CREATE TABLE blogs (id INT PRIMARY KEY AUTO_INCREMENT,\\\n",
    "username VARCHAR(30) NOT NULL, topic VARCHAR(100), heading VARCHAR(500) NOT NULL,\\\n",
    "content LONGTEXT NOT NULL, date DATE,  CONSTRAINT `fk_blogs_users` \\\n",
    "FOREIGN KEY (username) REFERENCES users (username) ON DELETE CASCADE)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_all_tables(cursor):\n",
    "    query = f\"SHOW TABLES\"\n",
    "    print(\"Total Tables: \", cursor.execute(query))\n",
    "    result = cursor.fetchall()\n",
    "    for table in result:\n",
    "        print(*table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Tables:  2\n",
      "blogs\n",
      "users\n"
     ]
    }
   ],
   "source": [
    "get_all_tables(cursor)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`DATABASE SETUP`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    'email'\n",
    "\n",
    "    'password'\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "email = \"sachin@grras.com\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"SELECT * FROM users WHERE email='sachin@grras.com';\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows = cursor.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(('sachin',\n",
       "  'sachin@grras.com',\n",
       "  'redhat',\n",
       "  'sachin',\n",
       "  'yadav',\n",
       "  '9782131159',\n",
       "  'MALE'),)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT * FROM users WHERE email='simrangrover@grras.com';\"\n",
    "rows = cursor.execute(query)\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT * FROM users WHERE username='sachin'\"\n",
    "rows = cursor.execute(query)\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT * FROM users WHERE username='sachin1'\"\n",
    "rows = cursor.execute(query)\n",
    "rows"
   ]
  },
  {
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}