[Tutor] sqlite3 Dilemma

[Khalid Al-Ghamdi]

Hi All,

I am a teacher at an industrial training center. We have to conduct
hands-on exams (fixing pumps, etc). I review all the test schedules for all
the students (made by other teachers) to make sure there are no clashes due
to lack of ample supply of equipment or tools. Meaning no two trainees are
to have the same test at the same time. If this is the case, then the date
or time or test code have to be changed to avoid the conflict.

I have the schedule in csv format. I have been able to export it to sqlite3
as seen below and I have found all the conflicts, too.

My dilemma is how to go about making sqlite3 alter the date, time, or exam
code if another trainee is scheduled at the same time and date with the
same test code.

What is the proper approach to handle this?

I'm using python 3 on windows.

Below is how far I've gotten.

Thanks


   1. import csv, sqlite3
   2.
   3.
   4. conn = sqlite3.connect(":memory:") #unless this is ":memory:" this
   database will be created in your CWD which is the resolver folder
   5. c = conn.cursor()
   6. c.execute("create table test (teams integer, sn integer, badge
   integer ,name text, grp integer,\
   7. major text, track text, stage text,  tc text, subject, text, course
   text, ws text, date text, \
   8. time text, proctor text, code text, no integer)")
   9.
   10. reader = csv.reader(open("final.csv", "r"))
   11.
   12. for (teams, sn, badge , name, grp, major, track, stage, tc, subject,
    course, ws,\
   13.     date, time, proctor, code, no) in reader:
   14.     c.execute('INSERT INTO test \
   15.    (teams, sn, badge, name, grp, major, track, stage,tc, subject,
   course, ws, date, time, proctor, code, no)\
   16.    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
   17.     (teams, sn, badge, name, grp, major, track, stage, tc,subject,
    course, ws, date, time, proctor,code, no))
   18.
   19. c.execute("select code, date, time, count (*) from test group by
   code, date, time having count(*)>1")
   20. c1_list=list(c.fetchall())
   21. print(c1_list)
   22. print("your table has {} clashes".format(len(c1_list)))
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20120730/1917f31c/attachment.html>