RunDB.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6fe48a6b",
   "metadata": {},
   "source": [
    "# Run Database\n",
    "This notebook reads your Strava API run data, extracts the date, time of day, distance (miles), and duration (hours + minutes) of your runs, and loads to a PostgreSQL database. The data can also be uploaded directly from a properly-formatted, 4-column csv.\n",
    "\n",
    "This notebook requires the following:\n",
    " - `strava_functions.py`\n",
    " - `pg_functions.py`\n",
    " - `analysis_functions.py`\n",
    " - json text file with strava api credentials/token + database connection credentials (see `sample-creds.json`)\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8826423e",
   "metadata": {},
   "source": [
    "## 1. Function Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca9ce364",
   "metadata": {},
   "outputs": [],
   "source": [
    "from strava_functions import obtain_api_access_info, get_past_runs, generate_runs_df\n",
    "from pg_functions import generate_db_connection, \\\n",
    "    get_last_logged_run, write_to_postgres, \\\n",
    "    update_postgres, display_db_as_df, \\\n",
    "    perform_sql_query, upload_from_csv\n",
    "from analysis_functions import generate_weekly_summary\n",
    "import datetime\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25fe150e",
   "metadata": {},
   "source": [
    "## 2. Define Database & File info + Variables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e57f5645",
   "metadata": {},
   "source": [
    "- `access_json_path` is the relative path to the file containing your Strava API and Postgres database credentials.<br>\n",
    "- `my_table' is the table within that database you'll be using.<br>\n",
    "- the `verbose` parameter will tell you the status of each function that you run.<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39883d76",
   "metadata": {},
   "outputs": [],
   "source": [
    "access_json_path = \"sample-creds.json\" #CHANGE THIS!!\n",
    "my_table = obtain_api_access_info(access_json_path)[\"postgres\"][\"table\"]\n",
    "verbose=True"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cc74f48",
   "metadata": {},
   "source": [
    "## 3. Getting Started"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ea00b17",
   "metadata": {},
   "source": [
    "### 3.1 Initializing your Database (only do this section once)\n",
    "First, go create a table in pgAdmin if you haven't already. Here's how you can go about doing it:<br>\n",
    "```\n",
    "CREATE TABLE IF NOT EXISTS public.runs\n",
    "    (start_date date,\n",
    "    start_time time without time zone,\n",
    "    miles real,\n",
    "    hours integer,\n",
    "    minutes integer);\n",
    "```\n",
    "Assuming that's all done, let's populate a your new table using `update_postgres(path_to_creds, db_conn_obj, table_name, date_to_update_to, verbose=True, manual_start_date=None)`. By default, this function pulls Strava API from the latest date in the database up to the date specified in the argument. Since you've got an empty table, we'll need to manually specify a starting date and loading up until today.<br>\n",
    "\n",
    "`connection` is the default variable name used for a psycopg2 Postgres database connection object.<br>\n",
    "!!! Be sure to close your connection (`connection.close()`) at the end of each cell block. I've included them by default."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b6089b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_date_string = \"2022-09-01\"\n",
    "today_datetime_obj = datetime.date.today() #returns a datetime object. We'll eventually turn it to YYYY-mm-dd string format.\n",
    "today_string = today_datetime_obj.strftime(\"%Y-%m-%d\") #this will be our argument in the function call.\n",
    "\n",
    "connection = generate_db_connection(access_json_path) #generate that connection object\n",
    "update_postgres(access_json_path, connection, my_table, today_string, verbose=verbose, manual_start_date=start_date_string)\n",
    "display_db_as_df(connection, my_table, start_date_string, today_string, verbose=verbose) #let's display it\n",
    "connection.close() #close your connection object to the database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c338fc99",
   "metadata": {},
   "source": [
    "### 3.2 Updating a Database\n",
    "Assuming you've done 3.1 already, and you have a database with some data, let's update our database with your most recent runs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "678afa96",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = generate_db_connection(access_json_path)\n",
    "update_postgres(access_json_path, connection, my_table, today_string, verbose=verbose)\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39b1139c",
   "metadata": {},
   "source": [
    "## 4. Weekly Run Stats"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "629ce170",
   "metadata": {},
   "source": [
    "Let's generate a Dataframe summarizing your daily and weekly mileage in the below format.\n",
    "\n",
    "| Week of:  | Mon   | Tues  | Weds  | Thurs | Fri   | Sat   | Sun   | Total |\n",
    "|---------- |-------|-------|-------|-------|-------|-------|-------|-------|\n",
    "| mmm dd    |  #    |  #    | #     | #     |  #    | #    | #     | #    |\n",
    "| mmm dd    |  #    |  #    | #     | #     |  #    | #    | #     | #    |\n",
    "| mmm dd    |  #    |  #    | #     | #     |  #    | #    | #     | #    |\n",
    "\n",
    "You can optionally set the display_heatmap boolean to display a heatmap of this dataframe.\n",
    "To change the colors and thresholds for the heatmap, edit the `generate_weekly_summary()` function within the `analysis_functions.py` document."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "418e62f7",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "start_date_string = (datetime.date.today() - datetime.timedelta(days=91)).strftime(\"%Y-%m-%d\") #2-months back default\n",
    "end_date_string = datetime.date.today().strftime(\"%Y-%m-%d\") #default value is current day\n",
    "connection = generate_db_connection(access_json_path)\n",
    "display_heatmap = True\n",
    "\n",
    "weekly_df = generate_weekly_summary(connection, my_table, start_date_string, end_date_string, display_heatmap=display_heatmap, verbose=verbose) \n",
    "display(weekly_df) #print the dataframe itself (not the heatmap)\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f01c1fe9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}