Re: [web2py] complex query with delete

2012-05-14 Thread Anthony 

>
> You may get exceptions from the database driver if the id list is empty. 
>  Using Postgres, I do something like this:
>
> ids = db()._select(db.users_keywords.keyword_id, distinct=True) 
> if len(ids):
>   db((~db.keyword.id.belongs(ids)) & (db.keyword.dictionary == 
> dictionary)). 
>   delete() 
>

Are you sure? Note, the ._select() doesn't generate a list of ids (which 
could potentially be empty) -- it generates the SQL to select the ids and 
is then used as part of a nested select. I'm not sure about all db's, but 
this doesn't generate an error in SQLite even if there are no ids to 
select. In any case, for the above to work, the ids query would have to be 
a regular .select() rather than a ._select() (the latter will always have 
length > 0 because it's just a SQL string). Rather than doing two separate 
queries, another option is to put the delete inside a try/except:

try:
db((~db.keyword.id.belongs(ids)) & (db.keyword.dictionary == dictionary
)).delete()
except OperationalError:
pass

Anthony

Re: [web2py] complex query with delete

2012-05-14 Thread Cliff 
Vincenzo,

You may get exceptions from the database driver if the id list is empty. 
 Using Postgres, I do something like this:

ids = db()._select(db.users_keywords.keyword_id, distinct=True) 
if len(ids):
  db((~db.keyword.id.belongs(ids)) & (db.keyword.dictionary == 
dictionary)). 
  delete() 


On Monday, May 14, 2012 2:14:44 AM UTC-4, Vincenzo Ampolo wrote:
>
> On 05/13/2012 02:50 PM, Anthony wrote: 
> > ids = db()._select(db.users_keywords.keyword_id, distinct=True) 
> > db((~db.keyword.id.belongs(ids)) & (db.keyword.dictionary == 
> dictionary)). 
> > delete() 
> > 
> > Anthony 
>
> Thanks a lot! 
>
> This is exactly what I was looking for! 
>
> -- 
> Vincenzo Ampolo 
> http://vincenzo-ampolo.net 
> http://goshawknest.wordpress.com 
>

Re: [web2py] complex query with delete

2012-05-13 Thread Vincenzo Ampolo 
On 05/13/2012 02:50 PM, Anthony wrote:
> ids = db()._select(db.users_keywords.keyword_id, distinct=True)
> db((~db.keyword.id.belongs(ids)) & (db.keyword.dictionary == dictionary)).
> delete()
> 
> Anthony 

Thanks a lot!

This is exactly what I was looking for!

-- 
Vincenzo Ampolo
http://vincenzo-ampolo.net
http://goshawknest.wordpress.com

Re: [web2py] complex query with delete

2012-05-13 Thread Anthony 

>
> db((db.keyword.id!=db.users_keywords.keyword_id)&(db.keyword.dictionary==dictionary)).delete(db.keyword)
>  
>
>

Exactly what set of records are you trying to delete from db.keyword? Are 
you trying to identify keywords that are not referenced by any records in 
db.users_keywords? If so, how about a nested select (
http://web2py.com/books/default/chapter/29/6#belongs):

ids = db()._select(db.users_keywords.keyword_id, distinct=True)
db((~db.keyword.id.belongs(ids)) & (db.keyword.dictionary == dictionary)).
delete()

Anthony

Re: [web2py] complex query with delete

2012-05-13 Thread Vincenzo Ampolo 
On 05/13/2012 12:02 AM, Andrew wrote:
> Does replacing select with delete work?  

Nope, i got:

TypeError: delete() got an unexpected keyword argument 'join'

It seems that delete does not accept any argument at all!

Yeah. I want to delete from db.keyword

If delete was more like select() would be good to do:

db((db.keyword.id!=db.users_keywords.keyword_id)&(db.keyword.dictionary==dictionary)).delete(db.keyword)

Unluckly it doesn't. Maybe it's time to add this kind of feature ?



-- 
Vincenzo Ampolo
http://vincenzo-ampolo.net
http://goshawknest.wordpress.com

[web2py] complex query with delete

2012-05-12 Thread Andrew 
P.s.  I assume you are wanting to delete from keyword ?  That is why it is 
ambiguous with joins,  which one is the target ?

[web2py] complex query with delete

2012-05-12 Thread Andrew 
Not that I've tried it, but does this syntax from the book help ?
>>> rows = db(db.person).select(join=db.dog.on(db.person.id==db.dog.owner))

Does replacing select with delete work?

[web2py] complex query with delete

2012-05-12 Thread Vincenzo Ampolo 
Hi,

In the web2py book I've seen simple delete queries like:

db(db.person.id > 3).delete()

where it's easy to understand that the persons with id > 3 will be
deleted from db.person.id

but what if i want to delete rows selected with this query:

db((db.keyword.id!=db.users_keywords.keyword_id)&(db.keyword.dictionary==dictionary)).delete()

It will say that too many tables are involved and will hang.

So how i can delete that set of rows quickly ?


Thanks


-- 
Vincenzo Ampolo
http://vincenzo-ampolo.net
http://goshawknest.wordpress.com