querying-databases.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying databases\n",
    "We've already seen a couple of SQL queries when creating a database table and checking if data was present. Now we will go beyond those basics queries to do slightly more advanced queries, like searching and filtering."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Work with an in-memory SQLite database again\n",
    "import sqlite3\n",
    "connection = sqlite3.connect(':memory:')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a table again for holding a path and size, just like before\n",
    "table = 'CREATE TABLE files (id integer primary key, path TEXT, bytes INTEGER)'\n",
    "cursor = connection.cursor()\n",
    "cursor.execute(table)\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a _large_files.py_ file that has a `files` variable which holds a list of tuples with some sample data we can use to populate the database. Import that module and use the list to iterate over it and then populate the database\n",
    "\n",
    "In this section you will use a special SQL syntax in SQLite to insert values from Python into the SQL query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "from large_files import files\n",
    "\n",
    "for metadata in files:\n",
    "    query = 'INSERT INTO files(path, bytes) VALUES(?, ?)'\n",
    "    # the execute() method accepts a query and optionally a tuple with values \n",
    "    # corresponding to the question marks in VALUES\n",
    "    cursor.execute(query, metadata)\n",
    "    connection.commit()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So far we've seen `CREATE` and `INSERT`. Let's try a new SQL statement to make a selection. The `SELECT` statement produces a result from one or more tables and from one or many rows. \n",
    "\n",
    "Note the particular (and strict) order of SQL statements:\n",
    "\n",
    "- `SELECT`\n",
    "- `FROM`\n",
    "- `WHERE`\n",
    "- `GROUP BY`\n",
    "- `HAVING`\n",
    "- `ORDER BY`\n",
    "\n",
    "Since SQLite returns an iterator as a result always, then it is required to loop over the resulting object. Create a query to count the items in the `files` table. This query will use the `COUNT()` function that produces a number:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2001,)\n"
     ]
    }
   ],
   "source": [
    "query = 'SELECT COUNT(id) from files'\n",
    "\n",
    "for i in cursor.execute(query):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extracting distinct row data\n",
    "Counting items is a good way of checking the amount of items that exist in the database. Without using `COUNT(id)` the previous query would've produced two thousand entries. Use the `LIMIT` statement to set the maximum number of entries that can be produced, then remove the `COUNT(id)` function and use `*` instead to use all rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, '/Users/alfredo/Library/Application Support/.DS_Store', 6148)\n",
      "(2, '/Users/alfredo/Library/Application Support/.settings', 557)\n",
      "(3, '/Users/alfredo/Library/Application Support/Firefox/installs.ini', 71)\n",
      "(4, '/Users/alfredo/Library/Application Support/Firefox/profiles.ini', 286)\n",
      "(5, '/Users/alfredo/Library/Application Support/Firefox/Profiles/3zgfj4k7.default/times.json', 47)\n",
      "(6, '/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/compatibility.ini', 220)\n",
      "(7, '/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/favicons.sqlite-wal', 524704)\n",
      "(8, '/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/addons.json', 24)\n",
      "(9, '/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/.parentlock', 0)\n",
      "(10, '/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/logins.json', 643)\n"
     ]
    }
   ],
   "source": [
    "query = 'SELECT * from files LIMIT(10)'\n",
    "for i in cursor.execute(query):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using `*` means every row in the table. The table in this case is `files`. The next query specifies using the `id` row only. \n",
    "\n",
    "**Exercise:** Update the cell contents so that it shows paths instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1,)\n",
      "(2,)\n"
     ]
    }
   ],
   "source": [
    "query = 'SELECT id FROM files LIMIT(10)'\n",
    "for i in cursor.execute(query):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next query uses ID and Bytes. Update the query once again to select two rows in the table: `bytes` and `path`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 6148)\n",
      "(2, 557)\n",
      "(3, 71)\n",
      "(4, 286)\n",
      "(5, 47)\n",
      "(6, 220)\n",
      "(7, 524704)\n",
      "(8, 24)\n",
      "(9, 0)\n",
      "(10, 643)\n"
     ]
    }
   ],
   "source": [
    "query = 'SELECT id,bytes FROM files LIMIT(10)'\n",
    "for i in cursor.execute(query):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extracting distinct data\n",
    "You now know how to extract data from certain rows and how to limit that data. Next, we'll use more SQL statements to further find and filter out results so that you can get specific results.\n",
    "\n",
    "**Exercise:** Use the next query to find 10 files that are bigger than 1mb (1000000 bytes) using a new statement (`WHERE`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/places.sqlite-wal', 2131512)\n",
      "('/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/favicons.sqlite', 5242880)\n",
      "('/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/places.sqlite', 5242880)\n",
      "('/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/security_state/data.safe.bin', 4902142)\n",
      "('/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/storage/permanent/chrome/idb/3870112724rsegmnoittet-es.sqlite', 14385152)\n",
      "('/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/gmp-gmpopenh264/1.8.1.1/libgmpopenh264.dylib', 1012096)\n",
      "('/Users/alfredo/Library/Application Support/Firefox/Profiles/vxy45f54.default-release/gmp-widevinecdm/4.10.2209.1/libwidevinecdm.dylib', 9698128)\n",
      "('/Users/alfredo/Library/Application Support/Spotify/User Data/WidevineCDM/4.10.2209.0/x64/libwidevinecdm.dylib', 11670960)\n",
      "('/Users/alfredo/Library/Application Support/Spotify/User Data/WidevineCDM/4.10.2209.0/arm64/libwidevinecdm.dylib', 10061392)\n",
      "('/Users/alfredo/Library/Application Support/Spotify/PersistentCache/Update/spotify-autoupdate-1.1.69.612.gb7409abc-13.tbz', 104654007)\n"
     ]
    }
   ],
   "source": [
    "query = 'SELECT path FROM files WHERE bytes>1000000 LIMIT(10)'\n",
    "for i in cursor.execute(query):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The query shows the paths but not the sizes. \n",
    "\n",
    "**Exercise:** Try updating the previous query to show both the path and the size. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = ''\n",
    "for i in cursor.execute(query):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL has many helper functions, in this case the next query uses `MAX()` which can find the highest value in a column. Do you think that `LIMIT(10)` makes sense in this query? Why? What happens if you remove the `LIMIT(10)` clause?\n",
    "\n",
    "**Exercise:** Remote the `LIMIT()` clause and check your results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('/Users/alfredo/Library/Application Support/Quake3/baseq3/pak0.pk3', 479493658)\n"
     ]
    }
   ],
   "source": [
    "query = 'SELECT path,MAX(bytes) FROM files LIMIT(10)'\n",
    "for i in cursor.execute(query):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL queries can be compounded for more conditionals. In Python, you can make the query more readable by using triple quotes and adding the queries in a multi-line variable.\n",
    "\n",
    "**Exercise:** Use other conditions to match different sizes and limit to a different number of entries returned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('/Users/alfredo/Library/Application Support/Code/CachedData/e7d7e9a9348e6a8cc8c03f877d39cb72e5dfb1ff/extension-ca2c1aa0053e7287d1f9d28629439dd1.code', 3226912)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/46d6e50d2ffc17893d2014fea87951879db7be6e/c39febc2-ae3b-41f4-a090-89f74e7869d1/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/8bb0178b48a96ad4ef4a0b4a835ad58c89c7d663/4c006a21-b197-439d-b3c6-6a2bb7ef9a68/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/e7d86aa06a3a0bb38c95db406df77476663db84b/ee1d8a80-45aa-4e2d-a19e-36095014c44d/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/c857019fd12bbf4dd5269cd1b1f74afd717e2616/867b6dd5-c514-4bfb-92fc-30ad367b606d/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/4ae4a6eedbab83c9bdaba67f2285a31b71563b7f/41ea4653-468e-447c-ad69-897ab79eff7b/65094749c9348204_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/56ba13c1ee192cf8ec4a661d5c054cc2c13e90f0/666a80cd-2414-4fe7-921d-cc98d468c0ff/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/8cc7265202634d6c9cbe28cd5c6061b1cdd1aedd/5df78c86-1076-48e8-99e4-6b65e8601eae/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/d232fc83e85c2a0e59f5d08e21441b93af6582e6/4f522e97-ef34-4a6d-804d-1d705105b32c/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/08b42bf2785a64f15bdd570827e032ec6ab90095/a0fda081-75f2-4bd7-b9f7-899d41e6f211/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/63162a5503857d57bf1b28f28d057b29b2f97029/1a8ecae0-cfc9-4cb1-8375-3a332dee95f6/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/d5a2cccfe2008c851d7daa8a4f18a6d84e1edba0/ff942740-5226-4f5b-9619-944f817a78c1/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/d9b79e0d8fb567b876b79a598c434a213f609f1f/5601af5d-949d-4df8-a3f6-2b53ac4459fe/65094749c9348204_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/b0c3d3460f56bff015985c70155c448fa3c04f8e/b0c9672c-2afa-44ff-a62a-138203f06150/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/2cab3b68f29cfa575c738e3c81a7b3665c4bb433/02b4c055-06d5-4d56-a768-96f6249a3fc2/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/e5b49ef32eaf1ba0b12bec5adf9c2367f119938e/9def1fc9-9ac9-4b19-99fa-0e65b44e18b8/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Code/Service Worker/CacheStorage/366104ae3efba3bd0529f6090f26192c8c8b40a5/f0433c4c-c492-49ed-ad77-bd8fa622cf10/848e0c80e6507d26_0', 3952816)\n",
      "('/Users/alfredo/Library/Application Support/Adobe/AcroCef/DC/Acrobat/Cache/Cache/data_3', 4202496)\n",
      "('/Users/alfredo/Library/Application Support/AddressBook/AddressBook-v22.abcddb-wal', 3790432)\n",
      "('/Users/alfredo/Library/Application Support/AddressBook/Sources/CCB8CED3-EBC7-43D3-AB10-6FA77C01BB88/AddressBook-v22.abcddb-wal', 3333112)\n"
     ]
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT path,bytes FROM files \n",
    "    WHERE bytes>3000000 \n",
    "    AND bytes<4400000 LIMIT(100)\n",
    "\"\"\"\n",
    "for i in cursor.execute(query):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Searching\n",
    "Sometimes you can't tell exactly what is it that you are looking for in a query. SQL allows for matching patterns. In the file paths situation, you might know that a specific file ends with `.zip` but you don't know where it is. \n",
    "\n",
    "**Exercise:** Use the `LIKE` operator to match and find a cache file related to an Address Book application."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('/Users/alfredo/Library/Application Support/AddressBook/AddressBook-v22.abcddb-wal', 3790432)\n",
      "('/Users/alfredo/Library/Application Support/AddressBook/Sources/CCB8CED3-EBC7-43D3-AB10-6FA77C01BB88/AddressBook-v22.abcddb', 2818048)\n",
      "('/Users/alfredo/Library/Application Support/AddressBook/Sources/CCB8CED3-EBC7-43D3-AB10-6FA77C01BB88/AddressBook-v22.abcddb-wal', 3333112)\n"
     ]
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT path,bytes FROM files \n",
    "    WHERE path LIKE '%AddressBook%'\n",
    "\"\"\"\n",
    "for i in cursor.execute(query):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using `%` means to match any text of zero or more characters. So `%AddressBook%` is very lenient for anything before it and after it. Try adding a condition that filters the result by size. Anything over 2MB (or 2000000 bytes) and see if you can reduce the amount of output."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are other variations for search like using an underscore (`_`). That means any single character. If you know that a file prefix or suffix is, you could use this to fine-tune your search.\n",
    "\n",
    "**Exercise:** Use other search items for the paths found in your filesystem and try to match them"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "904aaa8d64bef68f2ffda03272599bba12aa237240eb8594526423b3c4f1b9a0"
  },
  "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}