Re: help creating sqlalchemy/sqlsoup code for sql query

Mary Jane Boholst <[email protected]> Tue, 10 Oct 2006 15:57:29 +0100

Newsgroups	gmane.comp.python.spyce.general
Organization	IOP
Message-ID	<[email protected]>

When I add the id column to the table which I am displaying, the rows have the 
correct id associated with them, however when I try to delete a row the first 
row displayed is deleted. 
I attach the relevant code. If anyone could shed light on what is going on and 
what I am doing wrong I will be very grateful.
Thanks again.
MJ


On Tuesday 10 October 2006 15:10, Jonathan Ellis wrote:
> Somehow you are not using your loop correctly and everything is getting
> the same id.
>
> On Tue, 10 Oct 2006 11:45:31 +0100, "Mary Jane Boholst"
>
> <[email protected]> said:
> > Thanks for this. It seems that the handler was not getting executed.
> > This is fixed now. However, I have discovered a problem with my delete
> > and update handlers. The handlers are being called however the incorrect
> > entry is deleted. I am trying to as you can probably guess allow certain
> > users to update, delete or insert items to a database. In an attempt to
> > allow certain rows of the table to be deleted or updated I have created
> > delete and update buttons. The handlers do not work as intended which is
> > down to the id variable. The id variable is taken from the database and
> > is intended to be used to identify the row to update or delete. However
> > printing the id value reveals that the id of the uppermost row in the
> > table is being used rather than the id of the row from which the button
> > is pressed.
> > Could anyone explain why this is so? Does any one have an idea how I
> > could use this id variable to delete or update all the rows of the table
> > rather than just the uppermost row in the table? Or even just a way in
> > which I can allow the update and deletion of certain rows in a table?
> > I am not sure if what I have just said makes sense, but any help would
> > be useful.
> > Thanks in advance.
> > Regards,
> > MJ
> >
> > Jonathan Ellis wrote:
> > > Well, the first step is to make sure the handler is actually getting
> > > executed.  Put a print statement in there to check.
> > >
> > > -J
> > >
> > > On Fri, 06 Oct 2006 16:22:33 +0100, "Mary Jane Boholst"
> > >
> > > <[email protected]> said:
> > >> Thank you very much for this very helpful piece of code.
> > >> I have another question. (Last one for the week, promise!) I am trying
> > >> to create a button on a page which inserts values from a form into a
> > >> database. I have defined my handler and created the button but the
> > >> data does not seem to be committed to the database. I do not get any
> > >> error messages but checking the database for the data shows that it
> > >> has not been inserted.
> > >> The code for my handler is:
> > >> [[!
> > >>     def insertentry(self,
> > >> api,day,course,timeslottype,start,end,title,tutor):
> > >>
> > >> api.db.timetable.insert(day=day,course=course,timeslottype=timeslottyp
> > >>e,starttime=start,endtime=end,title=title,givenbby=tutor)
> > >> api.db.flush()
> > >> ]]
> > >>
> > >> The code for the button is:
> > >> <f:submit handler=self.insertentry value="Insert entry" />
> > >>  From reading around it seems that this should work. Is there
> > >> something I am missing?
> > >> Thanks in advance for all help.
> > >> MJ
> > >>
> > >> Jonathan Ellis wrote:
> > >>> from sqlalchemy import or_
> > >>> where = or_(db.timetable.c.course=='LAA',
> > >>> db.timetable.c.course=='COM') db.timetable.select(where,
> > >>> order_by=[db.timetable.c.day,
> > >>> db.timetable.c.starttime])
> > >>>
> > >>> On Wed, 4 Oct 2006 11:50:07 +0100, "Mary Jane Boholst"
> > >>>
> > >>> <[email protected]> said:
> > >>>> Hi again,
> > >>>> Another question which should be fairly simple to answer. I am
> > >>>> trying to carry
> > >>>> out a fairly word query within my spyce code and am not sure how to
> > >>>> do it.
> > >>>> The sql that I want to translate to sqlalchemy is select * from
> > >>>> timetable where course='LAA' or course='COM' order by day,
> > >>>> starttime. I have tried several times to do this but I get errors
> > >>>> trying to do both the
> > >>>> order by and the where clauses.
> > >>>> Any help will be gratefully received.
> > >>>> Thank you all for your help and suggestions. The login token problem
> > >>>> is now
> > >>>> fixed and I have at the moment decided to use the example on the
> > >>>> spyce pages
> > >>>> to help with the displaying of different pages for different users.
> > >>>> Thanks again,
> > >>>> MJ
> > >>>>
> > >>>> --------------------------------------------------------------------
> > >>>>----- Take Surveys. Earn Cash. Influence the Future of IT
> > >>>> Join SourceForge.net's Techsay panel and you'll get the chance to
> > >>>> share your
> > >>>> opinions on IT & business topics through brief surveys -- and earn
> > >>>> cash
> > >>>> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=D
> > >>>>EVDEV _______________________________________________
> > >>>> Spyce-users mailing list
> > >>>> [email protected]
> > >>>> https://lists.sourceforge.net/lists/listinfo/spyce-users
> > >>
> > >> ----------------------------------------------------------------------
> > >>--- Take Surveys. Earn Cash. Influence the Future of IT
> > >> Join SourceForge.net's Techsay panel and you'll get the chance to
> > >> share your
> > >> opinions on IT & business topics through brief surveys -- and earn
> > >> cash
> > >> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEV
> > >>DEV _______________________________________________
> > >> Spyce-users mailing list
> > >> [email protected]
> > >> https://lists.sourceforge.net/lists/listinfo/spyce-users
> >
> > -------------------------------------------------------------------------
> > Take Surveys. Earn Cash. Influence the Future of IT
> > Join SourceForge.net's Techsay panel and you'll get the chance to share
> > your
> > opinions on IT & business topics through brief surveys -- and earn cash
> > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> > _______________________________________________
> > Spyce-users mailing list
> > [email protected]
> > https://lists.sourceforge.net/lists/listinfo/spyce-users

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV

