Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-07-12 Thread Michael Bayer 

On Jul 12, 2014, at 8:17 AM, Michael Bayer  wrote:

> If decimal accuracy is of any concern, you should be using Decimal objects 
> fully, never a Python float, and if you are using a Numeric without 
> asdecimal=False this is more appropriate.
> 

sorry, asdecimal=True, use Decimal objects, asdecimal=False, use float objects.



-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-07-12 Thread Michael Bayer 

On Jul 12, 2014, at 4:49 AM, Staszek  wrote:

> 
> Not really, no, yes.
> 
> The problem seems to have been with assignment of float values. It was
> like this:
> 
> base.py:847: UPDATE buildings SET glatitude=%s, glongitude=%s WHERE
> buildings.id = %s
> base.py:849: (52.0210673, 20.2152834, 1)
> 
> It can't be seen here, but then I guess those float values were being
> converted by SQLAlchemy to decimals like:
> 
> Decimal('52.02106729998567727743647992610931396484375')

the Numeric type deals with Decimal objects.add usedecimal=False to deal 
with floats.   It only does a float <-> Decimal conversion if the backend 
doesn't support it.

> 
> which was not equal to the value stored in the column because it has
> lower precision.
> 
> However after converting those new values to Decimal *and* quantizing:
> 
> d = Decimal('0.0001')
> glat = Decimal.from_float(glat).quantize(d)
> glon = Decimal.from_float(glon).quantize(d)
> 
> the new Decimals are equal to the old Decimals and I can see no UPDATE
> in the log.
> 
> Not sure how it exactly works on SQLAlchemy side

it doesn't emit an UPDATE when the value that was loaded matches the value that 
it was modified to, using ==.


> but perhaps it could
> make sense to take the column precision into consideration when
> comparing decimals on assignment/update.

See decimal_return_scale at 
http://docs.sqlalchemy.org/en/rel_0_9/core/types.html?highlight=numeric#sqlalchemy.types.Numeric.__init__.

However, consider that the MySQL driver can be involved in decimal conversion 
operations as well; I'm not sure offhand which side does what in the case of 
MySQL.

If decimal accuracy is of any concern, you should be using Decimal objects 
fully, never a Python float, and if you are using a Numeric without 
asdecimal=False this is more appropriate.

None of this has anything to do with your original issue with the UPDATE not 
matching any rows, however.  The UPDATE here is not matching on the integer 
primary key.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-07-12 Thread Staszek 
On 2014-07-11 23:18, Mike Bayer wrote:
> 
> On 7/11/14, 5:07 PM, Staszek wrote:
>> On 2014-02-01 00:09, Michael Bayer wrote:
>>> On Jan 31, 2014, at 5:24 PM, lars van gemerden >> > wrote:
>>>
 Hi, all

 I am running into these 2 errors and have run out of ideas what to do
 about it (also because i don't what they mean); They seem to happen in
 exactly the same circumstances.

 mapper, table, update)
   File "build\bdist.win32\egg\sqlalchemy\orm\persistence.py", line
 514, in _emit_update_statements
 (table.description, len(update), rows))
 StaleDataError: UPDATE statement on table 'Company' expected to update
 1 row(s); 0 were matched.
>>> this means an object was meant to be UPDATEed via the ORM, however the
>>> row which is the target of the UPDATE is missing.  Either the primary
>>> key of this row changed somehow, or the row was deleted, *or* the row is
>>> not visible to your transaction (this seems to be your case).
>> I am running into something very similar:
>>
>> StaleDataError: UPDATE statement on table 'buildings' expected to update
>> 1 row(s); 0 were matched.
>>
>> However this case is very simple, just a single row in a table, and it
>> looks to get selected just before the update (in the same transaction).
>> Nothing is being deleted.
>>
>> The update:
>>
>> UPDATE buildings SET glatitude=%s, glongitude=%s WHERE buildings.id = %s
>>
>> The columns:
>>
>> id = Column(BigInteger, autoincrement=True, primary_key=True)
>> glatitude  = Column(Numeric(precision=11, scale=8))
>> glongitude = Column(Numeric(precision=11, scale=8))
>>
>> What's interesting is that the error occurs only if glatitude and
>> glongitude are being updated to the same values that are already in the
>> database (i.e.: no real update).
>> When the old values are NULL or otherwise different than the new values,
>> everything works fine!
>>
>> Is this a bug?
> It's unlikely to be a SQLAlchemy bug, but questions to be answered
> include:   Can you produce a self-contained test case?  Have you tried
> SQLAlchemy 0.9.6 rather than 0.9.1?  Also what backend is this, MySQL? 
> MySQL has had some bugs where the rowcount is not reported correctly.

