Get the ID of the just inserted row

[Thomas Dall'Agnese]

Hi,

Let say I have 2 tables:

db.define_table('post', Field('content'))

db.define_table('comment', Field('content'), Field('post', 'reference post))

When I add a "post" (through a FORM), I would like to automatically add 3 comments that refer to that post.

How can I retrieve the ID of the just inserted post and add the comments referring to it?

Currently, I handle to form to add a post like this:

def add_post():

   form = SQLFORM(db.post)

   if form.validate():

       try:

           comments = generateAutoComments()

       except Exception, e:

           response.flash = "Errors generating comments (%s)" % e.message

       else:

           if form.process().accepted:

               # I guess here I would like to add all the comments to that just added post?

               response.flash = 'Post added!'

   return locals()

[lyn2py]

Use form.vars.id to get the id of the new record inserted

More about it here:
http://web2py.com/books/default/chapter/29/7#SQLFORM
(search form.vars.id to get to the paragraph concerned)

On Dec 23, 3:01 pm, Thomas Dall'Agnese <thomas.dallagn...@gmail.com>
wrote:

[Thomas Dall'Agnese]

Thanks for your prompt response.

Best Regards,

Thomas

Sent from Android

[Anthony]

You have two options -- you can let the .process() method handle the insert, in which case the record id will be in form.vars.id after that, or you can do the insert manually, in which case the id will be returned by the .insert() method (i.e., id = db.post.insert(**dict(form.vars))).

Note, I don't think you can leave things as you have, with both form.validate() and form.process() being called. The validation process executed in .validate() checks the _formkey token in the session and then replaces it with a new one -- it is a one-time token to prevent double form submission and CSRF attacks. The .process() method also goes through the validation process, so when it checks the _formkey value in the session, it will no longer match. I suppose you could hack this process and re-assign the _formkey value in the session (or in request.post_vars), but it might be cleaner to do a manual insert, as described here: http://web2py.com/book/default/chapter/07#SQLFORM-without-database-IO.

Anthony

[Thomas Dall'Agnese]

Hi Anthony,

Thanks for your answer.

I used the form.vars.id way.

And indeed, I had to change my process because the form.process().accepted was false after doing a form.validate().

So I changed the form.validate() with form.process().accepted and delete the inserted post if an exception is raised trying to add the comments to that post.

def add_post():

   form = SQLFORM(db.post)

   if form.process().accepted:

       try:

           comments = ('comment one', 'comment two', 'comment three')

           for comment in comments:

               db.comment.insert(post=form.vars.id, comment=comment)

       except Exception, e:

           response.flash = "Errors generating comments (%s)" % e.message

           db(db.post.id == form.vars.id).delete()

       else:

           response.flash = 'Post added!'

   return dict(form=form)

Works well, thanks!

Cheers,

Thomas