Python & databases in Python 2.7 SQLITE3 and the WX LISTCTRL

Please start early in the week that this program is due; this gives you plenty of time to ponder how is should work. Moreover, there will be more to ponder as the course progresses. I am glad to help troubleshoot programs, but I go to bed reasonably early and am unlikely to stay up until midnight of the due date.

Write a program that reads a table from a database into a wx.ListCtrl. The database will be named college.sqlite and the table is to be named students. A link to a copy of the students data in the form of a CSV file is given later in this document. The CSV file contains a row of 9 heading and then 50 rows of student records. A single record and the proper field names for the table should look like this. The id is the primary key.

Your interface will need a wx Frame and a wx ListCtrl and 3 buttons. A display button loads all of the student table into the list control. An insert button will open a wx Dialog that allows the user to enter the 9 data items used for a student record. When the user closes the dialog the new record will be SQL INSERTED to the students table and the list control re-populated so as to display the inserted record. You also need a close button to close the program.

A possible interface might look like this; you may design your own layout as long is it works like it should. I've abbreviated the field names a bit to save a little space in the list control, but the list control should be wide enough that you don't need the horizontal scroll bar. The vertical scroll bar is fine.

Here's a link to the CSV file.

students.csv

If clicking the link opens the file, just save it to a convenient location.

Here's what you need to do to get 100% on this one.

create a database named college.sqlite and create a table named students with the field names spelled exactly like those given in the CSV file. I recommend using the Firefox SQlite Manager, but you may use whatever program or method you want.

Your program should work with my database.

import the CSV file into the students table with the exact field names as given in the file. You should have 50 records.

your program should properly connect to the database you created using techniques from the lecture notes. but, if you want to improvise a bit, that is OK too.

the id field should be an integer primary key (we will limit ourselves to 4 digits).

hrs_attempted should probably be a float. this will avoid an integer division issue later.

major should be of type text, (but we will limit ourselves to a 3-character code).

advisor_id should be an integer (but we will limit ourselves to using 2 digits).

the first and last names and email should be of type text

the display button should load the whole table into the list control

the insert button should open a custom dialog that allows entering a complete record

when the dialog is closed the new record is added to the student table using a SQL INSERT statement and the updated table should be re-displayed in the list control

the close button closes the program.

you may use sizers or absolute positioning, your choice.

If you get good and stuck attach a compressed version of your code (or paste it into the body of an email) and email it to me; I'll be glad to help you troubleshoot it. I have a copy of the database, so you don't need to send that.

Bonus Opportunity: if you do all of the above successfully and add a mechanism to use the list control to edit any data except the id in the underlying table, I'll add make your score 125%. But this step is only for those who like a challenge.

I have the csv file on my pc

first_name last_name hrs_completed hrs_attempted gpa_points major advisor_id email 1121 Rita Solertan 20 23 28 CIT 10 rsolertan@tru.edu first_name last_name hrs_completed hrs_attempted gpa_points major advisor_id email 1121 Rita Solertan 20 23 28 CIT 10 rsolertan@tru.edu