Not really, no, yes.

The problem seems to have been with assignment of float values. It was
like this:

base.py:847: UPDATE buildings SET glatitude=%s, glongitude=%s WHERE
buildings.id = %s
base.py:849: (52.0210673, 20.2152834, 1)

It can't be seen here, but then I guess those float values were being
converted by SQLAlchemy to decimals like:

Decimal('52.02106729998567727743647992610931396484375')

which was not equal to the value stored in the column because it has
lower precision.

However after converting those new values to Decimal *and* quantizing:

d = Decimal('0.0001')
glat = Decimal.from_float(glat).quantize(d)
glon = Decimal.from_float(glon).quantize(d)

the new Decimals are equal to the old Decimals and I can see no UPDATE
in the log.

Not sure how it exactly works on SQLAlchemy side but perhaps it could
make sense to take the column precision into consideration when
comparing decimals on assignment/update.

-- 
http://people.eisenbits.com/~stf/
http://www.eisenbits.com/

OpenPGP: 80FC 1824 2EA4 9223 A986  DB4E 934E FEA0 F492 A63B

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-07-11 Thread Mike Bayer 

On 7/11/14, 5:07 PM, Staszek wrote:
> On 2014-02-01 00:09, Michael Bayer wrote:
>> On Jan 31, 2014, at 5:24 PM, lars van gemerden > > wrote:
>>
>>> Hi, all
>>>
>>> I am running into these 2 errors and have run out of ideas what to do
>>> about it (also because i don't what they mean); They seem to happen in
>>> exactly the same circumstances.
>>>
>>> mapper, table, update)
>>>   File "build\bdist.win32\egg\sqlalchemy\orm\persistence.py", line
>>> 514, in _emit_update_statements
>>> (table.description, len(update), rows))
>>> StaleDataError: UPDATE statement on table 'Company' expected to update
>>> 1 row(s); 0 were matched.
>> this means an object was meant to be UPDATEed via the ORM, however the
>> row which is the target of the UPDATE is missing.  Either the primary
>> key of this row changed somehow, or the row was deleted, *or* the row is
>> not visible to your transaction (this seems to be your case).
> I am running into something very similar:
>
> StaleDataError: UPDATE statement on table 'buildings' expected to update
> 1 row(s); 0 were matched.
>
> However this case is very simple, just a single row in a table, and it
> looks to get selected just before the update (in the same transaction).
> Nothing is being deleted.
>
> The update:
>
> UPDATE buildings SET glatitude=%s, glongitude=%s WHERE buildings.id = %s
>
> The columns:
>
> id = Column(BigInteger, autoincrement=True, primary_key=True)
> glatitude  = Column(Numeric(precision=11, scale=8))
> glongitude = Column(Numeric(precision=11, scale=8))
>
> What's interesting is that the error occurs only if glatitude and
> glongitude are being updated to the same values that are already in the
> database (i.e.: no real update).
> When the old values are NULL or otherwise different than the new values,
> everything works fine!
>
> Is this a bug?
It's unlikely to be a SQLAlchemy bug, but questions to be answered
include:   Can you produce a self-contained test case?  Have you tried
SQLAlchemy 0.9.6 rather than 0.9.1?  Also what backend is this, MySQL? 
MySQL has had some bugs where the rowcount is not reported correctly. 


