py4e_ch15_databases.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Ch 15: Databases and SQL\n",
    "\n",
    "Chapter 15 of Py4E gets into databases and the first sections are a good place to start."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 15.1 What is a database? (p. 185)\n",
    "I do like Charles Severance's (the Py4E author) idea that, at its foundation\n",
    ">\"a database is a file that is organized for storing data.\" \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "Charles goes on to liken databases to Python dictionaries, except that they **store their data on disk** rather than in a data structure in memory. This allows a database to store more data.\n",
    "\n",
    "Databases are designed not only to store data, but to access that data--and access it quickly! Indexing the stored data is one method used by database systems to speed up data access.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Database Systems\n",
    "### Also called Relational Database Managements Systems (RDMS)\n",
    " * Usually server-based\n",
    " * Client-Server model\n",
    " * Performance and Scalability\n",
    " * ACID (Atomicity, Consistency, Isolation, Durability)\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "![ACID Image](https://s3-us-west-1.amazonaws.com/morpheus-staging/system/spud_media/332/original/acidoverwiew.png?1422558433)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "\n",
    "\n",
    "Databases can range from the example we'll use below, which requires nothing more than the Python install we've been using all along, to supercomputer sized systems used by large corporations. More than just a file, databases are managed by their own software, and often that software runs on a server.\n",
    "\n",
    "While, we will use SQLite for this class, which is designed to be light-weight and can be embedded in other applications (like your web browser as Py4E points out), the same concepts scale up to larger databases and server-based systems.\n",
    "\n",
    "Why use a server? For one, as databases grow, their files grow. Also more compute power is needed to quickly access that data. So, for larger databases, larger computers, typically servers are needed--*this can also scale to clusters of computers*. Also, larger databases tend to be used by more than one person at a time. Server-based databases are typically setup with a **client-server model**, where the database is running on a server that users do not actively log into. Users connect to the server using the client. This allows the server to be dedicated to processing client queries and allows for the server to be setup to manage concurrent use from multiple users. If multiple users try to change the same file on a computer at the same time, file corruption can happen. Using the client-server model prevents this.  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "\n",
    "### Common Database Systems\n",
    "\n",
    " * [SQLite](https://sqlite.org/index.html)\n",
    "     * Free\n",
    "     * Small\n",
    "     * Not server-based\n",
    " * [MySQL](https://www.mysql.com/)\n",
    "     * Free (Community Server)\n",
    "     * Relatively common and robust\n",
    " * [PostgreSQL](https://www.postgresql.org/)\n",
    "     * Free\n",
    "     * Geospatial extensions\n",
    " * [Oracle](https://www.oracle.com/index.html)\n",
    "     * Commercial\n",
    "     * UF Licensed\n",
    " \n",
    "         "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    " ### Desktop databases software\n",
    " * FileMaker\n",
    " * Microsoft Access\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### NOSQL (Non SQL, or Not Only SQL) Databases\n",
    " * [MongoDB](https://www.mongodb.com/)\n",
    "    * A document-based DB\n",
    "    * MongoDB stores data in flexible, JSON-like documents, meaning fields can vary from document to document and data structure can be changed over time\n",
    " * [Cassandra](http://cassandra.apache.org/)\n",
    "     * A wide column DB\n",
    " * [neo4j](https://neo4j.com/)\n",
    "     * A graph-based DB\n",
    "     ![neo4j graph representation](https://s3.amazonaws.com/dev.assets.neo4j.com/wp-content/uploads/20170731095054/Property-Graph-Concepts-Simple.svg)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Tables\n",
    "\n",
    "### Revisiting [Browman & Woo (2018)](https://www.tandfonline.com/doi/full/10.1080/00031305.2017.1375989)\n",
    "\n",
    "![Figure 2 of Browman & Woo 2018, showinf examples of spreadsheets that violate best practices](https://www.tandfonline.com/na101/home/literatum/publisher/tandf/journals/content/utas20/2018/utas20.v072.i01/00031305.2017.1375989/20180424-01/images/large/utas_a_1375989_f0002_b.jpeg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "We've worked with tables a lot already in class. Every problem set has had a table of data, we've parsed tables reading line-by-line, we've loaded tables into Pandas dataframes, and we had a reading ([Browman and Woo 2018)](https://www.tandfonline.com/doi/full/10.1080/00031305.2017.1375989)) that made recommendations for how to best store data in tables (spreadsheets are essentially tables of data).\n",
    "\n",
    "Databases extend tables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 15.4 Creating a database table (p. 187)\n",
    "\n",
    "Python has SQLite built in, you just need to import it.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('music.sqlite')\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute('DROP TABLE IF EXISTS Tracks')\n",
    "cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)') \n",
    "\n",
    "conn.close()\n",
    "\n",
    "# Code: http://www.py4e.com/code3/db1.py\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## A lot going on here...\n",
    "\n",
    "```python\n",
    "conn = sqlite3.connect('music.sqlite')\n",
    "cur = conn.cursor()\n",
    "```\n",
    " * Makes a connection, called `conn` in this case, to our database\n",
    " * Connection, because databases are often on a server\n",
    "     * Note that we now have a file called music.sqlite in the current folder\n",
    " * Call the `cursor()` method on the connection--similar to `open()` on a file\n",
    " * `cur` is similar to a file handle or web socket"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Py4E Figure 15.2: A database cursor\n",
    "![Py4E Figure 15.2: A database cursor](https://www.py4e.com/images/cursor.svg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "```python\n",
    "cur.execute('DROP TABLE IF EXISTS Tracks')\n",
    "cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)') \n",
    "```\n",
    "\n",
    " * Use the `cur` cursor to execute commands on the connected DB\n",
    " * `DROP` (delete) the table \"Tracks\" if is exists\n",
    " * `CREATE` a table \"Tracks\" with two columns:\n",
    "     * \"title\", a text column\n",
    "     * \"plays\", an integer column\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data types\n",
    "\n",
    " * Databases typically are very strict about types--the LinkedIn Learning introduction talked about how careful selection of data types and size limits allows databases to be more efficient.\n",
    " * SQLite, our DBMS, is an exception:\n",
    " >SQLite is \"typeless\". This means that you can store any kind of data you want in any column of any table, regardless of the declared datatype of that column...This behavior is a feature, not a bug. ... The strong typing system found in most other SQL engines and codified in the SQL language spec is a misfeature. [SQLite Datatypes Page](https://www.sqlite.org/datatypes.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Some MySQL data types\n",
    "\n",
    "|Data Type| Description|\n",
    "|-------|---------------|\n",
    "|INTEGER|An integer|\n",
    "|FLOAT| Real numbers, including scientific notation|\n",
    "|DATE|YYYY-MM-DD|\n",
    "|DATETIME|YYYY-MM-DD HH:MM:SS|\n",
    "|TEXT| Strings up to 65535 characters|\n",
    "|TINYTEXT|Strings up to 255 characters|\n",
    "|BLOB|Binary Large Object (e.g., images)|"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Let's add data to our music database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Tracks:\n('Thunderstruck', 20)\n('My Way', 15)\n"
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('music.sqlite')\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute('INSERT INTO Tracks (title, plays) VALUES (?, ?)',\n",
    "    ('Thunderstruck', 20))\n",
    "cur.execute('INSERT INTO Tracks (title, plays) VALUES (?, ?)',\n",
    "    ('My Way', 15))\n",
    "conn.commit()\n",
    "\n",
    "print('Tracks:')\n",
    "cur.execute('SELECT title, plays FROM Tracks') \n",
    "for row in cur:\n",
    "    print(row)\n",
    "\n",
    "cur.execute('DELETE FROM Tracks WHERE plays < 100') \n",
    "    \n",
    "cur.close()\n",
    "# Code: http://www.py4e.com/code3/db2.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## This looks complicated\n",
    "\n",
    "```python\n",
    "cur.execute('INSERT INTO Tracks (title, plays) VALUES (?, ?)',\n",
    "    ('Thunderstruck', 20))\n",
    "```\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "```python\n",
    "conn.commit()\n",
    "```\n",
    "From [StackOverflow](https://stackoverflow.com/questions/2847999/why-the-need-to-commit-explicitly-when-doing-an-update)\n",
    "> The DB-API spec requires that connecting to the database begins a new transaction, by default. You must `commit` to confirm any changes you make, or `rollback` to discard them.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "```python\n",
    "cur.execute('SELECT title, plays FROM Tracks') \n",
    "for row in cur:\n",
    "    print(row)\n",
    "```\n",
    "\n",
    "> ('Thunderstruck', 20)\n",
    "\n",
    ">  ('My Way', 15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "```python\n",
    "cur.execute('DELETE FROM Tracks WHERE plays < 100') \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 15.5 Structured Query Language summary (p. 189)\n",
    "\n",
    "* Structured Query Language was developed to standardize database interactions among vendors.\n",
    "  * But, there are differences\n",
    "* Upper case by convention, but not needed--mostly for readability, which **is* important!\n",
    "* We will cover some basics as we go, but...\n",
    "  * But we will largely use a Python module that converts Python methods to SQL for us\n",
    "\n",
    "After Sections 15.5, Py4E continues using the `sqlite3` module for most things, writing SQL in raw text. While this works, one of the issues is that if your project grows, or you move and decide to change the DBMS, those small differences among SQL implemenations on different DBMSs often breaks your code--the code here woks for sqlite, but may not work for MySQL or Oracle.\n",
    "\n",
    "Another issue is that you are learning another language--SQL. We are still learning Python and the \"Pythonic\" way of doing things. Wouldn't it be great if we could abstract the details of SQL and write Python code to do our database actions?\n",
    "\n",
    "There is (or maybe are...)! SQLAlchemy is this tool--a module that abstracts SQL managment, uses a unified language to translate to a large number of common 'dialects' for each DBMS, and more.\n",
    "\n",
    "So...we will leave Py4E for now. It is probably worth looking through and reading some of the text, not focusing on the code. Look at the figures and think about how these relate to the figures in the LinkedIn Learning introduction. All of this is similar. And we will do similar things too, but with different code.\n"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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.2-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}