Using MySQL and GAM to manage student accounts
https://go.eduk8.me/brainstorm19
Who am I?
https://go.eduk8.me/brainstorm19
Ryan Collins
Director of Technology
Kenton City Schools
about.ryancollins.org
https://go.eduk8.me/brainstorm19
Inside of every problem lies an opportunity.
Robert Kiyosaki
“
https://go.eduk8.me/brainstorm19
Managing student accounts with a database
5
Everything you need to play
https://go.eduk8.me/brainstorm19
6
Tools
macOS
Sequel Pro
Windows and Linux
MySQL Workbench
Web
phpMyAdmin
https://go.eduk8.me/brainstorm19
SQL support in other apps
https://go.eduk8.me/brainstorm19
Requirements
studentschedule.csv
10 PRINT “HELLO WORLD”
https://go.eduk8.me/brainstorm19
Database - MySQL
https://go.eduk8.me/brainstorm19
Schedule file from ITC
PrimaryStudentID,FirstName,LastName,TeacherID,SiteID,Grade,Term,Period,CourseID,CourseName
1002,Leroy,Zhang,f0b33,HS,12,YEAR,03,14,CONSUMER MATH
1002,Leroy,Zhang,ec370,HS,12,YEAR,01,90,CBI RELATED
1002,Leroy,Zhang,ec370,HS,12,YEAR,04,99,CBI ON THE JOB
1002,Leroy,Zhang,ec370,HS,12,YEAR,06,99,CBI ON THE JOB
1002,Leroy,Zhang,ec370,HS,12,YEAR,5A,99,CBI ON THE JOB
1002,Leroy,Zhang,1f6d5,HS,12,YEAR,02,96,CBI ENG.
1003,Freida,Bevington,f0b33,HS,12,YEAR,04,14,CONSUMER MATH
1003,Freida,Bevington,ec370,HS,12,YEAR,02,90,CBI RELATED
1003,Freida,Bevington,ec370,HS,12,YEAR,5C,99,CBI ON THE JOB
1003,Freida,Bevington,7c9e9,HS,12,2SEM,03,SH,STUDY HALL
1003,Freida,Bevington,1f6d5,HS,12,YEAR,01,96,CBI ENG.
https://go.eduk8.me/brainstorm19
Database Schema
CREATE TABLE `studentschedule` (
`student_id` int(11) unsigned NOT NULL,
`first_name` varchar(64) DEFAULT NULL,
`last_name` varchar(64) DEFAULT NULL,
`teacher_code` char(5) DEFAULT NULL,
`building_code` char(4) DEFAULT NULL,
`grade` varchar(2) DEFAULT NULL,
`term` varchar(4) DEFAULT NULL,
`period` char(2) DEFAULT NULL,
`class_code` varchar(12) DEFAULT NULL,
`class_name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
https://go.eduk8.me/brainstorm19
New students/left students
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `newstudents`
AS SELECT
distinct `studentschedule`.`student_id` AS `student_id`,
`studentschedule`.`grade` AS `grade`,
`studentschedule`.`first_name` AS `first_name`,
`studentschedule`.`last_name` AS `last_name`,
`studentschedule`.`building_code` AS `building_code`
FROM `studentschedule` where ((not(`studentschedule`.`student_id` in (select `users`.`uid` from `users`))) or `studentschedule`.`student_id` in (select `users`.`uid` from `users` where (`users`.`status` = 'inactive')));
https://go.eduk8.me/brainstorm19
newstudents.py
https://go.eduk8.me/brainstorm19
Anatomy of a SQL statement
https://go.eduk8.me/brainstorm19
Creating a Study Island Import
select uid as `SIS Primary Key`,
concat(username,"@highschool") AS `username`,
concat(upper(substr(firstname,1,1)),lower(substr(firstname,2))) AS `first name`,
concat(upper(substr(lastname,1,1)),lower(substr(lastname,2))) AS `last name`,
`password` as `password`,
31-substr(username,1,2) AS `grade`
from users where building='High School' AND type='student' AND status='Active'
https://go.eduk8.me/brainstorm19
Creating a SAM Import
select username as USER_NAME, password as PASSWORD, uid as SIS_ID, firstname as FIRST_NAME,
lastname as LAST_NAME,
if((31-substr(username,1,2)) = 0, 'K', (31-substr(username,1,2))) AS GRADE,
"Elementary School" AS SCHOOL_NAME, studentschedule.teacher_code AS CLASS_NAME
from studentschedule
INNER JOIN users ON studentschedule.student_id = users.uid
Where
users.type='student' AND users.status='Active'
AND users.building LIKE '%Elementary%'
AND ( `studentschedule`.class_name LIKE '%Lang% Arts%' OR `studentschedule`.`class_name` LIKE '%CC-ELA 4-6%' )
https://go.eduk8.me/brainstorm19
ConnectED
csv2excel.py - quick and dirty script to make an Excel file from a .csv file
https://go.eduk8.me/brainstorm19
Creating a TCI Import
select …
CASE
WHEN studentschedule.grade = "08" AND class_name = 'Social Studies' THEN 'MS9398'
WHEN studentschedule.grade = "07" AND class_name = 'Social Studies' THEN 'MS2061'
WHEN studentschedule.grade = "06" AND class_name = 'Social Studies' THEN 'MS2047'
WHEN studentschedule.grade = "05" AND class_name = 'Science' THEN 'EL9817'
WHEN studentschedule.grade = "05" AND class_name = 'Social Studies' THEN 'ELOH5T'
WHEN studentschedule.grade = "04" AND class_name = 'Science' THEN 'EL9763'
WHEN class_name = 'WORLD STUDIES' THEN 'HS9459'
WHEN class_name = 'U.S. STUDIES' THEN 'HS0854'
END AS program_code,
https://go.eduk8.me/brainstorm19
Date added field
`dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
End of Select statement:
AND dateadded > CURDATE();
https://go.eduk8.me/brainstorm19
GAM
https://go.eduk8.me/brainstorm19
A few quick GAM commands
gam update user USERNAME password PASSWORD
gam update user USERNAME ou “/Students/Penalty Box”
https://go.eduk8.me/brainstorm19
GAM and CSV
Suspend seniors:
gam csv seniors.csv gam update user ~username suspended on
seniors.csv:
username
19aarosunshine
19abadlinnea
19allamartina
19almafelton
https://go.eduk8.me/brainstorm19
Putting it all together
newstudents.py
createcsvimports
https://go.eduk8.me/brainstorm19
Thanks!
Any questions?
You can find me at:
about.ryancollins.org
25