Sql+with+python.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Proprietary content. © Great Learning. All Rights Reserved. Unauthorized use or distribution prohibited."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conda install -c anaconda mysql-connector-python\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Python needs a MySQL driver to access the MySQL database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: mysql-connector-python in c:\\users\\sampriti\\anaconda3\\lib\\site-packages (8.0.21)\n",
      "Requirement already satisfied: protobuf>=3.0.0 in c:\\users\\sampriti\\anaconda3\\lib\\site-packages (from mysql-connector-python) (3.13.0)\n",
      "Requirement already satisfied: six>=1.9 in c:\\users\\sampriti\\anaconda3\\lib\\site-packages (from protobuf>=3.0.0->mysql-connector-python) (1.15.0)\n",
      "Requirement already satisfied: setuptools in c:\\users\\sampriti\\anaconda3\\lib\\site-packages (from protobuf>=3.0.0->mysql-connector-python) (49.2.0.post20200714)\n"
     ]
    }
   ],
   "source": [
    "!pip install  mysql-connector-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<mysql.connector.connection.MySQLConnection object at 0x000001F66360A850>\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",\n",
    "  user=\"root\",\n",
    "  passwd=\"12345678\"\n",
    ")\n",
    "\n",
    "print(mydb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",\n",
    "  user=\"root\",\n",
    "  password=\"12345678\",\n",
    "  database=\"sq\"\n",
    ")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor = mydb.cursor()\n",
    "\n",
    "mycursor.execute(\"CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQLCursor: SHOW TABLES\n",
      "('customers',)\n"
     ]
    }
   ],
   "source": [
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",\n",
    "  user=\"root\",\n",
    "  password=\"12345678\",\n",
    "  database=\"sq\"\n",
    ")\n",
    "\n",
    "mycursor = mydb.cursor()\n",
    "\n",
    "mycursor.execute(\"SHOW TABLES\")\n",
    "\n",
    "print(mycursor)\n",
    "\n",
    "for x in mycursor:\n",
    "  print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "mycursor.execute(\"ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 record inserted.\n"
     ]
    }
   ],
   "source": [
    "sql = \"INSERT INTO customers (name, address) VALUES (%s, %s)\"\n",
    "val = (\"John\", \"Highway 21\")\n",
    "mycursor.execute(sql, val)\n",
    "\n",
    "mydb.commit()\n",
    "\n",
    "print(mycursor.rowcount, \"record inserted.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 was inserted.\n"
     ]
    }
   ],
   "source": [
    "mycursor = mydb.cursor()\n",
    "\n",
    "sql = \"INSERT INTO customers (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, \"was inserted.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('John', 'Highway 21', 1)\n",
      "('Peter', 'Lowstreet 4', 2)\n",
      "('Amy', 'Apple st 652', 3)\n",
      "('Hannah', 'Mountain 21', 4)\n",
      "('Michael', 'Valley 345', 5)\n",
      "('Sandy', 'Ocean blvd 2', 6)\n",
      "('Betty', 'Green Grass 1', 7)\n",
      "('Richard', 'Sky st 331', 8)\n",
      "('Susan', 'One way 98', 9)\n",
      "('Vicky', 'Yellow Garden 2', 10)\n",
      "('Ben', 'Park Lane 38', 11)\n",
      "('William', 'Central st 954', 12)\n",
      "('Chuck', 'Main Road 989', 13)\n",
      "('Viola', 'Sideway 1633', 14)\n"
     ]
    }
   ],
   "source": [
    "mycursor.execute(\"SELECT * FROM customers\")\n",
    "\n",
    "myresult = mycursor.fetchall()\n",
    "\n",
    "for x in myresult:\n",
    "  print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(14,)\n"
     ]
    }
   ],
   "source": [
    "mycursor = mydb.cursor()\n",
    "\n",
    "mycursor.execute(\"SELECT count(*) FROM customers\")\n",
    "\n",
    "myresult = mycursor.fetchall()\n",
    "\n",
    "for x in myresult:\n",
    "  print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Ben', 'Park Lane 38', 11)\n"
     ]
    }
   ],
   "source": [
    "mycursor = mydb.cursor()\n",
    "\n",
    "sql = \"SELECT * FROM customers WHERE address ='Park Lane 38'\"\n",
    "\n",
    "mycursor.execute(sql)\n",
    "\n",
    "myresult = mycursor.fetchall()\n",
    "\n",
    "for x in myresult:\n",
    "  print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('John', 'Highway 21', 1)\n",
      "('Susan', 'One way 98', 9)\n",
      "('Viola', 'Sideway 1633', 14)\n"
     ]
    }
   ],
   "source": [
    "mycursor = mydb.cursor()\n",
    "\n",
    "sql = \"SELECT * FROM customers WHERE address LIKE '%way%'\"\n",
    "\n",
    "mycursor.execute(sql)\n",
    "\n",
    "myresult = mycursor.fetchall()\n",
    "\n",
    "for x in myresult:\n",
    "  print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Amy', 'Apple st 652', 3)\n",
      "('Ben', 'Park Lane 38', 11)\n",
      "('Betty', 'Green Grass 1', 7)\n",
      "('Chuck', 'Main Road 989', 13)\n",
      "('Hannah', 'Mountain 21', 4)\n",
      "('John', 'Highway 21', 1)\n",
      "('Michael', 'Valley 345', 5)\n",
      "('Peter', 'Lowstreet 4', 2)\n",
      "('Richard', 'Sky st 331', 8)\n",
      "('Sandy', 'Ocean blvd 2', 6)\n",
      "('Susan', 'One way 98', 9)\n",
      "('Vicky', 'Yellow Garden 2', 10)\n",
      "('Viola', 'Sideway 1633', 14)\n",
      "('William', 'Central st 954', 12)\n"
     ]
    }
   ],
   "source": [
    "mycursor = mydb.cursor()\n",
    "\n",
    "sql = \"SELECT * FROM customers ORDER BY name\"\n",
    "\n",
    "mycursor.execute(sql)\n",
    "\n",
    "myresult = mycursor.fetchall()\n",
    "\n",
    "for x in myresult:\n",
    "  print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('William', 'Central st 954', 12)\n",
      "('Viola', 'Sideway 1633', 14)\n",
      "('Vicky', 'Yellow Garden 2', 10)\n",
      "('Susan', 'One way 98', 9)\n",
      "('Sandy', 'Ocean blvd 2', 6)\n",
      "('Richard', 'Sky st 331', 8)\n",
      "('Peter', 'Lowstreet 4', 2)\n",
      "('Michael', 'Valley 345', 5)\n",
      "('John', 'Highway 21', 1)\n",
      "('Hannah', 'Mountain 21', 4)\n",
      "('Chuck', 'Main Road 989', 13)\n",
      "('Betty', 'Green Grass 1', 7)\n",
      "('Ben', 'Park Lane 38', 11)\n",
      "('Amy', 'Apple st 652', 3)\n"
     ]
    }
   ],
   "source": [
    "mycursor = mydb.cursor()\n",
    "\n",
    "sql = \"SELECT * FROM customers ORDER BY name DESC\"\n",
    "\n",
    "mycursor.execute(sql)\n",
    "\n",
    "myresult = mycursor.fetchall()\n",
    "\n",
    "for x in myresult:\n",
    "  print(x)"
   ]
  },
  {
   "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
}