Example of web2py integration with an AJAX grid (preferably jquery based) which updates backend db ?

[Tim Richardson]

Can anyone provide tutorial/example of a web2py implementation of an AJAX grid which updates records? web2py slices has jqgrid in read only mode, (although the example doesn't work out of the box anymore).

My learning curve is working out good ways to send update requests back to the server, however pretty sure this wheel is already invented.

Overall, I want to have the skills to add inline editing to my web2py apps and I think using a javascript grid is the only realistic way to do this.

[Anthony]

Should be something here, but Bitbucket is down right now.

Anthony

[Niphlod]

totally unrelated note: did you try simply loading a grid via ajax ? if your db isn't slow, from the user perspective it's pretty fast.

[Tim Richardson]

Yes, I've tried that, and it is fast. But what I want is inline editing of (visual) rows. I've experimented by creating multiple SQLFORMs formatted into one line and LOADed then from the view to create a pseudo-SQLFORM.grid but it seems to me that the overhead to make this scale, with paging etc, is comparable to learning how to do it with a javascript grid. 

I don't know how to use SQLFORM.grids for inline editing, only via the edit button.

[Niphlod]

it's one of my long-end goals to provide an inline-editing-capable sqlform grid right in web2py source. As of right now, you're forced to choose a grid and reinvent the wheel....other project made for web2py I think need a tiddle bit of refactoring, but are a good and solid starting point. 

[Simon Ashley]

jeditable is reasonably easy to work with for a cell at a time editing, and works with SQLFORM.grids. Have played around with kendoui grids and their editing is some of the nicest. Could put together some crude examples.

[Tim Richardson]

Hadn't heard of jeditable ... 'works with SQLFORM.grids' sounds good ... crude examples or pointers would be good, particularly around communicating updates (this is the big unknown for me). 

[Simon Ashley]

Download jeditable, 

Install in the static/js folder.

Include in layout.html i.e.

  <script src="{{=URL('static','js/jquery.jeditable.js')}}"></script>

Model

db.define_table('dogs',
    Field('dog_name','string'))

Controller:

def populate():
    db.dogs.insert(dog_name='dagwood')
    db.dogs.insert(dog_name='daisy')
       
def dogs():
    db.dogs.dog_name.represent = lambda value, row: DIV(value if value else '-',_class='dog_name', _id=str(row.id)+'.dog_name')
    g = SQLFORM.grid(db.dogs, searchable=False, csv=False, user_signature=False)
    return dict(form = g)


def upd_dog_name():
    id,column = request.post_vars.id.split('.')
    value = request.post_vars.value
    db(db.dogs.id == id).update(**{column:value})
    return value

View:

{{extend 'layout.html'}}

<script>
jQuery(document).ready(function(){
    jQuery('.dog_name').editable("{{=URL('dogs', 'upd_dog_name')}}",{                  
        tooltip: "Click to edit, enter to save",
 indicator : 'updating',
 });})
</script>


<div id='dogs'>
    {{=form}}
</div>

[Tim Richardson]

Thanks, that is really cool (jeditable) and so easy to integrate with existing web2py code. I wonder if it works when the form is LOADed via a component ...

 I'm going to play a bit more, and then add it to web2py slices with attribution to you.  

[Simon Ashley]

Thanks, enjoy.  Most of our forms are loaded and still works fine.

[tomt]

Simon,

I wanted to thank you for the answer you posted.
As a casual user of web2py, I would have never figured this out
on my own, but your clear example showing db, controller and view
made it easy for me to understand and to implement.

It would be great if the experienced users of web2py would
contribute similar small examples. I believe that would be
one way to help make web2py more popular.

ps. I had to modify the following to get update to work:

    jQuery('.dog_name').editable("{{=URL('dogs', 'upd_dog_name')}}",{                 

to:
    jQuery('.dog_name').editable("{{=URL('default', 'upd_dog_name')}}",{                 

- Tom

On Wednesday, September 4, 2013 11:25:49 PM UTC-6, Simon Ashley wrote:

Download jeditable, 

Install in the static/js folder.

Include in layout.html i.e.

  <script src="{{=URL('static','js/jquery.jeditable.js')}}"></script>

Model

db.define_table('dogs',
    Field('dog_name','string'))

Controller:

def populate():
    db.dogs.truncate()
    db.fleas.truncate()

    db.dogs.insert(dog_name='dagwood')
    db.dogs.insert(dog_name='daisy')
       
def dogs():
    db.dogs.dog_name.represent = lambda value, row: DIV(value if value else '-',_class='dog_name', _id=str(row.id)+'.dog_name')
    g = SQLFORM.grid(db.dogs, searchable=False, csv=False, user_signature=False)
    return dict(form = g)


def upd_dog_name():
    id,column = request.post_vars.id.split('.')
    value = request.post_vars.value
    db(db.dogs.id == id).update(**{column:value})
    return value

[Tim Richardson]

I persevered with jqGrid and added a new slice on web2py slices, which describes how to get a nice looking jqGrid working for editing records.

http://www.web2pyslices.com/slice/show/1714/jqgrid-including-updating-data

[Py Dev]

Hi,

thank you for sharing the idea and the code!

When I tried the code, I had some errors:

First the second line in populate does not fit to the model: #db.fleas.truncate() #AttributeError: 'DAL' object has no attribute 'fleas'

Second, the "dogs" view shows a web2py grid, I can edit it normally. It seems that the inline-edit code "upd_dog_name" is never invoked? 

The idea is wonderful, even better when the code would work :-)

Can you please check this code? Thank you for your help!

Cheers!

pd

[Tim Richardson]

look on web2pyslices 
http://www.web2pyslices.com/slice/show/1714/jqgrid-viewing-and-updating-data

[Simon Ashley]

Just remark out/ delete the truncate fleas line.

If the 'upd_dog_name' function is not called, check installation and calling of jeditable

[Py Dev]

Hi Tim,

thanks for your tip with the jQuery Grid code. 

My question referred to the code from "Simon Ashley" how to use "jeditable", see best marked answer...

This code is not on web2pyslices

Cheers!

pd

my question 

[Py Dev]

Hi Simon, 

thanks for your quick answer! There is some progess, an other error message: :-)

Error Message:------------------------

Traceback (most recent call last):

  File "/Users/myname/PycharmProjects/DataBaseExamples/web2py/gluon/restricted.py", line 217, in restricted

    exec ccode in environment

  File "/Users/myname/PycharmProjects/DataBaseExamples/web2py/applications/DataBaseExamples/controllers/jeditable.py", line 19, in <module>

  File "/Users/myname/PycharmProjects/DataBaseExamples/web2py/gluon/globals.py", line 371, in <lambda>

    self._caller = lambda f: f()

  File "/Users/myname/PycharmProjects/DataBaseExamples/web2py/applications/DataBaseExamples/controllers/jeditable.py", 

line 14, in upd_dog_name

    id,column = request.post_vars.id.split('.') #'NoneType' object has no attribute 'split'

AttributeError: 'NoneType' object has no attribute 'split'

-----------------------------------

Did you take the code from a working example?

Thanks for your help!

pd

[Py Dev]

Hi Simon, 

I just found the problem:

The line:

  <script src="{{=URL('static','js/jquery.jeditable.js')}}"></script>

has to be inserted after(!)   

{{include 'web2py_ajax.html'}}

which adds jquery.js

Changing that, first my inline changes were reverted, but when I adapted the location 

in the line

jQuery('.dog_name').editable("{{=URL('jeditable', 'upd_dog_name')}}",{

to my location of „upd_dog_name“ now it works perfect!

Thank you very much for this wonderful example: I learned a lot with it, especially how to pass data from web2py/python to javascript and back. 

And this inline-editing is very useful too :-) 

This should be integrated into web2py and/or the quick examples!

Do you have more examples of this kind? :-)

Excellent! Perfect! 10 of 10 stars! Thanks a lot!

pd

[Adnan Smajlovic]

Simon,

Thank you for this. Very helpful, and just implemented it in one of my grids! Also checked Kendoui. Looks very nice.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Simon Ashley]

w2p is a great framework with many bells and whistles. 

Examples/ defaults are full of them. 

One of the issue when you're learning the framework, you need to take baby steps, and study them one at a time. 

(particularly if, like myself, you are new to python, js, CSS3, Bootstrap, etc, etc and feel, at times, you dumber than dogs...)

The example posted was stripped from a larger system, sanitised and tested. 

Unfortunately, I didn't strip out a few (i.e truncate) testing lines.

 

I find simple, one or 2 line code snippets, great (the smaller the better).

Its easier to join/ merge later.

Similar comments go for troubleshooting and posting issues.

Its always easier for others if you reduce code just to key elements, to illustrate the point.

Slices are good, but they are a bit static/ non interactive.

I'm now wondering how difficult it would be to clone and host a jsfiddle type sandbox in w2p, whereby we could post, share, test and modify code snippets within the community?

[Mark Billion]

I am sure I am an idiot, but when I hit enter to update (ie, petunia to petuniatest), it reverts back to the same old dog name (petunia). 

def dogs():
    db.dogs.dog_name.represent = lambda value, row: DIV(value if value else '-',_class='dog_name', _id=str(row.id)+'.dog_name')
    g = SQLFORM.grid(db.dogs, searchable=False, csv=False, user_signature=False)
    return dict(form = g)


def upd_dog_name():

    id,column = request.post_vars.id.split('.')

    value = request.post_vars.value
    db(db.dogs.id == id).update(**{column:value})
    return value

{{extend 'layout.html'}}

<script>
jQuery(document).ready(function(){
    jQuery('.dog_name').editable("{{=URL('dogs', 'upd_dog_name')}}",{                  
        tooltip: "Click to edit, enter to save",
 indicator : 'updating',
 });})
</script>


<div id='dogs'>
    {{=form}}
</div>

db.define_table('dogs',
    Field('dog_name','string'))

[Simon Ashley]

Try the simple things first (good exercise in debugging):

1. ensure that jeditable is being loaded (check the code, and paths)
2. if that passes, put a breakpoint (and the debugger) on the second line (beginning with id, column) of upd_dog_name.
1. check what request.vars are being passed (you can also use the console, toward the bottom of the debug page to query variables)