In another thread, niphlod also made a good point that in this particular
case, you cannot even do the particular update you are attempting, as the
expression to add a timedelta is Python specific and will not translate to
proper SQL. So, you're probably better off just looping through the records
and doing the updates one at a time.
Anthony
On Friday, November 20, 2015 at 8:25:28 AM UTC-5, Anthony wrote:
>
> I don't think the DAL can create the proper query because it doesn't know
> which table to update (it extracts the table names from the query, but has
> no way of selecting which table to update). I suppose you could subclass or
> monkey patch the DAL database adapter and change the get_table method:
>
> def get_table(self, *queries):
> tablenames = self.tables(*queries)
> if len(tablenames)==1:
> return tablenames[0]
> elif len(tablenames)<1:
> raise RuntimeError("No table selected")
> else:
> raise RuntimeError("Too many tables selected (%s)" % str(
> tablenames)):
>
> You could change it to return the first table when there is more than one.
> Otherwise, you could just generate the SQL manually and use db.executesql.
>
> You might also reconsider the data model, as this results in some
> redundancy between the tables. You could instead store just the time delta
> with the tournament and maybe use virtual fields to calculate the actual
> tournament times when needed.
>
> Anthony
>
> On Friday, November 20, 2015 at 1:52:05 AM UTC-5, [email protected]
> wrote:
>>
>> I need some help with a DAL query. I'm trying to update records, but my
>> query contains joins.
>>
>> *tournament Table*
>> | id | start | end |
>>
>> *match Table*
>> | id | tournamentID | home_team | away_team | start | end |
>>
>> db( ((db.tournament.start >= '2015-01-01 00:00:00') & (db.tournament.end
>> <= '2015-12-31 00:00:00')) &\
>> (db.tournament.id == db.match.tournamentID) ).update(start=
>> db.match.start + timedelta(hours=1), end= db.match.end + timedelta(hours=1))
>>
>> I want to add 1 hour to the start and end fields *in the match table*
>> (not the tournament table). But when I try to run this DAL statement, I get
>> an error saying "Too many tables" (because of the join, I suppose). What
>> would be an alternate single query for this?
>> Thanks
>>
>> Note: Start and End columns are datetime.
>>
>>
--
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.
