Help with posrtgresSQL SELECT and UPDATE

[Claudio Dusan Vega Ozuljevich]

        user = web.ctx.db.select('role', where="verify_email_token='" + path + "'")
        for data in user:
                with web.ctx.db.transaction():
                    web.ctx.db.update('role', where="verify_email_token ='" + path + "'", verify_email_token = '', email=data.new_email)
                    web.ctx.session.alerts.append('email_verified')
                    raise web.seeother("/", absolute = True)

The issue here is that the table is not being updated for some reason. I think that is must be the SELECT statement which does not allow to update the table afterwards.

When I just use the UPDATE statement the table does update. I need it so the email column updates with the data in new_email column.

Any idea?

Googling I found that there is a SELECT FOR UPDATE clause, but there's no mention of it in web.py cookbook.

Thanks guys!

[Anand Chitipothu]

You are raising an exception in the with block, that'll rollback the transaction. Moving the raise out of the with block will fix the issue.

Also, you are constructing the where clause by concatenating values, which is very bad idea. Here is what you should do:

        user = web.ctx.db.select('role', where="verify_email_token=$path", vars={"path": path})

        for data in user:
                with web.ctx.db.transaction():

                    web.ctx.db.update('role', where="verify_email_token =$path', verify_email_token = '', email=data.new_email, vars={"path": path})

                    web.ctx.session.alerts.append('email_verified')
                raise web.seeother("/", absolute = True)

Anand

[Claudio Dusan Vega Ozuljevich]

Thanks for the solution Anand, that did the trick, and thanks for the clause tip!

--
You received this message because you are subscribed to the Google Groups "web.py" group.
To unsubscribe from this group and stop receiving emails from it, send an email to webpy+un...@googlegroups.com.
To post to this group, send email to we...@googlegroups.com.
Visit this group at http://groups.google.com/group/webpy.
For more options, visit https://groups.google.com/groups/opt_out.