-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-07-11 Thread Staszek 
On 2014-02-01 00:09, Michael Bayer wrote:
> 
> On Jan 31, 2014, at 5:24 PM, lars van gemerden  > wrote:
> 
>> Hi, all
>>
>> I am running into these 2 errors and have run out of ideas what to do
>> about it (also because i don't what they mean); They seem to happen in
>> exactly the same circumstances.
>>
>> mapper, table, update)
>>   File "build\bdist.win32\egg\sqlalchemy\orm\persistence.py", line
>> 514, in _emit_update_statements
>> (table.description, len(update), rows))
>> StaleDataError: UPDATE statement on table 'Company' expected to update
>> 1 row(s); 0 were matched.
> 
> this means an object was meant to be UPDATEed via the ORM, however the
> row which is the target of the UPDATE is missing.  Either the primary
> key of this row changed somehow, or the row was deleted, *or* the row is
> not visible to your transaction (this seems to be your case).

I am running into something very similar:

StaleDataError: UPDATE statement on table 'buildings' expected to update
1 row(s); 0 were matched.

However this case is very simple, just a single row in a table, and it
looks to get selected just before the update (in the same transaction).
Nothing is being deleted.

The update:

UPDATE buildings SET glatitude=%s, glongitude=%s WHERE buildings.id = %s

The columns:

id = Column(BigInteger, autoincrement=True, primary_key=True)
glatitude  = Column(Numeric(precision=11, scale=8))
glongitude = Column(Numeric(precision=11, scale=8))

What's interesting is that the error occurs only if glatitude and
glongitude are being updated to the same values that are already in the
database (i.e.: no real update).
When the old values are NULL or otherwise different than the new values,
everything works fine!

Is this a bug?

This is SQLAlchemy 0.9.1:

  File "[...]/sqlalchemy/orm/session.py", line 765, in commit
self.transaction.commit()
  File "[...]/sqlalchemy/orm/session.py", line 370, in commit
self._prepare_impl()
  File "[...]/sqlalchemy/orm/session.py", line 350, in _prepare_impl
self.session.flush()
  File "[...]/sqlalchemy/orm/session.py", line 1879, in flush
self._flush(objects)
  File "[...]/sqlalchemy/orm/session.py", line 1997, in _flush
transaction.rollback(_capture_exception=True)
  File "[...]/sqlalchemy/util/langhelpers.py", line 57, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File "[...]/sqlalchemy/orm/session.py", line 1961, in _flush
flush_context.execute()
  File "[...]/sqlalchemy/orm/unitofwork.py", line 370, in execute
rec.execute(self)
  File "[...]/sqlalchemy/orm/unitofwork.py", line 523, in execute
uow
  File "[...]/sqlalchemy/orm/persistence.py", line 59, in save_obj
mapper, table, update)
  File "[...]/sqlalchemy/orm/persistence.py", line 528, in
_emit_update_statements
(table.description, len(update), rows))
StaleDataError: UPDATE statement on table 'buildings' expected to update
1 row(s); 0 were matched.

-- 
http://people.eisenbits.com/~stf/
http://www.eisenbits.com/

OpenPGP: 80FC 1824 2EA4 9223 A986  DB4E 934E FEA0 F492 A63B

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-02-01 Thread lars van gemerden 
Ok, Michael,

That helped a lot, what i have done is (for future reference/others);

1) turned of autoflush on all sessions,
2) shortened the lifespan of session to a minimum,
3) removed the "if object_session(obj): session.merge(obj)" option in the 
Session() function,

This seems to have solved the problem for now (needs more testing)!

Thanks for the link to the talk as well.

Cheerio, Lars



