I am using web2py to power my web site. I decided to use the web2py DAL for a long running program that runs behind the site. This program does not seem to update its data or the database (sometimes).
from gluon.sql import *
from gluon.sql import SQLDB
from locdb import *
# contains
# db = SQLDB("mysql://user/pw@localhost/mydb", pool_size=10)
# db.define_table('orders', Field('status', 'integer'), Field('item', 'string'),
# migrate='orders.table')
orderid = 20 # there is row with id == 20 in table orders
#when I do
db(db.orders.id==orderid).update(status=6703)
db.commit()
It does not update the database, and a select on orders with this id, shows the correct data. In some circumstances a "db.rollback()" after a commit seems to help.
Very strange to say the least. Have you seen this? More importantly do you know the solution?
UPDATE:
Correction:
The select in question is done within the program, not outside it.
Sometimes, when doing a series of updates, some will work and be available outside and some will not be available. Also some queries will return the data it originally returned even though the data has changes in the DB since th4 original query.
I am tempted to dump this approach and move to another method, any suggestions?