headers
akira | 2 Oct 2007 03:03
Picon

Dropping all rows of a given matching field or id

Hi!

How does one drop all the rows matching a given id or field value? I 
tried this:

class Profile(object):
    __storm_table__ = "test_delete"
    id = Int(primary=True)
    field1= Unicode()
    field2= Unicode()
    field3= Unicode()
    field4= Unicode()

store = Store(database)

resultset = store.find(Profile, Profile.id == 1).remove()

but I  received an error. What should be the right syntax? Thanks
Permalink | Reply | 0 comments |
headers
akira | 2 Oct 2007 03:35
Picon

Re. Dropping all rows of a given matching field or id SOLVED

used trial an error, I always forget the "commit" :-)

from storm.locals import *

class Profile(object):
    __storm_table__ = "table_profile"
    id = Int(primary=True)
    field1= Unicode()
    field2= Unicode()
    field3= Unicode()
    field4= Unicode() 

store = Store(database)

resultset = store.find(Profile, Profile.field1 == u"man").remove()
#store.execute("DELETE FROM table_profile")
store.commit()
Permalink | Reply | 0 comments |
headers
akira | 2 Oct 2007 18:53
Picon

Freeze while dropping multiple rows spanning multiple tables

I have to delete several rows of data at once, I am doing something like 
this:

store.find(JobseekerApplication, JobseekerApplication.user_id == 
session["user_id"]).remove()
store.find(JobseekerAcademicRecord, JobseekerAcademicRecord.user_id == 
session["user_id"]).remove()
store.find(JobseekerIntendedBranch, JobseekerIntendedBranch.user_id == 
session["user_id"]).remove()
store.find(JobseekerIntendedCountry, JobseekerIntendedCountry.user_id == 
session["user_id"]).remove()
store.find(JobseekerIntendedPostcode, JobseekerIntendedPostcode.user_id 
== session["user_id"]).remove()
store.find(JobseekerProfessionalRecord, 
JobseekerProfessionalRecord.user_id == session["user_id"]).remove()
store.find(JobseekerProfile, JobseekerProfile.user_id == 
session["user_id"]).remove()

the problem is, this freezes or crashes once in a while, what would be 
the best way to take care of this?

thanks
Permalink | Reply | 7 comments |
headers
Gustavo Niemeyer | 2 Oct 2007 19:14
Favicon
Gravatar

Re: Freeze while dropping multiple rows spanning multiple tables


> the problem is, this freezes or crashes once in a while, what would be 
> the best way to take care of this?

Freezing usually indicates that due to concurrent access your database
is holding a lock.  I suspect the same is true for "crashing", but without
more information it could be pretty much anything.

--

-- 
Gustavo Niemeyer
http://niemeyer.net
Permalink | Reply | 6 comments |
headers
akira | 2 Oct 2007 19:49
Picon

Re: Freeze while dropping multiple rows spanning multiple tables

