Re: [sqlalchemy] attaching an AttributeExt to an existing mapper
On Mon, Feb 07, 2011 at 10:26:18AM -0500, Michael Bayer wrote: Its easier to do in 0.7 where you just say event.listen(MyObject.attribute, append, fn), any time you want.Though we haven't implemented remove yet. I'd wait for the 0.7 betas if possible. nice!, but it's not an option to migrate to 0.7 right now. I'll consider it for the future. Otherwise you can append your AttributeExtension into the listeners collection on the attribute, I'd have to check the source of attributes.py to recall the exact name of the collection. a hint here would be appreciated. thanks sandro *:-) -- 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] attaching an AttributeExt to an existing mapper
On Mon, Feb 07, 2011 at 01:32:52PM -0500, Michael Bayer wrote: Otherwise you can append your AttributeExtension into the listeners collection on the attribute, I'd have to check the source of attributes.py to recall the exact name of the collection. a hint here would be appreciated. heh, check attributes.py is a hint. The actual answer is, MyClass.someattribute.impl.extensions.append(my_extension). correct! so thanks for the actual answer! ;-) It seems that .impl only exists after I instantiated at leat 1 object, correct? class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) name = Column(String(30), nullable=False) In [1]: print User.name.impl None In [2]: User() Out[2]: __main__.User object at 0x8a9cf6c In [3]: User.name.impl Out[3]: sqlalchemy.orm.attributes.ScalarAttributeImpl object at 0x8aa00cc sandro *:-) -- 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] mapper for outerjoin: getting None objects
Hi, I have a join between 2 tables (User/Adresses, complete code below). I create a mapper as the join of the 2 classes as: m = orm.mapper(Join, User.__table__.outerjoin(Address.__table__) , properties = { 'j_id' : [Address.__table__.c.user_id, User.__table__.c.id ] } ) Now I run the query sess.query(m).all() and get: [Join sandro (san...@home.it), None] I don't really understand why one row is None, it's clearly the row that has a user w/o address, the row that I want to see (otherwise I would have just used join). I guess my problem is in the way I setup the mapper but I can't understand how I should configure it. I read http://www.sqlalchemy.org/docs/orm/mapper_config.html#mapping-a-class-against-multiple-tables but could not understand eather... any hints? thanks in advanced *:-) The complete code of my example: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, ForeignKey, text, func from sqlalchemy.types import * from sqlalchemy import orm Base = declarative_base() Base.metadata.bind = 'sqlite://' Session = orm.sessionmaker(bind=Base.metadata.bind) sess = Session() class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) name = Column(String(30), nullable=False) class Address(Base): __tablename__ = 'address' aid= Column(Integer, primary_key=True) address= Column(String(30), nullable=True) user_id= Column(ForeignKey(User.id), nullable=False) user = orm.relation(User, backref='addresses', lazy=False) Base.metadata.create_all() u = User(name='sandro') b = User(name='bianco') sess.add(u) sess.add(b) sess.commit() a = Address(address='san...@home.it', ) a.user = u sess.add(a) sess.commit() class Join(object): def __repr__(self): return Join %s (%s) % (self.name, getattr(self, 'address', None)) m = orm.mapper(Join, User.__table__.outerjoin(Address.__table__) , properties = { 'j_id' : [Address.__table__.c.user_id, User.__table__.c.id ] } ) q = sess.query(m) print q.all() -- Sandro Dentella *:-) http://www.reteisi.org Soluzioni libere per le scuole http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] .info dict on Column
On Tue, Aug 24, 2010 at 02:48:37PM -0400, Michael Bayer wrote: Info option is clearly very handy. At the moment I implemented an image field in sqlkit, (that's just a bunch of handler in the gui). In order to do that I used a type inherited with no addition, just to understand that that field is the path to an Image: class Image(String): pass clearly another alternative would be to use info={ 'image': true} or similar. Is there some caveat that would make one preferred over the other? The caveat there is that table reflection, which isn't used in your case, would produce VARCHAR and not String, or your custom Image type. It also creates a hard linkage of SQLAlchemy type objects to the behavior of your application. The info approach allows the type and your application's interpretation of a field to vary independently. ok, I switched to using .info and I honestly appreciate it. am I wrong or Columns created with orm.column_property() don't have .info attribute? Any chance to add it? sandro *:-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] CircularDependencyError on 0.6 (works on 0.5.8)
Hi again, sorry for flooding with email this week... I stumbled on the CircularDependencyError in some occasions with self referencing models. I do understand that it can be tricky to INSERT and DELETE but I'm just updating rows. I reduced my problem to the bare minimum. It works both on 0.5.8 and 0.6.3 in the test case, but as long as I use the same objects from a graphical GUI I get an error, Debugging with pdb, the problem arises on session.begin() and in that moment the situation seems to me exactly the one of the test (2 objects, one modified). I hope the error message is meaningful to you... So the situation: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, ForeignKey from sqlalchemy.types import * from sqlalchemy import orm, sql Base = declarative_base() URL = 'postgresql://localhost/scuole' Base.metadata.bind = URL Session = orm.sessionmaker() sess = Session(bind=Base.metadata.bind, expire_on_commit=False, autoflush=False, autocommit=True, ) class School(Base): __tablename__ = 'scuola_scuola' cod = Column(String(10), primary_key=True) denominazione= Column(String(120)) cod_riferimento = Column(String(10), ForeignKey(cod)) cliente = Column(Boolean, nullable=False) sedi = orm.relation('School', ) def __repr__(self): return self.cod Base.metadata.create_all() # s1 = School(cod=S1, cod_riferimento=S1, cliente=False) # d1 = School(cod=D1, cod_riferimento=S1, cliente=False) # sess.add(s1) # sess.add(d1) # sess.commit() s1 = sess.query(School).get('S1') d1 = sess.query(School).get('D1') d1.cliente = False sess.begin() sess.commit() This same peace of code (i.e.: same session with just s1, d1), run from within a GUI raises an error (only with SA 0.6.3, 0.5.8 just works). The error is: Traceback (most recent call last): File /misc/src/hg/py/sqlkit/sqlkit/widgets/mask/mask.py, line 388, in record_save_cb self.record_save(None) File /misc/src/hg/py/sqlkit/sqlkit/widgets/mask/mask.py, line 422, in record_save self.commit() File /misc/src/hg/py/sqlkit/sqlkit/widgets/common/sqlwidget.py, line 972, in commit self.session.begin() File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py, line 598, in begin self, nested=nested) File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py, line 223, in __init__ self._take_snapshot() File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py, line 271, in _take_snapshot self.session.flush() File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py, line 1346, in flush self._flush(objects) File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py, line 1427, in _flush flush_context.execute() File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py, line 291, in execute postsort_actions): File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/topological.py, line 31, in sort_as_subsets (find_cycles(tuples, allitems), _dump_edges(edges, True))) sqlalchemy.exc.CircularDependencyError: Circular dependency detected: cycles: set([SaveUpdateState(School at 0x8da616c), ProcessState(OneToManyDP(School.sedi), School at 0x8da616c, delete=False)]) all edges: [(SaveUpdateState(School at 0x8da616c), ProcessState(OneToManyDP(School.sedi), School at 0x8da616c, delete=False)), (ProcessState(OneToManyDP(School.sedi), School at 0x8da616c, delete=False), SaveUpdateState(School at 0x8da616c)), (SaveUpdateState(School at 0x8da616c), SaveUpdateState(School at 0x8da616c)), (SaveUpdateState(School at 0x8da616c), SaveUpdateState(School at 0x8da61ec)), (ProcessState(OneToManyDP(School.sedi), School at 0x8da616c, delete=False), SaveUpdateState(School at 0x8da61ec))] How can I further investigate what Is wrong from the setup of my GUI? Thanks again sandro *:-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] upgrading code with prop.backref from 0.5 - 0.6
On Sat, Aug 21, 2010 at 01:45:48PM -0400, Michael Bayer wrote: columns in a property column = prop.columns[0] props = [] for pr in mapper.iterate_properties: if isinstance(pr, properties.RelationProperty): if pr.direction.name in ('MANYTOONE',): for col in pr.local_remote_pairs[0]: # I can't use col in p.local_remote_pairs # as it uses 'col == p.local_remote_pairs' that evaluates # to a BinaryExpression if column is col: try: if pr.backref.prop.cascade.delete_orphan: props += [pr] except AttributeError, e: pass return tuple(props) This fails in sqla 0.6 as pr.backref is empty. Which is the correct way to get the properties that have a backref that have cascade with delete_orphan? why not put some info on the director_id column (i.e. Column(, info={'foo':'bar'}) ) that gives your application the information what you need in a succinct and direct way. There's no public API that links relationships to backrefs and the poking through lists of columns is hacky too. I could tell you where they're linked but it can change at any time. I wasn't aware of 'info' option. I do appreciate how easy it is to implement it this way. On the other hand the other approch didn't even need special configuration. Info option is clearly very handy. At the moment I implemented an image field in sqlkit, (that's just a bunch of handler in the gui). In order to do that I used a type inherited with no addition, just to understand that that field is the path to an Image: class Image(String): pass clearly another alternative would be to use info={ 'image': true} or similar. Is there some caveat that would make one preferred over the other? TIA sandro *:-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] session (autocommit/not autoflush) + begin dirty
Hi, I have been using session with autocommit=True and autoflush=False for a while now. I'm pretty happy with this but now I find something that I cannot understand: a session with dirty set full, after a begin() is empty, but no update is issued. How can that be possible? (And yes! session.dirty is *really* dirty, I changed a float value) In [57]: session.autocommit Out[57]: True In [58]: session.autoflush Out[58]: False In [59]: session.dirty Out[59]: IdentitySet([VALCUCINE - A008, VALCUCINE - Abcd, DESALTO OMA srl - divano, ABET Laminati - top 01]) In [60]: session.begin() 2009-12-15 09:40:32,727 INFO sqlalchemy.engine.base.Engine.0x...0e8c BEGIN 2009-12-15 09:40:32,739 INFO sqlalchemy.engine.base.Engine.0x...0e8c COMMIT Out[60]: sqlalchemy.orm.session.SessionTransaction object at 0xa0f852c In [61]: session.dirty Out[61]: IdentitySet([]) Where have the modification gone? TIA sandro *:-) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Type of Column added with column_property
Hi, is there a way to set the type of a column added to a mapper with column_property? m = mapper(New, t, properties={ 'my_bool': column_property( func.my_bool(t.c.id, type=Boolean) ) }) func 'my_bool' is a stored procedure on Postgresql and returns a boolean, but the type of the column is NullType: m.get_property('my_bool').columns[0].type NullType() -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] FlushError: instance is in unsaved, pending instance...
On Fri, Dec 04, 2009 at 02:52:37PM -0500, Michael Bayer wrote: On Dec 4, 2009, at 2:20 PM, Alessandro Dentella wrote: On Fri, Dec 04, 2009 at 01:27:46PM -0500, Michael Bayer wrote: On Dec 4, 2009, at 1:18 PM, Alessandro Dentella wrote: Is the only solution to attach an instance (u.job = myjob) or is there another solution that doesn't require me to build the instance? if you want SQLA's delete-orphan capability, that's the only way. If you want to rely upon CASCADE rules in your DB to handle it instead, that's another way to go. thanks, and really... managing in the db is such a simple thing... Is there a why to find out the related class (that one u.job should be instance of) so that I can issue a query on that? err, given what to start with ? ops. Let's say starting from the class and the instance. class Project(Base): __tablename__ = project id = Column(Integer, primary_key=True) name = Column(String(30), nullable=False) class Delivery(Base): __tablename__ = 'delivery' id= Column(Integer, primary_key=True) project_id= Column(ForeignKey(Project.id), nullable=False) project = orm.relation('Project', backref=orm.backref('deliveries', cascade=all, delete-orphan, lazy=False)) sess = session() p = Project() d = Delivery() d.project_id = p.id # now I want to create automatically the instance 'p' having just * the value 'p.id' * d * the name of the attribute (that is a ForeignKey) project_id well what you'd need here is the name project, pull that attribute off of d's mapper and figure it out from there.you see this is why SQLA doesn't know what to do with your d.project_id and why we don't get into attaching rules to foreign key identifiers. Its only a target during the flush, and a value inside a SQL expression that is populated during a lazy load. it could just as well be associated with multiple relations() attached to your Delivery class, and if the mapping is really exotic those relations could even be loading different classes based on the same column attribute - it can be in any number of primaryjoin expressions for example. So if you really only had project_id, and you want to assume its only used in one relation() on d, this is what you'd have to do: # get parent mapper mapper = object_mapper(d) # convert from attribute to actual column column = mapper.get_property('project_id').columns[0] # search through all properties for prop in mapper.iterate_properties: # search through local=remote pairs for that prop (usually just one tuple) if hasattr(prop, 'local_remote_pairs'): for local, remote in prop.local_remote_pairs: if local is column: result = remote break else: result = None if result is not None: target_class = prop.mapper.class_ # get the attribute name for the remote column. usually this # is column.key, but this is a more complete check in case # columns have been custom mapped. attr_name = prop.mapper._get_col_to_prop(result).key break else: target_class = attr_name = None if target_class: sess.query(target_class).filter_by(**{attr_name:d.project_id}) thanks. That's pretty clear and I could translate it also for the simpler case when I have 'project'. sandro *:-) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] mapper for join, insert and reused instances
Hi, docs suggests (Mapping a Class against Multiple Tables) to build a mapper as this: class AddressUser(object): pass j = join(users_table, addresses_table) mapper(AddressUser, j, properties={ 'user_id': [users_table.c.user_id, addresses_table.c.user_id] }) as a way to keep both of those columns set at the same value. That works perfectly when I want to use one mapper to present the join and to add new instances for both tables. As far as I can test it fails if I want to add a joined record composed of one existent record and another that doesn't ye exists:: new = AddressUser() new.name = 'myself' new.address = 'mys...@example.com' It correctly creates a User instance and an Address instance. But now I can't see how to add a record where the User is the already existent user 'myself' and the address is a new one. Even if the user.id is an already existent one SA will try to create a new obj (and in my real case it complains about not present not nullable fields, clearly). Is it possible to create a mapper that does that too? In the particular context I have I'd really would prefere not to split the operation in two steps (User / address). Any hints? thanks in advance sandro *:-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] FlushError: instance is in unsaved, pending instance...
Is the only solution to attach an instance (u.job = myjob) or is there another solution that doesn't require me to build the instance? if you want SQLA's delete-orphan capability, that's the only way. If you want to rely upon CASCADE rules in your DB to handle it instead, that's another way to go. thanks, and really... managing in the db is such a simple thing... Is there a why to find out the related class (that one u.job should be instance of) so that I can issue a query on that? sandro *:-) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] FlushError: instance is in unsaved, pending instance...
On Fri, Dec 04, 2009 at 01:27:46PM -0500, Michael Bayer wrote: On Dec 4, 2009, at 1:18 PM, Alessandro Dentella wrote: Is the only solution to attach an instance (u.job = myjob) or is there another solution that doesn't require me to build the instance? if you want SQLA's delete-orphan capability, that's the only way. If you want to rely upon CASCADE rules in your DB to handle it instead, that's another way to go. thanks, and really... managing in the db is such a simple thing... Is there a why to find out the related class (that one u.job should be instance of) so that I can issue a query on that? err, given what to start with ? ops. Let's say starting from the class and the instance. class Project(Base): __tablename__ = project id = Column(Integer, primary_key=True) name = Column(String(30), nullable=False) class Delivery(Base): __tablename__ = 'delivery' id= Column(Integer, primary_key=True) project_id= Column(ForeignKey(Project.id), nullable=False) project = orm.relation('Project', backref=orm.backref('deliveries', cascade=all, delete-orphan, lazy=False)) sess = session() p = Project() d = Delivery() d.project_id = p.id # now I want to create automatically the instance 'p' having just * the value 'p.id' * d * the name of the attribute (that is a ForeignKey) project_id Since I need to issue: p = sess.query(Project).get(p.id) is there a way to get Project (the class) from d and 'project_id' I hope now it's a little bit clearer sandro *:-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] FlushError: instance is in unsaved, pending instance...
Hi, I'm stuck with the code below raise FlushError complaining: Traceback (most recent call last): File stdin, line 49, in module File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py, line 673, in commit self.transaction.commit() File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py, line 378, in commit self._prepare_impl() File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py, line 362, in _prepare_impl self.session.flush() File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py, line 1356, in flush self._flush(objects) File /misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py, line 1416, in _flush mapperutil.state_str(state), path)) sqlalchemy.orm.exc.FlushError: Instance Delivery at 0x85cb82c is an unsaved, pending instance and is an orphan (is not attached to any parent 'Project' instance via that classes' 'deliveries' attribute) I don't understand what I should do to fix it. What I can't understand is why it is considered an orphan: I *do* set job_id on it and it should be enought to build the relation (I *do* need delete-orphan). Is the only solution to attach an instance (u.job = myjob) or is there another solution that doesn't require me to build the instance? Thanks in advance sandro *:-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy import datetime from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, ForeignKey, text, func from sqlalchemy.types import * from sqlalchemy import orm Base = declarative_base() Base.metadata.bind = 'sqlite://' #Base.metadata.bind = 'postgres://localhost/fossati' Session = orm.sessionmaker(bind=Base.metadata.bind) sess = Session() class Project(Base): __tablename__ = project id = Column(Integer, primary_key=True) name = Column(String(30), nullable=False) def __str__(self): return self.name class Delivery(Base): __tablename__ = 'delivery' id= Column(Integer, primary_key=True) data = Column(Date, nullable=True) job_id= Column(ForeignKey(Project.id), nullable=False) note = Column(Text) job = orm.relation(Project, backref=orm.backref('deliveries', cascade=all, delete-orphan)) # job = orm.relation(Project, backref='deliveries', lazy=False) def __repr__(self): return self.note __str__ = __repr__ Base.metadata.create_all() Base.metadata.bind.echo=True p = Project(name='test 1') sess.add(p) sess.commit() print DELIVERY u = Delivery() u.job_id = p.id # u.job = p## this way it works u.data = datetime.date(2009, 12, 3) sess.add(u) sess.commit() -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: delete-orphan assigning fk. Related to FAQ 5.13
Movie is attached to a Director or not. like the FAQ says, we choose not to get into generating events from foreign keys being set. which is not a forever rule, but if you look in trac there are about 300 ORM issues open that I'd rather get resolved before I have the time to consider the ramifications of a change like that. I didnt'm mean to push you on that. I just wanted to understand how dangerous it is to implement in my application some sort of dynamic creation of the proper object and put in the relation. My application is a general purpose one (a GUI), that makes introspection of the mapper to make assumptions on what is needed. I *do* understand all the related problems that prevents you from doing that in SA now, but working with a GUI somewhat narrows the problems (no huge number of select - just one attribute at a time, no doubt on precedence between an already existent object and a new one defined by setting a FK) and on the other hand I'd like to offer a solution (again in my app) in the situation in which the present of 'delete-orphan' would be a problem and getting rid of it is not a choice. My (temporary?) solution relays on RelationProperty.local_remote_pair (that is not present in the API documentation) to see if the ColumnProperty I set, would impact on a relation that has cascade with delete_orpahn set. Is there any better way to get the relation involved in the change of a fk or is local_remote_pair just ok? sandro -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy --~--~-~--~~~---~--~~ 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: Doubts on relation with cascade delete by backend
On Thu, Jul 23, 2009 at 02:48:12PM -0400, Michael Bayer wrote: add passive_deletes = True Thanks and sorry for the noise on such trivial question, present in the faq... , I've already used it and... forgot! sandro --~--~-~--~~~---~--~~ 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] delete-orphan assigning fk. Related to FAQ 5.13
In faq 5.13 it's explained why setting bar.foo_id would not generate the object bar.foo. I stumble in the same problem when creating a Movie in an example with Director/Movie and a relation -'movies' on director- that has 'delete-orphan'. running:: f = m.Movie(title=my title) f.director_id = 1 sess.commit() Would issue an error: Instance Movie at 0xb7dab72c is an unsaved, pending instance and is an orphan (is not attached to any parent 'Director' instance via that classes' 'movies' attribute) the reason is explained in the faq, no Director instance has been created. The problem is that I can't use the proposed solution of expiring the session since the object is not yet persisted (Instance 'Movie at 0xb7d8564c' is not persistent within this Session). Wouldn't be this a situation when an automatic loading would be possible? or at least would it be possible to trap the error as the error really only should enforce that no orpahn is left, but the foreign key (if existent) ensures that already. thanks sandro *:-) --- class Director(Base): __tablename__ = 'director' id = Column(Integer, primary_key=True) name= Column(String(60)) movies = relation('Movie', backref='director', cascade='all, delete-orphan',) class Movie(Base): __tablename__ = 'movie' id = Column(Integer, primary_key=True) title = Column(String(60), nullable=False) director_id= Column(Integer, ForeignKey('director.id'), nullable=False) --~--~-~--~~~---~--~~ 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] delete-orphan assigning fk. Related to FAQ 5.13
[Sorry for reposting, Erroneously sent to another thread.] In faq 5.13 it's explained why setting bar.foo_id would not generate the object bar.foo. I stumble in the same problem when creating a Movie in an example with Director/Movie and a relation -'movies' on director- that has 'delete-orphan'. running:: f = m.Movie(title=my title) f.director_id = 1 sess.commit() Would issue an error: Instance Movie at 0xb7dab72c is an unsaved, pending instance and is an orphan (is not attached to any parent 'Director' instance via that classes' 'movies' attribute) the reason is explained in the faq, no Director instance has been created. The problem is that I can't use the proposed solution of expiring the session since the object is not yet persisted (Instance 'Movie at 0xb7d8564c' is not persistent within this Session). Wouldn't be this a situation when an automatic loading would be possible? or at least would it be possible to trap the error as the error really only should enforce that no orpahn is left, but the foreign key (if existent) ensures that already. thanks sandro *:-) --- class Director(Base): __tablename__ = 'director' id = Column(Integer, primary_key=True) name= Column(String(60)) movies = relation('Movie', backref='director', cascade='all, delete-orphan',) class Movie(Base): __tablename__ = 'movie' id = Column(Integer, primary_key=True) title = Column(String(60), nullable=False) director_id= Column(Integer, ForeignKey('director.id'), nullable=False) --~--~-~--~~~---~--~~ 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: session.autocommit, session.begin double SessionExtension call
On Sat, May 23, 2009 at 02:43:33PM -0400, Michael Bayer wrote: On May 23, 2009, at 10:15 AM, Alessandro Dentella wrote: Hi, when from my pygtk application i commit, I really do:: if self.session.autocommit: self.session.begin() self.session.commit() I'm normally using session.autocommit = True as a mean to prevent all those 'idle in transaction' processes (that prevent me from changing the structure of the database - I use PostgreSQL). moreover I use autoflush=False to prevent flushing objects when I just need to ge more info from the database via normal 'select'. Everything seems to work nicely apart the fact that using this along with after_commit hook in SessionExtension, turns out in a double call of the hook. The first when I run session.begin() and the second when I run session.commit(). I can't see how that occurs. Can you please post a stack trace ? The following code demostrates what I mean: the after_commit hook is called twice, the first time is called after sess.begin() and the second anfter sess.commit(), the output: BEGIN Whithin SessionExtension 'after_commit' COMMIT Whithin SessionExtension 'after_commit' Thanks fo your attention sandro *:-) from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, ForeignKey, text, func from sqlalchemy.orm import sessionmaker, mapper, relation from sqlalchemy.orm.interfaces import SessionExtension from sqlalchemy.types import * Base = declarative_base() Base.metadata.bind = 'sqlite://' class SKSessionExtension(SessionExtension): def after_commit(self, session): print Whithin SessionExtension 'after_commit' Session = sessionmaker(bind=Base.metadata.bind, expire_on_commit=True, autoflush=False, autocommit=True, extension=SKSessionExtension(), ) sess = Session() class Status(Base): __tablename__ = 'ticket_status' id = Column(Integer, primary_key=True) status = Column(String(20)) Base.metadata.create_all() s = Status() s.status = test sess.add(s) print BEGIN sess.begin() print COMMIT sess.commit() --~--~-~--~~~---~--~~ 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] session.autocommit, session.begin double SessionExtension call
Hi, when from my pygtk application i commit, I really do:: if self.session.autocommit: self.session.begin() self.session.commit() I'm normally using session.autocommit = True as a mean to prevent all those 'idle in transaction' processes (that prevent me from changing the structure of the database - I use PostgreSQL). moreover I use autoflush=False to prevent flushing objects when I just need to ge more info from the database via normal 'select'. Everything seems to work nicely apart the fact that using this along with after_commit hook in SessionExtension, turns out in a double call of the hook. The first when I run session.begin() and the second when I run session.commit(). Is there a way to prevent the double call of the hook, i.e. double call of session.commit()? I tried issuing just self.begin() but that leaves the session with an open transaction (as long as I understand) and the second time I try it SA complains a transaction has already begun. Any hints on how to cope with this? sandro *:-) --~--~-~--~~~---~--~~ 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] creating objects in after_flush hook
Hi, in a sessionExtension.after_flush hook I create objects (namely todo actions depending on what people have inserted/updated). At present I create these objects in the current session, but I do understand is not clean as the flush has already occurred. It almost works, objects are really created but are left in the dirty set. I tried creating a different session from withing the hook, and committing the newly created object in that session, but the application hangs forever. Berfore digging more or sending a simple example I'd like to understand if there is a known reason why I shouln't be able to commit from within an after_flush hook, or alternatively which is the suggested way to go in these situations. Thanks *:-) --~--~-~--~~~---~--~~ 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] insert and joined mappers
Hi, how should I configure a mapper that represents a join between two tables so that inserting a new object writes the foreign key between the two in the proper way? class Table_a(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) description = Column(String(100)) class Table_b(Base): __tablename__ = 'b' idb = Column(Integer, primary_key=True) a_id = Column(ForeignKey(Table_a.id), nullable=False) a_table = Table_a.__table__ b_table = Table_b.__table__ class MyJoin(object): pass m = mapper(MyJoin, a_table.join(b_table)) j = MyJoin() j.description = 'xxx' sess.add(j) Base.metadata.bind.echo = True sess.commit() 2009-05-05 12:41:52,346 INFO sqlalchemy.engine.base.Engine.0x...7acL BEGIN 2009-05-05 12:41:52,347 INFO sqlalchemy.engine.base.Engine.0x...7acL INSERT INTO a (description) VALUES (?) 2009-05-05 12:41:52,347 INFO sqlalchemy.engine.base.Engine.0x...7acL ['xxx'] 2009-05-05 12:41:52,348 INFO sqlalchemy.engine.base.Engine.0x...7acL INSERT INTO b (a_id) VALUES (?) 2009-05-05 12:41:52,348 INFO sqlalchemy.engine.base.Engine.0x...7acL [None] 2009-05-05 12:41:52,348 INFO sqlalchemy.engine.base.Engine.0x...7acL ROLLBACK Is it possible to prepare the mapper so that a_id gets the value that the first object got as id? thanks sandro *:-) --~--~-~--~~~---~--~~ 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] puzzling outerjoin in the mapper
Hi, i'm playing with outerjoin defined in the mapper. I'm getting results different from what I expected, so that I would like to understand which is the underline logic. Where a Query w/ outerjoin SELECT has in the backend n rows and would have m rows in a simple join, I only get m rows plus one 'None' for all the others. I would have thought to get one instance for each output of the query, am I wrong? tanks in advance sandro *:-) The example: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, ForeignKey, text, func from sqlalchemy.types import * from sqlalchemy.orm import relation, sessionmaker, mapper, column_property from sqlalchemy.orm.interfaces import SessionExtension from datetime import datetime, timedelta Base = declarative_base() Base.metadata.bind = 'sqlite://' Session = sessionmaker(bind=Base.metadata.bind) sess = Session() class Entry(Base): __tablename__ = 'calendar_entry' # Todo and Events id = Column(Integer, primary_key=True) summary = Column(String(100)) dtstart = Column(DateTime(timezone=False), nullable=False, index=True) class Alarm(Base): __tablename__ = 'calendar_alarm' ida = Column(Integer, primary_key=True) trigger = Column(Interval, nullable=False) # o2m entry_id = Column(ForeignKey(Entry.id), nullable=False) entry = relation(Entry, backref='alarm', lazy=True) Base.metadata.create_all() e1 = Entry(summary=sum1, dtstart=datetime.now()) e2 = Entry(summary=sum2, dtstart=datetime.now()) a = Alarm(trigger=timedelta(days=1)) ea1 = Entry(summary=entry w/ alarm, dtstart=datetime.now(),alarm=[a]) for e in (e1, e2, ea1, a): sess.add(e) sess.commit() entry_table = Entry.__table__ alarm_table = Alarm.__table__ class MyJoin(object): def __str__(self): return %s % self.dtstart m = mapper(MyJoin, entry_table.outerjoin(alarm_table), ) q = sess.query(m) print q.count() for r in q.all(): print r --- with result -- 3 # count for the matches None??? 2009-04-19 20:34:04.188442 # the only joined entry (entry w/ alarm) --~--~-~--~~~---~--~~ 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] Attempting to flush an item of type...
Hi, I have a structure as this: fossati setup (module with attribute USER) models/cliente(User) calendar (Entry) apps/job in calendar.py: from cliente import User class Entry(Base): ... user = relation(User, secondary=calendar_entry_user, lazy=True) in apps.job I do something as: from fossati import setup from fossati.models.calendar import Entry user = session.merge(setup.USER, dont_load=True) e = Entry() e.user = [user] but when I session.commit() I get the following error, that I guess depends on the different way I can specify User as fossati.models.cliente.User or just models.cliente.User, but I can't understand how to fix it. The SA error is: Attempting to flush an item of type class 'models.cliente.User' on collection 'Entry.user', whose mapper does not inherit from that of class 'fossati.models.cliente.User' thanks sandro -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy --~--~-~--~~~---~--~~ 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] availability of related obj
Hi, I have a definition similar to this:: class Ticket(Base): __tablename__ = 'ticket' id = Column(Integer, primary_key=True) assigned_to_id = Column(ForeignKey(User.id)) assigned_to = relation(User, primaryjoin = assigned_to_id == User.id, lazy=True) If I instantiate ticket = Ticket() ticket.assigned_to_id = user.id I can commit and after that I can 'print ticket.assigned_to' Is there a way to have ticket_assigned_to available *before* committing? I'd like to have it available in after-flush phase of sessionExtension. SQLA knows how to retrieve it so I wandererd if it can be instructed to make it available on demand. thanks sandro -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy --~--~-~--~~~---~--~~ 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] session of an object
Hi, is there a way to get the session an object belongs to? I have been looking around and cannot find any function/way for that... Thanks in advance sandro *:-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy --~--~-~--~~~---~--~~ 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] getting referenced *class* from relation
Hi, I'd like to get programmatically the class to which a relation points. Suppose I have the following situation: class Project(Base): ... staff = relation(User, secondary=project_manager) manager = relation(User, secondary=project_staff) Now I want to get User class starting from Project and 'staff'. Project.__mapper__.get_property('manager')._get_target().class_ seems to do that but the leading underscore in _get_target suggest it's private, so I wandererd if that's the best way... thanks in advance sandro *:-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy --~--~-~--~~~---~--~~ 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] commit, hooks and SessionExtension
Hi, i'm adding in sqlkit library some signals around commit, and particularly in post-commit phase. As I'd like to have the possibility to see which attributes where modified from within the callback I used 'after_flush' method of SessionExtension. So I have 2 questions: 1. Trivial tests show it behaves as I want it but how confident can I be that the signal is not emitted if the commit goes wrong? Is it correct that -as flush has already occurred- it mainly depends on what has happened between the BEGIN statement and the COMMIT? 2. after_flush has a flush_context argument: what can I use it for? thanks a lot sandro *:-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy --~--~-~--~~~---~--~~ 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: puzzling setup with ForeignKey - SOLVED
On Thu, Feb 19, 2009 at 12:57:07PM +0100, Alessandro Dentella wrote: Hi, in a working setup I added a ForeignKey to table 'cliente_cliente' as follows (client_id): class Project(Base): __tablename__ = ticket_project __table_args__ = {'useexisting' : True} id = Column(Integer, primary_key=True) date_create = Column(Date(), server_default=text(CURRENT_TIMESTAMP), nullable=False) date_last_modify = Column(Date(), onupdate=func.now(), default=func.now()) status = Column(ForeignKey('ticket_status.id'), nullable=False) name = Column(String(30), nullable=False) description= Column(Text, nullable=False) date_start = Column(Date, default=func.now()) date_end = Column(Date) client_id = Column(ForeignKey(Cliente.id), nullable=True) this brakes session.query(Project) with the message I report below. Of course I *did* create new field on table in the Postgres database. Note that if I use autoload on all Tables everything works correctly, so I tend to think I made a mistake in the definition, but I already spent some hours w/o any better understanding. I'll try to reproduce the error on a simpler situation but is not that easy and I'd like to understand if the error message tells something that could address me to the solution thanks in advance sandro *:-) Traceback (most recent call last): File /home/misc/src/hg/py/fossati/fossati/job.py, line 86, in jobs_mask m.reload() File ../../sqlkit/widgets/mask/mask.py, line 67, in reload File /misc/src/hg/py/sqlkit/sqlkit/widgets/common/sqlwidget.py, line 703, in reload self.records = query.all() File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/orm/query.py, line 1007, in all return list(self) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/orm/query.py, line 1097, in __iter__ context = self._compile_context() File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/orm/query.py, line 1569, in _compile_context from_clause = sql_util.splice_joins(from_clause, eager_join, eager_join.stop_on) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/util.py, line 252, in splice_joins right.onclause = adapter.traverse(right.onclause) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/visitors.py, line 158, in traverse return replacement_traverse(obj, self.__traverse_options__, replace) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/visitors.py, line 250, in replacement_traverse obj = clone(obj) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/visitors.py, line 241, in clone newelem = replace(element) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/visitors.py, line 155, in replace e = v.replace(elem) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/util.py, line 473, in replace return self._corresponding_column(col, True) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/util.py, line 451, in _corresponding_column newcol = self.selectable.corresponding_column(col, require_embedded=require_embedded) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py, line 1834, in corresponding_column if self.c.contains_column(column): File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/util.py, line 1358, in __get__ obj.__dict__[self.__name__] = result = self.fget(obj) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py, line 1867, in _columns self._export_columns() File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py, line 1897, in _export_columns self._populate_column_collection() File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py, line 2594, in _populate_column_collection for col in self.element.columns: File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/util.py, line 1358, in __get__ obj.__dict__[self.__name__] = result = self.fget(obj) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py, line 1867, in _columns self._export_columns() File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py, line 1897, in _export_columns self._populate_column_collection() File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/sql/expression.py, line 3428, in _populate_column_collection c._make_proxy(self, name=self.use_labels and c._label or None) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/schema.py, line 743, in _make_proxy [c._init_items(f) for f in fk] File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/schema.py
[sqlalchemy] Re: (InterfaceError) connection already closed
On Sun, Feb 01, 2009 at 04:41:11PM -0500, Michael Bayer wrote: Here you go, its a psycopg2 bug. Familiarize yourself with the attached test case, then post it on the psycopg2 mailing list. Thanks a lot for you fast and valuable help, as usual. I verified that version 2.0.8 of psycopg2 does not suffer from this problem, so I installed it. Happy end. Should I want to support also the buggy version of pg, I guess I should force a reconnection to the db. How would I do that within the same session? sandro *:-) PS: I was using ubuntu/hardy that uses psycopg 2.0.6 -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy --~--~-~--~~~---~--~~ 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: (InterfaceError) connection already closed
stack trace you posted doesn't make sense to me though, as its issuing a SELECT statement but PG is raising an exception for an UPDATE / DELETE ? I've never seen that before. If you can provide a self- contained test case which reproduces that behavior we can try it out. Here is is. The behaviuor is as explained both on pg8.2 and 8.3. The error is raised only if ForeignKey has initially='DEFERRED' (or the database has that even if the SA definition does not. sandro *:-) -- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, ForeignKey, text, func from sqlalchemy.orm import relation, scoped_session, sessionmaker from sqlalchemy.types import * import sqlalchemy Base = declarative_base() Base.metadata.bind = postgres://localhost/sa4 meta = Base.metadata Session = scoped_session( sessionmaker(autocommit=False, autoflush=False, bind=meta.bind)) session = Session() class Project(Base): __tablename__ = 'ticket_project' id= Column(Integer, primary_key=True) name= Column(String(20)) class Report(Base): __tablename__ = 'timereport_report' id= Column(Integer, primary_key=True) job_id= Column('job_id', ForeignKey(Project.id, deferrable=True, initially='DEFERRED'), nullable=False) def populate(): meta.create_all() p1 = Project(name='TestPrj') session.add(p1) session.commit() p1 = session.query(Project).filter_by(name='TestPrj').one() r1= Report(job_id=p1.id) session.add(r1) session.commit() def delete(): global p1 p1 = session.query(Project).filter_by(name='TestPrj').one() session.delete(p1) try: session.commit() except Exception, e: print e session.rollback() populate() delete() print p1.name --~--~-~--~~~---~--~~ 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: (InterfaceError) connection already closed
connection already closed is a psycopg2 error indicating that the socket has been shut down. looking at your output, I see a raw execute() occuring within the string conversion of your Project object and theres a module called sqlkit/db/utils.py causing an exception throw. SQLA's rollback then fails too but probably for the same reason. since you're executing directly off of the engine there, it would seem unrelated to the state of the connection related to the session.its not apparent why the socket would be shut down, however, unless the database were restarted perhaps. sorry for not seeing that error myself, on the other hand: no postgres has never been shut down. Anyhow I got rid of that code (an on the fly __str__ builder), I go little further but get errors again. 1. prj is fetched from db by a session.query() id=18 2. I try to delete it, failing due to constraints on the db 3. session.rollback() 4. print prj.name - error 4b. session.expunge_all() session.flush() fetch a new one (id=20) -- error (reported below on id=18) So back to the question: in which state is 'prj' after failing deletion and after rolling back? Even if I 'session.expunge(prj)' I cannot refetch a new project (even a different one...) I'm really puzzled... sandro *:-) def delete_obj(obj): print ### deleting obj, repr(obj) try: session.delete(obj) print session.delete:, session.deleted session.commit() except Exception, e: print Problems..., e.message session.rollback() #session.bind.connect() q = session.query(Project) prj = q.filter_by(id=18).one() print repr(prj) delete_obj(prj) print session.deleted:, session.deleted for obj in session: print obj, hex(id(obj)), type(obj) session.expunge_all() session.flush() prj = q.filter_by(id=20).one() ## a different project ## san...@bluff:/misc/src/hg/py/fossati/fossati$ python test.py models.ticket.Project object at 0x88a4d8c ### deleting obj models.ticket.Project object at 0x88a4d8c session.delete: IdentitySet([models.ticket.Project object at 0x88a4d8c]) Problems... (OperationalError) ERROR: update or delete on table ticket_project violates foreign key constraint timereport_report_job_id_fkey on table timereport_report DETAIL: Key (id)=(18) is still referenced from table timereport_report. session.deleted: IdentitySet([]) obj 0x88a4d8c class 'models.ticket.Project' obj 0x88b6c4c class 'models.ticket.Organization' obj 0x88b61cc class 'models.cliente.User' No handlers could be found for logger sqlalchemy.pool.QueuePool.0x...cb8c Traceback (most recent call last): File test.py, line 36, in module prj = q.filter_by(id=20).one() File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/orm/query.py, line 1061, in one ret = list(self[0:2]) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/orm/query.py, line 961, in __getitem__ return list(res) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/orm/query.py, line 1101, in __iter__ return self._execute_and_instances(context) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/orm/query.py, line 1104, in _execute_and_instances result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none()) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/orm/session.py, line 755, in execute clause, params or {}) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/engine/base.py, line 824, in execute return Connection.executors[c](self, object, multiparams, params) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/engine/base.py, line 874, in _execute_clauseelement return self.__execute_context(context) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/engine/base.py, line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/engine/base.py, line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File /home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/engine/base.py, line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.OperationalError: (OperationalError) update or delete on table ticket_project violates foreign key constraint timereport_report_job_id_fkey on table timereport_report DETAIL: Key (id)=(18) is still referenced from table timereport_report. 'SELECT anon_1.ticket_project_id AS anon_1_ticket_project_id, anon_1.ticket_project_date_create AS anon_1_ticket_project_date_create, anon_1.ticket_project_date_last_modify AS anon_1_ticket_project_date_last_modify,
[sqlalchemy] Re: Autoloading float field from sqlite
On Wed, Jan 07, 2009 at 01:12:48PM -0500, Michael Trier wrote: Hi On Wed, Jan 7, 2009 at 9:35 AM, sandro dentella san...@e-den.it wrote: I realize now that autoloading a float field in Sqlite returns a SLNumeric rather that Float. The schema is: is this a known issue? It is now. :) If you have a chance would you create a ticket for this so we make sure we get it corrected. done! http://www.sqlalchemy.org/trac/ticket/1273 sandro *:-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy --~--~-~--~~~---~--~~ 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: query doctest
in another place comparing str fails just because a join has ON join condition inverted (but semantically equivalent). What's the correct way to test if two queries are semantically equivalent? Thanks sandro *:-) Hi sandro, did you want to compare compiled qry or its result? I wanted to compare queries, not the result. I don't even have the data in that doctest, and I think that adding data and looking at the result would open to many possible errors. As I implemented an alternative syntax around the query.filter syntax I want to be sure that that's eqivalent. sandro *:-) -- Sandro Dentella *:-) e-mail: [EMAIL PROTECTED] http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: flush session.is_modified()
On Tue, Nov 18, 2008 at 10:27:32AM -0500, Michael Bayer wrote: is_modified() shouldn't trigger a flush. is that with rc4 ? no it was svn rev. 4935. It's still the same with rev. 5311. I verified again: autoflush=False, just works. Autoflush=True shows that ## here session.new is not empty if self.session.dirty: for dirty in self.session.dirty: if self.session.is_modified(dirty): return True ## session.dirty is empty Do you need more details? sandro *:-) -- http://sqlkit.argolinux.org PyGTK + python + sqlalchemy --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: flush session.is_modified()
On Tue, Nov 18, 2008 at 11:31:38AM -0500, Michael Bayer wrote: is_modified() has a flag passive which when set to True, disables lazy loaders from executing. As it turns out the flag was also not hooked up so you'd have to use the latest trunk to get this. the reason it wants to lazily load things is because the modified calculation compares the current value to that which is present in the database, so when things are not loaded it can't make that determination. perfect! thanks a lot sandro *;-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ClauseList with join?
On Sat, Oct 11, 2008 at 11:19:31PM -0400, Michael Bayer wrote: On Oct 11, 2008, at 1:44 PM, sandro dentella wrote: Hi, I started using the .join() method on query and that' s really powerful, with reset_joinpoint and the list of attributes setting the path of relations. Now I'd like to being able to write join clause in advance with respect to the moment I have the the query available , in he same way I can write ClauseList in advance. Is there any way? this sounds like you mean..j = [SomeClass.someprop, SomeOtherClass.someotheroprop] .sess.query(SomeClass).join(*j) ? No. But what i wanted is really probably un-viable and anyhow I found a different way to do it. I'll explain anyhow. .filter() acts on a query that may have been composed with some .join() so that I'd like to see it as a single operation on query after wich I issue a .reset_joinpoint(). Since I have a GUI that allows to add many different filters in this way, I wanted to consider each 'join + filter + reset' as a unit to be applied to the original query. I mistakenly thought that ClauseList was sort of such a unit while this is just an argument to .filter() Now I just exposed the qyery object to each filter widget that applies directly the join+filter+reset. Thanks sandro *:-) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: classes and mapper
Yep: m = sqlalchemy.orm.class_mapper(User) Thanks a lot! *:-) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---