Session 4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database\n",
    "------\n",
    "RDMS(Relational Database Management System"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Open Sourse**\n",
    "- MySQL(php and web application)\n",
    "- PostgreSQl(huge web applications)\n",
    "- SQLITE (android applications)\n",
    "\n",
    "** Proprietary**\n",
    "- MSSQL\n",
    "- Oracle\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://docs.python.org/3.6/library/sqlite3.html\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "#import the driver\n",
    "##psycopg2 for protsgeSQL\n",
    "# pymysql for MySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('example.sqlite3')\n",
    "#connecting to sqlite 3 and makes a new database file if file not already present"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cur = conn.cursor()\n",
    "#makes a file cursor we can make multiple cursors as well"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a7fb60a0>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('CREATE TABLE countries (id integer, name text, iso3 text)')\n",
    "#creating a new table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a7fb60a0>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM countries')\n",
    "#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()\n",
    "#fetching the whole"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a7fb60a0>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('INSERT INTO countries (id,name,iso3) VALUES (1, \"Nepal\", \"NEP\")')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a7fb60a0>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM countries')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Nepal', 'NEP')]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a7fb60a0>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = '''INSERT INTO countries (id,name,iso3) VALUES (?,?,?)'''\n",
    "\n",
    "cur.executemany(sql , [(2, 'India','INA'),\n",
    "                       (3, 'Bhutan','BHU'),\n",
    "                       (4, 'Afghanistan','AFG')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a7fb60a0>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM countries')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Nepal', 'NEP'),\n",
       " (2, 'India', 'INA'),\n",
       " (3, 'Bhutan', 'BHU'),\n",
       " (4, 'Afghanistan', 'AFG')]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a7fb60a0>"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = 'INSERT INTO countries (id,name,iso3) VALUES (4, \"PAKISTAN\", \"PAK\")'\n",
    "cur.execute(sql)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Nepal', 'NEP'),\n",
       " (2, 'India', 'INA'),\n",
       " (3, 'Bhutan', 'BHU'),\n",
       " (4, 'Afghanistan', 'AFG'),\n",
       " (1, 'PAKISTAN', 'PAK'),\n",
       " (4, 'PAKISTAN', 'PAK')]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM countries')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a7fb60a0>"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = 'UPDATE countries SET id = 5 WHERE iso3 = \"4\"'\n",
    "cur.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(6, 'Nepal', 'NEP'),\n",
       " (5, 'India', 'INA'),\n",
       " (4, 'Bhutan', 'BHU'),\n",
       " (3, 'Afghanistan', 'AFG'),\n",
       " (2, 'Srilanka', 'SRI'),\n",
       " (1, 'Pakistan', 'PAK')]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM countries')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sql = 'UPDATE countries '"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(6, 'Nepal', 'NEP'),\n",
       " (5, 'India', 'INA'),\n",
       " (4, 'Bhutan', 'BHU'),\n",
       " (3, 'Afghanistan', 'AFG')]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM countries WHERE id > 2 ')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(4, 'Bhutan', 'BHU'), (3, 'Afghanistan', 'AFG'), (1, 'Pakistan', 'PAK')]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT *FROM countries WHERE name LIKE \"%an\"')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(4, 'Bhutan', 'BHU'),\n",
       " (3, 'Afghanistan', 'AFG'),\n",
       " (2, 'Srilanka', 'SRI'),\n",
       " (1, 'Pakistan', 'PAK')]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT *FROM countries WHERE name LIKE \"%an%\"')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Pakistan', 'PAK')]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT *FROM countries WHERE name LIKE \"Pa%\"')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a7fb60a0>"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('DELETE FROM countries')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT *FROM countries')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sql = 'INSERT INTO countries (id,name,iso3) VALUES(?,?,?)'\n",
    "_id = 1\n",
    "with open('countries.txt','r') as datafile:\n",
    "    csvfile = csv.DictReader(datafile)\n",
    "    for row in csvfile:\n",
    "        if row ['Common Name'] and row['ISO 3166-1 3 Letter Code']:\n",
    "            cur.execute(sql, (_id, row['Common Name'], row['ISO 3166-1 3 Letter Code']))\n",
    "            _id +=1       \n",
    "        \n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a7fb60a0>"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('DELETE FROM country_list')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT *FROM country_list')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a7fb60a0>"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = '''CREATE TABLE\n",
    "country_list(id integer primary key autoincrement,\n",
    "country_name text not null,\n",
    "iso3 text not null unique)'''\n",
    "cur.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sql = 'INSERT INTO country_list (country_name,iso3) VALUES(?,?)'\n",
    "\n",
    "with open('countries.txt','r') as datafile:\n",
    "    csvfile = csv.DictReader(datafile)\n",
    "    for row in csvfile:\n",
    "        if row ['Formal Name'] and row['Formal Name']:\n",
    "            cur.execute(sql, (row['Formal Name'], row['Formal Name']))\n",
    "              \n",
    "        \n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(563, 'Islamic State of Afghanistan', 'Islamic State of Afghanistan'),\n",
       " (564, 'Republic of Albania', 'Republic of Albania'),\n",
       " (565,\n",
       "  \"People's Democratic Republic of Algeria\",\n",
       "  \"People's Democratic Republic of Algeria\"),\n",
       " (566, 'Principality of Andorra', 'Principality of Andorra'),\n",
       " (567, 'Republic of Angola', 'Republic of Angola'),\n",
       " (568, 'Argentine Republic', 'Argentine Republic'),\n",
       " (569, 'Republic of Armenia', 'Republic of Armenia'),\n",
       " (570, 'Commonwealth of Australia', 'Commonwealth of Australia'),\n",
       " (571, 'Republic of Austria', 'Republic of Austria'),\n",
       " (572, 'Republic of Azerbaijan', 'Republic of Azerbaijan'),\n",
       " (573, 'Commonwealth of The Bahamas', 'Commonwealth of The Bahamas'),\n",
       " (574, 'Kingdom of Bahrain', 'Kingdom of Bahrain'),\n",
       " (575, \"People's Republic of Bangladesh\", \"People's Republic of Bangladesh\"),\n",
       " (576, 'Republic of Belarus', 'Republic of Belarus'),\n",
       " (577, 'Kingdom of Belgium', 'Kingdom of Belgium'),\n",
       " (578, 'Republic of Benin', 'Republic of Benin'),\n",
       " (579, 'Kingdom of Bhutan', 'Kingdom of Bhutan'),\n",
       " (580, 'Republic of Bolivia', 'Republic of Bolivia'),\n",
       " (581, 'Republic of Botswana', 'Republic of Botswana'),\n",
       " (582, 'Federative Republic of Brazil', 'Federative Republic of Brazil'),\n",
       " (583, 'Negara Brunei Darussalam', 'Negara Brunei Darussalam'),\n",
       " (584, 'Republic of Bulgaria', 'Republic of Bulgaria'),\n",
       " (585, 'Republic of Burundi', 'Republic of Burundi'),\n",
       " (586, 'Kingdom of Cambodia', 'Kingdom of Cambodia'),\n",
       " (587, 'Republic of Cameroon', 'Republic of Cameroon'),\n",
       " (588, 'Republic of Cape Verde', 'Republic of Cape Verde'),\n",
       " (589, 'Republic of Chad', 'Republic of Chad'),\n",
       " (590, 'Republic of Chile', 'Republic of Chile'),\n",
       " (591, \"People's Republic of China\", \"People's Republic of China\"),\n",
       " (592, 'Republic of Colombia', 'Republic of Colombia'),\n",
       " (593, 'Union of Comoros', 'Union of Comoros'),\n",
       " (594, 'Democratic Republic of the Congo', 'Democratic Republic of the Congo'),\n",
       " (595, 'Republic of the Congo', 'Republic of the Congo'),\n",
       " (596, 'Republic of Costa Rica', 'Republic of Costa Rica'),\n",
       " (597, \"Republic of Cote d'Ivoire\", \"Republic of Cote d'Ivoire\"),\n",
       " (598, 'Republic of Croatia', 'Republic of Croatia'),\n",
       " (599, 'Republic of Cuba', 'Republic of Cuba'),\n",
       " (600, 'Republic of Cyprus', 'Republic of Cyprus'),\n",
       " (601, 'Kingdom of Denmark', 'Kingdom of Denmark'),\n",
       " (602, 'Republic of Djibouti', 'Republic of Djibouti'),\n",
       " (603, 'Commonwealth of Dominica', 'Commonwealth of Dominica'),\n",
       " (604, 'Republic of Ecuador', 'Republic of Ecuador'),\n",
       " (605, 'Arab Republic of Egypt', 'Arab Republic of Egypt'),\n",
       " (606, 'Republic of El Salvador', 'Republic of El Salvador'),\n",
       " (607, 'Republic of Equatorial Guinea', 'Republic of Equatorial Guinea'),\n",
       " (608, 'State of Eritrea', 'State of Eritrea'),\n",
       " (609, 'Republic of Estonia', 'Republic of Estonia'),\n",
       " (610,\n",
       "  'Federal Democratic Republic of Ethiopia',\n",
       "  'Federal Democratic Republic of Ethiopia'),\n",
       " (611, 'Republic of the Fiji Islands', 'Republic of the Fiji Islands'),\n",
       " (612, 'Republic of Finland', 'Republic of Finland'),\n",
       " (613, 'French Republic', 'French Republic'),\n",
       " (614, 'Gabonese Republic', 'Gabonese Republic'),\n",
       " (615, 'Republic of The Gambia', 'Republic of The Gambia'),\n",
       " (616, 'Republic of Georgia', 'Republic of Georgia'),\n",
       " (617, 'Federal Republic of Germany', 'Federal Republic of Germany'),\n",
       " (618, 'Republic of Ghana', 'Republic of Ghana'),\n",
       " (619, 'Hellenic Republic', 'Hellenic Republic'),\n",
       " (620, 'Republic of Guatemala', 'Republic of Guatemala'),\n",
       " (621, 'Republic of Guinea', 'Republic of Guinea'),\n",
       " (622, 'Republic of Guinea-Bissau', 'Republic of Guinea-Bissau'),\n",
       " (623, 'Co-operative Republic of Guyana', 'Co-operative Republic of Guyana'),\n",
       " (624, 'Republic of Haiti', 'Republic of Haiti'),\n",
       " (625, 'Republic of Honduras', 'Republic of Honduras'),\n",
       " (626, 'Republic of Hungary', 'Republic of Hungary'),\n",
       " (627, 'Republic of Iceland', 'Republic of Iceland'),\n",
       " (628, 'Republic of India', 'Republic of India'),\n",
       " (629, 'Republic of Indonesia', 'Republic of Indonesia'),\n",
       " (630, 'Islamic Republic of Iran', 'Islamic Republic of Iran'),\n",
       " (631, 'Republic of Iraq', 'Republic of Iraq'),\n",
       " (632, 'State of Israel', 'State of Israel'),\n",
       " (633, 'Italian Republic', 'Italian Republic'),\n",
       " (634, 'Hashemite Kingdom of Jordan', 'Hashemite Kingdom of Jordan'),\n",
       " (635, 'Republic of Kazakhstan', 'Republic of Kazakhstan'),\n",
       " (636, 'Republic of Kenya', 'Republic of Kenya'),\n",
       " (637, 'Republic of Kiribati', 'Republic of Kiribati'),\n",
       " (638,\n",
       "  \"Democratic People's Republic of Korea\",\n",
       "  \"Democratic People's Republic of Korea\"),\n",
       " (639, 'Republic of Korea', 'Republic of Korea'),\n",
       " (640, 'State of Kuwait', 'State of Kuwait'),\n",
       " (641, 'Kyrgyz Republic', 'Kyrgyz Republic'),\n",
       " (642, \"Lao People's Democratic Republic\", \"Lao People's Democratic Republic\"),\n",
       " (643, 'Republic of Latvia', 'Republic of Latvia'),\n",
       " (644, 'Lebanese Republic', 'Lebanese Republic'),\n",
       " (645, 'Kingdom of Lesotho', 'Kingdom of Lesotho'),\n",
       " (646, 'Republic of Liberia', 'Republic of Liberia'),\n",
       " (647,\n",
       "  \"Great Socialist People's Libyan Arab Jamahiriya\",\n",
       "  \"Great Socialist People's Libyan Arab Jamahiriya\"),\n",
       " (648, 'Principality of Liechtenstein', 'Principality of Liechtenstein'),\n",
       " (649, 'Republic of Lithuania', 'Republic of Lithuania'),\n",
       " (650, 'Grand Duchy of Luxembourg', 'Grand Duchy of Luxembourg'),\n",
       " (651, 'Republic of Macedonia', 'Republic of Macedonia'),\n",
       " (652, 'Republic of Madagascar', 'Republic of Madagascar'),\n",
       " (653, 'Republic of Malawi', 'Republic of Malawi'),\n",
       " (654, 'Republic of Maldives', 'Republic of Maldives'),\n",
       " (655, 'Republic of Mali', 'Republic of Mali'),\n",
       " (656, 'Republic of Malta', 'Republic of Malta'),\n",
       " (657, 'Republic of the Marshall Islands', 'Republic of the Marshall Islands'),\n",
       " (658, 'Islamic Republic of Mauritania', 'Islamic Republic of Mauritania'),\n",
       " (659, 'Republic of Mauritius', 'Republic of Mauritius'),\n",
       " (660, 'United Mexican States', 'United Mexican States'),\n",
       " (661, 'Federated States of Micronesia', 'Federated States of Micronesia'),\n",
       " (662, 'Republic of Moldova', 'Republic of Moldova'),\n",
       " (663, 'Principality of Monaco', 'Principality of Monaco'),\n",
       " (664, 'Republic of Montenegro', 'Republic of Montenegro'),\n",
       " (665, 'Kingdom of Morocco', 'Kingdom of Morocco'),\n",
       " (666, 'Republic of Mozambique', 'Republic of Mozambique'),\n",
       " (667, 'Union of Myanmar', 'Union of Myanmar'),\n",
       " (668, 'Republic of Namibia', 'Republic of Namibia'),\n",
       " (669, 'Republic of Nauru', 'Republic of Nauru'),\n",
       " (670, 'Kingdom of the Netherlands', 'Kingdom of the Netherlands'),\n",
       " (671, 'Republic of Nicaragua', 'Republic of Nicaragua'),\n",
       " (672, 'Republic of Niger', 'Republic of Niger'),\n",
       " (673, 'Federal Republic of Nigeria', 'Federal Republic of Nigeria'),\n",
       " (674, 'Kingdom of Norway', 'Kingdom of Norway'),\n",
       " (675, 'Sultanate of Oman', 'Sultanate of Oman'),\n",
       " (676, 'Islamic Republic of Pakistan', 'Islamic Republic of Pakistan'),\n",
       " (677, 'Republic of Palau', 'Republic of Palau'),\n",
       " (678, 'Republic of Panama', 'Republic of Panama'),\n",
       " (679,\n",
       "  'Independent State of Papua New Guinea',\n",
       "  'Independent State of Papua New Guinea'),\n",
       " (680, 'Republic of Paraguay', 'Republic of Paraguay'),\n",
       " (681, 'Republic of Peru', 'Republic of Peru'),\n",
       " (682, 'Republic of the Philippines', 'Republic of the Philippines'),\n",
       " (683, 'Republic of Poland', 'Republic of Poland'),\n",
       " (684, 'Portuguese Republic', 'Portuguese Republic'),\n",
       " (685, 'State of Qatar', 'State of Qatar'),\n",
       " (686, 'Russian Federation', 'Russian Federation'),\n",
       " (687, 'Republic of Rwanda', 'Republic of Rwanda'),\n",
       " (688,\n",
       "  'Federation of Saint Kitts and Nevis',\n",
       "  'Federation of Saint Kitts and Nevis'),\n",
       " (689, 'Independent State of Samoa', 'Independent State of Samoa'),\n",
       " (690, 'Republic of San Marino', 'Republic of San Marino'),\n",
       " (691,\n",
       "  'Democratic Republic of Sao Tome and Principe',\n",
       "  'Democratic Republic of Sao Tome and Principe'),\n",
       " (692, 'Kingdom of Saudi Arabia', 'Kingdom of Saudi Arabia'),\n",
       " (693, 'Republic of Senegal', 'Republic of Senegal'),\n",
       " (694, 'Republic of Serbia', 'Republic of Serbia'),\n",
       " (695, 'Republic of Seychelles', 'Republic of Seychelles'),\n",
       " (696, 'Republic of Sierra Leone', 'Republic of Sierra Leone'),\n",
       " (697, 'Republic of Singapore', 'Republic of Singapore'),\n",
       " (698, 'Slovak Republic', 'Slovak Republic'),\n",
       " (699, 'Republic of Slovenia', 'Republic of Slovenia'),\n",
       " (700, 'Republic of South Africa', 'Republic of South Africa'),\n",
       " (701, 'Kingdom of Spain', 'Kingdom of Spain'),\n",
       " (702,\n",
       "  'Democratic Socialist Republic of Sri Lanka',\n",
       "  'Democratic Socialist Republic of Sri Lanka'),\n",
       " (703, 'Republic of the Sudan', 'Republic of the Sudan'),\n",
       " (704, 'Republic of Suriname', 'Republic of Suriname'),\n",
       " (705, 'Kingdom of Swaziland', 'Kingdom of Swaziland'),\n",
       " (706, 'Kingdom of Sweden', 'Kingdom of Sweden'),\n",
       " (707, 'Swiss Confederation', 'Swiss Confederation'),\n",
       " (708, 'Syrian Arab Republic', 'Syrian Arab Republic'),\n",
       " (709, 'Republic of Tajikistan', 'Republic of Tajikistan'),\n",
       " (710, 'United Republic of Tanzania', 'United Republic of Tanzania'),\n",
       " (711, 'Kingdom of Thailand', 'Kingdom of Thailand'),\n",
       " (712,\n",
       "  'Democratic Republic of Timor-Leste',\n",
       "  'Democratic Republic of Timor-Leste'),\n",
       " (713, 'Togolese Republic', 'Togolese Republic'),\n",
       " (714, 'Kingdom of Tonga', 'Kingdom of Tonga'),\n",
       " (715, 'Republic of Trinidad and Tobago', 'Republic of Trinidad and Tobago'),\n",
       " (716, 'Tunisian Republic', 'Tunisian Republic'),\n",
       " (717, 'Republic of Turkey', 'Republic of Turkey'),\n",
       " (718, 'Republic of Uganda', 'Republic of Uganda'),\n",
       " (719, 'United Arab Emirates', 'United Arab Emirates'),\n",
       " (720,\n",
       "  'United Kingdom of Great Britain and Northern Ireland',\n",
       "  'United Kingdom of Great Britain and Northern Ireland'),\n",
       " (721, 'United States of America', 'United States of America'),\n",
       " (722, 'Oriental Republic of Uruguay', 'Oriental Republic of Uruguay'),\n",
       " (723, 'Republic of Uzbekistan', 'Republic of Uzbekistan'),\n",
       " (724, 'Republic of Vanuatu', 'Republic of Vanuatu'),\n",
       " (725, 'State of the Vatican City', 'State of the Vatican City'),\n",
       " (726, 'Bolivarian Republic of Venezuela', 'Bolivarian Republic of Venezuela'),\n",
       " (727, 'Socialist Republic of Vietnam', 'Socialist Republic of Vietnam'),\n",
       " (728, 'Republic of Yemen', 'Republic of Yemen'),\n",
       " (729, 'Republic of Zambia', 'Republic of Zambia'),\n",
       " (730, 'Republic of Zimbabwe', 'Republic of Zimbabwe'),\n",
       " (731, 'Republic of Abkhazia', 'Republic of Abkhazia'),\n",
       " (732, 'Republic of China', 'Republic of China'),\n",
       " (733, 'Nagorno-Karabakh Republic', 'Nagorno-Karabakh Republic'),\n",
       " (734,\n",
       "  'Turkish Republic of Northern Cyprus',\n",
       "  'Turkish Republic of Northern Cyprus'),\n",
       " (735,\n",
       "  'Pridnestrovian Moldavian Republic',\n",
       "  'Pridnestrovian Moldavian Republic'),\n",
       " (736, 'Republic of Somaliland', 'Republic of Somaliland'),\n",
       " (737, 'Republic of South Ossetia', 'Republic of South Ossetia'),\n",
       " (738,\n",
       "  'Territory of Ashmore and Cartier Islands',\n",
       "  'Territory of Ashmore and Cartier Islands'),\n",
       " (739, 'Territory of Christmas Island', 'Territory of Christmas Island'),\n",
       " (740,\n",
       "  'Territory of Cocos (Keeling) Islands',\n",
       "  'Territory of Cocos (Keeling) Islands'),\n",
       " (741, 'Coral Sea Islands Territory', 'Coral Sea Islands Territory'),\n",
       " (742,\n",
       "  'Territory of Heard Island and McDonald Islands',\n",
       "  'Territory of Heard Island and McDonald Islands'),\n",
       " (743, 'Territory of Norfolk Island', 'Territory of Norfolk Island'),\n",
       " (744,\n",
       "  'Overseas Country of French Polynesia',\n",
       "  'Overseas Country of French Polynesia'),\n",
       " (745,\n",
       "  'Departmental Collectivity of Mayotte',\n",
       "  'Departmental Collectivity of Mayotte'),\n",
       " (746, 'Collectivity of Saint Barthelemy', 'Collectivity of Saint Barthelemy'),\n",
       " (747, 'Collectivity of Saint Martin', 'Collectivity of Saint Martin'),\n",
       " (748,\n",
       "  'Territorial Collectivity of Saint Pierre and Miquelon',\n",
       "  'Territorial Collectivity of Saint Pierre and Miquelon'),\n",
       " (749,\n",
       "  'Collectivity of the Wallis and Futuna Islands',\n",
       "  'Collectivity of the Wallis and Futuna Islands'),\n",
       " (750,\n",
       "  'Territory of the French Southern and Antarctic Lands',\n",
       "  'Territory of the French Southern and Antarctic Lands'),\n",
       " (751, 'Bailiwick of Guernsey', 'Bailiwick of Guernsey'),\n",
       " (752, 'Bailiwick of Jersey', 'Bailiwick of Jersey'),\n",
       " (753,\n",
       "  'Commonwealth of The Northern Mariana Islands',\n",
       "  'Commonwealth of The Northern Mariana Islands'),\n",
       " (754, 'Commonwealth of Puerto Rico', 'Commonwealth of Puerto Rico'),\n",
       " (755, 'Territory of American Samoa', 'Territory of American Samoa'),\n",
       " (756, 'Territory of Guam', 'Territory of Guam'),\n",
       " (757, 'United States Virgin Islands', 'United States Virgin Islands'),\n",
       " (758,\n",
       "  'Hong Kong Special Administrative Region',\n",
       "  'Hong Kong Special Administrative Region'),\n",
       " (759,\n",
       "  'Macau Special Administrative Region',\n",
       "  'Macau Special Administrative Region'),\n",
       " (760, 'Overseas Region of Guiana', 'Overseas Region of Guiana'),\n",
       " (761, 'Overseas Region of Guadeloupe', 'Overseas Region of Guadeloupe'),\n",
       " (762, 'Overseas Region of Martinique', 'Overseas Region of Martinique'),\n",
       " (763, 'Overseas Region of Reunion', 'Overseas Region of Reunion')]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT *FROM country_list')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data base for library management system\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "connn = sqlite3.connect(\"Library Management System.txt\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "curs = connn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "table books already exists",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-91-3b8cfdd5e857>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0misbn\u001b[0m \u001b[0minteger\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mnull\u001b[0m \u001b[0munique\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m book_name text)'''\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mcurs\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[0m",
      "\u001b[1;31mOperationalError\u001b[0m: table books already exists"
     ]
    }
   ],
   "source": [
    "sql = '''CREATE TABLE \n",
    "books(book_id text,\n",
    "isbn integer not null unique,\n",
    "book_name text)'''\n",
    "curs.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a32d62d0>"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql2 = '''CREATE TABLE\n",
    "Student( roll number integer not null unique,\n",
    "name text not null,\n",
    "faculty text)'''\n",
    "curs.execute(sql2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0xf1a32d62d0>"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql3 = '''CREATE TABLE\n",
    "Teacher(name text not null,\n",
    "faculty text)\n",
    "'''\n",
    "curs.execute(sql3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "\n",
    "class Temperature:\n",
    "    temp = 10\n",
    "    \n",
    "    def __intit__(self,temp):\n",
    "        self.temp = temp\n",
    "    \n",
    "    def get_temperature(self):\n",
    "        return self.temp\n",
    "    \n",
    "    @classmethod\n",
    "    def get_class_temperature(cls):\n",
    "        return cls.temp\n",
    "        \n",
    "    @staticmethod\n",
    "    def get_added_method(inst):\n",
    "        return inst.temp +10\n",
    "\n",
    "\n",
    "t = Temperature (20)\n",
    "#t = Temperature() --> Temperature.__init__(t,20)\n",
    "t.get_temperature() #20\n",
    "#Temperature.get_temperature(t)\n",
    "Temperature.get_class_temperature() #10\n",
    "#Temperature.get_class_temperature(Temperature) \n",
    "Temperature.get_added_temperature(t) #30\n",
    "#Temperature.get_added_temperature(t)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}