On Saturday, February 1, 2014 4:34:10 PM UTC+1, Michael Bayer wrote:
>
>
> On Feb 1, 2014, at 9:01 AM, lars van gemerden 
> > 
> wrote: 
>
> > Oh, on more question: 
> > 
> > might there be anything inherently wrong with the scoped_session 
> approach that i showed in the code snippets above? 
>
> the code which illustrates the @contextmanager and the “def Session()” 
> looks error-prone and entirely awkward, and the rationale for such a 
> context manager isn’t apparent.   
>
> It appears to be mixing the intent of wishing to share random rows between 
> multiple sessions (a bad idea) while at the same time trying to conceal the 
> details of how a delicate operation like that is performed (it guesses 
> whether add() or merge() should be used, etc).  It also seems to mix the 
> concerns of dealing with object mechanics and session creation at the same 
> time which are typically two different concerns, not to mention that it has 
> a complex system of committing or not committing using flags which makes it 
> unsurprising that you’re seeing non-existent rows show up in other 
> transactions. 
>
> So yeah, if it were me, I’d definitely try to approach whatever the 
> problem is it’s trying to solve in a different way, one which preferably 
> sticks to the patterns outlined in the ORM tutorial as much as possible 
> (e.g. one session at a time, load/manipulate objects, commit(), throw 
> everything away).   Those points at which an application actually uses two 
> sessions at once, or transfers objects between them, should be very 
> isolated cases with very explicit mechanics and clear rationale why this 
> operation is needed in this specific case (where typical cases are: sending 
> objects into worker threads or processes, moving objects in and out of 
> caching layers, or running two transactions simultaneously so that one can 
> commit and the other roll back, such as a transaction writing to an 
> application history table).The app wouldn’t have a generic “here’s one 
> of those places we need to use two sessions with a specific target object 
> to pull out of one of them” use case such that a context manager is needed, 
> there should be extremely few places where that kind of thing goes on. 
>
> The kind of issue you’re hitting is exactly the one I talk about in detail 
> in my talk, “The SQLAlchemy Session in Depth”: 
> http://techspot.zzzeek.org/2012/11/14/pycon-canada-the-sqlalchemy-session-in-depth/
>  Page 18 of the PDF illustrates a slide “Lack of Behavioral Constraints 
> Creates Confusion” illustrating an anti-pattern similar to the one I think 
> we’re seeing here. 
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-02-01 Thread Michael Bayer 

On Feb 1, 2014, at 9:01 AM, lars van gemerden  wrote:

> Oh, on more question:
> 
> might there be anything inherently wrong with the scoped_session approach 
> that i showed in the code snippets above?

the code which illustrates the @contextmanager and the “def Session()” looks 
error-prone and entirely awkward, and the rationale for such a context manager 
isn’t apparent.   

It appears to be mixing the intent of wishing to share random rows between 
multiple sessions (a bad idea) while at the same time trying to conceal the 
details of how a delicate operation like that is performed (it guesses whether 
add() or merge() should be used, etc).  It also seems to mix the concerns of 
dealing with object mechanics and session creation at the same time which are 
typically two different concerns, not to mention that it has a complex system 
of committing or not committing using flags which makes it unsurprising that 
you’re seeing non-existent rows show up in other transactions.

So yeah, if it were me, I’d definitely try to approach whatever the problem is 
it’s trying to solve in a different way, one which preferably sticks to the 
patterns outlined in the ORM tutorial as much as possible (e.g. one session at 
a time, load/manipulate objects, commit(), throw everything away).   Those 
points at which an application actually uses two sessions at once, or transfers 
objects between them, should be very isolated cases with very explicit 
mechanics and clear rationale why this operation is needed in this specific 
case (where typical cases are: sending objects into worker threads or 
processes, moving objects in and out of caching layers, or running two 
transactions simultaneously so that one can commit and the other roll back, 
such as a transaction writing to an application history table).The app 
wouldn’t have a generic “here’s one of those places we need to use two sessions 
with a specific target object to pull out of one of them” use case such that a 
context manager is needed, there should be extremely few places where that kind 
of thing goes on.