Hi Gustavo, you are right about the locks, the ere were many locks 
beeing held. Is there some way I can handle this better?  I´m doomed :-((

Gustavo Niemeyer wrote:
>> the problem is, this freezes or crashes once in a while, what would be 
>> the best way to take care of this?
>>     
>
> Freezing usually indicates that due to concurrent access your database
> is holding a lock.  I suspect the same is true for "crashing", but without
> more information it could be pretty much anything.
>
>   

--

-- 
storm mailing list
storm@...
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/storm
Permalink | Reply | 5 comments |
headers
Gustavo Niemeyer | 2 Oct 2007 20:14
Favicon
Gravatar

Re: Freeze while dropping multiple rows spanning multiple tables


> Hi Gustavo, you are right about the locks, the ere were many locks 
> beeing held. Is there some way I can handle this better?  I´m doomed :-((

Yes, but it's entirely dependent on your application.  In general, don't
keep transactions open (IOW, don't do actions on the store and forget
about it without commit/rollback).

-- 
Gustavo Niemeyer
http://niemeyer.net

--

-- 
storm mailing list
storm@...
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/storm
Permalink | Reply | 2 comments |
headers
akira | 2 Oct 2007 20:37
Picon

Re: Freeze while dropping multiple rows spanning multiple tables

ahh, thats a tip, should I commit after every table ? Like this?

store.find(JobseekerApplication, JobseekerApplication.user_id == 
session["user_id"]).remove()
store.commit()

store.find(JobseekerAcademicRecord, JobseekerAcademicRecord.user_id == 
session["user_id"]).remove()
store.commit()

thanks

Gustavo Niemeyer wrote:
>> Hi Gustavo, you are right about the locks, the ere were many locks 
>> beeing held. Is there some way I can handle this better?  I´m doomed :-((
>>     
>
> Yes, but it's entirely dependent on your application.  In general, don't
> keep transactions open (IOW, don't do actions on the store and forget
> about it without commit/rollback).
>
>   

--

-- 
storm mailing list
storm@...
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/storm
Permalink | Reply | 1 comment |
headers
Gustavo Niemeyer | 2 Oct 2007 20:41
Favicon
Gravatar

Re: Freeze while dropping multiple rows spanning multiple tables

> ahh, thats a tip, should I commit after every table ? Like this?

No, that's overkill.  To give you an idea, in web-based systems,
commit/rollback is usually done after the request is finished.

--

-- 
Gustavo Niemeyer
http://niemeyer.net
Permalink | Reply | 0 comments |
headers
Stuart Bishop | 3 Oct 2007 05:18
Gravatar

Re: Freeze while dropping multiple rows spanning multiple tables

akira wrote:
> Hi Gustavo, you are right about the locks, the ere were many locks 
> beeing held. Is there some way I can handle this better?  I´m doomed :-((

Usually this is caused by some other process being badly behaved, and that
is probably the best place to start if it is under your control. Badly
behaved in this case means running for a long time without committing -
maybe it is necessary because there are lot of changes that need to be made
and they need to be done in a single transaction to ensure integrity, or
maybe the developer doesn't fully understand the concept of transactions and
transaction isolation.

If these other processes are legitimately holding locks open for extended
periods of time or you just have to deal with it, then you need to decide
what the correct behaviour should be. You have the choice of blocking,
failing and returning an error, or detecting the outstanding lock and
skipping steps that need that resource.

Blocking is default behaviour in general, and what you probably have now.
There is also a chance, if your process and the competing process are trying
to access resources in different orders, of ending up with a deadlock. Under
PostgreSQL this will raise an exception (what backend are you using?)

If you want either of the other two options or handle deadlocks more
gracefully, it starts getting backend specific. Under PostgreSQL you use the
LOCK statement to explicitly obtain locks on the resources you need. To
detect a competing lock, you start a SAVEPOINT and issue a LOCK TABLE ... NO
WAIT statement which will raise an exception instead of blocking if the lock
cannot be obtained, at which point you can roll back to the savepoint and
continue on or report a pretty error to the user.
(Continue reading)

Permalink | Reply | 1 comment |
headers
Gustavo Niemeyer | 3 Oct 2007 16:54
Favicon
Gravatar

None vs. undefined

Hello Stormers,

The explanation posted on the bug Carsten Grohmann opened
in Launchpad (#148553) might be interesting for more people,
so I'm copying it over here for reference.

-----

Hello Cartsten,

I got through your example and figured out what's going on. Here's the
actual issue:

    class Share(object):
        __storm_table__ = "t_share"
        id = Int(primary=True)
        (...)
        def __init__(self, **kwargs):
            self.id = kwargs.get('id')

When you are creating an object, there's a difference between assigning
None to an attribute and leaving it unassigned. When you assign None,
Storm will interpret that as NULLing out that value, and will send that
column to the database as such. When nothing is assigned to an attribute
(or the attribute is deleted with something like "del obj.attr"), Storm
will interpret that as the value being undefined, and will ask the
database for that value in the next opportunity.

In the case above, self.id is being assigned the value None explicitly,
and so this is sent to the database as a NULL. Coincidently, sqlite3
(Continue reading)

Permalink | Reply | 0 comments |

Gmane