sqlite_database_builder.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQLite Database Builder\n",
    "By Kenneth Burchfiel\n",
    "\n",
    "Released under the MIT license\n",
    "\n",
    "This code converts 4 separate data sources into one SQLite database. Pandas DataFrames are used as a bridge between those data sources and SQL tables given the ease of use of the to_sql Pandas function. SQLAlchemy was used as the connector because it supports other types of databases (including PostgreSQL) as well.\n",
    "\n",
    "Because 'if exists == replace' was included in the to_sql functions, this file can be run multiple times without creating duplicate tables/entries in the SQLite database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "start_time = time.time() # Allows the program's runtime to be measured\n",
    "import sqlite3\n",
    "import pandas as pd\n",
    "import os\n",
    "import sqlalchemy\n",
    "# from sqlalchemy.orm import Session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 1: Creating the SQLite database\n",
    "\n",
    "The following code blocks connect to the SQLite database (or create it if it does not already exist). \n",
    "\n",
    "<span style=\"color:red\"> **Note:** Due to its size, I could not upload sqlite_database.db, the SQLite database that this script connects to, directly to GitHub. You can instead find it via Google Drive at the following link:</span> \n",
    "\n",
    "https://drive.google.com/file/d/1THyBZYXXT4le6zQz2SBhQlIjMk2MjhYz/view?usp=sharing\n",
    "\n",
    "Once you have downloaded it, copy it into the data folder of your copy of the repository. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlalchemy_sqlite_engine = sqlalchemy.create_engine(\n",
    "    'sqlite:///data\\\\sqlite_database.db') \n",
    "# Based on https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#connect-strings\n",
    "# Creates a database if one does not exist already\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This database will store four different tables:\n",
    "1. A 'flights' table with US passenger traffic data.\n",
    "\n",
    "2. A 'steps' table containing some of my Fitbit steps data \n",
    "\n",
    "3. A 'music' table containing information about 64 public-domain music clips. I found music available under a CC0 (public domain) license at https://freepd.com/. Since I like electronic music, I downloaded all of the tracks within the electronic music section (https://freepd.com/electronic.php). Of these tracks, ‘Fireworks’ by Alexander Nakarada was my favorite. I then went into Audacity and created 64 short clips of this song.\n",
    "\n",
    "4. A 'photos' table containing information about public-domain photos that I sourced from NASA and Flickr. Most Flickr photos were created by the US Government, but I also found public-domain images from other Flickr users as well. \n",
    "\n",
    "All of this data is free to use."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1a: Creating the 'flights' Table\n",
    "\n",
    "I built this table using my Flights Table Builder program, located at https://github.com/kburchfiel/route_maps_builder/blob/master/flights_table_builder.ipynb ."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<span style=\"color:red\"> **Note:** Due to its size, I could not upload routes_planes_coordinates.csv directly to GitHub. You can instead find it via Google Drive at the following link: </span> \n",
    "\n",
    "https://drive.google.com/file/d/1TBj5_zgdhtH8eBcPXLthqGgkJSRb4WFC/view?usp=sharing\n",
    "\n",
    "Once you have downloaded it, copy it into the data folder of your copy of the repository. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DEPARTURES_SCHEDULED</th>\n",
       "      <th>DEPARTURES_PERFORMED</th>\n",
       "      <th>PAYLOAD</th>\n",
       "      <th>SEATS</th>\n",
       "      <th>PASSENGERS</th>\n",
       "      <th>FREIGHT</th>\n",
       "      <th>MAIL</th>\n",
       "      <th>DISTANCE</th>\n",
       "      <th>RAMP_TO_RAMP</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>...</th>\n",
       "      <th>Code_y</th>\n",
       "      <th>Plane_Group_Text</th>\n",
       "      <th>Code</th>\n",
       "      <th>Plane_Config_Text</th>\n",
       "      <th>origin_iata_code</th>\n",
       "      <th>origin_lat</th>\n",
       "      <th>origin_lon</th>\n",
       "      <th>destination_iata_code</th>\n",
       "      <th>destination_lat</th>\n",
       "      <th>destination_lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21502.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>901.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>Jet, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>IAD</td>\n",
       "      <td>38.944</td>\n",
       "      <td>-77.456</td>\n",
       "      <td>FLL</td>\n",
       "      <td>26.072</td>\n",
       "      <td>-80.153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>64506.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>219.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>Jet, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>IAD</td>\n",
       "      <td>38.944</td>\n",
       "      <td>-77.456</td>\n",
       "      <td>JFK</td>\n",
       "      <td>40.640</td>\n",
       "      <td>-73.779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21502.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>851.0</td>\n",
       "      <td>144.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>Jet, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>IAH</td>\n",
       "      <td>29.980</td>\n",
       "      <td>-95.340</td>\n",
       "      <td>SAV</td>\n",
       "      <td>32.127</td>\n",
       "      <td>-81.202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21502.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>Jet, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>ILM</td>\n",
       "      <td>34.271</td>\n",
       "      <td>-77.903</td>\n",
       "      <td>RDU</td>\n",
       "      <td>35.877</td>\n",
       "      <td>-78.787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12500.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>Jet, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>IND</td>\n",
       "      <td>39.717</td>\n",
       "      <td>-86.294</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482268</th>\n",
       "      <td>1166.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>1049400.0</td>\n",
       "      <td>5247.0</td>\n",
       "      <td>3646.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>27284.0</td>\n",
       "      <td>21338.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Piston, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>ACK</td>\n",
       "      <td>41.253</td>\n",
       "      <td>-70.060</td>\n",
       "      <td>BOS</td>\n",
       "      <td>42.364</td>\n",
       "      <td>-71.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482269</th>\n",
       "      <td>1188.0</td>\n",
       "      <td>594.0</td>\n",
       "      <td>1069200.0</td>\n",
       "      <td>5346.0</td>\n",
       "      <td>3573.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>27799.0</td>\n",
       "      <td>21740.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Piston, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>BOS</td>\n",
       "      <td>42.364</td>\n",
       "      <td>-71.005</td>\n",
       "      <td>ACK</td>\n",
       "      <td>41.253</td>\n",
       "      <td>-70.060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482270</th>\n",
       "      <td>1216.0</td>\n",
       "      <td>608.0</td>\n",
       "      <td>1094400.0</td>\n",
       "      <td>5472.0</td>\n",
       "      <td>3827.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>28454.0</td>\n",
       "      <td>22253.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Piston, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>ACK</td>\n",
       "      <td>41.253</td>\n",
       "      <td>-70.060</td>\n",
       "      <td>BOS</td>\n",
       "      <td>42.364</td>\n",
       "      <td>-71.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482271</th>\n",
       "      <td>1258.0</td>\n",
       "      <td>629.0</td>\n",
       "      <td>1132200.0</td>\n",
       "      <td>5661.0</td>\n",
       "      <td>4056.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>29437.0</td>\n",
       "      <td>23021.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Piston, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>BOS</td>\n",
       "      <td>42.364</td>\n",
       "      <td>-71.005</td>\n",
       "      <td>ACK</td>\n",
       "      <td>41.253</td>\n",
       "      <td>-70.060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482272</th>\n",
       "      <td>2170.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>74400.0</td>\n",
       "      <td>264.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>1860.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>Helicopter/Stol</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>482273 rows × 62 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        DEPARTURES_SCHEDULED  DEPARTURES_PERFORMED    PAYLOAD   SEATS  \\\n",
       "0                        0.0                   1.0    21502.0    76.0   \n",
       "1                        0.0                   3.0    64506.0   228.0   \n",
       "2                        0.0                   1.0    21502.0    76.0   \n",
       "3                        0.0                   1.0    21502.0    76.0   \n",
       "4                        0.0                   1.0    12500.0    50.0   \n",
       "...                      ...                   ...        ...     ...   \n",
       "482268                1166.0                 583.0  1049400.0  5247.0   \n",
       "482269                1188.0                 594.0  1069200.0  5346.0   \n",
       "482270                1216.0                 608.0  1094400.0  5472.0   \n",
       "482271                1258.0                 629.0  1132200.0  5661.0   \n",
       "482272                2170.0                  44.0    74400.0   264.0   \n",
       "\n",
       "        PASSENGERS  FREIGHT  MAIL  DISTANCE  RAMP_TO_RAMP  AIR_TIME  ...  \\\n",
       "0              3.0      0.0   0.0     901.0         170.0     140.0  ...   \n",
       "1             75.0      0.0   0.0     228.0         219.0     140.0  ...   \n",
       "2             64.0      0.0   0.0     851.0         144.0     114.0  ...   \n",
       "3             55.0      0.0   0.0     122.0          58.0      31.0  ...   \n",
       "4             34.0      0.0   0.0     133.0          49.0      29.0  ...   \n",
       "...            ...      ...   ...       ...           ...       ...  ...   \n",
       "482268      3646.0      0.0   0.0      91.0       27284.0   21338.0  ...   \n",
       "482269      3573.0      0.0   0.0      91.0       27799.0   21740.0  ...   \n",
       "482270      3827.0      0.0   0.0      91.0       28454.0   22253.0  ...   \n",
       "482271      4056.0      0.0   0.0      91.0       29437.0   23021.0  ...   \n",
       "482272         0.0      0.0  44.0      59.0        1860.0       0.0  ...   \n",
       "\n",
       "       Code_y  Plane_Group_Text Code        Plane_Config_Text  \\\n",
       "0           6     Jet, 2-Engine    1  Passenger Configuration   \n",
       "1           6     Jet, 2-Engine    1  Passenger Configuration   \n",
       "2           6     Jet, 2-Engine    1  Passenger Configuration   \n",
       "3           6     Jet, 2-Engine    1  Passenger Configuration   \n",
       "4           6     Jet, 2-Engine    1  Passenger Configuration   \n",
       "...       ...               ...  ...                      ...   \n",
       "482268      1  Piston, 2-Engine    1  Passenger Configuration   \n",
       "482269      1  Piston, 2-Engine    1  Passenger Configuration   \n",
       "482270      1  Piston, 2-Engine    1  Passenger Configuration   \n",
       "482271      1  Piston, 2-Engine    1  Passenger Configuration   \n",
       "482272      3   Helicopter/Stol    1  Passenger Configuration   \n",
       "\n",
       "       origin_iata_code origin_lat origin_lon  destination_iata_code  \\\n",
       "0                   IAD     38.944    -77.456                    FLL   \n",
       "1                   IAD     38.944    -77.456                    JFK   \n",
       "2                   IAH     29.980    -95.340                    SAV   \n",
       "3                   ILM     34.271    -77.903                    RDU   \n",
       "4                   IND     39.717    -86.294                    NaN   \n",
       "...                 ...        ...        ...                    ...   \n",
       "482268              ACK     41.253    -70.060                    BOS   \n",
       "482269              BOS     42.364    -71.005                    ACK   \n",
       "482270              ACK     41.253    -70.060                    BOS   \n",
       "482271              BOS     42.364    -71.005                    ACK   \n",
       "482272              NaN        NaN        NaN                    NaN   \n",
       "\n",
       "        destination_lat  destination_lon  \n",
       "0                26.072          -80.153  \n",
       "1                40.640          -73.779  \n",
       "2                32.127          -81.202  \n",
       "3                35.877          -78.787  \n",
       "4                   NaN              NaN  \n",
       "...                 ...              ...  \n",
       "482268           42.364          -71.005  \n",
       "482269           41.253          -70.060  \n",
       "482270           42.364          -71.005  \n",
       "482271           41.253          -70.060  \n",
       "482272              NaN              NaN  \n",
       "\n",
       "[482273 rows x 62 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_flights = pd.read_csv('data\\\\routes_planes_coordinates.csv')\n",
    "df_flights.drop('Unnamed: 0', axis=1, inplace=True)\n",
    "df_flights.drop('Unnamed: 50', axis=1, inplace=True)\n",
    "df_flights\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The to_sql function easily imports these 482,273 rows and 62 columns into the SQLite database. If that column already exists, it will be replaced by the new table (due to the 'if_exists = 'replace'' argument)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_flights.to_sql('flights', con = sqlalchemy_sqlite_engine,\n",
    "if_exists = 'replace', index_label = 'index')\n",
    "# Source: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html\n",
    "# I learned about this method from 'Tennessee Leeuwenburg'\n",
    "# (https://stackoverflow.com/a/28802613/13097194)\n",
    "# Giving the index column a name makes importing this data somewhat easier."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following code block confirms that this table can in turn be read from the database back into a Pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DEPARTURES_SCHEDULED</th>\n",
       "      <th>DEPARTURES_PERFORMED</th>\n",
       "      <th>PAYLOAD</th>\n",
       "      <th>SEATS</th>\n",
       "      <th>PASSENGERS</th>\n",
       "      <th>FREIGHT</th>\n",
       "      <th>MAIL</th>\n",
       "      <th>DISTANCE</th>\n",
       "      <th>RAMP_TO_RAMP</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>...</th>\n",
       "      <th>Code_y</th>\n",
       "      <th>Plane_Group_Text</th>\n",
       "      <th>Code</th>\n",
       "      <th>Plane_Config_Text</th>\n",
       "      <th>origin_iata_code</th>\n",
       "      <th>origin_lat</th>\n",
       "      <th>origin_lon</th>\n",
       "      <th>destination_iata_code</th>\n",
       "      <th>destination_lat</th>\n",
       "      <th>destination_lon</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21502.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>901.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>Jet, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>IAD</td>\n",
       "      <td>38.944</td>\n",
       "      <td>-77.456</td>\n",
       "      <td>FLL</td>\n",
       "      <td>26.072</td>\n",
       "      <td>-80.153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>64506.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>219.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>Jet, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>IAD</td>\n",
       "      <td>38.944</td>\n",
       "      <td>-77.456</td>\n",
       "      <td>JFK</td>\n",
       "      <td>40.640</td>\n",
       "      <td>-73.779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21502.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>851.0</td>\n",
       "      <td>144.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>Jet, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>IAH</td>\n",
       "      <td>29.980</td>\n",
       "      <td>-95.340</td>\n",
       "      <td>SAV</td>\n",
       "      <td>32.127</td>\n",
       "      <td>-81.202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21502.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>Jet, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>ILM</td>\n",
       "      <td>34.271</td>\n",
       "      <td>-77.903</td>\n",
       "      <td>RDU</td>\n",
       "      <td>35.877</td>\n",
       "      <td>-78.787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12500.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>Jet, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>IND</td>\n",
       "      <td>39.717</td>\n",
       "      <td>-86.294</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482268</th>\n",
       "      <td>1166.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>1049400.0</td>\n",
       "      <td>5247.0</td>\n",
       "      <td>3646.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>27284.0</td>\n",
       "      <td>21338.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Piston, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>ACK</td>\n",
       "      <td>41.253</td>\n",
       "      <td>-70.060</td>\n",
       "      <td>BOS</td>\n",
       "      <td>42.364</td>\n",
       "      <td>-71.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482269</th>\n",
       "      <td>1188.0</td>\n",
       "      <td>594.0</td>\n",
       "      <td>1069200.0</td>\n",
       "      <td>5346.0</td>\n",
       "      <td>3573.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>27799.0</td>\n",
       "      <td>21740.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Piston, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>BOS</td>\n",
       "      <td>42.364</td>\n",
       "      <td>-71.005</td>\n",
       "      <td>ACK</td>\n",
       "      <td>41.253</td>\n",
       "      <td>-70.060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482270</th>\n",
       "      <td>1216.0</td>\n",
       "      <td>608.0</td>\n",
       "      <td>1094400.0</td>\n",
       "      <td>5472.0</td>\n",
       "      <td>3827.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>28454.0</td>\n",
       "      <td>22253.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Piston, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>ACK</td>\n",
       "      <td>41.253</td>\n",
       "      <td>-70.060</td>\n",
       "      <td>BOS</td>\n",
       "      <td>42.364</td>\n",
       "      <td>-71.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482271</th>\n",
       "      <td>1258.0</td>\n",
       "      <td>629.0</td>\n",
       "      <td>1132200.0</td>\n",
       "      <td>5661.0</td>\n",
       "      <td>4056.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>29437.0</td>\n",
       "      <td>23021.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Piston, 2-Engine</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>BOS</td>\n",
       "      <td>42.364</td>\n",
       "      <td>-71.005</td>\n",
       "      <td>ACK</td>\n",
       "      <td>41.253</td>\n",
       "      <td>-70.060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482272</th>\n",
       "      <td>2170.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>74400.0</td>\n",
       "      <td>264.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>1860.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>Helicopter/Stol</td>\n",
       "      <td>1</td>\n",
       "      <td>Passenger Configuration</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>482273 rows × 62 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        DEPARTURES_SCHEDULED  DEPARTURES_PERFORMED    PAYLOAD   SEATS  \\\n",
       "index                                                                   \n",
       "0                        0.0                   1.0    21502.0    76.0   \n",
       "1                        0.0                   3.0    64506.0   228.0   \n",
       "2                        0.0                   1.0    21502.0    76.0   \n",
       "3                        0.0                   1.0    21502.0    76.0   \n",
       "4                        0.0                   1.0    12500.0    50.0   \n",
       "...                      ...                   ...        ...     ...   \n",
       "482268                1166.0                 583.0  1049400.0  5247.0   \n",
       "482269                1188.0                 594.0  1069200.0  5346.0   \n",
       "482270                1216.0                 608.0  1094400.0  5472.0   \n",
       "482271                1258.0                 629.0  1132200.0  5661.0   \n",
       "482272                2170.0                  44.0    74400.0   264.0   \n",
       "\n",
       "        PASSENGERS  FREIGHT  MAIL  DISTANCE  RAMP_TO_RAMP  AIR_TIME  ...  \\\n",
       "index                                                                ...   \n",
       "0              3.0      0.0   0.0     901.0         170.0     140.0  ...   \n",
       "1             75.0      0.0   0.0     228.0         219.0     140.0  ...   \n",
       "2             64.0      0.0   0.0     851.0         144.0     114.0  ...   \n",
       "3             55.0      0.0   0.0     122.0          58.0      31.0  ...   \n",
       "4             34.0      0.0   0.0     133.0          49.0      29.0  ...   \n",
       "...            ...      ...   ...       ...           ...       ...  ...   \n",
       "482268      3646.0      0.0   0.0      91.0       27284.0   21338.0  ...   \n",
       "482269      3573.0      0.0   0.0      91.0       27799.0   21740.0  ...   \n",
       "482270      3827.0      0.0   0.0      91.0       28454.0   22253.0  ...   \n",
       "482271      4056.0      0.0   0.0      91.0       29437.0   23021.0  ...   \n",
       "482272         0.0      0.0  44.0      59.0        1860.0       0.0  ...   \n",
       "\n",
       "       Code_y  Plane_Group_Text Code        Plane_Config_Text  \\\n",
       "index                                                           \n",
       "0           6     Jet, 2-Engine    1  Passenger Configuration   \n",
       "1           6     Jet, 2-Engine    1  Passenger Configuration   \n",
       "2           6     Jet, 2-Engine    1  Passenger Configuration   \n",
       "3           6     Jet, 2-Engine    1  Passenger Configuration   \n",
       "4           6     Jet, 2-Engine    1  Passenger Configuration   \n",
       "...       ...               ...  ...                      ...   \n",
       "482268      1  Piston, 2-Engine    1  Passenger Configuration   \n",
       "482269      1  Piston, 2-Engine    1  Passenger Configuration   \n",
       "482270      1  Piston, 2-Engine    1  Passenger Configuration   \n",
       "482271      1  Piston, 2-Engine    1  Passenger Configuration   \n",
       "482272      3   Helicopter/Stol    1  Passenger Configuration   \n",
       "\n",
       "       origin_iata_code origin_lat origin_lon  destination_iata_code  \\\n",
       "index                                                                  \n",
       "0                   IAD     38.944    -77.456                    FLL   \n",
       "1                   IAD     38.944    -77.456                    JFK   \n",
       "2                   IAH     29.980    -95.340                    SAV   \n",
       "3                   ILM     34.271    -77.903                    RDU   \n",
       "4                   IND     39.717    -86.294                   None   \n",
       "...                 ...        ...        ...                    ...   \n",
       "482268              ACK     41.253    -70.060                    BOS   \n",
       "482269              BOS     42.364    -71.005                    ACK   \n",
       "482270              ACK     41.253    -70.060                    BOS   \n",
       "482271              BOS     42.364    -71.005                    ACK   \n",
       "482272             None        NaN        NaN                   None   \n",
       "\n",
       "        destination_lat  destination_lon  \n",
       "index                                     \n",
       "0                26.072          -80.153  \n",
       "1                40.640          -73.779  \n",
       "2                32.127          -81.202  \n",
       "3                35.877          -78.787  \n",
       "4                   NaN              NaN  \n",
       "...                 ...              ...  \n",
       "482268           42.364          -71.005  \n",
       "482269           41.253          -70.060  \n",
       "482270           42.364          -71.005  \n",
       "482271           41.253          -70.060  \n",
       "482272              NaN              NaN  \n",
       "\n",
       "[482273 rows x 62 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_flights_from_db = pd.read_sql(\"Select * from flights\", \n",
    "index_col='index', con = sqlalchemy_sqlite_engine)\n",
    "# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html\n",
    "df_flights_from_db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1b: Creating 'photos' Table\n",
    "\n",
    "To create the 'photos' and 'music' tables, I could have stored the original photo and music data within the table in binary format. However, a Stack Overflow discussion located at https://stackoverflow.com/questions/154707/what-is-the-best-way-to-store-media-files-on-a-database) led me to conclude that keeping the files within a separate storage option (such as a Google Cloud Storage bucket) made more sense. Therefore, both the 'photos' and 'music' tables will store file information rather than the files themselves.\n",
    "\n",
    "The code block below converts information about the photos in the data\\public_domain_photos_for_db folder into a DataFrame. The google_cloud_storage_bucket, cloud_storage_folder, and gcs_url_root strings are used to construct the Google Cloud Storage URL where each file could be located. (I subsequently deleted this storage bucket in order to save storage costs, so the URLs will no longer work. To replicate this approach, you will need to create your own storage bucket and upload the files there.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'list'>\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>file_name</th>\n",
       "      <th>size</th>\n",
       "      <th>created_date</th>\n",
       "      <th>modified_date</th>\n",
       "      <th>gcs_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10086848403_b33a695758_o.jpg</td>\n",
       "      <td>1027202</td>\n",
       "      <td>Sat Nov 13 11:34:49 2021</td>\n",
       "      <td>Mon Sep 13 20:44:36 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10171744985_76f8973d6b_o.jpg</td>\n",
       "      <td>160300</td>\n",
       "      <td>Sat Nov 13 11:34:49 2021</td>\n",
       "      <td>Mon Sep 13 01:42:14 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10822424126_91be9abb6d_o.jpg</td>\n",
       "      <td>402898</td>\n",
       "      <td>Sat Nov 13 11:34:49 2021</td>\n",
       "      <td>Mon Sep 13 01:42:25 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10843413524_82caa8b0f8_o.jpg</td>\n",
       "      <td>7405423</td>\n",
       "      <td>Sat Nov 13 11:34:49 2021</td>\n",
       "      <td>Mon Sep 13 01:32:56 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11309341065_6fcfbee752_o.jpg</td>\n",
       "      <td>466609</td>\n",
       "      <td>Sat Nov 13 11:34:49 2021</td>\n",
       "      <td>Mon Sep 13 20:38:00 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>S69-34316~orig.jpg</td>\n",
       "      <td>1339165</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Mon Sep 13 01:45:54 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>S71-41357~orig.jpg</td>\n",
       "      <td>2103357</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Mon Sep 13 20:48:45 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>S71-41759~orig.jpg</td>\n",
       "      <td>1470210</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Mon Sep 13 20:07:29 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>sts061-s-104~orig.jpg</td>\n",
       "      <td>2055089</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Mon Sep 13 20:58:49 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>STSCPanel.jpg</td>\n",
       "      <td>4307564</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Mon Sep 13 21:25:57 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>133 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        file_name     size              created_date  \\\n",
       "0    10086848403_b33a695758_o.jpg  1027202  Sat Nov 13 11:34:49 2021   \n",
       "1    10171744985_76f8973d6b_o.jpg   160300  Sat Nov 13 11:34:49 2021   \n",
       "2    10822424126_91be9abb6d_o.jpg   402898  Sat Nov 13 11:34:49 2021   \n",
       "3    10843413524_82caa8b0f8_o.jpg  7405423  Sat Nov 13 11:34:49 2021   \n",
       "4    11309341065_6fcfbee752_o.jpg   466609  Sat Nov 13 11:34:49 2021   \n",
       "..                            ...      ...                       ...   \n",
       "128            S69-34316~orig.jpg  1339165  Sat Nov 13 11:34:51 2021   \n",
       "129            S71-41357~orig.jpg  2103357  Sat Nov 13 11:34:51 2021   \n",
       "130            S71-41759~orig.jpg  1470210  Sat Nov 13 11:34:51 2021   \n",
       "131         sts061-s-104~orig.jpg  2055089  Sat Nov 13 11:34:51 2021   \n",
       "132                 STSCPanel.jpg  4307564  Sat Nov 13 11:34:51 2021   \n",
       "\n",
       "                modified_date  \\\n",
       "0    Mon Sep 13 20:44:36 2021   \n",
       "1    Mon Sep 13 01:42:14 2021   \n",
       "2    Mon Sep 13 01:42:25 2021   \n",
       "3    Mon Sep 13 01:32:56 2021   \n",
       "4    Mon Sep 13 20:38:00 2021   \n",
       "..                        ...   \n",
       "128  Mon Sep 13 01:45:54 2021   \n",
       "129  Mon Sep 13 20:48:45 2021   \n",
       "130  Mon Sep 13 20:07:29 2021   \n",
       "131  Mon Sep 13 20:58:49 2021   \n",
       "132  Mon Sep 13 21:25:57 2021   \n",
       "\n",
       "                                               gcs_url  \n",
       "0    https://storage.googleapis.com/kb_sample_datab...  \n",
       "1    https://storage.googleapis.com/kb_sample_datab...  \n",
       "2    https://storage.googleapis.com/kb_sample_datab...  \n",
       "3    https://storage.googleapis.com/kb_sample_datab...  \n",
       "4    https://storage.googleapis.com/kb_sample_datab...  \n",
       "..                                                 ...  \n",
       "128  https://storage.googleapis.com/kb_sample_datab...  \n",
       "129  https://storage.googleapis.com/kb_sample_datab...  \n",
       "130  https://storage.googleapis.com/kb_sample_datab...  \n",
       "131  https://storage.googleapis.com/kb_sample_datab...  \n",
       "132  https://storage.googleapis.com/kb_sample_datab...  \n",
       "\n",
       "[133 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "google_cloud_storage_bucket = 'kb_sample_database' # The name (within the \n",
    "# Google URL) where image data will be stored\n",
    "cloud_storage_folder = 'pub_domain_images'\n",
    "gcs_url_root = 'https://storage.googleapis.com'\n",
    "photo_location = \"data\\\\public_domain_photos_for_db\\\\\" \n",
    "photo_list = os.listdir(photo_location)\n",
    "print(type(photo_list))\n",
    "# print(photo_list)\n",
    "photo_dict_list = []\n",
    "for photo_path in photo_list:\n",
    "    full_path = photo_location + photo_path        \n",
    "    stat_file = os.stat(full_path)\n",
    "    gcs_url = (gcs_url_root+'/'+google_cloud_storage_bucket+'/'\n",
    "    + cloud_storage_folder + '/' + photo_path)\n",
    "    # Public Google Cloud Storage URL\n",
    "    # print(type(test_photo)) # The class is '_io.BufferedReader'\n",
    "    # print(type(time.ctime(stat_file.st_ctime))) # These are stored as strings\n",
    "    photo_dict_list.append({'file_name': photo_path, 'size': stat_file.st_size,\n",
    "    'created_date': time.ctime(stat_file.st_ctime),\n",
    "    'modified_date': time.ctime(stat_file.st_mtime),\n",
    "    'gcs_url': gcs_url}) \n",
    "df_photos = pd.DataFrame(photo_dict_list)\n",
    "df_photos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As with the 'flights' table, to_sql will be used to import the photos DataFrame into the SQLite database, and read_sql will be used to ensure that the information was imported successfully."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_photos.to_sql('photos', con = sqlalchemy_sqlite_engine,\n",
    "if_exists = 'replace', index_label = 'index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>file_name</th>\n",
       "      <th>size</th>\n",
       "      <th>created_date</th>\n",
       "      <th>modified_date</th>\n",
       "      <th>gcs_url</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10086848403_b33a695758_o.jpg</td>\n",
       "      <td>1027202</td>\n",
       "      <td>Sat Nov 13 11:34:49 2021</td>\n",
       "      <td>Mon Sep 13 20:44:36 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10171744985_76f8973d6b_o.jpg</td>\n",
       "      <td>160300</td>\n",
       "      <td>Sat Nov 13 11:34:49 2021</td>\n",
       "      <td>Mon Sep 13 01:42:14 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10822424126_91be9abb6d_o.jpg</td>\n",
       "      <td>402898</td>\n",
       "      <td>Sat Nov 13 11:34:49 2021</td>\n",
       "      <td>Mon Sep 13 01:42:25 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10843413524_82caa8b0f8_o.jpg</td>\n",
       "      <td>7405423</td>\n",
       "      <td>Sat Nov 13 11:34:49 2021</td>\n",
       "      <td>Mon Sep 13 01:32:56 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11309341065_6fcfbee752_o.jpg</td>\n",
       "      <td>466609</td>\n",
       "      <td>Sat Nov 13 11:34:49 2021</td>\n",
       "      <td>Mon Sep 13 20:38:00 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>S69-34316~orig.jpg</td>\n",
       "      <td>1339165</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Mon Sep 13 01:45:54 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>S71-41357~orig.jpg</td>\n",
       "      <td>2103357</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Mon Sep 13 20:48:45 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>S71-41759~orig.jpg</td>\n",
       "      <td>1470210</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Mon Sep 13 20:07:29 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>sts061-s-104~orig.jpg</td>\n",
       "      <td>2055089</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Mon Sep 13 20:58:49 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>STSCPanel.jpg</td>\n",
       "      <td>4307564</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Mon Sep 13 21:25:57 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>133 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          file_name     size              created_date  \\\n",
       "index                                                                    \n",
       "0      10086848403_b33a695758_o.jpg  1027202  Sat Nov 13 11:34:49 2021   \n",
       "1      10171744985_76f8973d6b_o.jpg   160300  Sat Nov 13 11:34:49 2021   \n",
       "2      10822424126_91be9abb6d_o.jpg   402898  Sat Nov 13 11:34:49 2021   \n",
       "3      10843413524_82caa8b0f8_o.jpg  7405423  Sat Nov 13 11:34:49 2021   \n",
       "4      11309341065_6fcfbee752_o.jpg   466609  Sat Nov 13 11:34:49 2021   \n",
       "...                             ...      ...                       ...   \n",
       "128              S69-34316~orig.jpg  1339165  Sat Nov 13 11:34:51 2021   \n",
       "129              S71-41357~orig.jpg  2103357  Sat Nov 13 11:34:51 2021   \n",
       "130              S71-41759~orig.jpg  1470210  Sat Nov 13 11:34:51 2021   \n",
       "131           sts061-s-104~orig.jpg  2055089  Sat Nov 13 11:34:51 2021   \n",
       "132                   STSCPanel.jpg  4307564  Sat Nov 13 11:34:51 2021   \n",
       "\n",
       "                  modified_date  \\\n",
       "index                             \n",
       "0      Mon Sep 13 20:44:36 2021   \n",
       "1      Mon Sep 13 01:42:14 2021   \n",
       "2      Mon Sep 13 01:42:25 2021   \n",
       "3      Mon Sep 13 01:32:56 2021   \n",
       "4      Mon Sep 13 20:38:00 2021   \n",
       "...                         ...   \n",
       "128    Mon Sep 13 01:45:54 2021   \n",
       "129    Mon Sep 13 20:48:45 2021   \n",
       "130    Mon Sep 13 20:07:29 2021   \n",
       "131    Mon Sep 13 20:58:49 2021   \n",
       "132    Mon Sep 13 21:25:57 2021   \n",
       "\n",
       "                                                 gcs_url  \n",
       "index                                                     \n",
       "0      https://storage.googleapis.com/kb_sample_datab...  \n",
       "1      https://storage.googleapis.com/kb_sample_datab...  \n",
       "2      https://storage.googleapis.com/kb_sample_datab...  \n",
       "3      https://storage.googleapis.com/kb_sample_datab...  \n",
       "4      https://storage.googleapis.com/kb_sample_datab...  \n",
       "...                                                  ...  \n",
       "128    https://storage.googleapis.com/kb_sample_datab...  \n",
       "129    https://storage.googleapis.com/kb_sample_datab...  \n",
       "130    https://storage.googleapis.com/kb_sample_datab...  \n",
       "131    https://storage.googleapis.com/kb_sample_datab...  \n",
       "132    https://storage.googleapis.com/kb_sample_datab...  \n",
       "\n",
       "[133 rows x 5 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_photos_from_db = pd.read_sql(\"Select * from photos\",\n",
    "index_col='index', con = sqlalchemy_sqlite_engine)\n",
    "df_photos_from_db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1c: Creating the 'steps' Table\n",
    "\n",
    "I obtained the steps data from my online Fitbit account as a series of .json files, then consolidated these files into a single .json file using a separate program I created (https://github.com/kburchfiel/fitbit_steps_and_whatpulse_keypresses_comparison/blob/master/fitbit_steps_reader_v2.ipynb). The below code block converts this .json file into a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_steps = pd.io.json.read_json('data\\\\combined_steps_file.json')\n",
    "# Took 34.6 seconds to run\n",
    "# See https://pandas.pydata.org/docs/reference/api/pandas.io.json.read_json.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dateTime</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-20 19:05:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-20 19:38:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-20 19:39:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-20 19:40:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-20 19:41:00</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529251</th>\n",
       "      <td>2021-09-16 18:26:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529252</th>\n",
       "      <td>2021-09-16 18:27:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529253</th>\n",
       "      <td>2021-09-16 18:28:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529254</th>\n",
       "      <td>2021-09-16 18:29:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529255</th>\n",
       "      <td>2021-09-16 18:30:00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>529256 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  dateTime  value\n",
       "0      2020-01-20 19:05:00      0\n",
       "1      2020-01-20 19:38:00      0\n",
       "2      2020-01-20 19:39:00      0\n",
       "3      2020-01-20 19:40:00      0\n",
       "4      2020-01-20 19:41:00     61\n",
       "...                    ...    ...\n",
       "529251 2021-09-16 18:26:00      0\n",
       "529252 2021-09-16 18:27:00      0\n",
       "529253 2021-09-16 18:28:00      0\n",
       "529254 2021-09-16 18:29:00      0\n",
       "529255 2021-09-16 18:30:00      0\n",
       "\n",
       "[529256 rows x 2 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_steps"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'll once again use to_sql and read_sql to import this DataFrame into my SQLite database, then read it back into a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_steps.to_sql('steps', con = sqlalchemy_sqlite_engine, if_exists = 'replace',\n",
    "index_label = 'index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dateTime</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-20 19:05:00.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-20 19:38:00.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-20 19:39:00.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-20 19:40:00.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-20 19:41:00.000000</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529251</th>\n",
       "      <td>2021-09-16 18:26:00.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529252</th>\n",
       "      <td>2021-09-16 18:27:00.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529253</th>\n",
       "      <td>2021-09-16 18:28:00.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529254</th>\n",
       "      <td>2021-09-16 18:29:00.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529255</th>\n",
       "      <td>2021-09-16 18:30:00.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>529256 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          dateTime  value\n",
       "index                                    \n",
       "0       2020-01-20 19:05:00.000000      0\n",
       "1       2020-01-20 19:38:00.000000      0\n",
       "2       2020-01-20 19:39:00.000000      0\n",
       "3       2020-01-20 19:40:00.000000      0\n",
       "4       2020-01-20 19:41:00.000000     61\n",
       "...                            ...    ...\n",
       "529251  2021-09-16 18:26:00.000000      0\n",
       "529252  2021-09-16 18:27:00.000000      0\n",
       "529253  2021-09-16 18:28:00.000000      0\n",
       "529254  2021-09-16 18:29:00.000000      0\n",
       "529255  2021-09-16 18:30:00.000000      0\n",
       "\n",
       "[529256 rows x 2 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_steps_from_db = pd.read_sql(\"Select * from steps\",\n",
    "index_col='index', con = sqlalchemy_sqlite_engine)\n",
    "df_steps_from_db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1d: Creating 'music' Table\n",
    "\n",
    "The 'music' table is created using the same method that I used to construct the 'photos' table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>music_file_name</th>\n",
       "      <th>music_size</th>\n",
       "      <th>music_created_date</th>\n",
       "      <th>music_modified_date</th>\n",
       "      <th>music_gcs_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sample_0.mp3</td>\n",
       "      <td>94391</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:32:27 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sample_1.mp3</td>\n",
       "      <td>114244</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:40:16 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sample_10.mp3</td>\n",
       "      <td>73493</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:14 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sample_11.mp3</td>\n",
       "      <td>94391</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:18 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sample_12.mp3</td>\n",
       "      <td>64088</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:22 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>sample_62.mp3</td>\n",
       "      <td>73493</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:38:15 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>sample_63.mp3</td>\n",
       "      <td>103795</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:38:19 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>sample_7.mp3</td>\n",
       "      <td>64088</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:03 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>sample_8.mp3</td>\n",
       "      <td>73493</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:06 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>sample_9.mp3</td>\n",
       "      <td>64088</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:10 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>64 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   music_file_name  music_size        music_created_date  \\\n",
       "0     sample_0.mp3       94391  Sat Nov 13 11:34:51 2021   \n",
       "1     sample_1.mp3      114244  Sat Nov 13 11:34:51 2021   \n",
       "2    sample_10.mp3       73493  Sat Nov 13 11:34:51 2021   \n",
       "3    sample_11.mp3       94391  Sat Nov 13 11:34:51 2021   \n",
       "4    sample_12.mp3       64088  Sat Nov 13 11:34:51 2021   \n",
       "..             ...         ...                       ...   \n",
       "59   sample_62.mp3       73493  Sat Nov 13 11:34:51 2021   \n",
       "60   sample_63.mp3      103795  Sat Nov 13 11:34:51 2021   \n",
       "61    sample_7.mp3       64088  Sat Nov 13 11:34:51 2021   \n",
       "62    sample_8.mp3       73493  Sat Nov 13 11:34:51 2021   \n",
       "63    sample_9.mp3       64088  Sat Nov 13 11:34:51 2021   \n",
       "\n",
       "         music_modified_date  \\\n",
       "0   Thu Sep 16 15:32:27 2021   \n",
       "1   Thu Sep 16 15:40:16 2021   \n",
       "2   Thu Sep 16 15:34:14 2021   \n",
       "3   Thu Sep 16 15:34:18 2021   \n",
       "4   Thu Sep 16 15:34:22 2021   \n",
       "..                       ...   \n",
       "59  Thu Sep 16 15:38:15 2021   \n",
       "60  Thu Sep 16 15:38:19 2021   \n",
       "61  Thu Sep 16 15:34:03 2021   \n",
       "62  Thu Sep 16 15:34:06 2021   \n",
       "63  Thu Sep 16 15:34:10 2021   \n",
       "\n",
       "                                        music_gcs_url  \n",
       "0   https://storage.googleapis.com/kb_sample_datab...  \n",
       "1   https://storage.googleapis.com/kb_sample_datab...  \n",
       "2   https://storage.googleapis.com/kb_sample_datab...  \n",
       "3   https://storage.googleapis.com/kb_sample_datab...  \n",
       "4   https://storage.googleapis.com/kb_sample_datab...  \n",
       "..                                                ...  \n",
       "59  https://storage.googleapis.com/kb_sample_datab...  \n",
       "60  https://storage.googleapis.com/kb_sample_datab...  \n",
       "61  https://storage.googleapis.com/kb_sample_datab...  \n",
       "62  https://storage.googleapis.com/kb_sample_datab...  \n",
       "63  https://storage.googleapis.com/kb_sample_datab...  \n",
       "\n",
       "[64 rows x 5 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "google_cloud_storage_bucket = 'kb_sample_database' # The name (within the \n",
    "# Google URL) where music data will be stored\n",
    "cloud_storage_folder = 'pub_domain_music'\n",
    "gcs_url_root = 'https://storage.googleapis.com'\n",
    "music_location = \"data\\\\public_domain_music\\\\\" \n",
    "music_list = os.listdir(music_location)\n",
    "\n",
    "music_dict_list = []\n",
    "for music_path in music_list:\n",
    "    full_path = music_location + music_path        \n",
    "    stat_file = os.stat(full_path)\n",
    "    gcs_url = (gcs_url_root+'/'+google_cloud_storage_bucket+'/'\n",
    "    + cloud_storage_folder + '/' + music_path)\n",
    "    # Public Google Cloud Storage URL\n",
    "    music_dict_list.append({'music_file_name': music_path,\n",
    "    'music_size': stat_file.st_size,\n",
    "    'music_created_date': time.ctime(stat_file.st_ctime),\n",
    "    'music_modified_date': time.ctime(stat_file.st_mtime),\n",
    "    'music_gcs_url': gcs_url}) \n",
    "df_music = pd.DataFrame(music_dict_list)\n",
    "df_music"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "to_sql and read_sql are used once again to import data into SQLite and ensure it can be read back into a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_music.to_sql('music', con = sqlalchemy_sqlite_engine,\n",
    "if_exists = 'replace', index_label = 'index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>music_file_name</th>\n",
       "      <th>music_size</th>\n",
       "      <th>music_created_date</th>\n",
       "      <th>music_modified_date</th>\n",
       "      <th>music_gcs_url</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sample_0.mp3</td>\n",
       "      <td>94391</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:32:27 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sample_1.mp3</td>\n",
       "      <td>114244</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:40:16 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sample_10.mp3</td>\n",
       "      <td>73493</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:14 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sample_11.mp3</td>\n",
       "      <td>94391</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:18 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sample_12.mp3</td>\n",
       "      <td>64088</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:22 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>sample_62.mp3</td>\n",
       "      <td>73493</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:38:15 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>sample_63.mp3</td>\n",
       "      <td>103795</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:38:19 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>sample_7.mp3</td>\n",
       "      <td>64088</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:03 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>sample_8.mp3</td>\n",
       "      <td>73493</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:06 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>sample_9.mp3</td>\n",
       "      <td>64088</td>\n",
       "      <td>Sat Nov 13 11:34:51 2021</td>\n",
       "      <td>Thu Sep 16 15:34:10 2021</td>\n",
       "      <td>https://storage.googleapis.com/kb_sample_datab...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>64 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      music_file_name  music_size        music_created_date  \\\n",
       "index                                                         \n",
       "0        sample_0.mp3       94391  Sat Nov 13 11:34:51 2021   \n",
       "1        sample_1.mp3      114244  Sat Nov 13 11:34:51 2021   \n",
       "2       sample_10.mp3       73493  Sat Nov 13 11:34:51 2021   \n",
       "3       sample_11.mp3       94391  Sat Nov 13 11:34:51 2021   \n",
       "4       sample_12.mp3       64088  Sat Nov 13 11:34:51 2021   \n",
       "...               ...         ...                       ...   \n",
       "59      sample_62.mp3       73493  Sat Nov 13 11:34:51 2021   \n",
       "60      sample_63.mp3      103795  Sat Nov 13 11:34:51 2021   \n",
       "61       sample_7.mp3       64088  Sat Nov 13 11:34:51 2021   \n",
       "62       sample_8.mp3       73493  Sat Nov 13 11:34:51 2021   \n",
       "63       sample_9.mp3       64088  Sat Nov 13 11:34:51 2021   \n",
       "\n",
       "            music_modified_date  \\\n",
       "index                             \n",
       "0      Thu Sep 16 15:32:27 2021   \n",
       "1      Thu Sep 16 15:40:16 2021   \n",
       "2      Thu Sep 16 15:34:14 2021   \n",
       "3      Thu Sep 16 15:34:18 2021   \n",
       "4      Thu Sep 16 15:34:22 2021   \n",
       "...                         ...   \n",
       "59     Thu Sep 16 15:38:15 2021   \n",
       "60     Thu Sep 16 15:38:19 2021   \n",
       "61     Thu Sep 16 15:34:03 2021   \n",
       "62     Thu Sep 16 15:34:06 2021   \n",
       "63     Thu Sep 16 15:34:10 2021   \n",
       "\n",
       "                                           music_gcs_url  \n",
       "index                                                     \n",
       "0      https://storage.googleapis.com/kb_sample_datab...  \n",
       "1      https://storage.googleapis.com/kb_sample_datab...  \n",
       "2      https://storage.googleapis.com/kb_sample_datab...  \n",
       "3      https://storage.googleapis.com/kb_sample_datab...  \n",
       "4      https://storage.googleapis.com/kb_sample_datab...  \n",
       "...                                                  ...  \n",
       "59     https://storage.googleapis.com/kb_sample_datab...  \n",
       "60     https://storage.googleapis.com/kb_sample_datab...  \n",
       "61     https://storage.googleapis.com/kb_sample_datab...  \n",
       "62     https://storage.googleapis.com/kb_sample_datab...  \n",
       "63     https://storage.googleapis.com/kb_sample_datab...  \n",
       "\n",
       "[64 rows x 5 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_music_from_db = pd.read_sql(\"Select * from music\",\n",
    "index_col='index', con = sqlalchemy_sqlite_engine)\n",
    "df_music_from_db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the SQLite database has been created, I can import its tables into various online databases (Amazon Web Services, Google Cloud Platform, Azure, etc.) within the database_uploader.ipynb file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Completed run at Mon Nov 22 11:49:01 2021 (local time)\n",
      "Total run time: 76.99 second(s) (1.0 minute(s) and 16.99 second(s))\n"
     ]
    }
   ],
   "source": [
    "end_time = time.time()\n",
    "run_time = end_time - start_time\n",
    "run_minutes = run_time // 60\n",
    "run_seconds = run_time % 60\n",
    "print(\"Completed run at\",time.ctime(end_time),\"(local time)\")\n",
    "print(\"Total run time:\",'{:.2f}'.format(run_time),\n",
    "\"second(s) (\"+str(run_minutes),\"minute(s) and\",'{:.2f}'.format(run_seconds),\n",
    "\"second(s))\") \n",
    "# Only meaningful when the program is run nonstop from start to finish"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "7a016a462c2a18f177a2554c94a9e2d8b52c0ae6e623363f8138b3d5b2656021"
  },
  "kernelspec": {
   "display_name": "Python 3.9.5 64-bit ('ga15pyd': conda)",
   "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.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}