web2py: multiple tables: conditional insert/update/delete: from one form
I have written code for managing conditional insert/update/delete to multiple tables from single form in 'web2py'. I agree, the code is in very raw form & may not be ‘pythonic’. There are code repeatitions. But at least I have something to go ahead & build a refined structure.
MODELS:
db.define_table('mdlmst',
Field('mdlmstid','id'),
Field('mdlmstcd'),
Field('mdlmstnm'),
migrate=False,
format='%(mdlmstnm)s'
)
db.define_table('wrmst',
Field('wrmstid','id'),
Field('wrmstcd'),
Field('wrmstnm'),
migrate=False,
format='%(wrmstnm)s'
)
db.define_table('extwrmst',
Field('extwrmstid','id'),
Field('extwrmstcd'),
Field('extwrmstnm'),
migrate=False,
format='%(extwrmstnm)s'
)
from the FORM, data will be populated in the following two tables
db.define_table('mdlwr',
Field('mdlwrid','id'),
Field('mdlmstid',db.mdlmst),
Field('wrmstid',db.wrmst),
migrate=False
)
db.define_table('mdlextwr',
Field('mdlextwrid','id'),
Field('mdlmstid',db.mdlmst),
Field('extwrmstid',db.extwrmst),
migrate=False
)
CONTROLLERS:
‘modelwar’ controller will render the records from ‘mdlmst’ table
def modelwar():
models = db(db.mdlmst.mdlmstid>0).select(orderby=db.mdlmst.mdlmstnm)
return dict(models=models)
after clicking a particular record, ‘war_edit’ controller will
manage the tables – ‘mdlwr’ & ‘mdlextwr’
def war_edit():
mdl_id = request.args(0)
mdl_id is a variable identifying the ‘mdlmstid’ (which record to be modified)
mdl_nm = request.args(1)
mdl_nm is a variable for getting the ‘mdlmstnm’
warset = db(db.mdlwr.mdlmstid==mdl_id) # fetch a set
extwarset = db(db.mdlextwr.mdlmstid==mdl_id) # fetch a set
warlist = db(db.mdlwr.mdlmstid==mdl_id).select() # get a ROW object
extwarlist = db(db.mdlextwr.mdlmstid==mdl_id).select() # get a ROW object
form_war=FORM(TABLE(TR("Basic Warranty",
SELECT(_type="select",_name="baswar",*[OPTION(x.wrmstnm,_value=x.wrmstid)
for x in db().select(db.wrmst.ALL)]),
TR("Extended Warranty",
SELECT(_type="select",_name="extwar",*[OPTION(x.extwrmstnm,_value=x.extwrmstid)
for x in db().select(db.extwrmst.ALL)]),
TR("", INPUT(_type='submit',_value='Save')), ))))
pre-populate the fields in‘form_war’
if len(warlist)>0:
form_war.vars.baswar = warlist[0].wrmstid
if len(extwarlist)>0:
form_war.vars.extwar = extwarlist[0].extwrmstid
after successful form submission, manage the table 'mdlwr'
if form_war.accepts(request.vars, session):
if there was any record in the list fetched from database & sent to FORM,
if len(warlist)>0:
delete if value returned from FORM field is blank, else update
if form_war.vars.baswar==''
warset.delete()
else:
warset.update(wrmstid=form_war.vars.baswar)
else insert
else:
db.mdlwr.insert(mdlmstid=mdl_id, wrmstid=form_war.vars.baswar)
Similarly, manage the table 'mdlextwr'
if len(extwarlist)>0:
if form_war.vars.extwar=='':
extwarset.delete()
else:
extwarset.update(extwrmstid=form_war.vars.extwar)
else:
db.mdlextwr.insert(mdlmstid=mdl_id, extwrmstid=form_war.vars.extwar)
response.flash = 'Warranty definition saved'
return dict(form_war=form_war,mdlnm=mdl_nm)
VIEW for 'mdlmst' table
{{response.files.append(URL(r=request,c='static',f='jquery.dataTables.min.j
开发者_如何学编程s'))}}
{{response.files.append(URL(r=request,c='static',f='demo_table.css'))}}
{{extend 'layout.html'}}
jQuery(document).ready(function()
{ jQuery('.smarttable').dataTable();});
Modelwise Warranty Master
Model IDModel CodeModel Name
{{for model in models:}}
{{=model.mdlmstid}}
{{=model.mdlmstcd}}
{{=model.mdlmstnm}}
{{=A('edit
warranty',_href=URL('war_edit',args=[model.mdlmstid,model.mdlmstnm]))}}
{{pass}}
Pl. tell me if I have coded anything stupid here.
I would highly welcome any ideas/suggestions for improvements.
Thanks, Vineet
Your database design looks strange to me.
In each table you have a field of type 'id'. This will replace the id field automatically generated by web2py - a bad idea. From the web2py book: "Do not declare a field called "id", because one is created by web2py anyway. Every table has a field called "id" by default. It is an auto-increment integer field (starting at 1) used for cross-reference and for making every record unique, so "id" is a primary key"
You have created a many to many relationship between table 'mdlmst' and 'wrmst' and another many to many relationship between 'mdlmst' and 'extwrmst'. While this is not necessarily wrong, it strikes me as extremely unlikely this is what you want.
My feeling is that your database design needs work. This should be sorted out before you start designing forms.
精彩评论