Alma_API_Student_Attendance.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a46daaab",
   "metadata": {},
   "source": [
    "## Alma API Single Student Attendance\n",
    "\n",
    "Writing a code that returns the attendance data of a signle student from the Alma API url endpoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "61a23b80",
   "metadata": {},
   "outputs": [],
   "source": [
    "#importing relevant packages\n",
    "import pandas as pd\n",
    "import requests\n",
    "import json\n",
    "from requests.auth import HTTPDigestAuth\n",
    "from google.cloud import bigquery\n",
    "from google.oauth2 import service_account\n",
    "from dotenv import load_dotenv\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9bb8c020",
   "metadata": {},
   "outputs": [],
   "source": [
    "env_file_path = '/Users/scipio/Alma_API_Scripts/ALMA_API.env'\n",
    "google_auth_env_file_path = '/Users/scipio/Alma_API_Scripts/GOOG_AUTH.env'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "21a325c5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "load_dotenv(dotenv_path = env_file_path )\n",
    "load_dotenv(dotenv_path = google_auth_env_file_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7604cfbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# retrieving releavnt variables from the .env file\n",
    "API_KEY = os.getenv('API_KEY')\n",
    "AUTH_SECRET = os.getenv('AUTH_SECRET')\n",
    "google_credentials_path = os.getenv('GOOGLE_CREDENTIALS')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ae4352f",
   "metadata": {},
   "source": [
    "**FCA Current School Year**: 65e8a8461e0c3dd517076bcf\n",
    "\n",
    "**363 Current School Year ID**: 664cc55127c6b4a81806658b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "98235295",
   "metadata": {},
   "outputs": [],
   "source": [
    "# setting up BigQuery authentication\n",
    "credentials = service_account.Credentials.from_service_account_file(google_credentials_path)\n",
    "\n",
    "#initializing BigQuery client\n",
    "client = bigquery.Client(credentials=credentials, project=credentials.project_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90648343",
   "metadata": {},
   "source": [
    "### Extraction of Student Attendance Data FCA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c87622a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Defining credential api_key and auth_secret \n",
    "api_key = API_KEY\n",
    "auth_secret = AUTH_SECRET \n",
    "\n",
    "# URL of the API endpoint FCA\n",
    "\n",
    "#url endpoint to return attendance data of Jaden McKellar\n",
    "url1= 'https://facs.api.getalma.com/v2/fca/students/60ca0ea65a24b06c6a107faf/attendance?schoolYearId=65e8a8461e0c3dd517076bcf'\n",
    "\n",
    "#url endpoint to return attendance data of Melvin McKellar\n",
    "url2= 'https://facs.api.getalma.com/v2/fca/students/60ca3aa494882a4b7d791aef/attendance?schoolYearId=65e8a8461e0c3dd517076bcf'\n",
    "\n",
    "# url endpoint to return attendance data of Julian Tineo\n",
    "url3= 'https://facs.api.getalma.com/v2/fca/students/60ca0eb4832dc100d146db46/attendance?schoolYearId=65e8a8461e0c3dd517076bcf'\n",
    "\n",
    "\n",
    "# Headers\n",
    "headers = {\n",
    "    'Content-Type':'application/json',\n",
    "    'Accept':'application/json, application/problem+json'\n",
    "}\n",
    "\n",
    "\n",
    "# Make the GET request with Digest Authentication\n",
    "response_fca_att1 = requests.get(url1, headers=headers, auth=HTTPDigestAuth(api_key, auth_secret))\n",
    "response_fca_att2 = requests.get(url2, headers=headers, auth=HTTPDigestAuth(api_key, auth_secret))\n",
    "response_fca_att3 = requests.get(url3, headers=headers, auth=HTTPDigestAuth(api_key, auth_secret))\n",
    "\n",
    "\n",
    "# Making GET request into a json object\n",
    "r_fca_att1 = response_fca_att1.json()\n",
    "r_fca_att2 = response_fca_att2.json()\n",
    "r_fca_att3 = response_fca_att3.json()\n",
    "\n",
    "# Accessing 'response' key value to return lists of dictionaries\n",
    "r_fca_att1 = r_fca_att1['response']\n",
    "r_fca_att2 = r_fca_att2['response']\n",
    "r_fca_att3 = r_fca_att3['response']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b5e3ef1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating dataframe for attendance extraction for fca student\n",
    "fca_att_df1 = pd.DataFrame(r_fca_att1)\n",
    "fca_att_df2 = pd.DataFrame(r_fca_att2)\n",
    "fca_att_df3 = pd.DataFrame(r_fca_att3)\n",
    "\n",
    "# adding primary key student_id of Jaden McKellar\n",
    "fca_att_df1['student_id'] = '60ca0ea65a24b06c6a107faf'\n",
    "fca_att_df1['district'] = 'Rancocas Valley Regional High School'\n",
    "\n",
    "# adding primary key student_id Melvin McKellar\n",
    "fca_att_df2['student_id'] = '60ca3aa494882a4b7d791aef'\n",
    "fca_att_df2['district'] = 'Rancocas Valley Regional High School'\n",
    "\n",
    "# adding primary key student_ id Julian Tineo\n",
    "fca_att_df3['student_id'] = '60ca0eb4832dc100d146db46'\n",
    "fca_att_df3['district'] = \"Penn's Grove\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19e69f0f",
   "metadata": {},
   "source": [
    "### Extraction of Student Attendance Data FACS 363"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "098e5c73",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Defining credential api_key and auth_secret \n",
    "api_key = API_KEY\n",
    "auth_secret = AUTH_SECRET \n",
    "\n",
    "# URL of the API endpoint FCA\n",
    "\n",
    "# url endpoint to return attendance data of Miabella Tineo\n",
    "url1= 'https://facs.api.getalma.com/v2/facs363/students/60c9f26d1f86da023b455d96/attendance?schoolYearId=664cc55127c6b4a81806658b' # --> will return grade level ids\n",
    "\n",
    "# url endpoint to return attendance data of Jordan Tineo\n",
    "url2= 'https://facs.api.getalma.com/v2/facs363/students/60c9feef7f8d901080325e07/attendance?schoolYearId=664cc55127c6b4a81806658b'\n",
    "\n",
    "# url endpoint to return attendance data of Jaelyon Tineo\n",
    "url3= 'https://facs.api.getalma.com/v2/facs363/students/60c9fefbee570b7782727eee/attendance?schoolYearId=664cc55127c6b4a81806658b'\n",
    "\n",
    "\n",
    "# Headers\n",
    "headers = {\n",
    "    'Content-Type':'application/json',\n",
    "    'Accept':'application/json, application/problem+json'\n",
    "}\n",
    "\n",
    "\n",
    "# Make the GET request with Digest Authentication\n",
    "response_363_att1 = requests.get(url1, headers=headers, auth=HTTPDigestAuth(api_key, auth_secret))\n",
    "response_363_att2 = requests.get(url2, headers=headers, auth=HTTPDigestAuth(api_key, auth_secret))\n",
    "response_363_att3 = requests.get(url3, headers=headers, auth=HTTPDigestAuth(api_key, auth_secret))\n",
    "\n",
    "# Making GET requests into a json object\n",
    "r_363_att1 = response_363_att1.json()\n",
    "r_363_att2 = response_363_att2.json()\n",
    "r_363_att3 = response_363_att3.json()\n",
    "\n",
    "# Accessing 'response' key value to return lists of dictionaries\n",
    "r_363_att1 = r_363_att1['response']\n",
    "r_363_att2 = r_363_att2['response']\n",
    "r_363_att3 = r_363_att3['response']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "63347205",
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating a df for the \n",
    "facs_363_df1 = pd.DataFrame(r_363_att1)\n",
    "facs_363_df2 = pd.DataFrame(r_363_att2)\n",
    "facs_363_df3 = pd.DataFrame(r_363_att3)\n",
    "\n",
    "# adding student_ id for Miabella Tineo 60c9f26d1f86da023b455d96\n",
    "facs_363_df1['student_id'] = '60c9f26d1f86da023b455d96'\n",
    "facs_363_df1['district'] = \"Penn's Grove\"\n",
    "\n",
    "\n",
    "# adding student_id for Jordan Tineo 60c9feef7f8d901080325e07\n",
    "facs_363_df2['student_id'] = '60c9feef7f8d901080325e07'\n",
    "facs_363_df2['district'] = \"Penn's Grove\"\n",
    "\n",
    "# adding student_id for Jaelyon Tineo\n",
    "facs_363_df3['student_id'] = '60c9fefbee570b7782727eee'\n",
    "facs_363_df3['district'] = \"Penn's Grove\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6cbfc3fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# concating dfs from resoective campuses\n",
    "df = pd.concat([fca_att_df1,fca_att_df2,fca_att_df3,facs_363_df1,facs_363_df2,facs_363_df3])\n",
    "\n",
    "# returning relevaant columns\n",
    "df = df[['student_id', 'date','status', 'district']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1942e6b5",
   "metadata": {},
   "source": [
    "### Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a1ff2d05",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|███████████████████████████████████████████| 1/1 [00:00<00:00, 3872.86it/s]\n"
     ]
    }
   ],
   "source": [
    "# loading into BigQuery database\n",
    "table_id = 'Alma_Data_API.Student_Attendance'\n",
    "\n",
    "# loading df to the BigQuery database to append data to table with each upload\n",
    "df.to_gbq(table_id, project_id=credentials.project_id, if_exists='replace', credentials=credentials)"
   ]
  }
 ],
 "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}