Menu
▾
▴
[SQLObject] Deleting items
|
From: Chris P. <ch...@sp...> - 2003-04-29 13:19:52
|
Hi
Sorry:
- if this is a basic question
- if I've already posted the same question, but my connection
dropped while I sent it and I can't find it in the archive
Anyway, when I try and delete an item from my table I get
Traceback (most recent call last): File "/var/www/cgi-bin/timesheet.py", line 267, in ? e.destroy() File
"/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 831, in destroy self._connection.cache.purge(self.id) AttributeError: 'CacheSet'
object has no attribute 'purge'
Although the item is deleted from the table.
The code that I'm using is
if form['action'].value == "delEntry":
if form.has_key("entryID"):
e = Entry(int(form['entryID'].value))
e.destroy()
int(form['entryID'].value) is definitely a valid integer for
the table.
Using MySQL-python V0.9.2
Using MySQL V4.0.12
Using Python V2.2
Full code and table structure below
Thank you!
Chris P
DROP DATABASE IF EXISTS speak_timesheet;
CREATE DATABASE speak_timesheet;
Use speak_timesheet;
CREATE TABLE user (
id SMALLINT NOT NULL AUTO_INCREMENT,
first_name CHAR(20) NOT NULL,
last_name CHAR(30) NOT NULL,
login_name CHAR(20) NOT NULL,
user_state ENUM("Active", "Inactive") DEFAULT "Active" NOT NULL,
normal_week_hours DOUBLE(5,2) DEFAULT 37.5 NOT NULL,
toil_remaining SMALLINT DEFAULT 0 NOT NULL,
toil_expire SMALLINT DEFAULT 0 NOT NULL,
holiday_year_start DATE NOT NULL,
holidays_per_year SMALLINT DEFAULT 25 NOT NULL,
holidays_used SMALLINT DEFAULT 0 NOT NULL,
PRIMARY KEY (id),
UNIQUE INDEX (login_name),
);
LOAD DATA INFILE 'c:/mysql/tsload/users.txt' INTO TABLE user;
CREATE TABLE week (
id INT NOT NULL AUTO_INCREMENT,
start_date DATE NOT NULL,
week_state ENUM("Open", "Closed", "Archive") DEFAULT "Open" NOT NULL,
user_id SMALLINT NOT NULL,
work_hours DOUBLE(5,2) DEFAULT 0 NOT NULL,
target_hours DOUBLE(5,2) NOT NULL,
holiday_used DOUBLE(5,2) DEFAULT 0 NOT NULL,
PRIMARY KEY (id),
);
CREATE TABLE entry (
id INT NOT NULL AUTO_INCREMENT,
week_id INT NOT NULL,
dow INT NOT NULL,
type ENUM("Normal", "Holiday", "Sick", "Bank Holiday") DEFAULT "Normal" NOT NULL,
duration DOUBLE(5,2) NOT NULL,
PRIMARY KEY (id),
);
#!/usr/bin/python
from SQLObject import *
import sys
import os
import string
import time
import cgi
import Cookie
import re
######## Utility Functions #############
__connection__ = MySQLConnection('localhost', "speak_timesheet","cgiuser", "")
dayFromName = { "Mon" : 0, "Tue" : 1, "Wed" : 2, "Thu" : 3, "Fri" : 4, "Sat" : 5 , "Sun" : 6 }
daysOfWeek = ("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
def firstDayOfThisWeek():
timeNow = time.time()
tim = time.localtime(timeNow)
timeNow = timeNow - (tim[6] * 24 * 3600) - (tim[3] * 3600) - (tim[4] * 60) - tim[5]
return time.localtime(timeNow)
def strptime_cjp(tstr):
y, m, d = string.split(tstr, '-')
return (int(y), int(m), int(d), 0, 0, 0, 0, 0, -1)
def timeToFloat(timeStr):
try:
ret = re.match('([0-2]*[0-9]):([0-5][0-9])', timeStr)
retValue = round(float(float(ret.group(1))) + float(float(ret.group(2))/60),1)
except AttributeError:
pass
else:
return retValue
try:
ret = re.match('([0-2]*[0-9])', timeStr)
retValue = round(float(int(ret.group(0))),1)
except AttributeError:
pass
else:
return retValue
return None
######## DB Classes ####################
class User(SQLObject):
_fromDatabase = True
_columns = [Col('loginName',alternateID=True)]
_joins = [MultipleJoin('Week')]
def updateWeek(self):
if self.userState != 'Active':
return
# add up holiday from this holiday year and total
# add up toil from last 6 weeks before start of this week
startThisWeek = time.mktime(firstDayOfThisWeek())
seventyDaysAgo = startThisWeek - (70 * 24 * 3600)
fiftySixDaysAgo = startThisWeek - (56 * 24 * 3600)
toilRemaining = 0
toilExpire = 0
for w in self.weeks:
if w.weekState == "Closed":
startWeek = time.mktime(strptime_cjp(w.startDate))
if startWeek >= seventyDaysAgo:
toilRemaining = toilRemaining + w.workHours - w.targetHours
if startWeek < fiftySixDaysAgo:
toilExpire = toilExpire + w.workHours - w.targetHours
self.toilRemaining = int(toilRemaining)
self.toilExpire = int(toilExpire)
def userPrint(self):
print '<table width=550 col=4 BGCOLOR="#ffff33">'
print '<tr><td>Name: </td><td><b> ' + self.firstName + ' ' + self.lastName + \
'</b></td><td>  </td><td> </td></tr>'
print '<tr><td>TOIL remaining (hours):</td><td><b>'+str(self.toilRemaining)+'</b></td><td>' + \
'Expire next 2 wks:</td><td><b> '
tr = self.toilExpire
if tr > 0:
print '<font color="#990000">' + str(tr) + '</font>'
else:
print str(tr)
print '</b></td></tr>'
print '<tr><td>Holiday year start:</td><td><b>' + self.holidayYearStart + '</b></td>' + \
'<td>Normal hours/week:</td><td><b>' + str(self.normalWeekHours) + '</tr>'
print '<tr><td>Holiday remaining: </td><td><b>' + \
str(self.holidaysPerYear - self.holidaysUsed) + '</b></td>' + \
'<td>Holiday per year</td><td><b>' + str(self.holidaysPerYear) + \
'</b></td></tr></table>'
class Week(SQLObject):
_fromDatabase = True
_columns = [Col('userID',foreignKey='User')]
_joins = [MultipleJoin('Entry')]
_defaultOrder = "start_date DESC"
def closeWeek(self):
if self.weekState != 'Closed':
self.weekState = 'Closed'
self.workHours = 0
self.holidayUsed = 0
# For each entry in week:
# Add up hours worked if counted as worked
# Add to holiday if holiday
# Then call person close week to change totals
for e in self.entrys:
if e.type == 'Normal' or e.type == 'Sick' or e.type == 'Bank Holiday':
self.workHours = self.workHours + e.duration
elif e.type == 'Holiday':
if e.duration > 3.75:
holiday = 1
elif e.duration > 0:
holiday = 0.5
else:
holiday = 0
self.holidayUsed = self.holidayUsed + holiday
p = self.user
p.updateWeek()
def reopenWeek(self):
if self.weekState == 'Closed':
self.weekState = 'Open'
p = self.user
p.updateWeek()
def printEntryTable(self):
if self.weekState == 'Open':
print '<table valign="center" width=550 bgcolor=#66cc00 bordercolor="#000000" border=0>'
print '<form method="post" action="/cgi-bin/timesheet.py">'
print '<tr><td>Week begin: <b>' + self.startDate + \
'</b></td><td><b>' + self.weekState + '</b></td><td>' + \
'Target: ' + \
'<INPUT TYPE="HIDDEN" NAME="action" VALUE="closeWeek"> \
<INPUT TYPE="TEXT" NAME="targetHours" SIZE="3" VALUE="' + \
str(self.user.normalWeekHours) + '">' + \
'<INPUT TYPE="HIDDEN" NAME="weekId" VALUE="' + str(self.id) + '">' + \
'  <INPUT TYPE="SUBMIT" VALUE = "Close"></td></tr></form>' + \
'<tr><td COLSPAN="3">' + \
'<table align="center" width=450 style="border:0px;" bgcolor=#66cc00 border=0>' + \
'<tr><th>Day</th><th>Type</th><th>Hours</th><th></th></tr>'
lastDay = 6
for e in Entry.select(Entry.q.weekId == self.id, orderBy=("entry.dow","entry.id")):
print '<tr><td>' + daysOfWeek[e.dow] + '</td><td>' + e.type + '</td><td>' + str(round(e.duration,2)) + \
'</td><td> <a href="/cgi-bin/timesheet.py?action=delEntry&entryID=' + str(e.id) + '">Delete</a></td></tr>'
lastDay = e.dow
print '<tr><td> </td><td> </td><td> </td><td> </td></tr>'
# First end line has 'hours' input box
print ' <tr VALIGN="TOP"> <td><FORM METHOD="POST" ACTION="/cgi-bin/timesheet.py"> \
<INPUT TYPE="HIDDEN" NAME="action" VALUE="setEntry"> \
<INPUT TYPE="HIDDEN" NAME="weekId" VALUE="' + str(self.id) + '"> \
<SELECT NAME="weekday" SIZE="1">'
for i in range(7):
if i == (lastDay + 1) % 7:
print '<OPTION SELECTED> ' + daysOfWeek[i]
else:
print '<OPTION> ' + daysOfWeek[i]
print '</SELECT></td><td> \
<SELECT NAME="type" SIZE="1"> \
<OPTION SELECTED> Normal <OPTION> Holiday <OPTION> Sick </SELECT> </td><td> \
Hours: <INPUT TYPE="TEXT" NAME="hours" VALUE="7.5" SIZE="2"></td><td> \
<INPUT VALUE="Add" TYPE="submit"></FORM></td></tr>'
# Second end line has 'start/finish' input box
print '<FORM METHOD="POST" ACTION="/cgi-bin/timesheet.py"> \
<INPUT TYPE="HIDDEN" NAME="action" VALUE="setEntry"> \
<INPUT TYPE="HIDDEN" NAME="weekId" VALUE="' + str(self.id) + '"> \
<tr VALIGN="TOP"> <td>\
<SELECT NAME="weekday" SIZE="1">'
for i in range(7):
if i == (lastDay + 1) % 7:
print '<OPTION SELECTED> ' + daysOfWeek[i]
else:
print '<OPTION> ' + daysOfWeek[i]
print '</SELECT></td><td> \
<SELECT NAME="type" SIZE="1"> \
<OPTION SELECTED> Normal <OPTION> Holiday <OPTION> Sick </SELECT> </td><td> \
Start: <INPUT TYPE="TEXT" NAME="startTime" VALUE="09:30" SIZE="4"> \
End: <INPUT TYPE="TEXT" NAME="endTime" VALUE="17:30" SIZE="4"></td><td> \
<INPUT VALUE="Add" TYPE="submit"><br><font size="-2"><center>24 hour clock please<center></font></FORM></td></tr></table></tr>'
print '</table>'
elif self.weekState == 'Closed':
print '<table width=550 style="border:0px;" bgcolor=#ff6600 border=0>'
print '<tr valign=top><td>Week begin: <b>' + self.startDate + \
'</b></td><td> <b>' + self.weekState + ' </b><a href="/cgi-bin/timesheet.py?action=reopenWeek&weekId='+str(self.id) + '"><font size="-1">(reopen)</font></td><td>' + \
'Worked: <b>' + str(round(self.workHours,1)) + '</b></td><td>Over/-under: <b>' + \
str(round(self.workHours-self.targetHours,1)) + '</b></td></tr>'
print '</table>'
class Entry(SQLObject):
_fromDatabase = True
_columns = [Col('weekID',foreignKey='Week')]
######## Page Code #####################
C = Cookie.SimpleCookie()
form = cgi.FieldStorage()
if form.has_key("tsUser"):
tsUser = form["tsUser"].value
C["tsUser"] = tsUser
C["tsUser"]["expires"] = 1000000
print C
else:
try:
C.load(os.environ["HTTP_COOKIE"])
except:
tsUser = ""
pass
else:
tsUser = C["tsUser"].value
print "Cache-control: no-cache"
print "Content-type: text/html"
print
print '<head><link rel="stylesheet" type="text/css" href="/office.css" ></head><body><font size="-2"><a style="color:#000000" href="/cgi-bin/timesheet.py">refresh</a> <a style="color:#000000" href="http://192.168.69.147/cgi-bin/webmin">home</a></font><center>'
sys.stderr = sys.stdout
if tsUser=="":
print """
<FORM METHOD="POST" ACTION="/cgi-bin/timesheet.py">
Enter name: <INPUT TYPE="TEXT" NAME="tsUser" VALUE="">
<INPUT TYPE="submit" Value="Login">
</FORM>
"""
else:
if form.has_key("action"):
#### setEntry
dur = 0
if form['action'].value == "setEntry":
if form.has_key("weekId") and form.has_key("weekday") \
and form.has_key("type") and (form.has_key("hours") or \
(form.has_key("startTime") and form.has_key("endTime"))):
if form.has_key("hours"):
dur = float(form['hours'].value)
else:
startHours = timeToFloat(form['startTime'].value)
endHours = timeToFloat(form['endTime'].value)
if startHours is None or endHours is None:
print 'Invalid time format, sorry'
dur = 0
else:
if endHours < startHours:
# assume went past midnight
endHours = endHours + 24;
print 'Warning: either you worked past midnight, or you didn\'t use the 24 hour clock just now. If the later, the entry you just added (%s, %s) is wrong, delete it and try again!' % (form['weekday'].value, str(endHours-startHours))
dur = endHours - startHours
if dur != 0:
e = Entry.new(weekID = int(form['weekId'].value), \
dow = dayFromName[form['weekday'].value], \
type = form['type'].value, \
duration = dur )
#### closeWeek
if form['action'].value == "closeWeek":
if form.has_key("targetHours") and form.has_key("weekId"):
w = Week(int(form['weekId'].value))
w.targetHours = float(form['targetHours'].value)
w.closeWeek()
#### reopenWeek
if form['action'].value == "reopenWeek":
if form.has_key("weekId"):
w = Week(int(form['weekId'].value))
w.reopenWeek()
#### delEntry
if form['action'].value == "delEntry":
if form.has_key("entryID"):
e = Entry(int(form['entryID'].value))
e.destroy()
p = User.byLoginName(tsUser)
p.userPrint()
# If we don't already have a week entry for this week, make one
thisWeekAsString = time.strftime("%Y-%m-%d", firstDayOfThisWeek())
if len(Week.select(AND(Week.q.startDate == thisWeekAsString, Week.q.userID == p.id))) == 0:
newWeek = Week.new(startDate=time.strftime("%Y-%m-%d",firstDayOfThisWeek),weekState='Open',userID=p.id,targetHours=p.normalWeekHours)
for w in p.weeks:
w.printEntryTable()
print '</table>'
print '</center></body></html>'
|