The kind of issue you’re hitting is exactly the one I talk about in detail in 
my talk, “The SQLAlchemy Session in Depth”: 
http://techspot.zzzeek.org/2012/11/14/pycon-canada-the-sqlalchemy-session-in-depth/
  Page 18 of the PDF illustrates a slide “Lack of Behavioral Constraints 
Creates Confusion” illustrating an anti-pattern similar to the one I think 
we’re seeing here.



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-02-01 Thread Michael Bayer 

On Feb 1, 2014, at 7:25 AM, lars van gemerden  wrote:

> Thanks, this helps some to narrow it down.
> 
> Trying to zoom in:
> 
> - why would sqla try to UPDATE (instead of INSERT) a row in the database, 
> when the row/object was never committed before?

UPDATE is emitted for an object that is in the “persistent” state, that is, has 
an identity key.  you can view this key via inspect(myobject).key as well as 
inspect(myobject).has_identity.


> - when you flush an object to the database and then close the session that 
> flushed (no commit), what happens to the flushed data?

if you close the session, the session doesn’t commit the transaction, it 
releases the connection to the connection pool, and assuming the pool is used 
with its default settings a rollback() is emitted on that connection.


> - if an object is in a session and it has_identity, why would accessing 
> obj.id (id is the primary key) fail (see above)? 

i really don’t know, the code snippets you’re sending are long yet are not 
complete and therefore not runnable, and omit details that may be important.  
if you want me to step through exactly what’s happening you need to give me a 
complete and self contained (and succinct!) test case.

> - Is there (in principle) a problem with:
>  + having an object of a mapped class which was never committed (but 
> maybe was added to a session and flushed, after which the session was closed)
>  + setting an attribute of that object with another object that was 
> queried from the database
>  + committing the first object to the database?

yeah, if that first object was flushed, then its session would have considered 
it to be persistent, yet if the session were not committed but the now detached 
object still used, it essentially represents a bogus row.




signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-02-01 Thread lars van gemerden 
Oh, on more question:

might there be anything inherently wrong with the scoped_session approach 
that i showed in the code snippets above?

CL

On Saturday, February 1, 2014 1:25:27 PM UTC+1, lars van gemerden wrote:
>
> Thanks, this helps some to narrow it down.
>
> Trying to zoom in:
>
> - why would sqla try to UPDATE (instead of INSERT) a row in the database, 
> when the row/object was never committed before?
> - when you flush an object to the database and then close the session that 
> flushed (no commit), what happens to the flushed data?
> - if an object is in a session and it has_identity, why would accessing 
> obj.id (id is the primary key) fail (see above)? 
> - Is there (in principle) a problem with:
>  + having an object of a mapped class which was never committed 
> (but maybe was added to a session and flushed, after which the session was 
> closed)
>  + setting an attribute of that object with another object that 
> was queried from the database
>  + committing the first object to the database?
>
> Cheers, Lars
>
> On Saturday, February 1, 2014 3:42:09 AM UTC+1, Michael Bayer wrote:
>>
>>
>> On Jan 31, 2014, at 8:11 PM, lars van gemerden  
>> wrote:
>>
>>
>> "this means an object was meant to be UPDATEed via the ORM, however the 
>> row which is the target of the UPDATE is missing.  Either the primary key 
>> of this row changed somehow, or the row was deleted, *or* the row is not 
>> visible to your transaction (this seems to be your case)."
>>
>> -  could the error also occur when the object was never committed to the 
>> database (which seems to be the case; the commit where the error occurs 
>> should be the first time the Company object is committed to the database)?
>>
>>
>> sure
>>
>> -  this seems to suggest that it is possible that a row is in the 
>> database, but that it is not visible to a transaction; is that possible?
>>
>>
>> absolutely, the link http://en.wikipedia.org/wiki/Transaction_isolation is 
>> relevant here
>>
>>
>>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-02-01 Thread lars van gemerden 
Thanks, this helps some to narrow it down.

Trying to zoom in:

