Database from CSV Homework

For this assignment you will build a database from a CSV file, much like we did in class with the dogs.csv file. See the build_dog_db.py file from the in-class activity for an example.

Instead of using dogs.csv, you will use fifa18.csv which is available here. Download this file and place it in the directory for the assignment. To download the file rather than display it in your browser, right click on the link and select the option to save the link.

This CSV file is a trimmed down version of this data set:

https://www.kaggle.com/thec03u5/fifa-18-demo-player-dataset

Requirements

Write a program in build_fifa18_db.py which reads fifa18.csv and creates a SQLite database in the file fifa18.sqlite. As we did with dogs.csv, you must put the data in multiple tables rather than putting all the data in a single table. You will need to decide the best way to organize the schema.

You do not need to use every column from the CSV. At a minimum, you must be able to access the following information about a player from your database:

You can store more information if you like, but it is not required.

If Python complains about not being able to decode characters, you will need to explicitly tell the open() function about the character encoding of the file like this:

    with open('fifa18.csv', encoding='utf-8') as f:
        ...

To demonstrate how to query your database, write a query in the file query.sql which selects the above information about a specific player using a player’s primary key. You can hardcode the primary key in the query. For example you might have player_id = 1 in your query.

Related tables must be linked together with foreign key constraints.

Submission

Submit your modified versions of build_fifa18_db.py and query.sql to git-keeper, but do not add fifa18.csv or fifa18.sqlite.

Your code will be checked for PEP 8 violations, but correctness will be graded manually.