classes/2. OOP and Python Data Model/Extra - Persistence.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Persistence"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, our focus is on **persistent data** — the kind that outlives a program that creates it. \n",
    "\n",
    "That’s not true by default for objects a script constructs, of course; things like lists, dictionaries, and even class instance objects live in your computer’s memory and are lost as soon as the script ends. \n",
    "\n",
    "To make data live longer, we need to do something special. \n",
    "\n",
    "In Python there are (at least) five *traditional* ways to save information in between program executions:\n",
    "\n",
    "* *Flat files*\n",
    "    * Text and bytes stored directly on your computer\n",
    "    \n",
    "* *DBM keyed files*\n",
    "    * Keyed access to strings stored in dictionary-like files\n",
    "    \n",
    "* *Pickled objects*\n",
    "    * Pickled objects \n",
    "    \n",
    "* *Shelve files*\n",
    "    * Pickled Python objects saved in DBM keyed files\n",
    "\n",
    "* *SQL relational databases (RDBMSs)*\n",
    "    * Table-based storage that supports SQL queries (SQLite, MySQL, PostGreSQL, etc.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL Database Interface"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For programs that can benefit from the power of SQL, Python also broadly supports relational database management systems (**RDBMSs**).\n",
    "\n",
    "The databases we’ll meet in this notebook, though, are structured and processed in very different ways:\n",
    "\n",
    "* They store data in related tables of columns (rather than in persistent dictionaries of arbitrarily structured persistent Python objects).\n",
    "\n",
    "* They support the SQL query language for accessing data and exploiting relation- ships among it (instead of Python object traversals).\n",
    "\n",
    "For some applications, the end result can be a potent combination. Moreover, some SQL-based database systems provide industrial-strength persistence support for enterprise-level data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python Modules"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Today, there are freely available interfaces that let Python scripts utilize all common relational database systems, both free and commercial: MySQL, Oracle, Sybase, Informix, InterBase, PostgreSQL (Postgres), SQLite, ODBC, and more.\n",
    "\n",
    "In addition, the Python community has defined a **database API** (*aka* `DB API`) specification that works portably with a variety of underlying database packages. \n",
    "\n",
    "Scripts written for this API can be migrated to different database vendor packages, with minimal or no source code changes.\n",
    "\n",
    "As of Python 2.5, Python itself includes built-in support for the SQLite relational database system as part of its standard library. \n",
    "\n",
    "Because this system supports the portable database API, it serves as a tool for both program storage and prototyping—systems developed with SQLite work largely unchanged when a more feature-rich database such as MySQL or Oracle is deployed.\n",
    "\n",
    "Moreover, the popular `SQLObject` and `SQLAlchemy` third-party systems both provide an **Object Relational Mapper (ORM)**, which grafts an object interface onto your database, in which tables are modeled by as Python classes, rows by instances of those classes, and columns by instance attributes. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python SQL Interface"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **Python Database API** (DB API) specification defines an interface for communicating with underlying database systems from Python scripts. \n",
    "\n",
    "Vendor-specific database interfaces for Python may or may not conform to this API completely, but all database extensions for Python in common use are minor variations on a theme. \n",
    "\n",
    "Under the database API, SQL databases in Python are grounded on three core concepts:\n",
    "\n",
    "* **Connection Objects**:\n",
    "    > Represent a connection to a database, are the interface to rollback and commit operations, provide package implementation details, and generate cursor objects.\n",
    "    \n",
    "* **Cursor Objects**:\n",
    "    > Represent an SQL statement submitted as a string and can be used to access and step through SQL statement results.\n",
    "    \n",
    "* **Query results of SQL select statements**:\n",
    "    > Are returned to scripts as Python sequences of sequences (e.g., a *list of tuples*), representing database tables of rows. Within these row sequences, column field values are normal Python objects such as strings, integers, and floats (e.g., `[('bob', 48), ('emily',47)]`). Column values may also be special types that encapsulate things such as date and time, and database `NULL` values are returned as the Python `None` object."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connection Objects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Beyond this, the API defines a standard set of database exception types, special database type object constructors, and informational top-level calls including thread safety and replacement style checks.\n",
    "\n",
    "For instance, to establish a database connection under the Python API-compliant **Oracle** interface, install the commonly used Python Oracle extension module (i.e. `pip install cx_oracle`) as well as Oracle itself, and then run a statement of this form:\n",
    "\n",
    "```python\n",
    "    connobj = connect(\"user/password@system\")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This call’s arguments may vary per database and vendor (e.g., some may require network details or a local file’s name), but they generally contain what you provide to log in to your database system. \n",
    "\n",
    "Once you have a connection object, there a variety of things you can do with it, including:\n",
    "\n",
    "```python\n",
    "    connobj.close()     # close connection now (not at object __del__ time)\n",
    "    connobj.commit()    # commit any pending transactions to the database \n",
    "    connobj.rollback()  # roll database back to start of pending transactions\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cursor Objects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But one of the most useful things to do with a connection object is to generate a cursor object:\n",
    "\n",
    "```python\n",
    "    cursobj = connobj.cursor()  # return a new cursor object for running SQL\n",
    "```\n",
    "\n",
    "Cursor objects have a set of methods, too (e.g., close to close the cursor before its destructor runs, and callproc to call a stored procedure), but the most important may be this one:\n",
    "\n",
    "```python\n",
    "    cursobj.execute(sqlstring [, parameters])  # run SQL query or command string\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Parameters are passed in as a sequence or mapping of values, and are substituted into the `SQL` statement string according to the interface module’s replacement target conventions. \n",
    "\n",
    "The execute method can be used to run a variety of `SQL` statement strings:\n",
    "\n",
    "* DDL definition statements (e.g., `CREATE TABLE`);\n",
    "* DML modification statements (e.g., `UPDATE` or `INSERT`);\n",
    "* DQL query statements (e.g., `SELECT`)\n",
    "\n",
    "After running an SQL statement, the cursor’s `rowcount` attribute gives the number of rows *changed* (for **DML** changes) or *fetched* (for **DQL** queries), and the cursor’s `description` attribute gives column names and types after a query; \n",
    "`execute` also returns the number of rows affected or fetched in the most vendor interfaces. \n",
    "\n",
    "For **DQL** query statements, you must call one of the `fetch` methods to complete the operation:\n",
    "\n",
    "```python\n",
    "    single_tuple = cursobj.fetchone()          # fetch next row of a query result \n",
    "    list_of_tuple = cursobj.fetchmany([size])  # fetch next set of rows of query result\n",
    "    list_of_tuple = cursobj.fetchall()         # fetch all remaining rows of the result\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And once you’ve received fetch method results, table information is processed using normal Python sequence operations; for example, you can step through the tuples in a `fetchall` result list with a simple for loop or comprehension expression. \n",
    "\n",
    "Most Python database interfaces also allow you to provide values to be passed to `SQL` statement strings, by providing targets and a tuple of parameters. For instance:\n",
    "\n",
    "```python\n",
    "\n",
    "query = 'SELECT name, shoesize FROM spam WHERE job = ? AND age = ?' \n",
    "cursobj.execute(query, (value1, value2))\n",
    "results = cursobj.fetchall()\n",
    "for row in results: \n",
    "    pass  # do something\n",
    "    \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this event, the database interface utilizes *prepared statements* (an optimization and convenience) and correctly passes the parameters to the database regardless of their Python types. \n",
    "\n",
    "The notation used to code targets in the query string may vary in some database interfaces (e.g., `:p1` and `:p2` or two `%s`, rather than the two `?s` used by the **Oracle interface**); in any event, this is not the same as Python’s \n",
    "`%` string formatting operator, as it sidesteps security issues along the way.\n",
    "\n",
    "Finally, if your database supports stored procedures, you can call them with the `callproc` method or by passing an `SQL CALL` or `EXEC` statement string to the execute method. \n",
    "\n",
    "`callproc` may generate a result table retrieved with a `fetch` variant, and returns a modified copy of the input sequence — input parameters are left untouched, and output and input/output parameters are replaced with possibly new values. \n",
    "\n",
    "Additional API features, including support for database `blobs` (roughly, with sized results), is described in the API’s documentation. \n",
    "\n",
    "For now, let’s move on to do some real SQL processing in Python."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## An SQL Database API Tutorial with SQLite"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We don’t have space to provide an exhaustive reference for the database API in this notebook. \n",
    "\n",
    "To sample the flavor of the interface, though, let’s step through a few simple examples. \n",
    "\n",
    "We’ll use the **SQLite** database system for this tutorial. \n",
    "\n",
    "SQLite is a standard part of Python itself, which you can reasonably expect to be available in all Python installations. Although SQLite implements a complete relational database system, it takes the form of an in-process library instead of a server. \n",
    "\n",
    "This generally makes it better suited for program storage than for enterprise-level data needs."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note:\n",
    "\n",
    "Thanks to Python’s portable DB API, though, other popular database packages such as **PostgreSQL, MySQL, and Oracle** are used almost identically; the initial call to log in to the database will be all that normally requires different argument values for scripts that use standard SQL code. \n",
    "\n",
    "Because of this, we can use the SQLite system both as a prototyping tool in applications development and as an easy way to get started with the Python SQL database API in this book.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting Started"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Regardless of which database system your scripts talk to, the basic SQL interface in Python is very simple. \n",
    "\n",
    "In fact, it’s hardly object-oriented at all queries and other database commands are sent as strings of SQL. \n",
    "\n",
    "Whether large or small, though, the Python code needed to process your database turns out to be surprisingly straightforward. \n",
    "\n",
    "To get started, the first thing we need to do is open a connection to the database and create a table for storing records:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect('data/dbase1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We start out by importing the Python SQLite interface here— it’s a standard library module called `sqlite3` to our scripts. \n",
    "\n",
    "Next we create a **connection** object, passing in the items our database requires at start-up time—here, the name of the local file where our databases will be stored. \n",
    "\n",
    "This file is what you’ll want to back up to save your database. It will create the file if needed, or open its current content; SQLite also accepts that special string `:memory:` to create a temporary database in memory instead."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As long as a script sticks to using standard SQL code, the connect call’s arguments are usually the only thing that can vary across different database systems. \n",
    "\n",
    "For example, in the MySQL interface this call accepts a network host’s domain name, user name, and password, passed as keyword arguments instead, and the **Oracle example** sketched earlier expects a more specific sting syntax. \n",
    "\n",
    "Once we’ve gotten past this platform-specific call, though, the rest of the API is largely database **neutral**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Making Database and Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, let’s make a cursor for submitting SQL statements to the database server, and submit one to create a first table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "curs = conn.cursor()\n",
    "try:\n",
    "    curs.execute('drop table people')\n",
    "except:\n",
    "    pass  # did not exist\n",
    "curs.execute('create table people (name char(30), job char(10), pay int(4))')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The last command here creates the table called “people” within the database; the name, job, and pay information specifies the columns in this table, as well as their datatypes, using a “type(size)” syntax — two strings and an integer. \n",
    "\n",
    "Datatypes can be more sophisticated than ours, but we’ll ignore such details here. \n",
    "\n",
    "In SQLite, the file is the database, so there’s no notion of creating or using a specific database within it, as there is in some systems."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding Records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are three basic statement-based approaches we can use here: \n",
    "\n",
    "* inserting one row at a time; \n",
    "* inserting multiple rows with a single call statement;\n",
    "* using a Python loop.\n",
    "\n",
    "Here is the simple case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "curs.execute('insert into people values (?, ?, ?)', ('Bob', 'dev', 50000))\n",
    "curs.rowcount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sqlite3.paramstyle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, `qmark` means this module accepts `?` for replacement targets. \n",
    "\n",
    "Other database modules might use styles such as format (meaning a `%s` target), or numeric indexes or mapping keys; see the **DB API** for more details."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To insert multiple rows with a single statement, use the `executemany` method and a sequence of row sequences (e.g., a list of lists). This call is like calling `execute` once for each row sequence in the argument, and in fact may be implemented as such; database interfaces may also use database-specific techniques to make this run quicker, though:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "curs.executemany('insert into people values (?, ?, ?)', \n",
    "                     [ ('Sue', 'mus', '70000'),\n",
    "                       ('Ann', 'mus', '60000')])\n",
    "curs.rowcount"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We inserted two rows at once in the last statement. \n",
    "\n",
    "It’s hardly any more work to achieve the same result by inserting one row at a time with a Python loop:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rows = [['Tom', 'mgr', 100000], ['Kim', 'adm', 30000], ['pat', 'dev', 90000]]\n",
    "for row in rows:\n",
    "    curs.execute('insert into people values (? , ?, ?)', row)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Blending Python and SQL like this starts to open up all sorts of interesting possibilities. \n",
    "\n",
    "Notice the last command; we always need to call the connection’s `commit` method to write our changes out to the database. Otherwise, when the connection is closed, our changes may be lost. \n",
    "\n",
    "In fact, until we call the `commit` method, none of our inserts may be visible from other database connections."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Side note:\n",
    "\n",
    "Technically, the API suggests that a connection object should automatically call its `rollback` method to back out changes that have not yet been committed, when it is closed (which happens manually when its close method is called, or automatically when the connection object is about to be garbage collected). \n",
    "\n",
    "For database systems that don’t support transaction commit and rollback operations, these calls may do nothing. SQLite implements both the commit and rollback methods; the latter rolls back any changes made since the last commit."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Running Queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "curs.execute('select * from people')\n",
    "for row in curs.fetchall():\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Tuple unpacking** comes in handy in loops here, too, to pick out column values as we go. \n",
    "\n",
    "Here’s a simple formatted display of two of the columns’ values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "curs.execute('select * from people')\n",
    "for (name, job, pay) in curs.fetchall():\n",
    "    print(name, ':', pay)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because the query result is a sequence, we can use Python’s powerful sequence and iteration tools to process it. \n",
    "\n",
    "For instance, to select just the name column values, we can run a more specific SQL query and get a list of tuples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "curs.execute('select name from people')\n",
    "names = curs.fetchall()\n",
    "names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `fetchall` vs `fetchone`\n",
    "\n",
    "The `fetchall` call we’ve used so far fetches the entire query result table all at once, as a single sequence (an empty sequence comes back, if the result is empty). \n",
    "\n",
    "That’s convenient, but it may be slow enough to block the caller temporarily for large result tables or generate substantial network traffic if the server is running remotely (something could easily require a parallel thread in GUI). \n",
    "\n",
    "To avoid such a bottleneck, we can also grab just one row, or a bunch of rows, at a time with `fetchone` and `fetchmany`. \n",
    "\n",
    "The `fetchone` call returns the next result row or a `None` false value at the end of the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "curs.execute('select * from people')\n",
    "while True:\n",
    "    row = curs.fetchone() \n",
    "    if not row: \n",
    "        break \n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "curs.execute('select * from people')\n",
    "for row in curs:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `fetchmany` call returns a sequence of rows from the result, but not the entire table; you can specify how many rows to grab each time with a parameter or rely on the default as given by the cursor’s `arraysize` attribute. \n",
    "\n",
    "Each call gets at most that many more rows from the result or an empty sequence at the end of the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "curs.execute('select * from people')\n",
    "while True:\n",
    "    rows = curs.fetchmany()  # size=N optional argument\n",
    "    if not rows: \n",
    "        break\n",
    "    for row in rows:\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### More sophisticated Example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "curs.description"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "curs.execute('select * from people')\n",
    "colnames = [desc[0] for desc in curs.description]\n",
    "for row in curs:\n",
    "    print('-' * 30)\n",
    "    for (name, value) in zip(colnames, row):\n",
    "        print('%s => %s' % (name, value))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise:\n",
    "\n",
    "Convert list of row tuples to list of row dicts with field name keys."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# %load files/makedicts.py\n",
    "\"\"\"\n",
    "convert list of row tuples to list of row dicts with field name keys\n",
    "\"\"\"\n",
    "import sqlite3\n",
    "\n",
    "def makedicts(cursor, query, params=()):\n",
    "    cursor.execute(query, params)\n",
    "    colnames = [desc[0] for desc in cursor.description]\n",
    "    rowdicts = [dict(zip(colnames, row)) for row in cursor]\n",
    "    return rowdicts\n",
    "\n",
    "conn = sqlite3.connect('data/dbase1')\n",
    "cursor = conn.cursor()\n",
    "query  = 'select name, pay from people where pay < ?'\n",
    "lowpay = makedicts(cursor, query, [70000])\n",
    "for rec in lowpay: \n",
    "    print(rec)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise no. 2\n",
    "\n",
    "Load Data from Text files: load table from comma-delimited text file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!cat data/data.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!cat data/data2.txt"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}