_______________________________________________
Spyce-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/spyce-users

action.py (application/x-python, 692 B)

from spyceConfig import db

def delete(api, item_id):
	remove = api.db.timetable.selectone_by(id=item_id)
	api.db.delete(remove)
	api.db.flush()
	


def update(api, item_id, day, course, start, end, title, tutor):
	api.db.timetable.update(db.timetable.c.id==item_id, day=day, course=course, starttime=start, endtime=end, title=title, givenby=tutor)
	api.db.flush()
	

def new_entry(api, day, course, timeslottype, start, end, title, tutor):
	api.db.timetable.insert(day=day, course=course, timeslottype=timeslottype, starttime=start, endtime=end, infourl='', title=title, givenby=tutor, tutorinfo='')
	api.db.flush()
	
	
def home(api):
	api.redirect.external('index.spy')
	api.response.end()

index.spy (text/plain, 6.2 KB)

<spy:parent title="Edit Summer School Timetable" />
[[.import name=redirect]]
[[!
	def insert(self, api):
		api.redirect.external('insert.spy')
		api.response.end()
]]

[[from sqlalchemy import or_]]

<f:form>
 <spy:login_required />
[[ current_user = db.users.selectone_by(username=request.login_id()) ]]
  <b>Welcome [[= current_user.username]]</b></br>
  <spy:logout /></br>

</br>

