Python & databases in Python 2.7

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.

id	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

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.

Below at the bottom is the data the students.csv file

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.

Below is the data in the students.csv file

id,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 1398,Rivka,Alexander,39,41,103,EET,20,ralexander@tru.edu 1498,Ned,Ramos,28,28,100,BUS,30,nramos@tru.edu 1675,Jon,Martinez,36,36,86,WEB,10,jmartinez@tru.edu 1686,Carl,McElan,51,54,155,WEB,10,cmcelan@tru.edu 1842,Bob,Palla,17,21,36,CIT,20,bpalla@tru.edu 1870,Tilda,Downey,30,31,86,CIT,40,tdowney@tru.edu 2059,Mary,Proth,33,33,105,CIT,50,mproth@tru.edu 2191,Zelda,Rowe,12,12,39,BUS,40,zrowe@tru.edu 2268,Alice,Vonna,7,7,22,CIT,20,avonna@tru.edu 2355,Manny,Alvarez,48,48,192,CIT,50,malvarez@tru.edu 2453,Sally,Eldorodo,53,53,168,CIT,50,seldorodo@tru.edu 2467,Jena,Hardy,9,14,23,CIT,10,jhardy@tru.edu 2575,Donald,Bennett,27,27,78,CIT,30,dbennett@tru.edu 2579,Jack,Bass,55,55,200,CIT,30,jbass@tru.edu 2588,Rick,Cildaro,47,47,140,BUS,10,rcildaro@tru.edu 2756,Dave,Bean,14,14,44,CIT,20,dbean@tru.edu 3117,Jenn,Labanda,21,21,44,EET,40,jlabanda@tru.edu 3726,Elle,Rose,30,34,113,CIT,50,erose@tru.edu 3968,Larissa,Tolfer,17,19,37,EET,50,ltolfer@tru.edu 3983,Jenna,Mamiya,61,61,201,CIT,30,jmamiya@tru.edu 4235,Jayson,Matherson,13,16,40,CIT,40,jmatherson@tru.edu 4982,Karl,Riccardo,22,24,45,CIT,10,kriccardo@tru.edu 4991,Clevon,Forrester,40,44,103,EET,50,cforrester@tru.edu 5435,Joyce,Harden,21,21,75,CIT,50,jharden@tru.edu 5462,Moses,Fernandez,32,32,99,WEB,40,mfernandez@tru.edu 6084,Albert,Morris,21,21,67,CIT,20,amorris@tru.edu 6241,Aaron,Lee,32,32,100,EET,40,alee@tru.edu 6645,Ellen,Alvarez,27,27,100,CIT,20,ealvarez@tru.edu 6875,Jemma,Furfy,26,26,99,CIT,10,jfurfy@tru.edu 6896,Jan,Hurzell,54,60,176,CIT,10,jhurzell@tru.edu 6917,Morris,Subuman,17,17,54,CIT,20,msubuman@tru.edu 6984,Mika,Stanton,27,27,87,CIT,50,mstanton@tru.edu 7204,Paul,Brand,29,29,88,CIT,30,pbrand@tru.edu 7205,Ilsa,Frost,19,22,50,BUS,50,ifrost@tru.edu 7242,Boris,Berguin,35,35,111,BUS,30,bberguin@tru.edu 7287,Carol,Ramos,9,12,21,WEB,10,cramos@tru.edu 7617,Jack,Hall,42,44,99,CIT,10,jhall@tru.edu 8058,Allan,Iberia,17,17,59,CIT,50,aiberia@tru.edu 8290,Kelly,Fleischer,28,28,100,EET,30,kfleischer@tru.edu 8340,Will,Wall,35,35,91,CIT,20,wwall@tru.edu 8618,Frank,Zimmerman,25,25,100,CIT,50,fzimmerman@tru.edu 8712,Sasha,Olson,48,53,106,CIT,10,solson@tru.edu 8766,Len,Geng,18,18,66,CIT,40,lgeng@tru.edu 8771,Ortiz,Prince,23,23,88,EET,20,oprince@tru.edu 9193,Tyler,Scott,27,27,74,BUS,20,tscott@tru.edu 9451,Celest,Nguyen,50,55,100,CIT,40,cnguyen@tru.edu 9547,Linda,Sharpe,47,50,101,BUS,40,lsharpe@tru.edu 9649,Nancy,Detton,25,25,79,CIT,30,ndetton@tru.edu 9736,Darla,Campbell,26,26,83,CIT,10,dcampbell@tru.edu