Program 7 - Baseball Database in SQLite3
Consider the following data, stored in file db.sql, describing baseball teams and results of games.
pragma foreign_keys = OFF;
drop table if exists teams;
drop table if exists results;
pragma foreign_keys = ON;
create table teams (
tname varchar2(20),
primary key (tname)
);
insert into teams values ('ATL');
insert into teams values ('STL');
insert into teams values ('CHC');
insert into teams values ('ARI');
insert into teams values ('CLE');
--
create table results (
datePlayed date,
home varchar2(20),
visitor varchar2(20),
hruns number(3),
vruns number(3),
primary key (datePlayed,home),
foreign key (home) references teams,
foreign key (visitor) references teams
);
--
insert into results values ('2004-03-20','ARI','CHC',10,11);
--
insert into results values ('2004-03-23','ATL','STL',0,1);
insert into results values ('2004-03-27','STL','CHC',7,9);
insert into results values ('2004-03-27','CLE','ATL',1,0);
--
insert into results values ('2004-03-30','ATL','CHC',10,5);
insert into results values ('2004-04-01','CLE','ARI',8,8);
--
insert into results values ('2004-04-15','ARI','ATL',3,11);
insert into results values ('2004-04-17','CLE','STL',7,11);
--
insert into results values ('2004-04-20','STL','ARI',10,12);
insert into results values ('2004-04-22','CHC','CLE',7,4);
--insert into results values ('2004-04-24','CHC','ARI',7,12);
--
insert into results values ('2004-04-29','STL','ATL',2,10);
--
insert into results values ('2004-05-01','ATL','CLE',14,14);
insert into results values ('2004-05-01','CHC','STL',10,0);
--
--insert into results values ('2004-05-04','CHC','ATL',10,8);
insert into results values ('2004-05-04','ARI','CLE',8,7);
--
insert into results values ('2004-05-08','ATL','ARI',6,8);
--
insert into results values ('2004-05-13','STL','CLE',3,6);
--
insert into results values ('2004-05-15','ARI','STL',7,13);
insert into results values ('2004-05-15','CLE','CHC',6,8);
--
insert into results values ('2004-05-18','ARI','CHC',13,5);
--
insert into results values ('2004-05-22','ATL','STL',3,6);
--
To create the sqlite3 database run the following command on MacOS/Linux:
sqlite3 baseball.db < db.sql
and the following command on Windows Powershell:
cat db.sql | sqlite3 baseball.db
This should create the sqlite3 database for you. Now,
complete the Python program
baseball.py to view individual team results as shown in the
following program run:
mirage:baseball raj$ python3 baseball.py
(s) Standings
(t) Team results
(q) Quit
What do you want to see: s
TEAM WINS LOSSES TIES PERCENT
-------------------- ------ ------ ------ -------
CHC 5 2 0 0.714
ARI 4 3 1 0.500
STL 4 5 0 0.444
ATL 3 4 1 0.375
CLE 2 4 2 0.250
(s) Standings
(t) Team results
(q) Quit
What do you want to see: t
Enter team code (e.g. ARI, ATL, CHC, CLE, STL): ATL
Team: ATL
DATE OPPONENT US THEM RESULT
2004-03-23 STL 0 1 LOSS
2004-03-27 at CLE 0 1 LOSS
2004-03-30 CHC 10 5 WIN
2004-04-15 at ARI 11 3 WIN
2004-04-29 at STL 10 2 WIN
2004-05-01 CLE 14 14 TIE
2004-05-08 ARI 6 8 LOSS
2004-05-22 STL 3 6 LOSS
Overall Record: 3-4-1
(s) Standings
(t) Team results
(q) Quit
What do you want to see: q
mirage:baseball raj$