CIS $3145$ Project $\backslash$#$4$

Team Management Program: Version $4$

Use a database to store the data for the Baseball Team Manager program. The user interface and objects class will change very little. This is an exercise in changing the data layer tier with minimal changes to the interface and business logic tiers.

Player table sql script

The following is the sql script that can be used to create the sqlite database table. This script is provided only to illustrate how the database is created and is not used in the program. The "player$\_$db$.$sqlite" file required for the project is included on the assignment page. Keep a backup copy of the sqlite file in case the data becomes corrupted. If the file is OKB replace it with a copy of the backup. Note that the Player table has a playerID and batOrder field. The Player class will need to be updated to have a playerID attribute and batOrder attribute.

$```$

$--$ Drop the table if it already exists

DROP TABLE IF EXISTS Player;

$--$ Create the table

CREATE TABLE Player$($

playerID INTEGER PRIMARY KEY NOT NULL,

batOrder INTEGER NOT NULL,

firstName TEXT NOT NULL,

lastName TEXT NOT NULL,

position TEXT NOT NULL,

atBats INTEGER NULL,

hits INTEGER NULL

$)$;

$--$ Populate the table

INSERT INTO Player VALUES

$(1,1,$'Dominick','Gilbert',$\text{'}1$B$\text{'},537,170),$

$(2,2,$'Craig','Mitchel',$\text{'}$CF$\text{'},396,125),$

$(3,3,$'Jack','Quinn', $\text{'}$RF$\text{'},545,99),$

$(4,4,$ 'Simon','Harris',$\text{'}$C$\text{'},450,135),$

$(5,5,$'Darryl','Moss', $\text{'}3$B$\text{'},501,120),$

$(6,6,$'Grady','Guzman',$\text{'}$sS$\text{'},443,131),$

$(7,7,$'Wallace','Cruz',$\text{'}$LF$\text{'},443,131),$

$(8,8,$'Cedric','Cooper',$\text{'}2$B$\text{'},165,54),$

$(9,9,$ 'Alberto','Gomez',$\text{'}$P$\text{'},72,19)$;

$```$

DB Specifications

$-$ Modify the db module so that it provides all functions necessary to work with the player data. This will include functions for reading all players, adding a player, deleting a player, updating the batting order for all players, and updating the data for a player.

$-$ To update multiple columns for a single row, you can use an SQL statement like this:

$```$

UPDATE Player

SET position $=?,$

atBats $=?,$

hits $=?$

WHERE playerID $=?$

$```$ CIS $3145$ Project $\backslash$#$4$

Submission details

Projects can require the use of material from any of the previous chapters covered in the textbook.

Create a Team Management Python program that satisfies the specifications above.

Put General Comments at the beginning of the project that includes $(1)$ your name, $(2)$ the project name, $(3)$ the date, and $(4)$ a description of the project.

Turn in a ZIP file of the final version of the program. Include a Word document which contains output of the testing done on the program. The Word document must be inside the ZIP file.

In the Comments section on the Assignment webpage, report $($A$)$ an estimate of the time it took to complete the project. Report a single value in minutes, and $($B$)$ a single rating of the project, on an ordinal scale, as either $(1)$ Easy, $(2)$ Moderate, $(3)$ Hard, OR $(4)$ Challenging. $```$

import sqlite$3$

from contextlib import closing

from Objects import Player, Lineup

conn $=$ None

def connect$()$:

global conn

if not conn:

DB$\_$FILE $=$ "player$\_$db$.$sqlite"

conn $=$ sqlite$3.$connect$($DB$\_$FILE$)$

conn.row$\_$factory $=$ sqlite$3.$Row

def close$()$:

if conn:

conn.close$()$

def get$\_$players$()$:

return None #remove this line when code is added.

# SQL statement to select all $7$ fields for all players

# Use a with statement to execute the query

# Create a lineup object

# use a loop to populate the lineup object with player objects

# return the lineup object

def get$\_$player$($playerID$)$:

return None #remove this line when code is added.

# SQL statement to select all $7$ fields for a player

# Use a with statement to execute the query & return a player object if the

player exists

def add$\_$player$($player$)$:

return None #remove this line when code is added.

# SQL statement to insert $6$ fields for a player added to the table

# Use a with statement to execute the query

def delete$\_$player$($player$)$:

return None #remove this line when code is added.

# SQL statement to delete a single player

# Use a with statement to execute the query

def update$\_$bat$\_$order$($lineup$)$:

return None #remove this line when code is added.

$``````$

# Use a loop to call a SQL statement that updates

# the batOrder for each player based on their playerID

# Use a with statement to execute the query

def update$\_$player$($player$)$:

return None #remove this line when code is added.

# SQL statement to update $6$ fields of a player based on the playerID

# Use a with statement to execute the query

def main$()$:

# code to test the get$\_$players function

connect$()$

players $=$ get$\_$players$()$

if players $!=$ None:

for player in players:

print$($player$.$batOrder, player.firstName, player.lastName,

player.position, player.atBats, player.hits,

player.getBattingAvg$())$

else:

print$("$Code is needed for the get$\_$players function."$)$

if $\_\_\_$mame$\_\_=="\_\_$main$\_\_"$:

$```$

for i$,$ player in enumerate$($lineup$,1)$:

print$($f$"\{$i:$<mn>3</mn><mi>\}</mi><mtext></mtext><mi>\{</mi>$player$.$get$\_$full$\_$name$()$:$<mn>2</mn><mn>0</mn><mi>\}</mi><mtext></mtext><mi>\{</mi>$player$.$position:$<mn>4</mn><mi>\}</mi><mtext></mtext><mi>\{</mi>$player$.$at$\_$bats:$<mn>5</mn><mi>\}</mi><mtext></mtext><mi>\{</mi>$player$.$hits:$<mn>5</mn><mi>\}</mi><mtext></mtext><mi>\{</mi>$player$.$get$\_$batting$\_$average$()$

print$("="*60)$

def add$\_$player$($lineup$)$:

first$\_$name $=$ input$("$Enter player's first name: $")$

last$\_$name $=$ input$("$Enter player's last name: $")$

position $=$ input$("$Enter