[Tutor] sqlite3 Dilemma
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)))
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite3 Dilemma
On 30 July 2012 14:57, Khalid Al-Ghamdi emailkg...@gmail.com wrote: 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. How exactly is the date or time changed, and by who? Is the process always the same or is there discretion involved? 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? It depends on the current process of managing collisions. If this is discretionary, then the approach for the program should be one where your program identifies the collisions, identifies suitable alternatives and then requests the user to supply the alternate date/time to resolve the conflict, repeating this for every conflict. If the collision management rules can be expressed entirely objectively then it might perhaps be fully automated. There may also be a best effort type middle road where many/most cases can be automatically handled while some will require operator input. By the way, nice job using SQLite to do set based operations. To move toward a solution, thinking off the top of my head, you will need to probably do something along the following lines: 1) For each collision detected in your previous work, find the details for that specific collision. 2) Determine which of the entries will keep the slot and remove it from the list of colliding entries 3) Find alternate slots for each of the remaining entries. 3) Will involve something like: 3.1) Determine list of suitable/available slots, suitably ordered 3.2) Update the colliding entry with a new selected date/time 3.3) Repeat for each collision. So 1) would involve another query, 2) involve input from the user, 3) likely involves 2 more queries, one for retrieving available slots and one for updating with a new date/time Finally I imagine you may want to write the entire lot back out to CSV (not sure if that's your goal or not.) Anyway, that's just a few thoughts off the top of my head. Walter ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite3 Dilemma
Thanks for your input Walter. Are there any techniques to achieve this via code or sql you share. Because I was thinking along the same you mentioned but didnt knwo how to go about it. بتاريخ الاثنين، ٣٠ يوليو، ٢٠١٢، جاء من Walter Prins wpr...@gmail.com: On 30 July 2012 14:57, Khalid Al-Ghamdi emailkg...@gmail.comjavascript:; wrote: 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. How exactly is the date or time changed, and by who? Is the process always the same or is there discretion involved? 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? It depends on the current process of managing collisions. If this is discretionary, then the approach for the program should be one where your program identifies the collisions, identifies suitable alternatives and then requests the user to supply the alternate date/time to resolve the conflict, repeating this for every conflict. If the collision management rules can be expressed entirely objectively then it might perhaps be fully automated. There may also be a best effort type middle road where many/most cases can be automatically handled while some will require operator input. By the way, nice job using SQLite to do set based operations. To move toward a solution, thinking off the top of my head, you will need to probably do something along the following lines: 1) For each collision detected in your previous work, find the details for that specific collision. 2) Determine which of the entries will keep the slot and remove it from the list of colliding entries 3) Find alternate slots for each of the remaining entries. 3) Will involve something like: 3.1) Determine list of suitable/available slots, suitably ordered 3.2) Update the colliding entry with a new selected date/time 3.3) Repeat for each collision. So 1) would involve another query, 2) involve input from the user, 3) likely involves 2 more queries, one for retrieving available slots and one for updating with a new date/time Finally I imagine you may want to write the entire lot back out to CSV (not sure if that's your goal or not.) Anyway, that's just a few thoughts off the top of my head. Walter ___ Tutor maillist - Tutor@python.org javascript:; To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] sqlite3 Dilemma
Khalid Al-Ghamdi wrote: 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 think what you have is a constraint satisfaction problem. There are packages in Python that address it, but I have no experience with them and therefore cannot make any recommendations. This one http://pypi.python.org/pypi/constraint/ ...has an example similar to your problem which may help you get started: http://www.logilab.org/3441 I'm using python 3 on windows. I see no mention of Python 3 on their site, so you probably have to resort to Python 2 should you end up with the solver mentioned above. Below is how far I've gotten. I'd forget the database for the moment, but keep the code to verify the solution once you have arrived at it through other means. ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor
[Tutor] sqlite3 Dilemma
Damn! So its so serious they have a name for it! No wonder I was stumped
...
Any way thanks you guys have been super helpful.
بتاريخ الاثنين، ٣٠ يوليو، ٢٠١٢، جاء من Peter Otten
__pete...@web.dejavascript:_e({}, 'cvml', 'pete...@web.de');
:
Khalid Al-Ghamdi wrote:
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 think what you have is a constraint satisfaction problem. There are
packages in Python that address it, but I have no experience with them and
therefore cannot make any recommendations. This one
http://pypi.python.org/pypi/constraint/
...has an example similar to your problem which may help you get started:
http://www.logilab.org/3441
I'm using python 3 on windows.
I see no mention of Python 3 on their site, so you probably have to resort
to Python 2 should you end up with the solver mentioned above.
Below is how far I've gotten.
I'd forget the database for the moment, but keep the code to verify the
solution once you have arrived at it through other means.
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
