ok, let's elaborate.
db(db.person.id > 3).update(name='Ken')
and
db(db.person.id > 3).update(name=db.person.name+'Ken')
are actually REALLY different (from the database perspective).
The first passes a FIXED value to the database, which discards any value
contained in the "name" column and replaces it with a FIXED value that you
pass to it ('Ken'). Given, e.g., 4 records that have "id" > 3, ALL 4
records will end up having "Ken" as the value hold in the "name" column.
It's an indempotent function: no matter how many times you execute it, the
result is always the same.
The second instead asks the database to "append" the "Ken" fixed value to
whatever the column is holding already. It's NOT idempotent. Each time you
execute it, you end up having different results.
Now, back to the pyDAL.
When you ask pydal to do an update of the "latter" case, you pass "down the
pipe" that assignment
to the database. You're trying to leverage the backend's (and not python's)
capabilities. This is labelled as an "expression" (see here
<http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Expressions>for
the book docs about it)
The first statement translates roughly to
UPDATE person
SET name = 'Ken'
WHERE id > 3
The second one, instead, translates to
UPDATE person
SET name = *name + 'Ken'*
WHERE id > 3
The funny thing here can not be really obvious, but the second query (the
"expression") actually calls a function in the backend (in bold with the
operator in red), that is, in this case, a simple "append" (NB: different
backend have different syntax for this simple "append" function)
Now, backend are generally MORE limited than python when dealing with
functions, and pyDAL can "translate" only a fraction of functions.
Strings "append" and "prepend" are some of them, and also "addition" and
"subtractions" of numeric values, as in
db(db.vote.id == 3).update(value=db.vote.value+1)
Date operations, on the other end, are REALLY easy in python and REALLY
complicated in backend (every dialect has its own intricacies)
pyDAL can't intercept and translate correctly all of them, so you need to
test CAREFULLY complicate function in python when passed to an update to
pyDAL.
For a
db(db.appointments.id == 3).update(start_date=db.appointments.start_date +
1)
that under the hood translate to a
UPDATE appointments
SET start_date = start_date + 1
WHERE id = 3
some backend may implicitly add a second, or a day, or a month. Or throw an
exception.
What you're doing adds yet another layer of intricacies: the backend
doesn't know the first thing about "timedelta(hours=1)" bit. pyDAL knows
it, and tries to stringify it.
It's easy to know what query pyDAL is actually passing down the pipe: you
can see any update() issued to the backend in its dialect using _update()
(see the underscore).
tl;dr: if you need to update a set of records to a FIXED value (first case
scenario), no problems on passing a FIXED value down the pipe.
if you need to update a set of records to a CALCULATED value, which is the
result of a function (i.e. when you have db.table.columnname on the right
end of the assignment) BEWARE that only SIMPLE operations are
"transparently" passed to the backend.
Use _update() to inspect the actual query and TEST your code at every step.
Not EVERY python function (which is, at all effects, passed down the pipe
and DEMANDED to the backend) has a proper way to be passed to the backend
dialect to have the same result you'd have in python.
To overcome it and be sure the function is calculated in python, you need
to fetch the value first, and then update() passing a FIXED value.
This "form" is slower (need to fetch the values first, at the very least)
but ensures python doing the bits, and not the database
rows = db(db.person.id > 3).select()
for row in rows:
row.update_record(name=row.name + 'Ken')
or, for the timedelta example
rows = db(db.mytable.id == 1).select()
for row in rows:
row.update_record(start=row.start + timedelta(hours=1))
Please mind that the difference ISN'T the fact that it uses update_record()
instead of update().
It could be very well rewritten as
db(db.mytable.id == row.id).update(start=row.start + timedelta(hours=1))
The key difference is that on the right end of the assignment there isn't
db.tablename.columnname (which will trigger the "expressions" behaviour,
relinquishing control to the backend). With no expressions, the calculation
is computed in python (up to a fixed value) BEFORE passing it the statement
to the backend.
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