- why would sqla try to UPDATE (instead of INSERT) a row in the database, 
when the row/object was never committed before?
- when you flush an object to the database and then close the session that 
flushed (no commit), what happens to the flushed data?
- if an object is in a session and it has_identity, why would accessing 
obj.id (id is the primary key) fail (see above)? 
- Is there (in principle) a problem with:
 + having an object of a mapped class which was never committed 
(but maybe was added to a session and flushed, after which the session was 
closed)
 + setting an attribute of that object with another object that was 
queried from the database
 + committing the first object to the database?

Cheers, Lars

On Saturday, February 1, 2014 3:42:09 AM UTC+1, Michael Bayer wrote:
>
>
> On Jan 31, 2014, at 8:11 PM, lars van gemerden 
> > 
> wrote:
>
>
> "this means an object was meant to be UPDATEed via the ORM, however the 
> row which is the target of the UPDATE is missing.  Either the primary key 
> of this row changed somehow, or the row was deleted, *or* the row is not 
> visible to your transaction (this seems to be your case)."
>
> -  could the error also occur when the object was never committed to the 
> database (which seems to be the case; the commit where the error occurs 
> should be the first time the Company object is committed to the database)?
>
>
> sure
>
> -  this seems to suggest that it is possible that a row is in the 
> database, but that it is not visible to a transaction; is that possible?
>
>
> absolutely, the link http://en.wikipedia.org/wiki/Transaction_isolation is 
> relevant here
>
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-01-31 Thread Michael Bayer 

On Jan 31, 2014, at 8:11 PM, lars van gemerden  wrote:

> 
> "this means an object was meant to be UPDATEed via the ORM, however the row 
> which is the target of the UPDATE is missing.  Either the primary key of this 
> row changed somehow, or the row was deleted, *or* the row is not visible to 
> your transaction (this seems to be your case)."
> 
> -  could the error also occur when the object was never committed to the 
> database (which seems to be the case; the commit where the error occurs 
> should be the first time the Company object is committed to the database)?

sure

> -  this seems to suggest that it is possible that a row is in the database, 
> but that it is not visible to a transaction; is that possible?

absolutely, the link http://en.wikipedia.org/wiki/Transaction_isolation is 
relevant here




signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-01-31 Thread lars van gemerden 
Hi Michael,

With some more detail:

What i do is:
1 make a new object (mapped)-> obj1
2 create a scoped session (with context manager)-> session1
3 do session1.add(obj) 
4 create another scoped session -> session2 
5 do session2.query(someclass).get(some_id)->obj2 
6 close session2, no commit, no flush -> obj2 is detached (right?)
7 do obj1.someattr.append(obj2)
8 do session1.commit()
9 get the first ERROR above 

basically i use

def Session( objs):
session = session_maker()
for obj in objs:
if object_session(obj) is None:
session.add(obj)
else:
session.merge(obj)
return session

@contextmanager
def scoped_session(objs = [], commit = True):
session = Session(objs)
try:
yield session
if commit:
session.commit()
except:
session.rollback()
raise
finally:
session.close()

and essentially code description (1-8) above comes down to:

obj1 = cls1()

with scoped_session([obj1]) as session1:
obj1.somefield = "somevalue"
with scoped_session(commit = False) as session2:
obj2 = session2.query(cls2).get(some_id)
obj1.someattr.append(obj2)

if i just do:

with scoped_session([obj1]) as session1:
obj1.somefield = "somevalue"

there is no problem.

Also:

"this means an object was meant to be UPDATEed via the ORM, however the row 
which is the target of the UPDATE is missing.  Either the primary key of 
this row changed somehow, or the row was deleted, *or* the row is not 
visible to your transaction (this seems to be your case)."

-  could the error also occur when the object was never committed to the 
database (which seems to be the case; the commit where the error occurs 
should be the first time the Company object is committed to the database)?
-  this seems to suggest that it is possible that a row is in the database, 
but that it is not visible to a transaction; is that possible?

As far as i know in the code that causes the problem, i do not do any 
deletes and i do not call flush myself.

