PYTHON There should be a file called \"Baseball_Data_Small.csv\" on D2L. FILE LO

Question

PYTHON

There should be a file called "Baseball_Data_Small.csv" on D2L. FILE LOCATED AT BOTTOM This text file contains 200 lines of data related to professional baseball players. Each line of data is of the following format :

<year>,<last>,<first>,<league>,<atbats>,<runs>,<hits>,<average>

<year>, <at bats>, <runs>, and <hits> are integers.

<last>, <first>, and <league> are strings.

<average> is a double.

Example:

1980,Adams,Glenn,AL,262,32,75,.286

I have provided the following additional information for those not familiar with baseball:

year

The year the data was collected.

Notes: An integer that ranges from 1980-2000.

last

The last name of the baseball player.

Notes: Text data.

first

The first name of the baseball player.

Notes: Text data.

league

Which league the player is in.

Notes: Text data. AL for American League, NL for National League.

atbats

The number of times the player got to bat.

Notes: An integer that ranges from 0 to 9,999,999.

runs

The number of runs the player scored.

Notes: An integer that ranges from 0 to 9,999,999.

hits

The number of times the player safely hit the ball and made it to a base.

Notes: An integer that ranges from 0 to 9,999,999.

average

The number of hits a player had divided by the number of atbats.

NOTE: Re-Write Using Module Variable

Problem 1 Create a function called readData that has one input parameter. The input parameter is the name of the file to be read in. Any file read in should be formatted as explained above. The function will read in all the data and store it into a List. The list needs to be a module level variable called baseballList.

Example:If "Baseball Dataset.csv" contained the following lines of text:

1980,Adams,Glenn,AL,262,32,75,.286 1980,Aikens,Willie,AL,543,70,151,.278 1980,Ainge,Danny,AL,111,11,27,.243

The list baseballList would look like:

[[1980,"Adams","Glenn","AL",262,32,75,.286], [1980,"Aikens","Willie","AL",543,70,151,.278], [1980,"Ainge",Danny","AL",111,11,27,.243]]

Problem 2 Create a function called constructDB that has no input parameters and will create a database called baseball and a table called players in that database. Each row of the table should be able to hold the information for one baseball player. The table players should consist of 8 columns: 4 of which store integer values (year, at bats, runs, hits), 3 of which store string values (last, first, league), and 1 which stores a double (average). The function should not try and create the database or the table if they already exist.

Problem 3Create a function called storeDataInDB that will use the baseball database and store all the data the list baseballList into the table called players. After the data from the list has been stored in the database it should be removed from the List baseballList.

If baseballList stored the following: [[1980,"Adams","Glenn","AL",262,32,75,.286], [1980,"Aikens","Willie","AL",543,70,151,.278], [1980,"Ainge","Danny","AL",111,11,27,.243]]

Then after calling storeDataInDB the table players should look like:

year

last

first

league

atbats

runs

  

hits

average

1980

"Adams"

"Glenn"

"AL"

262

32

75

0.286

1980

"Aikens"

"Willlie"

  

"AL"

543

70

151

0.278

1980

"Ainge"

"Danny"

  

"AL"

111

11

27

0.243

And baseballList should be empty.

Data File: Baseball.Data.Small-2.csv IN EXCEL

Rows:

A B C D E F G H

1   1980

year

The year the data was collected.

Notes: An integer that ranges from 1980-2000.

last

The last name of the baseball player.

Notes: Text data.

first

The first name of the baseball player.

Notes: Text data.

league

Which league the player is in.

Notes: Text data. AL for American League, NL for National League.

atbats

The number of times the player got to bat.

Notes: An integer that ranges from 0 to 9,999,999.

runs

The number of runs the player scored.

Notes: An integer that ranges from 0 to 9,999,999.

hits

The number of times the player safely hit the ball and made it to a base.

Notes: An integer that ranges from 0 to 9,999,999.

average

The number of hits a player had divided by the number of atbats.

Explanation / Answer

Problem 1 Solution:

import csv

# Function for reading data from CSV files and storing in list variable
def readData(input_file):
   f = open(input_file)
   csv_f = csv.reader(f)
   baseballList = [] #list initializing

   for row in csv_f:
       baseballList.append(row) #fetching data and appending it to the list

   print baseballList #printing the data present in list

_______________________________________________________________________________________________

Problem 2 Solution:

import MySQLdb

# Function for constructing a database and constructing a table in it
def constructDB:
   conn = MySQLdb.connect(user='root')
   conn.query('CREATE DATABASE baseball') # creates a database
   conn.commit()
   conn.close()

   conn = MySQLdb.connect(db='baseball')
   cur = conn.curor()
   cur.execute('CREATE TABLE players(year INT, last varchar2(20), first varchar2(20), league varchar2(20), atbats INT, runs INT, hits INT, average decimal(10,5))') # creates a table
   conn.close();

_______________________________________________________________________________________________

Problem 3 Solution:

import MySQLdb

# Function for storing data in to database
def storeDataInDB:
   conn = MySQLdb.connect(db='baseball') # Database connection
   cur = conn.curor()
  
   # Fetching data from list and storing in database table
   for player in baseball:
       cur.execute('INSERT INTO players values(player[0], player[1], player[2], player[3], player[4], player[5], player[6], player[7])')

   baseball[:] = [] #clears the list
   conn.close();

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.