MySql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('information_schema',)\n",
      "('mysql',)\n",
      "('performance_schema',)\n",
      "('sakila',)\n",
      "('sys',)\n",
      "('test',)\n",
      "('testdb',)\n",
      "('world',)\n"
     ]
    }
   ],
   "source": [
    "mydb= mysql.connector.connect(host=\"localhost\",user='username',passwd=\"your password\")\n",
    "mycur=mydb.cursor()\n",
    "#creating a database\n",
    "mycur.execute(\"CREATE DATABASE test\")\n",
    "#showing database\n",
    "mycur.execute(\"SHOW DATABASES\")\n",
    "for db in mycur:\n",
    "    print(db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydb= mysql.connector.connect(host=\"localhost\",user=\"root\",passwd=\"orion\",database=\"testdb\")\n",
    "mycur=mydb.cursor()\n",
    "#creating a table in database\n",
    "mycur.execute(\"create table Detail(Name varchar(255),Age integer(10))\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('detail',)\n",
      "('student',)\n",
      "('teacher',)\n"
     ]
    }
   ],
   "source": [
    "#displaying table\n",
    "mycur.execute(\"show tables\")\n",
    "for i in mycur:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql=\"INSERT INTO student(name,age) VALUES(%s,%s)\"\n",
    "student1=(\"Vivek\",19)\n",
    "students=[(\"Chotoo\",42),(\"Amit\",36)]\n",
    "#enter singlr value in table\n",
    "mycur.execute(sql,student1)\n",
    "mydb.commit()\n",
    "#entering multiple values in the table\n",
    "mycur.executemany(sql,students)\n",
    "mydb.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Utkarsh', 18)\n",
      "('Tanish', 15)\n",
      "('Shambhu nath mishra', 47)\n",
      "('Vivek', 19)\n",
      "('Chotoo', 42)\n",
      "('Amit', 36)\n",
      "('Vivek', 19)\n",
      "('Chotoo', 42)\n",
      "('Amit', 36)\n"
     ]
    }
   ],
   "source": [
    "mycur.execute(\"select * from student\")\n",
    "#fetching all the data from table and displaying it\n",
    "result= mycur.fetchall()\n",
    "for i in result:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Amit', 36)\n",
      "('Amit', 36)\n"
     ]
    }
   ],
   "source": [
    "#using where command\n",
    "mycur.execute(\"select * from student where age>20 && age<40\")\n",
    "result= mycur.fetchall()\n",
    "for i in result:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Utkarsh', 18)\n",
      "('Tanish', 15)\n",
      "('Shambhu nath mishra', 47)\n",
      "('Chotoo', 42)\n",
      "('Amit', 36)\n",
      "('Chotoo', 42)\n",
      "('Amit', 36)\n"
     ]
    }
   ],
   "source": [
    "#using like command\n",
    "mycur.execute(\"select * from student where name like '%t%'\")\n",
    "result= mycur.fetchall()\n",
    "for i in result:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Utkarsh', 19)\n",
      "('Tanish', 15)\n",
      "('Shambhu nath mishra', 47)\n",
      "('Vivek', 19)\n",
      "('Chotoo', 42)\n",
      "('Amit', 36)\n",
      "('Vivek', 19)\n",
      "('Chotoo', 42)\n",
      "('Amit', 36)\n"
     ]
    }
   ],
   "source": [
    "#update the value in the table\n",
    "mycur.execute(\"update student set age=19 where name='Utkarsh'\")\n",
    "mydb.commit()\n",
    "#display the table\n",
    "mycur.execute(\"select * from student\")\n",
    "result= mycur.fetchall()\n",
    "for i in result:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Shambhu nath mishra', 47)\n",
      "('Vivek', 19)\n",
      "('Chotoo', 42)\n"
     ]
    }
   ],
   "source": [
    "#using LIMIT keyword\n",
    "sql=\"Select * from student LIMIT 3 OFFSET 2\"\n",
    "mycur.execute(sql)\n",
    "result= mycur.fetchall()\n",
    "for i in result:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Vivek', 19)\n",
      "('Vivek', 19)\n",
      "('Utkarsh', 19)\n",
      "('Tanish', 15)\n",
      "('Shambhu nath mishra', 47)\n",
      "('Chotoo', 42)\n",
      "('Chotoo', 42)\n",
      "('Amit', 36)\n",
      "('Amit', 36)\n"
     ]
    }
   ],
   "source": [
    "# using order by \n",
    "sql=\"Select * from student order by name DESC\"\n",
    "mycur.execute(sql)\n",
    "result= mycur.fetchall()\n",
    "for i in result:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "ename": "InterfaceError",
     "evalue": "No result set to fetch from.",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mInterfaceError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-12-e61ce4c39ae5>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"delete from student where age<20\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mmycur\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msql\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 4\u001b[1;33m \u001b[0mresult\u001b[0m\u001b[1;33m=\u001b[0m \u001b[0mmycur\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      5\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mi\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      6\u001b[0m     \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mi\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\mysql\\connector\\cursor_cext.py\u001b[0m in \u001b[0;36mfetchall\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    487\u001b[0m         \"\"\"\n\u001b[0;32m    488\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_cnx\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munread_result\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 489\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0merrors\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mInterfaceError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"No result set to fetch from.\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    490\u001b[0m         \u001b[0mrows\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_cnx\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_rows\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    491\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_nextrow\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_nextrow\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mInterfaceError\u001b[0m: No result set to fetch from."
     ]
    }
   ],
   "source": [
    "#delete a row from the table\n",
    "sql=\"delete from student where age<20\"\n",
    "mycur.execute(sql)\n",
    "result= mycur.fetchall()\n",
    "for i in result:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop a table\n",
    "sql=\"drop table student\"\n",
    "mycur.execute(sql)"
   ]
  },
  {
   "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}