1. Objectives

After complete this assignment, students are expected to be able to use Python to

• create a SQLite3 database;
• create and alter attributes in a table;
• specify keys and foreign keys in a table;
• manipulate SQL database with individual SQL commands;
• be able to read CSV files and convert them into SQL database tables using Python;
• manipulate SQL database with Python functions.

2. Overview

In this assignment, you will learn how to manipulate SQLite3 database through the use of Python programming language. The actual database task is relatively simple. You will create a database with a single table; insert a few records into the database; alter information in the database; and query information from the database. All are done from within a Python program.

You are given a working Python program that works with a SQLite3 database, including creating the database, inserting records into the database, altering database information, and querying database. Your task is to study the program and re-write this program to re-create the database you did in Homework 1, except that you only need to have the Books table. You do not need to import the information from a spreadsheet any more. You can directly store the book information in a Python list, and create the Books table from the Python list.

Use the web as your source of information if you are not sure how to do certain things. Please don't hesitate to talk to your instructor if you have questions.

3. Setup

Create a directory named "hw02" in your "csci305" directory under your git repo. Download the sample Python program and the database created by the program to your "hw02" directory.

• sqlite3_db.py This is a sample Python program that works with SQLite3 database.
• testPY_DB.db This is SQLite3 database created by running the above program and naming the database testPY_DB.db.
• csv2list.py This example Python program reads the information from a CSV file and stores them into a Python list.

Alternatively you may copy all relevant files from the Linux directory.

~csci305/2018-spring/student/hw/hw02/*

4. Your Work

As discussed in the Overview section, your work is to revise the Python program sqlite3_db.py to create a database containing the table of Books that is similar to what you did in Homework 1. The Books table should contain the following attributes.

• Title should be a text string. You may use this as the primary key.
• Author_last_name should be a text string to mean the last name of the author.
• Author_first_name should be a text string to mean the first name of the author.
• Edited should be a text string to mean the name of the editor or translator of the book.
• Publisher should be a text string to mean the name of the publishing company of the book.
• Pub_year should be an integer to mean the year of publication.

Note that in Homework 1, some of us used variable names with double quotes. If you are comfortable with this notion, it is fine. If you prefer to use a more conventional variable name, you can use either the CamelCase notation or the more popular PEP 8 naming convention that uses lower case letters along with underscores. This link gives a more concise guideline from Google.

For your reference (or convenience), here is the books.csv file I generated when doing Homework 1.

You are given an example program csv2list.py that can read a CSV file and convert its content into a Python list. You may want to use this example in your program to read from the CSV file(s) and store the contents in Python list(s).

Note:

To make the grading a bit easier, please do the following.

• In the above description of your work, your Python program asks the user to type in a database file name. After completing your work, please make this file name a part of your program, i.e., do not ask the user to type in the file name when executing the program.
• After the program works correctly, before submission, rename the database generated by your program to be something else, e.g., hw1.db.orig

5. Submission

You are to submit the following four files through your git repo.

1. Revised and completed sqlite3_db.py;
2. The database file created by your program. (See the notes immediately before the Submission section);
3. A plain text file named README.txt briefly explain your experience in doing this work, any challenges, and any suggestions. A couple paragraphs should suffice.

6. References

• https://www.python.org/dev/peps/pep-0008/ PEP 8 style guide
• https://google.github.io/styleguide/pyguide.html A more concise guide from Google
• http://www.tutorialspoint.com/sqlite/ SQLite Tutorial
• https://www.tutorialspoint.com/sqlite/sqlite_python.htm SQLite and Python