JarrydAllison_Final_Project_Food_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "#  CSCI 3287  -- Final Project\n",
    "    \n",
    "## FDA Food Database Exploration and Application"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "Name: Jarryd Allison\n",
    "\n",
    "Email: allisonj@colorado.edu\n",
    "\n",
    "GitHub ID: jarrydallison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import csv\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove the food.db file before running the rest of this notebook\n",
    "try:\n",
    "    os.remove('food.db')\n",
    "except:\n",
    "    print(\"food.db doesn't exist yet, skipping\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Background\n",
    "The rapid and pervasive onset of obesity in America is confusing given the massive amount of\n",
    "data available to make wise food choices. Many food items used when grocery shopping are\n",
    "not necessarily required to have nutrition information, to include things like fresh produce, and\n",
    "the combination of various food items could yield important information, such as aggregated\n",
    "nutritional values of a shopping list. The data set and queries involved in this product would be\n",
    "available to consumers to up ideally in a mobile application, but for the purposes of this project\n",
    "we will assume that users generally know SQL, or, given enough time, can interact with this\n",
    "database using a rudimentary frontend such as a Jupyter Notebook to query various food items and\n",
    "return information about an individual or multiple items."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "## Food Database Tables\n",
    "\n",
    "This project uses data from the FDA Food Data Central, a free database based on FDA research that allows users to access a large amount of nutrient information. To begin, we will create five tables from the downloaded csv's contained within this project. You can download various CSV versions of the database on the [FDA Website](https://fdc.nal.usda.gov/download-datasets.html)\n",
    "\n",
    "<hr>\n",
    "\n",
    "To fill the tables, we will define a few functions and constants in order to streamline the fills and save space later on in the project."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The file for our database\n",
    "database = 'food.db'\n",
    "# The local directory where our csvs are stored\n",
    "csvDirectory = 'FoodData_Central_foundation_food_csv_2023-10-26'\n",
    "\n",
    "# Function to read data from the supplied CSV filename\n",
    "def readCSV(file):\n",
    "  with open(file) as f:\n",
    "    reader = csv.reader(f)\n",
    "    data = list(reader)\n",
    "  return data\n",
    "\n",
    "# Function to create the tables. The individual creation statements\n",
    "# are supplied as strings to this function.\n",
    "def generateDB(createTable: str, csvFileName: str, insertTable: str):\n",
    "  # Create the initial database connection\n",
    "  conn = sqlite3.connect(database)\n",
    "  # Create a cursor object\n",
    "  c = conn.cursor()\n",
    "  c.execute('PRAGMA foreign_keys=on;')\n",
    "  # Create the database\n",
    "  c.execute(createTable)\n",
    "\n",
    "  # Fill the database\n",
    "  data = readCSV(csvDirectory+\"/\"+csvFileName)\n",
    "  count = 0\n",
    "  for row in data:\n",
    "    # Omit the table headers from each CSV\n",
    "    if (count > 0):\n",
    "      c.execute(insertTable, row)\n",
    "    count += 1\n",
    "  # Commit the changes\n",
    "  conn.commit()\n",
    "  conn.close()\n",
    "\n",
    "# Function to run a query\n",
    "def runQuery(query: str):\n",
    "  conn = sqlite3.connect(database)\n",
    "  c = conn.cursor()\n",
    "  c.execute('PRAGMA foreign_keys=on;')\n",
    "  result = c.execute(query).fetchall()\n",
    "  conn.commit()\n",
    "  conn.close()\n",
    "  return result\n",
    "\n",
    "# Function to drop a table\n",
    "def dropTable(tableName: str):\n",
    "  conn = sqlite3.connect(database)\n",
    "  c = conn.cursor()\n",
    "  c.execute('PRAGMA foreign_keys=on;')\n",
    "  result = c.execute('DROP TABLE IF EXISTS '+tableName+';')\n",
    "  conn.commit()\n",
    "  conn.close()\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "### The Food Table\n",
    "\n",
    "This is our base table, created from the food.csv available at the [FDA Website](https://fdc.nal.usda.gov/download-datasets.html). Our tables are currently stored in csv form, so we will use python to convert the csv's into SQL tables, using sqlite3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Create the food table with the following commands\n",
    "# Create table statement\n",
    "createFoodTable = '''\n",
    "  CREATE TABLE IF NOT EXISTS food (\n",
    "          fdc_id INT PRIMARY KEY,\n",
    "          data_type VARCHAR(30),\n",
    "          description VARCHAR(40),\n",
    "          food_category_id INT NOT NULL,\n",
    "          publication_date DATE\n",
    "  );\n",
    "'''\n",
    "# The csv holding the food information\n",
    "foodCSV = 'food.csv'\n",
    "# The insertion statement\n",
    "foodInsertTable = \"INSERT INTO food VALUES (?, ?, ?, ?, ?)\"\n",
    "# Drop the table if it exists\n",
    "dropTable('food')\n",
    "# Now, use our generate function to create the table\n",
    "generateDB(createFoodTable, foodCSV, foodInsertTable)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Items:  60432\n",
      "First 5 items:\n",
      "fdc_id     data_type            description                    food_category_id     publication_date\n",
      "319874     sample_food          HUMMUS, SABRA CLASSIC          16                   2019-04-01\n",
      "319875     market_acquisition   HUMMUS, SABRA CLASSIC          16                   2019-04-01\n",
      "319876     market_acquisition   HUMMUS, SABRA CLASSIC          16                   2019-04-01\n",
      "319877     sub_sample_food      Hummus                         16                   2019-04-01\n",
      "319878     sub_sample_food      Hummus                         16                   2019-04-01\n"
     ]
    }
   ],
   "source": [
    "# Now, let's look at the unfiltered database\n",
    "print('Total Items: ', runQuery('SELECT COUNT(*) FROM food;')[0][0])\n",
    "print('First 5 items:')\n",
    "result = runQuery('SELECT * FROM food LIMIT 5;')\n",
    "formatted_row = '{:<10} {:<20} {:<30} {:<20} {:<10}'\n",
    "print(formatted_row.format(\"fdc_id\", \"data_type\", \"description\", \"food_category_id\", \"publication_date\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, the data is a bit messy. Even in this small sample, we can see hummus multiple times, which for some people is simply too much hummus! This dataset is split into different types of food data types. Let's look at 'foundation_food', which comprises core foundation food items from which other foodstuffs are made."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Items:  336\n",
      "First 5 items:\n",
      "fdc_id     data_type          description                                                                 food_category_id     publication_date\n",
      "321358     foundation_food    Hummus, commercial                                                          16                   2019-04-01\n",
      "321359     foundation_food    Milk, reduced fat, fluid, 2% milkfat, with added vitamin A and vitamin D    1                    2019-04-01\n",
      "321360     foundation_food    Tomatoes, grape, raw                                                        11                   2019-04-01\n",
      "321505     foundation_food    Salt, table, iodized                                                        2                    2019-04-01\n",
      "321611     foundation_food    Beans, snap, green, canned, regular pack, drained solids                    11                   2019-04-01\n"
     ]
    }
   ],
   "source": [
    "print('Total Items: ', runQuery('SELECT COUNT(*) FROM (SELECT * FROM food WHERE data_type = \"foundation_food\");')[0][0])\n",
    "print('First 5 items:')\n",
    "result = runQuery('SELECT * FROM food WHERE data_type = \"foundation_food\" LIMIT 5;')\n",
    "formatted_row = '{:<10} {:<18} {:<75} {:<20} {:<10}'\n",
    "print(formatted_row.format(\"fdc_id\", \"data_type\", \"description\", \"food_category_id\", \"publication_date\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "### More Tables\n",
    "\n",
    "This project focuses on data relationship between multiple tables in the FDA database to draw insights into food data that might be relevant for consumers. Here, we will use our previous functions to create the following tables:\n",
    "- nutrient table\n",
    "- portion Table\n",
    "- nutrient_conversion_factor Table\n",
    "- calorie_conversion_factor Table\n",
    "\n",
    "Each of these will allow us, through various queries, to draw conclusions and relationships between each selected food item."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Items:  140824\n",
      "First 5 items:\n",
      "id         fdc_id     nutrient_id     amount     data_points     derivation_id\n",
      "2201847    319877     1051            56.3       1               1         \n",
      "2201845    319877     1002            1.28       1               1         \n",
      "2201846    319877     1004            19.0       1               1         \n",
      "2201844    319877     1007            1.98       1               1         \n",
      "2201852    319878     1091            188.0      1               1         \n"
     ]
    }
   ],
   "source": [
    "## Create the nutrient table\n",
    "## Note the fdc_id Foreign Key that aligns with our food table. The index is provided by a nutrient id\n",
    "createNutrientTable = '''\n",
    "  CREATE TABLE IF NOT EXISTS nutrient (\n",
    "          id INT PRIMARY KEY,\n",
    "          fdc_id INT,\n",
    "          nutrient_id INT,\n",
    "          amount REAL,\n",
    "          data_points INT,\n",
    "          derivation_id INT,\n",
    "          min INT,\n",
    "          max INT,\n",
    "          median INT,\n",
    "          footnote VARCHAR(40),\n",
    "          min_year_acquired DATE,\n",
    "          FOREIGN KEY (fdc_id) REFERENCES food(fdc_id)\n",
    "  );\n",
    "'''\n",
    "nutrientCSV = 'food_nutrient.csv'\n",
    "nutrientInsertTable = \"INSERT INTO nutrient VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\"\n",
    "dropTable('nutrient')\n",
    "generateDB(createNutrientTable, nutrientCSV, nutrientInsertTable)\n",
    "print('Total Items: ', runQuery('SELECT COUNT(*) FROM nutrient;')[0][0])\n",
    "print('First 5 items:')\n",
    "result = runQuery('SELECT * FROM nutrient LIMIT 5;')\n",
    "formatted_row = '{:<10} {:<10} {:<15} {:<10} {:<15} {:<10}'\n",
    "print(formatted_row.format(\"id\", \"fdc_id\", \"nutrient_id\", \"amount\", \"data_points\", \"derivation_id\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Items:  10678\n",
      "First 5 items:\n",
      "id         fdc_id     seq_num    amount     measure_unit_id      portion_description  modifier   gram_weight         \n",
      "118702     319875                2.0        1001                                                 35.8                \n",
      "118703     319880                2.0        1001                                                 36.2                \n",
      "118704     319881                2.0        1001                                                 33.4                \n",
      "118705     319886                2.0        1001                                                 33.2                \n",
      "118706     319889                2.0        1001                                                 34.6                \n"
     ]
    }
   ],
   "source": [
    "# portion table\n",
    "## Note the fdc_id Foreign Key that aligns with our food table. The index is provided by a portion id\n",
    "createPortionTable = '''\n",
    "  CREATE TABLE IF NOT EXISTS portion (\n",
    "          id INT PRIMARY KEY,\n",
    "          fdc_id INT,\n",
    "          seq_num INT,\n",
    "          amount REAL NOT NULL,\n",
    "          measure_unit_id INT NOT NULL,\n",
    "          portion_description VARCHAR(10),\n",
    "          modifier VARCHAR(50),\n",
    "          gram_weight REAL NOT NULL,\n",
    "          data_points INT,\n",
    "          footnote VARCHAR(40),\n",
    "          min_year_acquired DATE,\n",
    "          FOREIGN KEY (fdc_id) REFERENCES food(fdc_id)\n",
    "  );\n",
    "'''\n",
    "portionCSV = 'food_portion.csv'\n",
    "portionInsertTable = \"INSERT INTO portion VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\"\n",
    "dropTable('portion')\n",
    "generateDB(createPortionTable, portionCSV, portionInsertTable)\n",
    "print('Total Items: ', runQuery('SELECT COUNT(*) FROM portion;')[0][0])\n",
    "print('First 5 items:')\n",
    "result = runQuery('SELECT * FROM portion LIMIT 5;')\n",
    "formatted_row = '{:<10} {:<10} {:<10} {:<10} {:<20} {:<20} {:<10} {:<20}'\n",
    "print(formatted_row.format(\"id\", \"fdc_id\", \"seq_num\", \"amount\", \"measure_unit_id\", \"portion_description\", \"modifier\", \"gram_weight\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Items:  11326\n",
      "First 5 items:\n",
      "id         fdc_id    \n",
      "22721      746952    \n",
      "22722      747429    \n",
      "22723      747693    \n",
      "22724      749420    \n",
      "22725      789890    \n"
     ]
    }
   ],
   "source": [
    "# nutrient_conversion_factor Table\n",
    "## Note the fdc_id Foreign Key that aligns with our food table. The index is provided by a nutrient_conversion_factor id\n",
    "## Note that not all fdc_ids here exist in food, so maybe create a tirgger for that\n",
    "createNutrientConversionFactorTable = '''\n",
    "  CREATE TABLE IF NOT EXISTS nutrient_conversion_factor (\n",
    "          id INT PRIMARY KEY,\n",
    "          fdc_id NOT NULL\n",
    "  );\n",
    "'''\n",
    "nutrientConversionFactorCSV = 'food_nutrient_conversion_factor.csv'\n",
    "nutrientConversionFactorInsertTable = \"INSERT INTO nutrient_conversion_factor VALUES (?, ?)\"\n",
    "dropTable('nutrient_conversion_factor')\n",
    "generateDB(createNutrientConversionFactorTable, nutrientConversionFactorCSV, nutrientConversionFactorInsertTable)\n",
    "print('Total Items: ', runQuery('SELECT COUNT(*) FROM nutrient_conversion_factor;')[0][0])\n",
    "print('First 5 items:')\n",
    "result = runQuery('SELECT * FROM nutrient_conversion_factor LIMIT 5;')\n",
    "formatted_row = '{:<10} {:<10}'\n",
    "print(formatted_row.format(\"id\", \"fdc_id\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Items:  290\n",
      "First 5 items:\n",
      "food_nutrient_conversion_factor_id       protein_value   fat_value       carbohydrate_value\n",
      "22503                                    3.47            8.37            4.07           \n",
      "22505                                    4.27            8.79            3.87           \n",
      "22507                                    2.44            8.37            3.57           \n",
      "22510                                    2.44            8.37            3.57           \n",
      "22512                                    2.44            8.37            3.57           \n"
     ]
    }
   ],
   "source": [
    "# calorie_conversion_factor Table\n",
    "## Note the nutrient_conversion_factor Foreign Key that aligns with our nutrient_conversion_factor table.\n",
    "## The index is provided by a nutrient id\n",
    "createCalorieConversionFactorTable = '''\n",
    "  CREATE TABLE IF NOT EXISTS calorie_conversion_factor (\n",
    "      food_nutrient_conversion_factor_id INT PRIMARY KEY,\n",
    "      protein_value REAL NOT NULL,\n",
    "      fat_value REAL NOT NULL,\n",
    "      carbohydrate_value REAL NOT NULL,\n",
    "      FOREIGN KEY (food_nutrient_conversion_factor_id) REFERENCES nutrient_conversion_factor(id)\n",
    "  );\n",
    "'''\n",
    "calorieConversionFactorCSV = 'food_calorie_conversion_factor.csv'\n",
    "calorieConversionFactorInsertTable = \"INSERT INTO calorie_conversion_factor VALUES (?, ?, ?, ?)\"\n",
    "dropTable('calorie_conversion_factor')\n",
    "generateDB(createCalorieConversionFactorTable, calorieConversionFactorCSV, calorieConversionFactorInsertTable)\n",
    "print('Total Items: ', runQuery('SELECT COUNT(*) FROM calorie_conversion_factor;')[0][0])\n",
    "print('First 5 items:')\n",
    "result = runQuery('SELECT * FROM calorie_conversion_factor LIMIT 5;')\n",
    "formatted_row = '{:<40} {:<15} {:<15} {:<15}'\n",
    "print(formatted_row.format(\"food_nutrient_conversion_factor_id\", \"protein_value\", \"fat_value\", \"carbohydrate_value\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Items:  477\n",
      "First 5 items:\n",
      "id         name                                     unit_name       nutrient_nbr    rank           \n",
      "2047       Energy (Atwater General Factors)         KCAL            957             280.0          \n",
      "2048       Energy (Atwater Specific Factors)        KCAL            958             290.0          \n",
      "1001       Solids                                   G               201             200.0          \n",
      "1002       Nitrogen                                 G               202             500.0          \n",
      "1003       Protein                                  G               203             600.0          \n"
     ]
    }
   ],
   "source": [
    "# Nutrient Definitions Table\n",
    "# This will display the types of nutrients by nutrient id\n",
    "createNutrientDefTable = '''\n",
    "    CREATE TABLE IF NOT EXISTS nutrient_definitions (\n",
    "        id INT PRIMARY KEY,\n",
    "        name VARCHAR(100),\n",
    "        unit_name VARCHAR(20),\n",
    "        nutrient_nbr INT,\n",
    "        rank REAL\n",
    "    );\n",
    "'''\n",
    "nutrientDefCSV = 'nutrient.csv'\n",
    "nutrientDefInsertTable = \"INSERT INTO nutrient_definitions VALUES (?, ?, ?, ?, ?)\"\n",
    "dropTable('nutrient_definitions')\n",
    "generateDB(createNutrientDefTable, nutrientDefCSV, nutrientDefInsertTable)\n",
    "print('Total Items: ', runQuery('SELECT COUNT(*) FROM nutrient_definitions;')[0][0])\n",
    "print('First 5 items:')\n",
    "result = runQuery('SELECT * FROM nutrient_definitions LIMIT 5;')\n",
    "formatted_row = '{:<10} {:<40} {:<15} {:<15} {:<15}'\n",
    "print(formatted_row.format(\"id\", \"name\", \"unit_name\", \"nutrient_nbr\", \"rank\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "### Advanced Queries\n",
    "\n",
    "Now that our tables are created, we can start making some unique queries. In the first query, we will be using a CTEs and various types of apples to determine if there are any nutritional differences between the various types, as determined by the FDA. In this first query, we are just going to print out a list of the macronutrients associated with apples grouped by description, which yields five different apples in the \"foundational_food\" data_type:\n",
    "- Red Delicious\n",
    "- Fuji\n",
    "- Gala\n",
    "- Granny Smith\n",
    "- Honeycrisp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FDC ID     Description                              Grams                                   \n",
      "1105897    Apples, fuji, with skin, raw             35.8                                    \n",
      "1105781    Apples, gala, with skin, raw             35.8                                    \n",
      "1105664    Apples, granny smith, with skin, raw     35.8                                    \n",
      "1105547    Apples, honeycrisp, with skin, raw       35.8                                    \n",
      "1105430    Apples, red delicious, with skin, raw    35.8                                    \n"
     ]
    }
   ],
   "source": [
    "## Simple query to get our base table of apples. We're also going to retrieve the portion size\n",
    "## to see if there's a difference in the weight of each apple type\n",
    "result = runQuery('''\n",
    "    SELECT apples.fdc_id, description, gram_weight\n",
    "    FROM (SELECT * FROM food\n",
    "    WHERE description LIKE '%APPLES,%'\n",
    "    and data_type = \"foundation_food\") as apples, portion\n",
    "    GROUP BY description\n",
    "''')\n",
    "formatted_row = '{:<10} {:<40} {:<40}'\n",
    "print(formatted_row.format(\"FDC ID\", \"Description\", \"Grams\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nutrient   Apple Type                               Amount     Units      Nutrient  \n",
      "1003       Apples, red delicious, with skin, raw    0.19       G          Protein   \n",
      "1003       Apples, fuji, with skin, raw             0.15       G          Protein   \n",
      "1003       Apples, gala, with skin, raw             0.13       G          Protein   \n",
      "1003       Apples, granny smith, with skin, raw     0.27       G          Protein   \n",
      "1003       Apples, honeycrisp, with skin, raw       0.1        G          Protein   \n",
      "1004       Apples, red delicious, with skin, raw    0.21       G          Total lipid (fat)\n",
      "1004       Apples, fuji, with skin, raw             0.16       G          Total lipid (fat)\n",
      "1004       Apples, gala, with skin, raw             0.15       G          Total lipid (fat)\n",
      "1004       Apples, granny smith, with skin, raw     0.14       G          Total lipid (fat)\n",
      "1004       Apples, honeycrisp, with skin, raw       0.1        G          Total lipid (fat)\n",
      "1005       Apples, red delicious, with skin, raw    14.8       G          Carbohydrate, by difference\n",
      "1005       Apples, fuji, with skin, raw             15.7       G          Carbohydrate, by difference\n",
      "1005       Apples, gala, with skin, raw             14.8       G          Carbohydrate, by difference\n",
      "1005       Apples, granny smith, with skin, raw     14.2       G          Carbohydrate, by difference\n",
      "1005       Apples, honeycrisp, with skin, raw       14.7       G          Carbohydrate, by difference\n"
     ]
    }
   ],
   "source": [
    "## Query to determine the nutritional conversion of different apples.\n",
    "## This joins the food, nutrient_conversion_factor, and calorie_conversion_factor tables\n",
    "\n",
    "## First, because we are going to look at different nutrient_ids, lets make a function so we can run this query multiple times\n",
    "def appleQuery(andStatement):\n",
    "    return runQuery('''\n",
    "        WITH \n",
    "        ids AS (\n",
    "            WITH apples AS (\n",
    "                SELECT *\n",
    "                FROM food\n",
    "                WHERE description LIKE '%APPLES,%'\n",
    "                GROUP BY description\n",
    "            )\n",
    "            SELECT NC.fdc_id, A.description as apple\n",
    "            FROM apples AS A, nutrient_conversion_factor AS NC, calorie_conversion_factor as C\n",
    "            WHERE A.fdc_id = NC.fdc_id AND C.food_nutrient_conversion_factor_id = NC.id\n",
    "        ),\n",
    "        nutrients AS (\n",
    "            SELECT * FROM nutrient, ids WHERE nutrient.fdc_id = ids.fdc_id\n",
    "        )\n",
    "        SELECT nutrient_id, apple, amount, unit_name, name FROM nutrients, nutrient_definitions\n",
    "            WHERE nutrients.nutrient_id = nutrient_definitions.id\n",
    "    '''+andStatement+';')\n",
    "\n",
    "# Now just check for nutrient_ids 1003-1005, which are protein, fat, and carbs\n",
    "result = appleQuery('AND nutrient_id IN (1003,1004,1005)')\n",
    "formatted_row = '{:<10} {:<40} {:<10} {:<10} {:<10}'\n",
    "print(formatted_row.format(\"Nutrient\", \"Apple Type\", \"Amount\", \"Units\", \"Nutrient\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "#### Sugars\n",
    "\n",
    "Already we can see some interesting things. While close in values, we can immediately see that Granny Smith apples are much higher in protein than the other apples, whereas Red Delicious leads the pack in fat. Fuji then blows away the competition (relatively) in terms of carbs. We can adjust this query slightly by searching for the total sugars in each apple and see if that's why Fuji apples are so high in carbs.\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nutrient   Apple Type                               Amount     Units      Nutrient  \n",
      "1063       Apples, fuji, with skin, raw             12.6       G          Sugars, Total\n",
      "1063       Apples, honeycrisp, with skin, raw       11.7       G          Sugars, Total\n",
      "1063       Apples, gala, with skin, raw             11.3       G          Sugars, Total\n",
      "1063       Apples, red delicious, with skin, raw    11.1       G          Sugars, Total\n",
      "1063       Apples, granny smith, with skin, raw     10.5       G          Sugars, Total\n"
     ]
    }
   ],
   "source": [
    "# Now just check for nutrient_id 1063, which is total sugars\n",
    "result = appleQuery('AND nutrient_id = 1063 ORDER BY amount DESC')\n",
    "formatted_row = '{:<10} {:<40} {:<10} {:<10} {:<10}'\n",
    "print(formatted_row.format(\"Nutrient\", \"Apple Type\", \"Amount\", \"Units\", \"Nutrient\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "#### Fructose\n",
    "Aha! Fuji apples are indeed the highest in total sugar count, just as we suspected. We can now search based on any nutrient ID that appears in the total list. However, a personal favorite apple type of mine is the Honeycrisp. Honeycrisp leads the pack in fructose, so it's possible that's why I like that type of apple so much:\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nutrient   Apple Type                               Amount     Units      Nutrient  \n",
      "1012       Apples, honeycrisp, with skin, raw       8.56       G          Fructose  \n",
      "1012       Apples, fuji, with skin, raw             8.4        G          Fructose  \n",
      "1012       Apples, gala, with skin, raw             7.76       G          Fructose  \n",
      "1012       Apples, red delicious, with skin, raw    7.56       G          Fructose  \n",
      "1012       Apples, granny smith, with skin, raw     6.76       G          Fructose  \n"
     ]
    }
   ],
   "source": [
    "# Now just check for nutrient_id 1012, which is fructose\n",
    "result = appleQuery('AND nutrient_id = 1012 ORDER BY amount DESC')\n",
    "formatted_row = '{:<10} {:<40} {:<10} {:<10} {:<10}'\n",
    "print(formatted_row.format(\"Nutrient\", \"Apple Type\", \"Amount\", \"Units\", \"Nutrient\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "#### Calcium\n",
    "And one final interesting note to display the capabilities of this query is looking at a microingredient that many people find important, calcium. Surprisingly, our pack leaders lag behing the Gala apple, which has a full mg more calcium than its closest competitor! I'll have to get my calcium elsewhere, as honeycrisp is bringing up the rear.\n",
    "\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nutrient   Apple Type                               Amount     Units      Nutrient  \n",
      "1087       Apples, gala, with skin, raw             7.0        MG         Calcium, Ca\n",
      "1087       Apples, fuji, with skin, raw             6.0        MG         Calcium, Ca\n",
      "1087       Apples, granny smith, with skin, raw     5.0        MG         Calcium, Ca\n",
      "1087       Apples, red delicious, with skin, raw    5.0        MG         Calcium, Ca\n",
      "1087       Apples, honeycrisp, with skin, raw       4.0        MG         Calcium, Ca\n"
     ]
    }
   ],
   "source": [
    "# Now just check for nutrient_id 1087, which is calcium\n",
    "result = appleQuery('AND nutrient_id = 1087 ORDER BY amount DESC')\n",
    "formatted_row = '{:<10} {:<40} {:<10} {:<10} {:<10}'\n",
    "print(formatted_row.format(\"Nutrient\", \"Apple Type\", \"Amount\", \"Units\", \"Nutrient\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "### Protein\n",
    "\n",
    "Protein is the name of the game in many diet-concious people these days. With that in mind, a use of this database could be to determine the highest protein content per weight in the FDA database. Per the [FDA's website](https://www.fao.org/3/Y5022E/y5022e04.htm), different types of proteins result in different multipliers (i.e. caloric conversion factors) for each food. So, let's say we wanted to do an advanced search to identify the highest protein multiplier in the FDA database, with the lowest carbohydrate multiplier. We conduct this query below, using three tables, a grouping, and an aggregator on the protein_value.\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FDC ID     Food                                                                                                    Protein        Fat      Carbs\n",
      "323604     Egg, whole, raw, frozen, pasteurized                                                                       4.36       9.02       3.68\n",
      "333374     Fish, haddock, raw                                                                                         4.27       9.02       3.87\n",
      "334849     Beef, loin, top loin steak, boneless, lip-on, separable lean only, trimmed to 1/8\" fat, choice, raw        4.27       9.02       3.87\n",
      "746952     Ham, sliced, restaurant                                                                                    4.27       9.02       3.87\n",
      "323121     Frankfurter, beef, unheated                                                                                4.27       9.02       3.87\n",
      "330869     Turkey, ground, 93% lean, 7% fat, pan-broiled crumbles                                                     4.27       9.02       3.87\n",
      "334462     Restaurant, Chinese, sweet and sour pork                                                                    4.0        9.0        4.0\n",
      "333008     Cookies, oatmeal, soft, with raisins                                                                        4.0        9.0        4.0\n",
      "1750348    Oil, peanut                                                                                                 4.0       8.84        4.0\n",
      "790085     Flour, whole wheat, unenriched                                                                             4.05       8.37       3.78\n"
     ]
    }
   ],
   "source": [
    "## Query to determine the highest protein value multiplier amongst the foundational_foods\n",
    "result = runQuery('''\n",
    "    WITH foods AS (\n",
    "        SELECT *\n",
    "        FROM food\n",
    "        WHERE data_type = \"foundation_food\"\n",
    "    )\n",
    "    SELECT F.fdc_id as 'FDC ID', F.description as 'Food', MAX(C.protein_value), C.fat_value, MIN(C.carbohydrate_value)\n",
    "    FROM foods AS F, nutrient_conversion_factor AS N, calorie_conversion_factor as C\n",
    "    WHERE F.fdc_id = N.fdc_id AND C.food_nutrient_conversion_factor_id = N.id\n",
    "    GROUP BY food_category_id\n",
    "    ORDER BY C.protein_value DESC LIMIT 10;\n",
    "''')\n",
    "formatted_row = '{:<10} {:<100} {:>10} {:>10} {:>10}'\n",
    "print(formatted_row.format(\"FDC ID\", \"Food\", \"Protein\", \"Fat\", \"Carbs\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "The humble egg edges out all of it's competitors. While the difference may be small, remember that the caloric_conversion_factor is a multiplier for the amount of protein, fat, and carbs inside the specific food item to calculate the true caloric value of the food. Now, let's take a look at the various macro and micro ingredients in side the egg by searching for it's fdc_id in the join between nutrient and nutrient definitions.\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nutrient                                           Unit           Amount\n",
      "Carbohydrate, by difference                        G                0.91\n",
      "Energy                                             KCAL            150.0\n",
      "Iron, Fe                                           MG               1.77\n",
      "25-hydroxycholecalciferol                          UG                0.6\n",
      "Water                                              G                75.4\n",
      "Sodium, Na                                         MG              121.0\n",
      "Ash                                                G                1.16\n",
      "Vitamin D (D2 + D3), International Units           IU               91.0\n",
      "Protein                                            G                12.3\n",
      "Iodine, I                                          UG               61.6\n",
      "Zinc, Zn                                           MG                1.2\n",
      "Manganese, Mn                                      MG                0.0\n",
      "Potassium, K                                       MG              117.0\n",
      "Phosphorus, P                                      MG              189.0\n",
      "Magnesium, Mg                                      MG               11.2\n",
      "Vitamin D3 (cholecalciferol)                       UG                2.3\n",
      "Nitrogen                                           G                1.97\n",
      "Vitamin D (D2 + D3)                                UG                2.3\n",
      "Calcium, Ca                                        MG               55.0\n",
      "Copper, Cu                                         MG                0.0\n",
      "Cholesterol                                        MG              420.0\n",
      "Energy                                             kJ              627.0\n",
      "Vitamin D2 (ergocalciferol)                        UG                0.0\n",
      "Total lipid (fat)                                  G                10.3\n",
      "Energy (Atwater General Factors)                   KCAL            146.0\n",
      "Energy (Atwater Specific Factors)                  KCAL            150.0\n"
     ]
    }
   ],
   "source": [
    "## Ok, we can see that eggs have the highest protein multiplier. Let's see what other nutrients are present in eggs\n",
    "result = runQuery('''\n",
    "    WITH \n",
    "    N AS (\n",
    "        SELECT * FROM nutrient WHERE fdc_id = 323604\n",
    "    ),\n",
    "    nutrients AS (\n",
    "        SELECT name, unit_name, amount, fdc_id FROM nutrient_definitions JOIN N WHERE N.nutrient_id = nutrient_definitions.id\n",
    "    )\n",
    "    SELECT * from nutrients;\n",
    "''')\n",
    "formatted_row = '{:<50} {:<10} {:>10}'\n",
    "print(formatted_row.format(\"Nutrient\", \"Unit\", \"Amount\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "As you can see, because we can query for a large number of micronutrients, not only could we establish queries for things like calories, protein, carbs, and fats, we could also optimize a user's dietary requirements down to micronutrients like calcium or vitamin D! However, we are merely interested in using the protein, fat, and carb conversions to calculate the caloric value of an egg. While we could simply reference the \"Energy\" item in the nutrients list, it's a bit more interesting to work through the query to calculate each value for an egg. The below query does the following:\n",
    "\n",
    "* Joins 4 tables on various indexes\n",
    "* Strips out the protein, fat, and carb multipliers and macronutrient values\n",
    "* Multiplies the values\n",
    "* Sums the result\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Egg calculated KCALs:  149.88280000000003\n"
     ]
    }
   ],
   "source": [
    "## Ok, now that we've found the egg has the highest protein conversion factor, let's calculate the caloric energy in an egg!\n",
    "result = runQuery('''\n",
    "    WITH \n",
    "    N AS (\n",
    "        SELECT * FROM nutrient WHERE fdc_id = 323604\n",
    "    ),\n",
    "    nutrients AS (\n",
    "        SELECT name, unit_name, amount, fdc_id FROM nutrient_definitions JOIN N WHERE N.nutrient_id = nutrient_definitions.id\n",
    "    ),\n",
    "    cals AS (\n",
    "        SELECT * FROM nutrient_conversion_factor as NC, calorie_conversion_factor as C\n",
    "        WHERE NC.fdc_id = '323604' AND C.food_nutrient_conversion_factor_id = NC.id\n",
    "    ),\n",
    "    macros AS (\n",
    "        SELECT name, amount, protein_value, fat_value, carbohydrate_value FROM cals, nutrients where cals.fdc_id = nutrients.fdc_id\n",
    "        AND name in (\"Protein\", \"Total lipid (fat)\", \"Carbohydrate, by difference\")\n",
    "    ),\n",
    "    egg_calories AS (\n",
    "        SELECT * FROM \n",
    "            (SELECT amount * protein_value as proteins FROM macros WHERE name = \"Protein\"),\n",
    "            (SELECT amount * fat_value as fats FROM macros WHERE name = \"Total lipid (fat)\"),\n",
    "            (SELECT amount * carbohydrate_value as carbs FROM macros WHERE name = \"Carbohydrate, by difference\")\n",
    "    )\n",
    "    SELECT proteins + fats + carbs FROM egg_calories;\n",
    "''')\n",
    "print('Egg calculated KCALs: ',result[0][0])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "### Inserting New Data\n",
    "\n",
    "As we saw when creating the food table, the descriptions are often duplicated, and relatively unhelpful. So, we are going to create a trigger for our database enforcing a unique description based on the LIKE pattern matching. This will ensure we don't have any more duplicates such as various hummus types and so forth. We will also raise a custom integrity error so the user knows what the issue is."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Trigger to enforce only inserting a food description that doesn't already exist in the LIKE pattern match\n",
    "## Note: We do this as a BEFORE INSERT statement\n",
    "conn = sqlite3.connect(database)\n",
    "c = conn.cursor()\n",
    "c.execute('DROP TRIGGER IF EXISTS no_repeat_descriptions')\n",
    "c.execute('''\n",
    "CREATE TRIGGER IF NOT EXISTS no_repeat_descriptions\n",
    "BEFORE INSERT ON food\n",
    "FOR EACH ROW\n",
    "WHEN (SELECT COUNT(*) FROM food WHERE description LIKE '%' || new.description || '%') > 0\n",
    "BEGIN\n",
    "    SELECT RAISE(FAIL,'Description must be unique');\n",
    "END\n",
    "''')\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "Great, so now let's test this to make sure we can't insert yet another hummus.\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL ERROR:  Description must be unique\n"
     ]
    }
   ],
   "source": [
    "## Lets test the trigger by attempting to insert yet another hummus. This should fail!\n",
    "conn = sqlite3.connect(database)\n",
    "c = conn.cursor()\n",
    "try:\n",
    "    c.execute('''\n",
    "        INSERT INTO food \n",
    "        VALUES (\n",
    "            1,\n",
    "            \"sample_food\", \n",
    "            \"hummus\",\n",
    "            30,\n",
    "            '2023-12-10'\n",
    "        );''').fetchall()\n",
    "except Exception as error:\n",
    "    print('SQL ERROR: ',error)\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "Excellent, we've stopped the unblocked entry of multiple hummus items. However, does this let us insert new items? Strangely, this selection of the FDA database doesn't have an entry for chocolate, so we will test using that. First, I'll prove that no chocolate entry exists:\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "runQuery('SELECT * FROM food WHERE description LIKE \"%chocolate%\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "Now let's correct this eggregious oversight by adding an entry for chocolate\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(2648874, 'foundation_food', 'chocolate', 30, '2023-12-10')]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Insert some chocolate\n",
    "conn = sqlite3.connect(database)\n",
    "c = conn.cursor()\n",
    "# Get the max fdc_id number and add one\n",
    "chocolate_fdc_id = c.execute('''\n",
    "    SELECT MAX(fdc_id) FROM food;\n",
    "''').fetchall()[0][0] + 1\n",
    "insertStatement = 'INSERT INTO food VALUES ('+str(chocolate_fdc_id)+',\"foundation_food\", \"chocolate\", 30,\"2023-12-10\");'\n",
    "c.execute(insertStatement)\n",
    "conn.commit()\n",
    "conn.close()\n",
    "runQuery('SELECT * FROM food WHERE description LIKE \"%chocolate%\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "Ok, we've corrected that grave error, but we don't have any other table entries for chocolate. Let's insert a few more entries for chocolate into the other tables, like calories. First, we need to create a conversion factor entry.\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id         fdc_id    \n",
      "23078      2648874   \n"
     ]
    }
   ],
   "source": [
    "## Insert chocolate into the nutrient_conversion_factor table\n",
    "conn = sqlite3.connect(database)\n",
    "c = conn.cursor()\n",
    "# Get the max id number and add one\n",
    "chocolate_nutrient_conversion_factor = c.execute('''\n",
    "    SELECT MAX(id) FROM nutrient_conversion_factor;\n",
    "''').fetchall()[0][0] + 1\n",
    "c.execute(('INSERT INTO nutrient_conversion_factor VALUES ('\n",
    "           +str(chocolate_nutrient_conversion_factor)\n",
    "           +','\n",
    "           +str(chocolate_fdc_id)+');'))\n",
    "conn.commit()\n",
    "conn.close()\n",
    "result = runQuery(('SELECT * FROM nutrient_conversion_factor WHERE id = '\n",
    "          +str(chocolate_nutrient_conversion_factor)\n",
    "          +';'))\n",
    "formatted_row = '{:<10} {:<10}'\n",
    "print(formatted_row.format(\"id\", \"fdc_id\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "Now that we have a conversion factor entry, we can add an entry to the calorie conversion factor table. We will simply use the standard Atwater General Factor System values of 4.0, 9.0, and 4.0 for protein, fat, and carbs respectively, as the specific factors are beyond the scope of this course.\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "food_nutrient_conversion_factor_id       protein_value   fat_value       carbohydrate_value\n",
      "23078                                    4.0             9.0             4.0            \n"
     ]
    }
   ],
   "source": [
    "## Insert chocolate into the calorie_conversion_factor table\n",
    "conn = sqlite3.connect(database)\n",
    "c = conn.cursor()\n",
    "c.execute(('INSERT INTO calorie_conversion_factor VALUES ('\n",
    "           +str(chocolate_nutrient_conversion_factor)\n",
    "           +',4.0, 9.0, 4.0'\n",
    "           +');'))\n",
    "conn.commit()\n",
    "conn.close()\n",
    "result = runQuery(('SELECT * FROM calorie_conversion_factor WHERE food_nutrient_conversion_factor_id = '\n",
    "         +str(chocolate_nutrient_conversion_factor)+';'))\n",
    "formatted_row = '{:<40} {:<15} {:<15} {:<15}'\n",
    "print(formatted_row.format(\"food_nutrient_conversion_factor_id\", \"protein_value\", \"fat_value\", \"carbohydrate_value\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "Now let's assume that we have new data with more specific protein/fat/carb values. Let's update the database to reflect our new values.\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "food_nutrient_conversion_factor_id       protein_value   fat_value       carbohydrate_value\n",
      "23078                                    3.5             10.5            17.0           \n"
     ]
    }
   ],
   "source": [
    "## Update our conversion factors for chocolate\n",
    "conn = sqlite3.connect(database)\n",
    "c = conn.cursor()\n",
    "c.execute(('UPDATE calorie_conversion_factor SET'\n",
    "           + ' protein_value = protein_value - 0.5,'\n",
    "           + ' fat_value = fat_value + 1.5,'\n",
    "           + ' carbohydrate_value = 17.0\\n'\n",
    "           + 'WHERE food_nutrient_conversion_factor_id = '\n",
    "           +str(chocolate_nutrient_conversion_factor)\n",
    "           +';'))\n",
    "conn.commit()\n",
    "conn.close()\n",
    "result = runQuery(('SELECT * FROM calorie_conversion_factor WHERE food_nutrient_conversion_factor_id = '\n",
    "         +str(chocolate_nutrient_conversion_factor)+';'))\n",
    "formatted_row = '{:<40} {:<15} {:<15} {:<15}'\n",
    "print(formatted_row.format(\"food_nutrient_conversion_factor_id\", \"protein_value\", \"fat_value\", \"carbohydrate_value\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "Ok great, insertion and update worked. But, there was probably a good reason that chocolate isn't in our database. Maybe it's in another location in a different csv. Either way, the step below will show how we can remove old or unwanted data from this database. Remember that these items all have foreign keys. calorie_conversion_factor is using the nutrient_conversion_factor_id as the foreign key, and nutrient_conversion_factor is using the fdc_id as the foreign key. There are two ways we can delete this entry. We can go backwards and remove the calorie_conversion_factor entry first, moving up to the food table, add a new trigger, or we can UPDATE our table to include an ON CASCADE.\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FOREIGN KEY constraint failed\n",
      "id         fdc_id    \n",
      "23078      2648874   \n"
     ]
    }
   ],
   "source": [
    "# First, let's try to remove chocolate from the calorie conversion table. It should fail.\n",
    "conn = sqlite3.connect(database)\n",
    "c = conn.cursor()\n",
    "c.execute('PRAGMA foreign_keys=on;')\n",
    "try:\n",
    "    c.execute(('DELETE FROM nutrient_conversion_factor WHERE id = '\n",
    "               +str(chocolate_nutrient_conversion_factor)\n",
    "               +';'))\n",
    "except Exception as error:\n",
    "    print(error)\n",
    "conn.commit()\n",
    "conn.close()\n",
    "result = runQuery(('SELECT * FROM nutrient_conversion_factor WHERE id = '\n",
    "               +str(chocolate_nutrient_conversion_factor)\n",
    "               +';'))\n",
    "formatted_row = '{:<10} {:<10}'\n",
    "print(formatted_row.format(\"id\", \"fdc_id\"))\n",
    "for Row in result:\n",
    "    print(formatted_row.format(*Row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "As we expected, that shouldn't have worked. As mentioned above, we COULD alter our table to include the ON CASCADE rule, but instead we are going to add a trigger to handle this cascading deletion for us.\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Trigger to allow for cascade deletion from nutrient_conversion_factor\n",
    "conn = sqlite3.connect(database)\n",
    "c = conn.cursor()\n",
    "c.execute('PRAGMA foreign_keys=on;')\n",
    "c.execute('DROP TRIGGER IF EXISTS delete_nutrient_conversion_factor')\n",
    "c.execute('''\n",
    "CREATE TRIGGER IF NOT EXISTS delete_nutrient_conversion_factor\n",
    "BEFORE DELETE ON nutrient_conversion_factor\n",
    "FOR EACH ROW\n",
    "BEGIN\n",
    "    DELETE FROM calorie_conversion_factor WHERE food_nutrient_conversion_factor_id = old.id;\n",
    "END\n",
    "''')\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nutrient Conversion Factor:  []\n",
      "Calorie Conversion Factor:  []\n"
     ]
    }
   ],
   "source": [
    "conn = sqlite3.connect(database)\n",
    "c = conn.cursor()\n",
    "c.execute('PRAGMA foreign_keys=on;')\n",
    "c.execute(('DELETE FROM nutrient_conversion_factor WHERE id = '\n",
    "           +str(chocolate_nutrient_conversion_factor)\n",
    "           +';'))\n",
    "conn.commit()\n",
    "conn.close()\n",
    "nutrientConversionFactor = runQuery(('SELECT * FROM nutrient_conversion_factor WHERE id = '\n",
    "               +str(chocolate_nutrient_conversion_factor)\n",
    "               +';'))\n",
    "calorieConversionFactor = runQuery(('SELECT * FROM calorie_conversion_factor WHERE food_nutrient_conversion_factor_id = '\n",
    "               +str(chocolate_nutrient_conversion_factor)\n",
    "               +';'))\n",
    "print('Nutrient Conversion Factor: ', nutrientConversionFactor)\n",
    "print('Calorie Conversion Factor: ', calorieConversionFactor)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "## Summary\n",
    "\n",
    "It almost goes without saying the value a database like this might have for the average consumer who is attempting to better understand their diet, caloric intake, and nutritional requirements. Being able to quickly and efficiently compare different food types, down to different types of apples, and assess which type best fits the dietary needs of the consumer would greatly enable more intelligent shopping. A secondary effect might be decreased food waste, more savings, and so on.\n",
    "\n",
    "On top of the user benefits of a more comprehensive application, this project also greatly contributed to my understanding of database creation and management, in line with the goals of this project laid out in the submission. During the creation of this project, I was able to:\n",
    "\n",
    "- Better understand how to implement multiple relations across large datasets.\n",
    "- Implement a real-world use case dataset solution for an existing problem set.\n",
    "- Understand how to seed a database from an existing CSV.\n",
    "- Implement and use a trigger in an actual use case.\n",
    "- Better understand the basic database concepts taught during this course.\n",
    "- Gain an appreciation for database projects currently in production (Facebook, Amazon, etc.).\n",
    "- Specifically, gain a better working knowledge of the FDA databases and derive meaningful insights from correlating data across multiple tables.\n",
    "\n",
    "<hr>"
   ]
  }
 ],
 "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.10.8"
  },
  "nteract": {
   "version": "0.14.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}