[Tutor] How to effectively use a Student class with SQLite [Was Re: How to design object interactions with an SQLite db?]

Alan Gauld alan.gauld at btinternet.com
Fri Aug 14 01:38:33 CEST 2015

On 13/08/15 20:18, boB Stepp wrote:

> that I am puzzling over is the very real possibility of duplicate
> student names.  Of course, I was planning on the Student db table
> having a primary key of student_id, which would be an unique
> identifier for a particular student.  The problem with this is from
> the user's perspective:  The teacher will naturally want to interact
> with the program using the student's name, possibly even a student's
> nickname.  These can be non-unique.

Yes, that's a standard problem in any HR type application. Names suck as 
database keys. But names are how humans interact.

The only way around it that I know is for you to assign and use
student IDs in the code but on the UI use search screens that
90%+ return one student but occasionally return 2 or 3 (rarely
many more) and the user has to figure out which one they want..

If you have a search form you have to check for multiple responses 
before filling the form. If its true then pop up a list dialog
to let the user pick. (One nice feature if you are likely to doing
a lot with one student is to provide a "sticky flag" that remembers
the choice for all future queries in that session - you need a
reset option somewhere too of course or the user has to quit the
system and restart!)

But many colleges now issue student cards with a unique ID. So the 
problem may not be as hard as you think.

> So this leads me naturally to the UI design, something I would
> normally mostly forget about until I had my main program logic well
> worked out.  But in this project it seems to me that program logic, db
> and UI are all intimately intertwined.

Hopefully not, you can create a web/desktop or CLI UI.
The UI (or specifically the Controller in MVC terms)
may well dictate or at least influence the API to the
data (or Model in MVC terminology.) The display (View in MVC)
part of the UI should typically be driven by the API rather
than the other way round.

The other thing that can really help is to use the Use
Case methodology. Write out the interaction dialog between
user and system for the "happy path" scenario for a given task.
Then analyse each step in turn for potential error cases and
write out the use case for how you'd handle each error type.
There is a ton of Use case material online. It's a very powerful 
technique and since its text based, there's no new tools to learn.
I've used it on everything from small personal projects to
multi-million dollar projects with hundreds of programmers.
As a free side-effect you get free test scripts too.

> In terms of UI I imagine the user will have some sort of list of
> student names displayed.  In a given school year in the case of two
> students with identical names, I imagine the target user (my wife)
> will want a student nickname displayed to differentiate between two
> (or more) such students.  So she would select a student in the UI and
> start doing stuff.

Yep, that's usually how it works. The class or year or age could
also be choice differentiators too)

I assume if I have a list of student names in the
> UI, then the program already created all of the student objects in the
> list, so the student_id primary key for each student would be freely
> available to use.  However, if the student did not exist in the list,
> then a new student object would have to be created and its relevant
> attributes recorded in the student db table with its newly created
> student_id primary key.

The "happy path" use case is only one student returned.
The first error case is multiple students including an
identifiable target.
The second error case is no identified target.

You are already beginning to think like a use case approach,
the technique just formalises it and so forces you to uncover
errors you might otherwise miss.

> So (Finally!) if I am understanding the important issues involved in
> creating a viable Student class, I should be doing:
>      1)  For the current school year, at program startup, the program
> should retrieve all current students' attributes from the db,
> instantiate each such student object, and be able to make each
> available in the UI.

Maybe not. You could have a report object that displays a subset
of student attributes without creating an object per student.
Then only when it comes time to modify or operate on a student
in some way do you need to instantiate the student object with
its associated data and methods (don't forget in OOP its the
methods that are  important, the data is the support act.)
If you only want to view data you probably want a Report (or
Query if you prefer - but I prefer to name objects after
the output. The query is part of the constructor and other
methods of the record - refresh, filter, etc) rather than
an object per record.

>      2)  If a new student needs to be created by the user, then the
> Student class would have an appropriate method which would allow for
> data entry via the UI to both create a new student object and a new
> student db table entry with its own unique student_id primary key.

The UI will have a StudentView object that knows about the data needed 
to create or display Student details. It can validate input fields etc. 
Once complete the Save/OK button will send a create message to make a 
new StudentModel which saves the data into the database. That's the 
usual MVC pattern.

>      3)  The program logic would reference each student object only
> with the student_id primary key uniquely associated with each such
> student object.

The program logic of the Model class would. The users of the Model 
reference it through a Python variable like anything else. Its
object oriented programming not database oriented programming.
Use the objects.

> Is this the way to approach this, or at least a good way?

You are on the right track.

> I don't want to worry about actually coding the UI at this point, but
> it looks like while testing things as I go along, I will need to have
> at least a text simulation of a UI,

Yes, a basic CLI is a really useful test tool.

> create an old command line-style menu system, which lists options by
> number, input number desired, action occurs etc.?

It could be even more basic than that. Just a set of command line 
options. argparse (or one of its friends) will do the heavy
lifting there. An option letter for each API call and a list
of arguments that get translated into a function call. Building
test scripts then becomes trivial...

But if you waqnt some5hing friendlier then text menus is one option.
But rather than build a traditional menu system I'd recommend you
take a look at the cmd module. It gives you a lot of power for
free - its what the help() function (and pdb) all use. More to
learn though...


Alan G
Author of the Learn to Program web site
Follow my photo-blog on Flickr at:

More information about the Tutor mailing list