Overview

This project intended to:

• Take in a CSV file from SEIS
  • Entered
  • Exited
• Clean up the SEIS CSV file
  • Convert data types
  • Filter out inconsistent data
• Compare SEIS data to what's currently in Aeries CSE table
  • Compare each ID's value (Aeries vs SEIS)
• Output a merged CSV file for upload into Aeries with all the changes
• Output a change log (dropped IDs and updated values)

Sequence Diagram

sequenceDiagram
  autonumber
  actor User
  User ->> spedFastAPI: Upload SEIS Entered/Exited .CSV
  spedFastAPI ->> spedFastAPI: Process file
  User ->> spedFastAPI: Request files
  spedFastAPI -->> User: Return files

Loading

TODOs

• Get CSV files from SEIS
  • Entered
  • Exited
• Read in SEIS files as dataframes
  • Entered
  • Exited
• Filter SEIS District IDs so they are all consistent
  • Remove blanks
  • Remove non numeric
• Convert SEIS District ID to integer
• Convert SEIS Disability Code 1 and Disability Code 2 to integer
• Get list of SEIS District IDs
• Initialize Aeries CSE dataframe
• Query SQL Server to get Aeries data
  • Get SQL Server connection variables
  • Create SQL statement
  • Execute SQL query
    • Save results for Aeries CSE table in dataframe
• Convert Aeries CSE table date fields to a readable format
• Export Aeries CSE dataframe to CSV Aeries output.csv as a snapshot
• Open the attribute mapping JSON file
• Iterate over Aeries dataframe IDs
  • Iterate over every mapped attribute
    • Check if conversion is needed for the attribute
      • If yes, process replace value function with conversion
        • School Type
        • Percent IN Regular Class
        • Case Manager
        • Plan Type (Edu Plan for SpEd Svcs)
      • If no, process regular replace value function
• Convert XR column from float64 to Int64
• Export Aeries dataframe as CSV merged.csv as the snapshot with changes
• Compare original snapshot CSV against the snapshot with changes CSV
  • Export change log to compare_data.json
  • Set up some analytics on the change log
• Add SQLAlchemy (replace pyODBC)

Mapping

SEIS	Aeries	Mapping Required?	Completed
SEIS ID	CSE.SEI
Last Name
FirstName
Student SSID	CSE.ID (map to STU.CID)
Date of Birth
District ID	CSE.ID
Disability 1 Code	CSE.DI
Disability 1
Disability 2 Code	CSE.DI2
Disability 2
Disability 3 Code	CSE.DI3
Disability 3
Student Eligibility Status
Date of Original SpEd Entry	CSE.ED
Date of Exit from SpEd	CSE.XD
Exit Reason	CSE.XR
School CDS Code	CSE.SS
School of Attendance
District of SPED Accountability CDS Code	CSE.DS & CSE.DR
School of Residence CDS Code
School of Residence
Case Manager	CSE.SI	True	Done
Case Manager Email
Percent IN Regular Class	CSE.IRC	True	Done
School Type (Attendance School)	CSE.TY	True	Done
SELPA	CSE.SE
Plan Type (Edu Plan for SpEd Svcs)	CSE.PT	True	Done
Graduation Plan Code	CSE.GP
Program Setting Code (Ages 0-2)	CSE.FI
Preschool Program Setting (3-5 year-old Preschool and 4 year-old TK/Kgn)	CSE.FP
Program Setting (TK/Kgn or greater, ages 5-22)	CSE.FS
Date of Next Annual Plan Review	CSE.AD
Date of Initial Referral	CSE.RD
Referred By	CSE.RB	True
Date of Initial Parent Consent	CSE.PC
Date of Infant Initial Referral	CSE.IRD
Infant Refer By	CSE.IRB
Date of Infant Parent Consent	CSE.IPC
Date of Initial Evaluation	CSE.IE
Meeting Delay Code	CSE.EDL
Date of Infant Initial Evaluation	CSE.IIE
Special Transportation	CSE.ST	True
Primary Residence	CSE.RS	True
Early Intervention Services	CSE.EI	True
SBAC Participation Code in ELA	CSE.PA2
SBAC Participation Description in ELA
SBAC Participation Code in Math	CSE.PA3
SBAC Participation Description in Math
SBAC Participation Code in Science	CSE.PA4
SBAC Participation Description in Science

Getting started

Clone the repo to your local machine

git clone https://github.com/manuellara/spedFastAPI.git

Mappings

The /mappings folder contains mappings specific to MY school district, so you will need to review these files. Do not change the structure, only the values

Environment variables

Add a .env to the root directory with the following values:

• sqlSA (SQL Server service account username)
• sqlSAPass (SQL Server service account password)
• sqlServer (SQL Server server name)
• sqlDatabase (Current Aeries database e.g. dst2...)

Run locally in a python virtual env

In the root directory, create a virtual env

Next, activate the virtual env

Next, install the requirements

Finally, use the included Taskfile to start the api. If you don't have Taskfile installed, be sure to install it before running the next command

task start

Build and run Docker container

For this step, you will need Docker and Taskfile installed

Using the Taskfile, run the following command to build the container

task build

After the container has been built, run the following command to run the container

task run 

Utility commands/scripts

List Taskfile commands

task -l

Count changes in compare file

import json

jsonFile = open('compare_data.json')
data = json.load(jsonFile)

print( len(data['changed']) )

Resources

Containerizing FastAPI application

Adding file uploads to FastAPI application