SQLFORM and insert/update/delete in web2py

About SQLFORM

SQLFORM offers creation, updation, and deletion of forms from an existing database table using the high-level API.

As a self-aware program, SQLFORM has the ability to change itself and provide error messages if the input does not pass validation. The variables that have been validated on the forms as well as any error messages that have been produced by validation can be retrieved with queries.

Now, let’s start with an important method known as the “process” method.

The process method

The process method is a method that is inherited by the SQLFORM from FORM. 

The request and session arguments are not required for form.process(...).accepted (the same as FORM()) (although you can specify them optionally). 

Because it returns the form, it also differs from accepts. Process calls accept and transmit their arguments to one another internally. Form.accepted stores the value returned by accepts.

SQLFORM and insert/update/delete

Adding a new Database

When the form is approved, SQLFORM adds a new database record. Let’s assume

myform = SQLFORM(db.test)

 

Then “form.vars.id” can be used to retrieve the ID of the most recent record made.

Update

The form transforms into an UPDATE form for a record if you supply it as the second optional argument to the SQLFORM constructor. This indicates that no new record is added; instead, the existing record is changed when the form is submitted. 

The UPDATE form shows a "Check to delete" checkbox if the input “deletable=True” is specified. A record is erased if the box is checked.

The attribute form.deleted is changed to True when a form is submitted, and the delete option is selected.

We will modify the controller, which will allow passing an additional argument that will be an integer in the URL path, e.g.:

/test/default/display_form/2

 

Update/Delete

And also, if there is a record with the corresponding id, the SQLFORM generates an UPDATE/DELETE form for the record:

def display_form():
   myrecord = db.person(request.args(0)) or redirect(URL('index'))
   myform = SQLFORM(db.person, record)
   if myform.process().accepted:
       response.flash = 'form is accepted'
   elif myform.errors:
       response.flash = 'form has some issues'
   return dict(myform=myform)

 

Line 2 searches the record, and line 3 makes an UPDATE/DELETE form. Then, line 4 does all the corresponding form processing.

Create Form vs Update Form

A create form and an update form are pretty similar, with the exception that an update form previews photos and is pre-populated with the current data. Deletable is set to True by default, which causes the update form to show a "remove record" option.

In order to identify the record, edit forms also have a hidden INPUT field with the name="id". For added protection, this id is also kept on the server. If a visitor tampers with this field's value, web2py throws a SyntaxError with the message "user is tampering with form," which prevents the UPDATE from taking place.

When a field is designated as writable=False, it is not displayed in create forms and is displayed read-only in update forms. When both writable and readable are set to False, a field is never displayed at all, not even in update forms.

Forms created using:

myform = SQLFORM(..., ignore_rw=True)

 

Ignores the readable and writable attributes and always shows all fields. Forms in appadmin ignore them by default.

Forms created with:

myform = SQLFORM(table, record_id, readonly=True)

 

It shows all fields in readonly mode every time, and they cannot be accepted.

When a field is marked with the value writable=False, it is not processed as part of the form, and the value of the request.vars.field is not taken into account. However, if you set a value for the form.vars.field, the form's insert or update will include this value. This allows us to change the value of any fields you do not want to be included in a form for some reason.

SQL Form in HTML

Sometimes you want to utilize SQLFORM to take advantage of its form production and processing, but you require a level of HTML customization that you can't get with the SQLFORM object's arguments, so you have to create the form in HTML.

Edit the earlier controller now to include the new action:

def display_manual_form():
    myform = SQLFORM(db.person)
    if myform.process(session=None, formname='testform').accepted:
        response.flash = 'form is accepted'
    elif myform.errors:
        response.flash = 'form has some errors or issues'
    else:
        response.flash = 'please fill or complete the form below'
    # Note: no form instance has been passed to the view
    return dict()

 

Adding the form to the "default/display manual form.html" view linked with it:

{{extend 'layout.html'}}
<form action="#" enctype="multipart/form-data" method="post">
<ul>
  <li>The name is <input name="name" /></li>
</ul>
  <input type="submit" />
  <input type="hidden" name="_formname" value="test" />
</form>

 

Because the form does not need to be passed to the view, the action does not return it. A manually constructed HTML form can be found in the view. The form has a hidden field called "_formname" that needs to match the formname supplied in the action's accepts parameter. In the event that there are numerous forms on the same page, web2py uses the form name to identify which one was submitted. If only one form is present on the page, you can omit the hidden field from the display by setting formname=None.

Response.vars are searched for data that matches fields in the database table db.person by form.accepts. These fields are formatted as follows when expressed in HTML.

<input name="field_name_goes_here" />

 

The HTML form uses the POST protocol and the multipart/form-data encoding type. Therefore form variables won't be sent on to the URL. The latter is required for the operation of upload fields.

SQLFORM and uploads

