Database connectivity.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database connectivity and operations using Python.\n",
    "\n",
    "For Example, the following is the example of connecting with MySQL database \"my_database1\" and creating table grades1 and inserting values inside it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1112e57a0>"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#!/usr/bin/python\n",
    "\n",
    "import sqlite3\n",
    "#connecting with the database.\n",
    "db = sqlite3.connect(\"my_database3.db\")\n",
    "# Drop table if it already exist using execute() method.\n",
    "db.execute(\"drop table if exists grades1\")\n",
    "# Create table as per requirement\n",
    "db.execute(\"create table grades1(id int, name text, score int)\")\n",
    "#inserting values inside the created table\n",
    "db.execute(\"insert into grades1(id, name, score) values(101, 'John',99 )\")\n",
    "db.execute(\"insert into grades1(id, name, score) values(102, 'Gary',90 )\")\n",
    "db.execute(\"insert into grades1(id, name, score) values(103, 'James', 80 )\")\n",
    "db.execute(\"insert into grades1(id, name, score) values(104, 'Cathy', 85 )\")\n",
    "db.execute(\"insert into grades1(id, name, score) values(105, 'Kris',95 )\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(101, 'John', 99)\n",
      "(102, 'Gary', 90)\n",
      "(103, 'James', 80)\n",
      "(104, 'Cathy', 85)\n",
      "(105, 'Kris', 95)\n",
      "------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "results = db.execute(\"select * from grades1 order by id\")\n",
    "for row in results:\n",
    "    print(row)\n",
    "print(\"-\" * 60 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(102, 'Gary', 90)\n",
      "------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "results = db.execute(\"select * from grades1 where name = 'Gary' \")\n",
    "for row in results: print(row)\n",
    "print(\"-\" * 60 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(101, 'John', 99)\n",
      "(102, 'Gary', 90)\n",
      "(105, 'Kris', 95)\n",
      "------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "results = db.execute(\"select * from grades1 where score >= 90 \")\n",
    "for row in results:\n",
    "    print(row)\n",
    "print(\"-\" * 60 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('John', 99)\n",
      "('Kris', 95)\n",
      "('Gary', 90)\n",
      "('Cathy', 85)\n",
      "('James', 80)\n",
      "------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "results = db.execute(\"select name, score from grades1 order by score desc \")\n",
    "for row in results:\n",
    "    print(row)\n",
    "print(\"-\" * 60 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('James', 80)\n",
      "('Cathy', 85)\n",
      "('Gary', 90)\n",
      "('Kris', 95)\n",
      "('John', 99)\n",
      "------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "results = db.execute(\"select name, score from grades1 order by score\")\n",
    "for row in results:\n",
    "    print(row)\n",
    "print(\"-\" * 60 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('James', 80)\n",
      "('Cathy', 85)\n",
      "('Gary', 90)\n",
      "('Kris', 95)\n",
      "('John', 99)\n"
     ]
    }
   ],
   "source": [
    "results = db.execute(\"select name, score from grades1 order by score\")\n",
    "for row in results:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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
}