[[course=current_user.username]]
[[--print course--]]
[[if course == 'laa': {]]
[[coursename= 'Linkage and Association']]
<h2><center>Edit Summer School [[=coursename]] Timetable</center></h2>
[[where=or_(db.timetable.c.course=='LAA',db.timetable.c.course=='COM')]]
[[ lists = db.timetable.select(where,order_by=[db.timetable.c.day,db.timetable.c.starttime]) ]]
[[--print lists--]]
<table align=center>
<tr>
  [[--<th>ID</th>--]]
  <th>Day</th>
  <th>Course</th>
  <th>Start</th>
  <th>End</th>
  <th>Title</th>
  <th>Tutor</th>
 </tr>
[[r=0]]
[[for c in lists: {]]
<tr>
<f:hidden name="item_id" value="=lists[r].id" />
<td><f:text name="item_id" value="=lists[r].id" size =4 /></td>
<td><f:text name="day" value="=lists[r].day" size =2 /></td>
<td><f:text name="course" value="=lists[r].course" size =5 /></td>
<td><f:text name="start" value="=lists[r].starttime" size =5 /></td>
<td><f:text name="end" value="=lists[r].endtime" size =5 /></td>
<td><f:text name="title" value="=lists[r].title" size =80 /></td>
<td><f:text name="tutor" value="=lists[r].givenby" size =30 /></td>
<td><f:submit handler=action.delete value="Delete Entry" /></td>
<td><f:submit handler=action.update value="Update Entry" /></td> 

</tr>
[[r=r+1]]
[[}]]
</table>
[[}]]
[[elif course=='tmf': {]]
[[coursename= 'Twin Model Fitting']]
<h2><center>Edit Summer School [[=coursename]] Timetable</center></h2>
[[where=or_(db.timetable.c.course=='TMF',db.timetable.c.course=='COM')]]
[[ lists = db.timetable.select(where,order_by=[db.timetable.c.day,db.timetable.c.starttime]) ]]
[[--print lists--]]
<table align=center>
<tr>
  [[--<th>ID</th>--]]
  <th>Day</th>
  <th>Course</th>
  <th>Start</th>
  <th>End</th>
  <th>Title</th>
  <th>Tutor</th>
 </tr>
[[r=0]]
[[for c in lists: {]]
<tr>
<f:hidden name="item_id" value="=lists[r].id" />
<td><f:text name="item_id" value="=lists[r].id" size =4 /></td>
<td><f:text name="day" value="=lists[r].day" size =2 /></td>
<td><f:text name="course" value="=lists[r].course" size =5 /></td>
<td><f:text name="start" value="=lists[r].starttime" size =5 /></td>
<td><f:text name="end" value="=lists[r].endtime" size =5 /></td>
<td><f:text name="title" value="=lists[r].title" size =80 /></td>
<td><f:text name="tutor" value="=lists[r].givenby" size =30 /></td>
<td><f:submit handler=action.delete value="Delete Entry" /></td>
<td><f:submit handler=action.update value="Update Entry" /></td> 

</tr>
[[r=r+1]]
[[}]]
</table>
[[}]]
[[elif course=='mge': {]]
[[coursename= 'Microarray and Gene Expression']]
<h2><center>Edit Summer School [[=coursename]] Timetable</center></h2>
[[where=or_(db.timetable.c.course=='MGE',db.timetable.c.course=='COM')]]
[[ lists = db.timetable.select(where,order_by=[db.timetable.c.day,db.timetable.c.starttime]) ]]
[[--print lists--]]
<table align=center>
<tr>
  [[--<th>ID</th>--]]
  <th>Day</th>
  <th>Course</th>
  <th>Start</th>
  <th>End</th>
  <th>Title</th>
  <th>Tutor</th>
 </tr>
[[r=0]]
[[for c in lists: {]]
<tr>
<f:hidden name="item_id" value="=lists[r].id" />
<td><f:text name="item_id" value="=lists[r].id" size =4 /></td>
<td><f:text name="day" value="=lists[r].day" size =2 /></td>
<td><f:text name="course" value="=lists[r].course" size =5 /></td>
<td><f:text name="start" value="=lists[r].starttime" size =5 /></td>
<td><f:text name="end" value="=lists[r].endtime" size =5 /></td>
<td><f:text name="title" value="=lists[r].title" size =80 /></td>
<td><f:text name="tutor" value="=lists[r].givenby" size =30 /></td>
<td><f:submit handler=action.delete value="Delete Entry" /></td>
<td><f:submit handler=action.update value="Update Entry" /></td>

</tr>
[[r=r+1]]
[[}]]
</table>
[[}]]
[[elif course=='com': {]]
[[coursename= 'Common Components']]
<h2><center>Edit Summer School [[=coursename]] Timetable</center></h2>
[[ lists = db.timetable.select(db.timetable.c.course=='COM',order_by=[db.timetable.c.day,db.timetable.c.starttime]) ]]
[[--print lists--]]
<table align=center>
<tr>
  [[--<th>ID</th>--]]
  <th>Day</th>
  <th>Course</th>
  <th>Start</th>
  <th>End</th>
  <th>Title</th>
  <th>Tutor</th>
 </tr>
[[r=0]]
[[for c in lists: {]]
<tr>
<f:hidden name="item_id" value="=lists[r].id" />
<td><f:text name="item_id" value="=lists[r].id" size =4 /></td>
<td><f:text name="day" value="=lists[r].day" size =2 /></td>
<td><f:text name="course" value="=lists[r].course" size =5 /></td>
<td><f:text name="start" value="=lists[r].starttime" size =5 /></td>
<td><f:text name="end" value="=lists[r].endtime" size =5 /></td>
<td><f:text name="title" value="=lists[r].title" size =80 /></td>
<td><f:text name="tutor" value="=lists[r].givenby" size =30 /></td>
<td><f:submit handler=action.delete value="Delete Entry" /></td>
<td><f:submit handler=action.update value="Update Entry" /></td> 

</tr>
[[r=r+1]]
[[}]]
</table>
[[}]]
[[elif course=='schooladmin': {]]
<table>
<tr><td><f:submit handler=self.insert value="Insert a unit/module" /></td></tr>
</table>
<h2><center>Edit Summer School Timetable</center></h2>

[[ lists = db.timetable.select(order_by=[db.timetable.c.day,db.timetable.c.course,db.timetable.c.starttime]) ]]
<table align=center>
<tr>
  <th>Day</th>
  <th>Course</th>
  <th>Start</th>
  <th>End</th>
  <th>Title</th>
  <th>Tutor</th>
 </tr>
[[r=0]]
[[for c in lists: {]]
<tr>
<f:hidden name="item_id" value="=lists[r].id" />
<td><f:text name="item_id" value="=lists[r].id" size =4 /></td>
<td><f:text name="day" value="=lists[r].day" size =2 /></td>
<td><f:text name="course" value="=lists[r].course" size =5 /></td>
<td><f:text name="start" value="=lists[r].starttime" size =5 /></td>
<td><f:text name="end" value="=lists[r].endtime" size =5 /></td>
<td><f:text name="title" value="=lists[r].title" size =80 /></td>
<td><f:text name="tutor" value="=lists[r].givenby" size =30 /></td>
<td><f:submit handler=action.delete value="Delete Entry" /></td>
<td><f:submit handler=action.update value="Update Entry" /></td> 
</tr>
[[r=r+1]]
[[}]]
</table>
[[}]]

</f:form>