tutorial_sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Crash Course databases and python (and some xml)\n",
    "\n",
    "Parts of the examples are from http://www.dr-chuck.com/)\n",
    "\n",
    "Structured Query Language(SQL)  is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database. SQL uses certain commands like Create, Drop, Insert etc. to carry out the required tasks.\n",
    "\n",
    "These SQL commands are mainly categorized into four categories as"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Data definition language (DDL): `CREATE`, `DROP`, `ALTER`, `ADD`, `CHECK`\n",
    "- Data Manipulation language (DML): `SELECT`, `INSERT INTO`, `UPDATE`, `DELETE`, `SET`\n",
    "- Data Authorization language (DAL): `GRANT`, `REVOKE`\n",
    "- Data control language (DCL):`CREATE INDEX`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See also https://www.w3schools.com/SQL/\n",
    "\n",
    "Talking to a database is like making a phonecall. Before we can talk to the database we need to make a connection first. We can connect to a database with the Python Database API. Access to the database is made available through connection objects. The constructor is:\n",
    "\n",
    "    connect( parameters... )\n",
    "    \n",
    "The parameters are database system dependable. In the example below we connect to an sqlite database. \n",
    "Sqlite is a free downloadable database system. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect('track.sqlite') #opens or create database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The connection object has several methods: `.close()`, `.commit`, `.rollback`, `.cursor()` where the `cursor()` method returns a new cursor object. This object is mostly used to give 'instructions' to the database. Simple instructions we can give with the `conn.cursor().execute`, multiline scripts we can execute with `conn.cursor().executescript` In the example below a cursor is created and a sql command `DROP TABLE` is given. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7fe555c935e0>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur = conn.cursor()\n",
    "cur.execute('DROP TABLE IF EXISTS Artist;')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the data definition language we can create tables structures. Good practice is to write a create script for all the tables and executing the whole script with the `conn.cursor().executescript` method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7fe555c935e0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.executescript('''\n",
    "DROP TABLE IF EXISTS Artist;\n",
    "DROP TABLE IF EXISTS Album;\n",
    "DROP TABLE IF EXISTS Track;\n",
    "DROP TABLE IF EXISTS Genre;\n",
    "\n",
    "\n",
    "CREATE TABLE Artist (\n",
    "    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n",
    "    name    TEXT UNIQUE\n",
    ");\n",
    "\n",
    "CREATE TABLE Genre (\n",
    "    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n",
    "    name    TEXT UNIQUE\n",
    ");\n",
    "\n",
    "CREATE TABLE Album (\n",
    "    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n",
    "    artist_id  INTEGER,\n",
    "    title   TEXT UNIQUE\n",
    ");\n",
    "\n",
    "CREATE TABLE Track (\n",
    "    id  INTEGER NOT NULL PRIMARY KEY\n",
    "        AUTOINCREMENT UNIQUE,\n",
    "    title TEXT  UNIQUE,\n",
    "    album_id  INTEGER,\n",
    "    genre_id  INTEGER,\n",
    "    len INTEGER, \n",
    "    rating INTEGER, \n",
    "    count INTEGER\n",
    ");\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the data manipulation language we can fill the table structures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7fe555c935e0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artist = 'Drake'\n",
    "cur.execute('INSERT OR IGNORE INTO Artist (name) VALUES ( ? )', ( artist, ) )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And with the data manipulation language we can retrieve information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'Drake')]\n"
     ]
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM Artist')\n",
    "print(cur.fetchall())\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To retrieve information from multiple tables into one view we should make `JOIN` statements"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pandas and SQL\n",
    "\n",
    "Pandas has a `read_sql_query` function that can read tables from querys. It needs the SQL query in the data manipulation language and a connection. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Drake</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id   name\n",
       "0   1  Drake"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_sql_query('SELECT * FROM Artist', conn)\n",
    "print(type(df))\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we are finised with the database instructions we can close the connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieve from XML\n",
    "\n",
    "XML and HTML are structured simular but XML is more general. An example of XML you find below. XML is another common structured data format supporting hierarchal nested data with metadata."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Name: Fenna\n",
      "Attr: yes\n"
     ]
    }
   ],
   "source": [
    "import xml.etree.ElementTree as ET\n",
    "\n",
    "data = '''\n",
    "<person>\n",
    "  <name>Fenna</name>\n",
    "  <phone type=\"intl\">\n",
    "    +31646080034\n",
    "  </phone>\n",
    "  <email hide=\"yes\" />\n",
    "</person>'''\n",
    "\n",
    "tree = ET.fromstring(data)\n",
    "print('Name:', tree.find('name').text)\n",
    "print('Attr:', tree.find('email').get('hide'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unfortunately Pandas package does not have a function to import data from XML so we need to use standard XML package and do some extra work to convert the data to Pandas DataFrames. Below you find an XML file that has track information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dict count: 404\n",
      "2015 11 04 16 00 Closing Keynote The Next Generation of Teaching and Learning Tools None None\n"
     ]
    }
   ],
   "source": [
    "fname = '../data/Library.xml'\n",
    "\n",
    "# <key>Track ID</key><integer>369</integer>\n",
    "# <key>Name</key><string>Another One Bites The Dust</string>\n",
    "# <key>Artist</key><string>Queen</string>\n",
    "def lookup(d, key):\n",
    "    found = False\n",
    "    for child in d:\n",
    "        if found : return child.text\n",
    "        if child.tag == 'key' and child.text == key :\n",
    "            found = True\n",
    "    return None\n",
    "\n",
    "stuff = ET.parse(fname)\n",
    "all = stuff.findall('dict/dict/dict') # find third dict which is track\n",
    "print('Dict count:', len(all))\n",
    "for entry in all:\n",
    "    if ( lookup(entry, 'Track ID') is not None ) : \n",
    "        name = lookup(entry, 'Name') #title of track\n",
    "        artist = lookup(entry, 'Artist') # name of artist\n",
    "        album = lookup(entry, 'Album') #title of album\n",
    "print(name, artist, album)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Look into the Library.xml file. All the information to fill the track database is in the xml file. Retrieve the information from the xml file and fill all the table structures. Hint: if you need enter an artist in the artist table and you want the id to use as a foreign key to the track table you can use the following code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('SELECT id FROM Artist WHERE name = ? ', (artist, ))\n",
    "artist_id = cur.fetchone()[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check with https://sqlitebrowser.org/ if you succeeded. Make sure that you commit to confirm the transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 2\n",
    "\n",
    "In the following url you find an xml file with plant information. Try to fetch the data and retrieve the information. Put it in a pandas dataframe. Remember to decode the data if you retrieve it directly from the web."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://bioinf.nl/~fennaf/DSLS/plants.xml'"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}