5. Sqlite.ipynb

{
 "metadata": {
  "name": ""
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Create or Connect a adatabase\n",
      "import sqlite3\n",
      "conn = sqlite3.connect('test.db')\n",
      "print \"Opened Database Successfully!\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Opened Database Successfully!\n"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Create table\n",
      "conn.execute('''\n",
      "             CREATE TABLE USER\n",
      "             (NAME VARCHAR NOT NULL,\n",
      "             USERNAME VARCHAR PRIMARY KEY NOT NULL,\n",
      "              PASSWORD VARCHAR NOT NULL);\n",
      "             ''')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 2,
       "text": [
        "<sqlite3.Cursor at 0x7f241821a110>"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#insert data\n",
      "conn.execute('''\n",
      "            INSERT INTO USER (NAME,USERNAME,PASSWORD) VALUES('Prashant','prashant2018','1234');\n",
      "            ''')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 3,
       "text": [
        "<sqlite3.Cursor at 0x7f241821a2d0>"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "conn.execute('''\n",
      "            INSERT INTO USER (NAME,USERNAME,PASSWORD) VALUES('Adarsh','adarsh123','abcde');\n",
      "            ''')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 4,
       "text": [
        "<sqlite3.Cursor at 0x7f241821a260>"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# save the db\n",
      "conn.commit()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# close the db\n",
      "conn.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#select operation\n",
      "# connect to the db first\n",
      "\n",
      "cursor = conn.execute('''\n",
      "            SELECT NAME,USERNAME,PASSWORD FROM USER;\n",
      "            ''')\n",
      "type(cursor)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 9,
       "text": [
        "sqlite3.Cursor"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#iterate the cursor\n",
      "for row in cursor:\n",
      "    print \"Name : \",row[0]\n",
      "    print \"UserName : \",row[1]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Name :  Prashant\n",
        "UserName :  prashant2018\n",
        "Name :  Adarsh\n",
        "UserName :  adarsh123\n"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "conn.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 11
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}