Re: [sqlalchemy] Query about exception being raised on violating uniqueness constraint
I'm not familiar with that codebase (though I know, its the openstack base, maybe I should be), so it's hard to say. The begin() method on Session is usually not used because the docs recommend the Session remain in autocommit=False mode, so I'd need to better understand the overarching patterns of Session use here. Overall, there should be a consistent usage pattern for Session here - there shouldn't be any ad-hoc let's use begin() here, but not there type of thing. Using the session in autocommit mode without any transaction is strongly discouraged as it leads to confusion like this. Anyway, the original question would be easier to answer if you'd send me the stack trace illustrating this TypeError you refer to. On Jan 8, 2013, at 5:40 AM, Gurjar, Unmesh wrote: Hi, I wrote (the attached) script to reproduce the issue, however, it seems to be working fine (i.e it returns the right exception). After a detailed check of my application source (https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/api.py#L1981 ), I found that adding a session.begin( ) call before calling the method save( ) (which internally does a session.add( ) and session.flush( )), resolves the issue. Does this fix look correct? Thanks Regards, Unmesh Gurjar | Lead Engineer | NTT DATA Global Technology Services Private Limited | w. +91.20.6604.1500 x 379 | m. +91.982.324.7631 | unmesh.gur...@nttdata.com | Learn more atnttdata.com/americas From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Friday, January 04, 2013 10:12 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Query about exception being raised on violating uniqueness constraint Would need much more detail here including a full stack trace, what line you're referring to in _flush(), and preferably code which reproduces the error you're seeing. On Jan 4, 2013, at 1:55 AM, Gurjar, Unmesh wrote: Hi, I have installed SQLAlchemy 0.7.9 (backend – MySQL, Python 2.7). I have a defined a table having a uniqueness constraint. On inserting a record which violates this constraint, my application gets a ‘TypeError’ exception instead of ‘IntegrityError’. After debugging the issue, I found that it can be resolved by replacing the 'raise' statement by 'raise exc' in _flush( ) method of sqlalchemy/orm/session.py. Can someone please confirm if this should be the expected behavior? Thanks Regards, Unmesh Gurjar. __ Disclaimer:This email and any attachments are sent in strictest confidence for the sole use of the addressee and may contain legally privileged, confidential, and proprietary data. If you are not the intended recipient, please advise the sender by replying promptly to this email and then delete and destroy this email and any attachments without any further use, copying or forwarding -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. __ Disclaimer:This email and any attachments are sent in strictest confidence for the sole use of the addressee and may contain legally privileged, confidential, and proprietary data. If you are not the intended recipient, please advise the sender by replying promptly to this email and then delete and destroy this email and any attachments without any further use, copying or forwarding -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. integrity_error_test.py -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Strange behaviour while trying to swap related records, or am I doing it wrong?
Thanks for the detailed explanation and the workarounds, works nicely. 2013/1/8 Michael Bayer mike...@zzzcomputing.com assuming you can try 0.8 which provides inspect(), this will show what is happening: from sqlalchemy import inspect print p.children[2] = c2 print p.children print inspect(c2).attrs.parent.history print inspect(c3).attrs.parent.history print p.children[1] = c3 print p.children print inspect(c2).attrs.parent.history print inspect(c3).attrs.parent.history print we see: [Mary, John, John] History(added=(), unchanged=[Thomas], deleted=()) History(added=[None], unchanged=(), deleted=[Thomas]) [Mary, Kenny, John] History(added=[None], unchanged=(), deleted=[Thomas]) History(added=(), unchanged=[Thomas], deleted=()) basically, somecollection[n] = someobject will also fire a backref event. The object already at somecollection[n] fires a replace event, which sends a remove to the backref at somecollection[n]. The confusion arises because p.children[2] = c2 means that c2 is now present in the list twice, which is not a condition the collection mechanics support. The assignment back of c3 leads the system to believe that c2 is being removed from the collection, hence the backref sets c2.parent to None, yet c2 is in the list twice so it isn't actually being removed. It's the backref event being fired off inappropriately that's the core of the issue. Tracking this count like the ticket states would add an expensive id() + dictionary storage for all objects in all collections everywhere, for a pretty infrequent use case, hitting us both in memory and time spent. It's a bad situation. i can see having this as perhaps an option on relationship(), track_dupes=True, we'd have to recommend it in conjunction with ordering_list. Some ways to work around include, assigning a slice, so that both items are removed first: p.children[1:2] = [c2, c1] or just doing the second set event again: p.children[1], p.children[2] = p.children[2], p.children[1] p.children[1] = p.children[1] I'll add these workarounds to the ticket. On Jan 7, 2013, at 9:09 PM, Alexey Vihorev wrote: Tried to replace this with this, but results are the same ** ** temp1 = p.children[1] temp2 = p.children[2] ** ** p.children[2] = temp1 p.children[1] = temp2 ** ** *From:* sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] *On Behalf Of *Michael Bayer *Sent:* Tuesday, January 08, 2013 2:23 AM *To:* sqlalchemy@googlegroups.com *Subject:* Re: [sqlalchemy] Strange behaviour while trying to swap related records, or am I doing it wrong? ** ** ** ** On Jan 7, 2013, at 7:05 PM, Alexey Vihorev wrote: p.children[1], p.children[2] = p.children[2], p.children[1] *print*(p.children) *#prints [Mary, Kenny, John]* ** ** yeah, without looking too deeply I'm fairly certain this is this trac ticket: ** ** http://www.sqlalchemy.org/trac/ticket/1103 ** ** ** ** basically would add a good chunk of complexity and overhead to the list instrumentation. this is a blue sky ticket for that reason. For now you'd need to just assign to an intermediary variable and do one assignment at a time. ** ** ** ** ** ** -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- Алексей Вихорев -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to handle 'sub-commits'?
On Jan 8, 2013, at 4:00 PM, Ken Lareau wrote: Given this and your previous comments, and after some conversations with a coworker, I decided to expose the 'engine' variable from the database library and create a context manager as so: @contextlib.contextmanager def isolated_transaction(NewSession=None): Manage a new transaction (session) within an existing session needed_session = True existing = Session.registry() if NewSession is None: NewSession = scoped_session(sessionmaker(bind=engine)) else: needed_session = False Session.registry.set(NewSession) try: yield finally: Session.registry.set(existing) if needed_session: NewSession.close() (This code hasn't quite been tested yet and may need some tweaking) With this, whenever I need an 'isolated' transaction I can simply do from my program: with isolated_transaction(): do stuff with Session And things should 'just work'... at least that's my hope! I can also pass an existing new session if I want to use one in multiple places (not sure I'll need that just yet, but it was easy enough to write into the method, so...) Hopefully this seems like a sane solution to my problem. :) its good, though you can save yourself all the trouble with scoped_session/sessionmaker by just saying this: from sqlalchemy.orm import Session new_session = Session(bind=engine) or which might be even more portable, use the sessionmaker from your existing registry: new_session = Session.session_factory() session_factory is the sessionmaker() you stuck onto scoped_session in the first place. In that case you don't need to refer to the Engine explicitly. Depending on the database in use, using low isolation levels can have the effect that other transactions can view dirty reads as the transaction proceeds, but this is obviously an all-or-nothing thing.When I need certain resources exposed during a long running transaction, I transfer that data to a different Session and commit() those changes distinctly. My current needs would tend to use the short transactions for things that are mostly isolated from anything going on in the longer running (main) transaction, though I do suspect I might need what you mention in your last sentence, but might you be able to refer me to an example of how it would work, perchance? I'd advise against going this route, you'd pretty much need to use MySQL MyISAM tables to get guaranteed dirty reads, that is, there's no transaction at all, and it's not really how transactions were meant to be used.Lowering the isolation level is usually just a means to get more transaction throughput. Okay, I suspect I misunderstood what was being mentioned here, which is no problem; I'll avoid it. :) Thanks once again for all the help. -Ken -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- - Ken Lareau -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to handle 'sub-commits'?
On Tue, Jan 8, 2013 at 2:16 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 8, 2013, at 4:00 PM, Ken Lareau wrote: Given this and your previous comments, and after some conversations with a coworker, I decided to expose the 'engine' variable from the database library and create a context manager as so: @contextlib.contextmanager def isolated_transaction(NewSession=None): Manage a new transaction (session) within an existing session needed_session = True existing = Session.registry() if NewSession is None: NewSession = scoped_session(sessionmaker(bind=engine)) else: needed_session = False Session.registry.set(NewSession) try: yield finally: Session.registry.set(existing) if needed_session: NewSession.close() (This code hasn't quite been tested yet and may need some tweaking) With this, whenever I need an 'isolated' transaction I can simply do from my program: with isolated_transaction(): do stuff with Session And things should 'just work'... at least that's my hope! I can also pass an existing new session if I want to use one in multiple places (not sure I'll need that just yet, but it was easy enough to write into the method, so...) Hopefully this seems like a sane solution to my problem. :) its good, though you can save yourself all the trouble with scoped_session/sessionmaker by just saying this: from sqlalchemy.orm import Session new_session = Session(bind=engine) or which might be even more portable, use the sessionmaker from your existing registry: new_session = Session.session_factory() session_factory is the sessionmaker() you stuck onto scoped_session in the first place. In that case you don't need to refer to the Engine explicitly. Nice, the second one definitely works and I no longer need to export 'engine' and only need to import Session from my library where I have the context manager place. :) Thanks again! - Ken Depending on the database in use, using low isolation levels can have the effect that other transactions can view dirty reads as the transaction proceeds, but this is obviously an all-or-nothing thing. When I need certain resources exposed during a long running transaction, I transfer that data to a different Session and commit() those changes distinctly. My current needs would tend to use the short transactions for things that are mostly isolated from anything going on in the longer running (main) transaction, though I do suspect I might need what you mention in your last sentence, but might you be able to refer me to an example of how it would work, perchance? I'd advise against going this route, you'd pretty much need to use MySQL MyISAM tables to get guaranteed dirty reads, that is, there's no transaction at all, and it's not really how transactions were meant to be used.Lowering the isolation level is usually just a means to get more transaction throughput. Okay, I suspect I misunderstood what was being mentioned here, which is no problem; I'll avoid it. :) Thanks once again for all the help. -Ken -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- - Ken Lareau -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- - Ken Lareau -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] events: Which attributes did change (after_commit)?
Hi, my question is how/if I can detect which attributes were changed when I get the 'after_commit' event. (I think it isn't possible based on what I read in the docs but I'd like to be sure.) If not, which other event could I use? What I want to achieve: I want to update an external search index incrementally after a commit. Ideally I can check if one of the attributes changed which are relevant for searching. fs -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] events: Which attributes did change (after_commit)?
Hi, my question is how/if I can detect which attributes were changed when I get the 'after_commit' event. (I think it isn't possible based on what I read in the docs but I'd like to be sure.) If not, which other event could I use? What I want to achieve: I want to update an external search index incrementally after a commit. Ideally I can check if one of the attributes changed which are relevant for searching. fs -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Initiate re-numeration of related records using sqlalchemy.ext.orderinglist
Hi all!
Apparently, a relation declared using sqlalchemy.ext.orderinglist does not
track sorting:
1. class Parent(Base):
.
2. name = Column(Text)
3. children = relationship('Child', backref='parent',
4. collection_class=ordering_list('position', 1),
5. order_by='Child.position'
6. )
p = Parent(name='Thomas')
session.add(p)
lst = p.children
lst.append(Child(name=Mary))
lst.append(Child(name=John))
lst.append(Child(name=Kenny))
session.commit()
p = session.query(Parent).get(1)
print(p.children) #prints [Mary, position:1, John, position:2, Kenny,
position:3]
p.children.sort(key=attrgetter('name'))
print(p.children) #prints [John, position:2, Kenny, position:3, Mary,
position:1]
session.commit() does not help the matters either. So, how do I initiate
re-numeration manually (except for iterating records and assigning them
numbers)?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