Doing some more testing, now i get more of the second error in:

def __str__(self): #in mapped class
print object_session(self) is not None, has_identity(self) # True, 
True, <= OK
print self.id #<= ERROR
..

with trace:
File "d:\Documents\Code\python\floware\models\flow\processes.py", line 333, 
in run
  self.execute(input, output)
File "d:\Documents\Code\python\floware\toolshed\logs.py", line 55, in 
wrapper
  f_result = func(*v, **k)
File "d:\Documents\Code\python\floware\models\flow\libraries\basic.py", 
line 159, in execute
  print "%s %s" % (self.cursor, str(i.item))
File "d:\Documents\Code\python\floware\models\data\database.py", line 281, 
in __str__
  print object_session(self), has_identity(self), self.id
File 
"C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\attributes.py",
 
line 316, in __get__
File 
"C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\attributes.py",
 
line 611, in get
File 
"C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\state.py",
 
line 380, in __call__
File 
"C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\loading.py",
 
line 606, in load_scalar_attributes

sqlalchemy.orm.exc.ObjectDeletedError: Instance '' 
has been deleted, or its row is otherwise not present.

CL

>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] StaleDataError/ObjectDeletedError

2014-01-31 Thread Michael Bayer 

On Jan 31, 2014, at 5:24 PM, lars van gemerden  wrote:

> Hi, all
> 
> I am running into these 2 errors and have run out of ideas what to do about 
> it (also because i don't what they mean); They seem to happen in exactly the 
> same circumstances.
> 
> mapper, table, update)
>   File "build\bdist.win32\egg\sqlalchemy\orm\persistence.py", line 514, in 
> _emit_update_statements
> (table.description, len(update), rows))
> StaleDataError: UPDATE statement on table 'Company' expected to update 1 
> row(s); 0 were matched.

this means an object was meant to be UPDATEed via the ORM, however the row 
which is the target of the UPDATE is missing.  Either the primary key of this 
row changed somehow, or the row was deleted, *or* the row is not visible to 
your transaction (this seems to be your case).

Here’s the DELETE case:

sess = Session()

m1 = MyObject()
sess.add(m1)
sess.flush()

# out of band DELETE, ORM has no clue
sess.execute(“DELETE FROM my_object WHERE id=:id”, {“id”: m1.id})

# modify object
m1.foo = ‘bar’

# row is gone, boom
sess.flush()


> 
> File 
> "C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\loading.py",
>  line 606, in load_scalar_attributes
> sqlalchemy.orm.exc.ObjectDeletedError: Instance '' has 
> been deleted, or its row is otherwise not present.


same idea, object is expired:

m1 = MyObject()
sess.add(m1)

# flush m1, also expire it
sess.commit()

# out of band DELETE, ORM has no clue
sess.execute(“DELETE FROM my_object WHERE id=:id”, {“id”: m1.id})

# row is gone, boom
print m1.foo


> 
> What i do is:
> 1 make a new object (mapped)-> obj1
> 2 add it to a session -> session1 

> 3 start another session -> session2 
> 4 do a get(id) on session2 (which should result in access to the database, 
> since i just started the session) -> obj2 
> 5 close session2
> 6 do obj1.someattr.append(obj2)
> 7 do session1.commit()
> 8 get the first ERROR above 

this description isn’t specific enough to understand the issue.  What does “add 
it to a session” mean, did you flush that session?  was an INSERT emitted?  did 
you commit the transaction?  When you attempt to use “obj2” with obj1, you mean 
you are using the detached obj2 ?   What is the “id” you’re using get() on, is 
that the “id” that you know was created in #2 ?

