07-working-with-sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Accessing SQLite Databases Using Python and Pandas\n",
    "\n",
    "### Objectives:\n",
    "    - \"Use the sqlite3 module to interact with a SQL database.\"\n",
    "    - \"Access data stored in SQLite using Python.\"\n",
    "    - \"Describe the difference in interacting with data stored as a CSV file versus in SQLite.\"\n",
    "    - \"Describe the benefits of accessing data using a database compared to a CSV file.\"\n",
    "\n",
    "### Python and SQL\n",
    "\n",
    "When you open a CSV in python, and assign it to a variable name, you are using\n",
    "your computers memory to save that variable. Accessing data from a database like\n",
    "SQL is not only more efficient, but also it allows you to subset and import only\n",
    "the parts of the data that you need.\n",
    "\n",
    "In the following lesson, we'll see some approaches that can be taken to do so.\n",
    "\n",
    "### The `sqlite3` module\n",
    "\n",
    "The [sqlite3] module provides a straightforward interface for interacting with\n",
    "SQLite databases. A connection object is created using `sqlite3.connect()`; the\n",
    "connection must be closed at the end of the session with the `.close()` command.\n",
    "While the connection is open, any interactions with the database require you to\n",
    "make a cursor object with the `.cursor()` command. The cursor is then ready to\n",
    "perform all kinds of operations with `.execute()`.\n",
    "\n",
    "[sqlite3]: https://docs.python.org/3/library/sqlite3.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "# Create a SQL connection to our SQLite database\n",
    "con = sqlite3.connect(\"data/portal_mammals.sqlite\")\n",
    "\n",
    "cur = con.cursor()\n",
    "\n",
    "# The result of a \"cursor.execute\" can be iterated over by row\n",
    "for row in cur.execute('SELECT * FROM species;'):\n",
    "    print(row)\n",
    "\n",
    "# Be sure to close the connection\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Queries\n",
    "\n",
    "One of the most common ways to interact with a database is by querying:\n",
    "retrieving data based on some search parameters. Use a SELECT statement string.\n",
    "The query is returned as a single tuple or a tuple of tuples. Add a WHERE\n",
    "statement to filter your results based on some parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "# Create a SQL connection to our SQLite database\n",
    "con = sqlite3.connect(\"data/portal_mammals.sqlite\")\n",
    "\n",
    "cur = con.cursor()\n",
    "\n",
    "# Return all results of query\n",
    "cur.execute('SELECT plot_id FROM plots WHERE plot_type=\"Control\"')\n",
    "print(cur.fetchall())\n",
    "\n",
    "# Return first result of query\n",
    "cur.execute('SELECT species FROM species WHERE taxa=\"Bird\"')\n",
    "print(cur.fetchone())\n",
    "\n",
    "# Be sure to close the connection\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Accessing data stored in SQLite using Python and Pandas\n",
    "\n",
    "Using pandas, we can import results of a SQLite query into a dataframe. Note\n",
    "that you can use the same SQL commands / syntax that we used in the SQLite\n",
    "lesson. An example of using pandas together with sqlite is below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3\n",
    "\n",
    "# Read sqlite query results into a pandas DataFrame\n",
    "con = sqlite3.connect(\"data/portal_mammals.sqlite\")\n",
    "df = pd.read_sql_query(\"SELECT * from surveys\", con)\n",
    "\n",
    "# Verify that result of SQL query is stored in the dataframe\n",
    "print(df.head())\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Storing data: CSV vs SQLite\n",
    "\n",
    "Storing your data in an SQLite database can provide substantial performance\n",
    "improvements when reading/writing compared to CSV. The difference in performance\n",
    "becomes more noticable as the size of the dataset grows (see for example [these\n",
    "benchmarks]).\n",
    "\n",
    "[these benchmarks]: http://sebastianraschka.com/Articles/2013_sqlite_database.html#results-and-conclusions\n",
    "\n",
    "\n",
    "### Challenge - SQL\n",
    "\n",
    "1. Create a query that contains survey data collected between 1998 - 2001 for\n",
    "  observations of sex \"male\" or \"female\" that includes observation's genus and\n",
    "  species and site type for the sample. How many records are returned?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "2. Create a dataframe that contains the total number of observations (count)\n",
    "   made for all years, and sum of observation weights for each site, ordered by\n",
    "   site ID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Storing data: Create new tables using Pandas\n",
    "\n",
    "We can also us pandas to create new tables within an SQLite database. Here, we run we re-do an excercise we did before with CSV files using our SQLite database. We first read in our survey data, then select only those survey results for 2002, and then save it out to its own table so we can work with it on its own later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3\n",
    "\n",
    "con = sqlite3.connect(\"data/portal_mammals.sqlite\")\n",
    "\n",
    "# Load the data into a DataFrame\n",
    "surveys_df = pd.read_sql_query(\"SELECT * from surveys\", con)\n",
    "\n",
    "# Select only data for 2002\n",
    "surveys2002 = surveys_df[surveys_df.year == 2002]\n",
    "\n",
    "# Write the new DataFrame to a new SQLite table\n",
    "surveys2002.to_sql(\"surveys2002\", con, if_exists=\"replace\")\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Challenge - Checking your work\n",
    "\n",
    "1. Write code to check the results from the newly create `surveys2002` table in the SQL database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}