The "upload" field type is unique. They are presented as INPUT fields with a “file” type. Unless otherwise requested, the uploaded file is streamed in with the help of a buffer and is then automatically given a new safe name and put in the application's "uploads" folder. After that, the file's name is recorded in the "upload" field.

Take the following model, for instance:

db.define_table('person',
    Field('name', requires=IS_NOT_EMPTY()),
    Field('image', 'upload'))

 

Use the controller action "display form" as was previously shown.

The form lets you browse for a file when you add a new record. Choose a jpeg image as an illustration. The uploaded and saved file is named:

applications/test/uploads/person.img.XXXXX.jpg

 

The file's "XXXXXX" identifier was chosen at random by web2py.

Be aware that a file's old filename is b16encoded by default and utilized to create the new name for the file. The default "download" action retrieves this name and uses it to set the content-disposition header to the original filename.

It only retains its extension. The filename might contain special characters that could enable a visitor to conduct directory traversal attacks or other harmful actions; hence this is a security necessity.

Additionally, form.vars.image stores the updated filename.

So it would be helpful to display a link to the currently uploaded file when amending the record using an UPDATE form, and web2py offers a means to achieve it.

Web2py uses the action at the given URL to download the file when a URL is passed to the SQLFORM constructor via the upload argument. Take the following steps:

def display_form():
   myrecord = db.person(request.args(0))
   myform = SQLFORM(db.person, myrecord, deletable=True,
                  upload=URL('download'))
   if myform.process().accepted:
       response.flash = 'form is accepted'
   elif myform.errors:
       response.flash = 'form has some errors'
   return dict(myform=myform)

def download():
    return response.download(request, db)

 

Now, let’s insert a new record at the URL or address:

http://127.0.0.1:8000/test/default/display_form

 

We will upload an image, and submit the form. After that, edit the newly created record by visiting:

http://127.0.0.1:8000/test/default/display_form/3

 

When this form is serialized, it generates the following HTML:

<td><label id="person_img__label" for="person_img">Image: </label></td>
<td><div><input type="file" id="person_img" class="upload" name="person image"
/>[<a href="/test/default/download/person.img.0246683463831.jpg">file</a>|
<input type="checkbox" name="image_deletion" />delete]</div></td><td></td></tr>
<tr id="delete_record_row">
<td><label id="delete_record_label" for="delete_record">Check for deletion:</label></td>
<td><input type="checkbox" id="delete_record"
class="delete" name="delete_this_record" /></td>

 

which has a checkbox to remove the file from the database record, saving NULL in the "image" column, and a link to allow downloading of the uploaded file.

Let’s see the output when we try to remove:

The default destination for submitted files is "app/uploads," but you can specify another location:

Field('image', 'upload', uploadfolder='...')

 

When multiple files are in a single folder, accessing the file system can become slow in most operating systems. You can instruct Web2py to group your uploads into subfolders if you intend to upload more than 1000 files:

Field('image', 'upload', uploadseparate=True)

 

Frequently Asked Questions

What is web2py used for?

Python dynamic web content programming is made possible via Web2py. Web2py is made to make laborious web development jobs more accessible, such as creating web forms from scratch, while a web developer can still do it if necessary.

Which is better, web2py or Django?

Due to its smaller size, short learning curve, and lack of project-level configuration files, web2py differs from Django. Compared to PHP-based frameworks and Java-based frameworks, web2py is far less verbose and has a lot clearer syntax. This makes applications easier to comprehend, maintain, and create.

Should I learn web2py?

More than web-development purposes, web2py is primarily used as a teaching tool. Because of its graphical interface and built-in web IDE, it makes it easy for the developer to learn server-side web development.

Does web2py support Python 3?

web2py actually runs with CPython (the C implementation) and PyPy (Python written in Python) on Python 2.7 and Python 3.

Is web2py a good framework?

To summarize, Web2py is a free, fast, secure web development framework that is entirely written in python and encourages using python in every way possible (model, view, controller). It is an excellent framework for small web applications or prototypes but fails to fulfill the enterprise-class quality requirements.

Conclusion

In this article, we learned SQLFORM and insert/update/delete in web2py. After reading about SQLFORM and insert/update/delete in web2py which is , are you not feeling excited to read/explore more articles on the topic of web2py? Don't worry; Coding Ninjas has you covered. 

To learn more about this, see wiki components to authenticate that wiki in web2py and Markmin basics and oembed Protocol in web2py.

Refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Data Structures and Algorithms, Competitive Programming, JavaScript, System Design, and many more! If you want to test your competency in coding, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio! But if you have just started your learning process and are looking for questions asked by tech giants like Amazon, Microsoft, Uber, etc., you must look at the problems, interview experiences, and interview bundle for placement preparations.

Nevertheless, you may consider our paid courses to give your career an edge over others!

Do upvote our blogs if you find them helpful and engaging!

Merry Learning!