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)
fo­r x in db().select(db.wrmst.ALL)]), 
TR("Extended Warranty",
SELECT(_type="select",_name="extwar",*[OPTION(x.extwrmstnm,_value=x.extwrms­­tid)
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.