Basically your problem comes down to sharing rows between transactions where 
those transactions aren’t aware of the rows you’re referring to (which is due 
to transaction isolation, see 
http://en.wikipedia.org/wiki/Transaction_isolation).  When you move an object 
between sessions, you should generally use merge() which will emit a SELECT for 
that row first.



signature.asc
Description: Message signed with OpenPGP using GPGMail

[sqlalchemy] StaleDataError/ObjectDeletedError

2014-01-31 Thread lars van gemerden 
Hi, all

I am running into these 2 errors and have run out of ideas what to do about 
it (also because i don't what they mean); They seem to happen in exactly 
the same circumstances.

Traceback (most recent call last):
  File "C:\python27\lib\site-packages\bottle.py", line 781, in _handle
return route.call(**args)
  File "C:\python27\lib\site-packages\bottle.py", line 1592, in wrapper
rv = callback(*a, **ka)
  File "d:\Documents\Code\python\floware\server\webserver.py", line 202, in 
post_task
task.from_json(request.json, path = pathlist([task._id_]))
  File "C:\python27\lib\contextlib.py", line 24, in __exit__
self.gen.next()
  File "d:\Documents\Code\python\floware\models\flow\tasks.py", line 55, in 
scoped_session
session.commit()
  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 721, in 
commit
self.transaction.commit()
  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 354, in 
commit
self._prepare_impl()
  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 334, in 
_prepare_impl
self.session.flush()
  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 1818, in 
flush
self._flush(objects)
  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 1936, in 
_flush
transaction.rollback(_capture_exception=True)
  File "build\bdist.win32\egg\sqlalchemy\util\langhelpers.py", line 58, in 
__exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 1900, in 
_flush
flush_context.execute()
  File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 372, in 
execute
rec.execute(self)
  File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 525, in 
execute
uow
  File "build\bdist.win32\egg\sqlalchemy\orm\persistence.py", line 59, in 
save_obj
mapper, table, update)
  File "build\bdist.win32\egg\sqlalchemy\orm\persistence.py", line 514, in 
_emit_update_statements
(table.description, len(update), rows))
StaleDataError: UPDATE statement on table 'Company' expected to update 1 
row(s); 0 were matched.

OR

File "d:\Documents\Code\python\floware\models\flow\processes.py", line 333, 
in run
  self.execute(input, output)
File "d:\Documents\Code\python\floware\toolshed\logs.py", line 55, in 
wrapper
  f_result = func(*v, **k)
File "d:\Documents\Code\python\floware\models\flow\libraries\basic.py", 
line 159, in execute
  print "%s %s" % (self.cursor, str(i.item))
File "d:\Documents\Code\python\floware\models\data\database.py", line 282, 
in __str__
  return '{%s-> %s}' %(type(self).__name__, ', '.join(['%s: %s' % (str(k), 
repr(getattr(self, k))) for k in self._entity_]))
File 
"C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\attributes.py",
 
line 316, in __get__
File 
"C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\attributes.py",
 
line 611, in get
File 
"C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\state.py",
 
line 380, in __call__
File 
"C:\python27\lib\site-packages\sqlalchemy-0.8.3-py2.7-win32.egg\sqlalchemy\orm\loading.py",
 
line 606, in load_scalar_attributes
sqlalchemy.orm.exc.ObjectDeletedError: Instance '' 
has been deleted, or its row is otherwise not present.

What i do is:
1 make a new object (mapped)-> obj1
2 add it to a session -> session1 
3 start another session -> session2 
4 do a get(id) on session2 (which should result in access to the database, 
since i just started the session) -> obj2 
5 close session2
6 do obj1.someattr.append(obj2)
7 do session1.commit()
8 get the first ERROR above 

OR
9 close session1
10 add obj1 to another session -> session3
11 do the returnb the string from __str__: return '{%s-> %s}' 
%(type(self).__name__, ', '.join(['%s: %s' % (str(k), repr(getattr(self, 
k))) for k in self._entity_])) (see stacktrace)
12 get the second ERROR above

I think thats all that could be relevant; if i don't do steps 3-6 there is 
no problem!

Can anyone point me in the right direction of what this error might mean, 
how to find the error or how to solve the problem?

Why would the first error not occur in all cases?

Cheers, Lars

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.