-
Notifications
You must be signed in to change notification settings - Fork 0
/
by_race.pl
executable file
·65 lines (56 loc) · 1.24 KB
/
by_race.pl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
#! env perl
use 5.38.0;
use DBI;
use Getopt::Long;
my $grades;
GetOptions("grades=s" => \$grades) # string
or usage();
if (not defined $grades) {
say STDERR "FATAL: Argument 'grades' is mandatory.";
usage();
}
my $dbh = DBI->connect("dbi:SQLite:dbname=ops.sqlite3","","");
# Ummm... not sure how to handle clean start vs. multiple runs for this:
# $dbh->do("DROP TABLE IF EXISTS reasons");
# $dbh->do("
# CREATE TABLE reasons (
# grades TEXT,
# raceEthnicity TEXT,
# eventName TEXT,
# occurrences INT
# )
# ");
my $strsql = <<SQL;
SELECT RaceEthnicity, eventName, count(*)
FROM disc
WHERE Grade IN ($grades)
AND RaceEthnicity = ?
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10;
SQL
my $sth1 = $dbh->prepare($strsql);
$strsql = <<SQL;
INSERT INTO reasons (grades, raceEthnicity, eventName, occurrences)
VALUES (?, ?, ?, ?)
SQL
my $sth2 = $dbh->prepare($strsql);
foreach my $race (
"African American",
"Asian",
"Hispanic",
"Multi Racial",
"Native American",
"Pacific Islander",
"White",
) {
$sth1->execute($race);
while (my @row = $sth1->fetchrow) {
say join "|", @row;
$sth2->execute($grades, @row);
}
}
sub usage {
say STDERR "Usage: $0 " . '--grades "\'PK\', \'KG\', 1, 2, 3"';
exit;
}