sqlobject-discuss

[SQLObject] Question about multiple deletes

[David M. <da...@re...>]

Hi,

What's the best way, within SQLObject, to delete an entire results set 
from a table?

In other words, the equivalent of an SQL query like:

   DELETE FROM mytable WHERE last_name = 'Jones' AND age > 50

I know I can iterate through an SQLObject result set and invoke
.destroySelf() on every element, but this feels painfully inefficient.

Can someone please advise me of the fast, SQLOBject-esque way of doing it?

Also, what's the best way of pumping a raw query?

I know conn.getConnection().cursor().execute(query) works, but is there
a better way, and hopefully one which won't cause any problems within 
the SQLObject data structures?

-- 

Kind regards
David

--

leave this line intact so your email gets through my junk mail filter

[SQLObject] Re: Question about multiple deletes

[Chris G. <ch...@il...>]

"David McNab" <da...@re...> wrote in message
news:402...@re......
> What's the best way, within SQLObject, to delete an entire results set
> from a table?
>
> In other words, the equivalent of an SQL query like:
>
>    DELETE FROM mytable WHERE last_name = 'Jones' AND age > 50
>
> I know I can iterate through an SQLObject result set and invoke
> .destroySelf() on every element, but this feels painfully inefficient.

There's nothing in SQLObject right now that does this, but you could add
your own method pretty easily...

Just add a function to the SQLObject class called .multiDelete() or
something, which works the same as .select().. (i.e. takes in the same
parameters... obviously it would do something quite different. ;)

Re: [SQLObject] Re: Question about multiple deletes

[Ian B. <ia...@co...>]

Chris Gahan wrote:
> "David McNab" <da...@re...> wrote in message
> news:402...@re......
> 
>>What's the best way, within SQLObject, to delete an entire results set
>>from a table?
>>
>>In other words, the equivalent of an SQL query like:
>>
>>   DELETE FROM mytable WHERE last_name = 'Jones' AND age > 50
>>
>>I know I can iterate through an SQLObject result set and invoke
>>.destroySelf() on every element, but this feels painfully inefficient.
> 
> 
> There's nothing in SQLObject right now that does this, but you could add
> your own method pretty easily...
> 
> Just add a function to the SQLObject class called .multiDelete() or
> something, which works the same as .select().. (i.e. takes in the same
> parameters... obviously it would do something quite different. ;)

Oops, guess I missed this message.

It's a little more complicated than that, because a delete should really 
go through the SQLObject instances.  Selects should be extended so you 
can retrieve IDs instead of full objects, then there should be a way to 
fetch an object only if it already exists in Python.  Then you'd iterate 
over the select results and call destroySelf on any objects that 
existed, and then do another SQL statement to delete any rows that 
didn't have Python instances.

   Ian

[SQLObject] Re: Re: Question about multiple deletes

[Chris G. <ch...@il...>]

> > There's nothing in SQLObject right now that does this, but you could add
> > your own method pretty easily...
> >
> > Just add a function to the SQLObject class called .multiDelete() or
> > something, which works the same as .select().. (i.e. takes in the same
> > parameters... obviously it would do something quite different. ;)
>
> Oops, guess I missed this message.
>
> It's a little more complicated than that, because a delete should really
> go through the SQLObject instances.  Selects should be extended so you
> can retrieve IDs instead of full objects, then there should be a way to
> fetch an object only if it already exists in Python.  Then you'd iterate
> over the select results and call destroySelf on any objects that
> existed, and then do another SQL statement to delete any rows that
> didn't have Python instances.

Wow, yeah, I didn't think about object-to-database consistency. What a
bitch. :)

Object databases are hard!

Hmmm.. couldn't you have a simpler method though, where the object was
ignorant of its being deleted until it actally tried to sync itself with the
database? I mean, what's the reason for using destroySelf to kill objects?
Is it to keep the cache clean?

Re: [SQLObject] Re: Re: Question about multiple deletes

[Ian B. <ia...@co...>]

Chris Gahan wrote:
>>>There's nothing in SQLObject right now that does this, but you could add
>>>your own method pretty easily...
>>>
>>>Just add a function to the SQLObject class called .multiDelete() or
>>>something, which works the same as .select().. (i.e. takes in the same
>>>parameters... obviously it would do something quite different. ;)
>>
>>Oops, guess I missed this message.
>>
>>It's a little more complicated than that, because a delete should really
>>go through the SQLObject instances.  Selects should be extended so you
>>can retrieve IDs instead of full objects, then there should be a way to
>>fetch an object only if it already exists in Python.  Then you'd iterate
>>over the select results and call destroySelf on any objects that
>>existed, and then do another SQL statement to delete any rows that
>>didn't have Python instances.
> 
> 
> Wow, yeah, I didn't think about object-to-database consistency. What a
> bitch. :)
> 
> Object databases are hard!

Actually, while there's a bunch of details, none of them are 
particularly difficult.  Everything's there, a few new APIs just have to 
be exposed (to get IDs, and to get objects from the cache)

> Hmmm.. couldn't you have a simpler method though, where the object was
> ignorant of its being deleted until it actally tried to sync itself with the
> database? I mean, what's the reason for using destroySelf to kill objects?
> Is it to keep the cache clean?

Actually, I think they might stay in the cache now (though obviously 
they shouldn't).  As a basic principle, destroySelf should be called so 
that objects can clean themselves up.  This allows for things like 
cascading deletes on databases that won't do it for you.  (But it also 
means we have to calculate the cascades even on databases that do 
support doing it themselves)

   Ian