[sqlalchemy] Inactive transactions and commit/rollback from Session
Hi - I'm using SA in conjunction w/ Pylons, in their default (0.9.7) approach -- i.e. using scoped session classes created by calling something like: sm = orm.sessionmaker(autoflush=True, autocommit=False, bind=engine) meta.Session = orm.scoped_session(sm) I have a base controller that is designed to handle any session cleanup -- i.e. do any pending commits and rollback and then remove the session. This is mostly working; however, I'm getting errors about The transaction is invalid when attempting to perform commits. I'm not sure that this is actually resulting in an error -- and while I think a refactor is in order here anyway, I would like to better understand what I'm doing wrong. :) Here's my base controller: class CommitDisposeBaseController(WSGIController): A base controller class that automatically commits any outstanding SA changes, closes (removes) the SA Session, and disposes of the engine (or pool, if applicable). def __call__(self, environ, start_response): try: result = WSGIController.__call__(self, environ, start_response) if meta.Session.is_active: meta.Session.commit() return result except Exception, e: if meta.Session.is_active: meta.Session.rollback() _log.exception(e) raise finally: meta.Session.remove() meta.engine.dispose() The errors (The transaction is inactive.) are coming from my meta.Session.commit() line. Two questions: 1) Isn't this what meta.Session.is_active should be testing for? 2) Is there something special about using these methods as class methods instead of instance methods? My assumption is that this transparently grabs the current/active Session instance and executes on that. I do know, though, that some methods such as remove() are not available on instances -- only on the class. This is a bit confusing. Any help would be appreciated. Thanks, Hans --~--~-~--~~~---~--~~ 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] Understanding CircularDependencyError
Hi everyone! I'm using the Declarative plugin to generate/handle a DB of about 50 entities, with every kind of relation between them. I've just added a simple one-to-many relation, and now SA is complaining that: sqlalchemy.exc.CircularDependencyError: Circular dependency detected [(base_files, aut_trasporto), (base_files, aut_stoccaggio), (aut_stoccaggio, jun_cer_abilitati_aut_stoccaggi), (aut_stoccaggio, conf), (aut_stoccaggio, formulari), (targhe, jun_cer_abilitati_targhe), (targhe, formulari), (formulari_dett, fatture_dett), (formulari_dett, ddt_dett), (aut_trasporto, formulari), (aut_trasporto, conf), (aut_trasporto, targhe), (ddt_dett, fatture_dett), (formulari, formulari_dett), (formulari, base_files)][] But I can't understand how the circle comes into play, since the new triggering relation has as many side an entity with just plain attributes (Unicode/Integer/DateTime).. So my question: what is the list of table names that follows CircularDependencyError? Should I use it to find out where the circular dependency is? Thanks for your 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Understanding CircularDependencyError
It looks like I should tinker with the use_alter parameter.. :) --~--~-~--~~~---~--~~ 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] Re: Identical column names in parent and child classes with joined-table inheritance
We have tried suggested, but: field 'modified' exists in both parent and child tables, when we redefined property 'modified' in mapper with something like this: mapper(Child, child_table, properties={'modified' = child_table.c.modified, ...}), modified field still returned value from parent's table. We also tried to add property to class like this: class Parent(object) : table = None ... def _fieldFromQuery(self, field): if not self.table: return None return Session.object_session(self).execute(select([getattr (self.table.c, field)]).where(self.table.c.id==self.id)).scalar() def _modified(self): return self._fieldFromQuery(modified) def _modified_by(self): return self._fieldFromQuery(modified_by) modified = property(_modified) modified_by = property(_modified_by) where self.table is assigned just before mapper is created. Mapper itself again redefined attribute 'modified' and 'modified_by' so we were back to starting problem. On Oct 29, 3:56 pm, Michael Bayer mike...@zzzcomputing.com wrote: bojanb wrote: Hi, Can I have identical column names in both parent and child classes that are part of a joined-table inheritance? These are simply created, created_by, modified, modified_by columns that are populated by defaults defined for them (ie. default, server_default, onupdate). The values are written to the database correctly, but I have a problem reading them because parent's column values override child values. So, if the parent has null values for modified, modified_by and the child some actual values, ORM returns None for child.modified and child.modified_by. use the properties dictionary to mapper to redefine the names. or the declarative equivalent. seehttp://www.sqlalchemy.org/docs/05/mappers.html#customizing-column-pro... Suggestions? --~--~-~--~~~---~--~~ 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] Re: Inactive transactions and commit/rollback from Session
Hans Lellelid wrote: The errors (The transaction is inactive.) are coming from my meta.Session.commit() line. Two questions: 1) Isn't this what meta.Session.is_active should be testing for? it is. I'm not familiar with a codepath which can cause that to happen, and in fact even if the session isn't active, it still should be totally fine to call commit(). Try this as an example: from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine(mysql://scott:ti...@localhost/test, echo=True) s = scoped_session(sessionmaker(bind=engine)) s.commit() s.rollback() s.commit() s.commit() s.rollback() s.rollback() s.rollback() s.commit() s.commit() It seems like some unhandled exception on the connection itself might have occurred, i.e. after acquiring it via session.connection(), and the session wasn't notified of this failure (i.e. via rollback()). I don't ever use is_active myself, its something we put there for the benefit of TurboGears. To my mind its really not needed for anything. My recommendation would be to not call is_active() and to only call commit() within controller methods as needed. you also can call rollback() any number of times so the is_active() isnt needed there. 2) Is there something special about using these methods as class methods instead of instance methods? My assumption is that this transparently grabs the current/active Session instance and executes on that. this is the expected usage if the session is a scoped_session() which is the case in a pylons setup. I do know, though, that some methods such as remove() are not available on instances -- only on the class. This is a bit confusing. remove() is a method on scoped_session() which is not a session. It is a thread local container object with proxying behavior. If it makes it less confusing, you can use scoped_session like this: # create session registry my_scoped_session = scoped_session(sessionmaker()) # acquire the session from the registry session = my_scoped_session() # use the session session.commit() # remove session from the current scope my_scoped_session.remove() After using the above pattern for about five minutes, you'll probably see the utility of scoped_session() proxying common methods to the underlying session. --~--~-~--~~~---~--~~ 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] Re: Identical column names in parent and child classes with joined-table inheritance
xaotuk wrote: We have tried suggested, but: field 'modified' exists in both parent and child tables, when we redefined property 'modified' in mapper with something like this: mapper(Child, child_table, properties={'modified' = child_table.c.modified, ...}), modified field still returned value from parent's table. here is an example illustrating how to move modified away as an attribute name within each mapped class, allowing them to be accessible separately despite the tables having the same column names. If you want to set the parent's modified column on the child, use the parent_modified attribute: from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine(sqlite://, echo=True) m = MetaData() parent = Table('parent', m, Column('id', Integer, primary_key=True), Column('type', String), Column('modified', String) ) child = Table('child', m, Column('id', Integer, ForeignKey('parent.id'), primary_key=True), Column('modified', String) ) m.create_all(engine) class Parent(object): def __init__(self, modified): self.modified = modified def modified(self): return self.parent_modified def _set_modified(self, m): self.parent_modified = m modified = property(modified, _set_modified) class Child(Parent): def modified(self): return self.child_modified def _set_modified(self, m): self.child_modified = m modified = property(modified, _set_modified) mapper(Parent, parent, polymorphic_on=parent.c.type, polymorphic_identity='parent', properties={ 'parent_modified':parent.c.modified }) mapper(Child, child, inherits=Parent, polymorphic_identity='child', properties={ 'child_modified':child.c.modified }) s = sessionmaker(engine)() p1 = Parent('p1') c1 = Child('c1') assert p1.modified == 'p1' assert c1.modified == 'c1' s.add_all([p1, c1]) s.commit() assert p1.modified == 'p1' assert c1.modified == 'c1' We also tried to add property to class like this: class Parent(object) : table = None ... def _fieldFromQuery(self, field): if not self.table: return None return Session.object_session(self).execute(select([getattr (self.table.c, field)]).where(self.table.c.id==self.id)).scalar() def _modified(self): return self._fieldFromQuery(modified) def _modified_by(self): return self._fieldFromQuery(modified_by) modified = property(_modified) modified_by = property(_modified_by) where self.table is assigned just before mapper is created. Mapper itself again redefined attribute 'modified' and 'modified_by' so we were back to starting problem. On Oct 29, 3:56 pm, Michael Bayer mike...@zzzcomputing.com wrote: bojanb wrote: Hi, Can I have identical column names in both parent and child classes that are part of a joined-table inheritance? These are simply created, created_by, modified, modified_by columns that are populated by defaults defined for them (ie. default, server_default, onupdate). The values are written to the database correctly, but I have a problem reading them because parent's column values override child values. So, if the parent has null values for modified, modified_by and the child some actual values, ORM returns None for child.modified and child.modified_by. use the properties dictionary to mapper to redefine the names. or the declarative equivalent. seehttp://www.sqlalchemy.org/docs/05/mappers.html#customizing-column-pro... Suggestions? --~--~-~--~~~---~--~~ 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] Re: Inactive transactions and commit/rollback from Session
Hi Michael - Thanks for the response! 1) Isn't this what meta.Session.is_active should be testing for? it is. I'm not familiar with a codepath which can cause that to happen, and in fact even if the session isn't active, it still should be totally fine to call commit(). Try this as an example: snip Ok -- I'm glad that this is the case; this does conform with what I'd seen in other cases in the past. Indeed -- I also noticed that is_active seemed to be unnecessary. It seems like some unhandled exception on the connection itself might have occurred, i.e. after acquiring it via session.connection(), and the session wasn't notified of this failure (i.e. via rollback()). Thank you; that's a helpful direction. Indeed in this particular case, I had passed the exception (from session.connection()) into a class which performed a number of operations. While that was supposed to be an atomic unit, perhaps an exception is not being properly handled in there. I do know, though, that some methods such as remove() are not available on instances -- only on the class. This is a bit confusing. remove() is a method on scoped_session() which is not a session. It is a thread local container object with proxying behavior. If it makes it less confusing, you can use scoped_session like this: # create session registry my_scoped_session = scoped_session(sessionmaker()) # acquire the session from the registry session = my_scoped_session() # use the session session.commit() # remove session from the current scope my_scoped_session.remove() Yeah, actually, I think that is a clearer convention. Since we typically fetch instances and work with them (rather than using meta.Session.query(), for example), I think I will adopt this naming convention. Thanks again! Hans --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---