Lab 5: Database Loader Solution

$30.00

Description

Introduction

Lab 5 is an opportunity for you to automate a common database administrator activity using Python. You are provided a small file containing student registration records. Each record contains information about the student and the class in which they are registered. You are to take this information and create two tables in a database using the Python scripting language.

The input file is in Comma Separated Values format (CSV) and the output is a SQLite3 database. Python provides support for both file types using plug-in modules.

Requirements

Your Python script shall be named dbload and be marked executable.

Usage: ./dbload INPUTCSV OUTPUTDB

Your Python script shall read CSV file INPUTCV and create a SQLite3 database OUTPUTDB . In that database you will create two tables classes and students with the following attributes (please use these exact names and

data types):

classes

id (text)

subjcode (text)

coursenumber (text)

termcode (text)

TEXT

students

id (text, primary key unique)

lastname (text)

firstname (text)

major (text)

email (text)

city (text)

state (text)

zip (text)

TEXT

A sample input CSV file is found at /var/classes/cs3030/lab5/studentregs.csv . Inspect it carefully to learn where the fields are located. The following is true about all input files you will need to process:

The field separator character is a comma. Double quote marks are used to enclose strings with imbedded spaces.

There is one record for each class registration and there may be more than one registration per student.

The student information is duplicated for each class registration by that student.

The data is complete and in its proper format so (hopefully) no error checking should be required while reading the data.

The first record of the CSV file is a typical CSV header record; skip over it.

There should be no blank records in the CSV file.

Two fields in the CSV file require explanation:

Use the wnumber field in the CSV file as the ID field in both tables

The course field in the CSV file is composed of the subject code and the course number. For example, course “CS 3030” is composed of the subject code CS and the course number 3030. Break it up using split(” “) as in this example (adapt this snippet to your code):

s = “CS 3030″.split(” “)

# s[0] is “CS”, s[1] is “3030”

Hints

Suggested logic (and this is not the only way it could be done):

Issue an appropriate usage message and exit(1) if the user did not specify both the input CSV file and the output database file.

Open the CSV file using try/except and print the exception and `exit(1)`if an error occurs.

Open the database using try/except and print the exception and exit(1) if an error occurs

Drop the two tables if they exist and create the two tables.

For each record in the CSV file:

skip the header record (the first record in the CSV file)

attempt to retrieve the student from the students table using the wnumber

if the retrieval fails, insert the student record into the students table insert the class record into the classes table

exit(0) after all records have been added to the database

The students table uses the ID field as a primary key; attempts to add duplicate students will result in an exception in SQLite3.

Add comments to document your logic.

Don’t forget to import modules sqlite3 , csv and sys or you will have errors galore.

Use /usr/bin/python for this lab.

CS 3030 Cowan 01-07-2019 07:26 PM 2

Clone your private repo on github.com

In the assignment module in Canvas, find the link to the Canvas page entitled “Clone your Lab 5 Repo”, click on it and follow the instructions.

Copy your private repo down to icarus

BASH

git clone https://github.com/cowancs3030spring19/lab5-YOURGITHUBUSERNAME

cd lab5-YOURGITHUBUSERNAME

Write and test dbload

Fire up your favorite text editor, and update the header:

#!/usr/bin/python

TEXT

  • (Your name)

  • Lab 5 – Database Loader

  • CS 3030 – Scripting Languages

(add your celestial code here)

Run cucumber to check your progress

./cucumber -s

  • cucumber randomly generates testfiles so you will want to run cucumber many, many times to verify your script’s operation.

Submit your assignment code for grading

  • Remember, you must push your script in your private repo to github.com to receive any points, for all assignments in this course.

BASH

git add dbload

git commit -m“COMMIT MESSAGE”

git push origin master

Files created for this lab

dbload

Grading

Here is how you earn points for this assignment:

CS 3030 Cowan 01-07-2019 07:26 PM 3

FEATURES

POINTS

Must-Have Features

Script is named correctly and found in its proper place in your private repo

5

Script is executable

5

Required Features

Script prints a “Usage:” statement and exits rc=1 if either the INPUTCSV or

10

OUTPUTDB files are not specified on the commandline

Script prints an error message containing the word “Error” and exits rc=1 if the

15

INPUTCSV file cannot be opened

Script prints an error message containing the word “Error” and exits rc=1 if the

15

OUTPUTDB file cannot be opened

Script exits rc=0 on successful completion

10

Script correctly defines the students table in the database

30

Script correctly defines the classes table in the database

30

Script adds the right number of students to the students table

40

Script adds the right number of classes to the classes table

40

Script add the correct student data to the students table

50

Script adds the correct classes data to the classes table

50

Grand Total

300

CS 3030 Cowan 01-07-2019 07:26 PM

4


error: Content is protected !!