ch11.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Case Study Preliminaries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Web APIs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# A Python dictionary...\n",
    "user_dict = {\"name\": \"Jane Doe\",\n",
    "             \"age\": 23,\n",
    "             \"married\": False,\n",
    "             \"children\": None,\n",
    "             \"hobbies\": [\"hiking\", \"reading\"]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# ...converted to a JSON string\n",
    "# by json.dumps (\"dump string\"). The \"indent\" parameter is\n",
    "# optional and prettifies the printing.\n",
    "user_json = json.dumps(user_dict, indent=4)\n",
    "print(user_json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Convert the JSON string back to a native Python data structure\n",
    "json.loads(user_json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "response = requests.get(\"https://pypi.org/pypi/pandas/json\")\n",
    "response.status_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# response.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "releases = []\n",
    "for version, files in response.json()['releases'].items():\n",
    "    releases.append(f\"{version}: {files[0]['upload_time']}\")\n",
    "releases[:3]  # show the first 3 elements of the list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Databases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.parse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "urllib.parse.quote_plus(\"pa$$word\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's start with the imports\n",
    "import sqlite3\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Our SQL query: \"select all columns from the packages table\"\n",
    "sql = \"SELECT * FROM packages\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Option 1: Database driver (sqlite3 is part of the standard library)\n",
    "# Using the connection as context manager automatically commits\n",
    "# the transaction or rolls it back in case of an error.\n",
    "with sqlite3.connect(\"packagetracker/packagetracker.db\") as con:\n",
    "    cursor = con.cursor()  # We need a cursor to run SQL queries\n",
    "    result = cursor.execute(sql).fetchall()  # Return all records\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Option 2: SQLAlchemy\n",
    "# \"create_engine\" expects the connection string of your database.\n",
    "# Here, we can execute a query as a method of the connection object.\n",
    "engine = create_engine(\"sqlite:///packagetracker/packagetracker.db\")\n",
    "with engine.connect() as con:\n",
    "    result = con.execute(sql).fetchall()\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Option 3: pandas\n",
    "# Providing a table name to \"read_sql\" reads the full table.\n",
    "# Pandas requires an SQLAlchemy engine that we reuse from\n",
    "# the previous example.\n",
    "df = pd.read_sql(\"packages\", engine, index_col=\"package_id\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# \"read_sql\" also accepts an SQL query\n",
    "pd.read_sql(sql, engine, index_col=\"package_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# The DataFrame method \"to_sql\" writes DataFrames to tables\n",
    "# \"if_exists\" has to be either \"fail\", \"append\" or \"replace\"\n",
    "# and defines what happens if the table already exists\n",
    "df.to_sql(\"packages2\", con=engine, if_exists=\"append\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# The previous command created a new table \"packages2\" and\n",
    "# inserted the records from the DataFrame df as we can\n",
    "# verify by reading it back\n",
    "pd.read_sql(\"packages2\", engine, index_col=\"package_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Let's get rid of the table again by running the\n",
    "# \"drop table\" command via SQLAlchemy\n",
    "with engine.connect() as con:\n",
    "    con.execute(\"DROP TABLE packages2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's start by importing SQLAlchemy's text function\n",
    "from sqlalchemy.sql import text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# \":package_id\" is the placeholder\n",
    "sql = \"\"\"\n",
    "SELECT v.uploaded_at, v.version_string\n",
    "FROM packages p\n",
    "INNER JOIN package_versions v ON p.package_id = v.package_id\n",
    "WHERE p.package_id = :package_id\n",
    "ORDER BY v.uploaded_at\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Via SQLAlchemy\n",
    "with engine.connect() as con:\n",
    "    result = con.execute(text(sql), package_id=1).fetchall()\n",
    "result[:3]  # Print the first 3 records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Via pandas\n",
    "pd.read_sql(text(sql), engine, parse_dates=[\"uploaded_at\"],\n",
    "            params={\"package_id\": 1},\n",
    "            index_col=[\"uploaded_at\"]).head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exceptions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def print_reciprocal(number):\n",
    "    result = 1 / number\n",
    "    print(f\"The reciprocal is: {result}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "print_reciprocal(0)  # This will raise an error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def print_reciprocal(number):\n",
    "    try:\n",
    "        result = 1 / number\n",
    "    except Exception as e:\n",
    "        # \"as e\" makes the Exception object available as variable \"e\"\n",
    "        # \"repr\" stands for \"printable representation\" of an object\n",
    "        # and gives you back a string with the error message\n",
    "        print(f\"There was an error: {repr(e)}\")\n",
    "        result = \"N/A\"\n",
    "    else:\n",
    "        print(\"There was no error!\")\n",
    "    finally:\n",
    "        print(f\"The reciprocal is: {result}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "print_reciprocal(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "print_reciprocal(\"a\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "print_reciprocal(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def print_reciprocal(number):\n",
    "    try:\n",
    "        result = 1 / number\n",
    "        print(f\"The reciprocal is: {result}\")\n",
    "    except (TypeError, ZeroDivisionError):\n",
    "        print(\"Please type in any number except 0.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "print_reciprocal(\"a\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def print_reciprocal(number):\n",
    "    try:\n",
    "        result = 1 / number\n",
    "        print(f\"The reciprocal is: {result}\")\n",
    "    except TypeError:\n",
    "        print(\"Please type in a number.\")\n",
    "    except ZeroDivisionError:\n",
    "        print(\"The reciprocal of 0 is not defined.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print_reciprocal(\"a\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print_reciprocal(0)"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}