Re: [sqlalchemy] Looks like there is no event to catch "before" a rollback happens
On Fri, Mar 10, 2017 at 3:40 PM, mike bayer wrote: > If this is truly, "unexpected error but we need to do things", perhaps you > can use before_flush() to memoize the details you need for a restore inside > of session.info. > > An event hook can be added but it would need to be carefully considered > what the specific use case for this hook is. For example I'm not sure > "before_rollback()" is really what this should be, it likely should be "on > flush exception" similar to how engine does it. > My specific need is related to https://github.com/amol-/depot/issues/36 DEPOT allows loading files associated to database data. In case of a rollback DEPOT deletes the files that got uploaded. That works in case of `.flush()` + `.rollback()` because it gathers the history of the entity and the changed files in `before_flush`, but if a rollback is issued without a flush it currently lacks an event from which it can get the state of the objects and their history before the rollback. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Looks like there is no event to catch "before" a rollback happens
I have been looking for a way to know what's going to be rolled back in SQLAlchemy so that I can know what was changed and restore other database unrelated things to their previous state. By http://docs.sqlalchemy.org/en/latest/orm/events.html#session-events it looks like it's available an after_soft_rollback event, but in that even the objects already got rolled back and so their history is gone. In the most common scenario users do Session.flush() and then Session.rollback(), and in that case I have before_flush that can tell me everything that changed (than I can restore the state of related things into after_soft_rollback which can benefit from the knowledge that I gathered in before_flush) but if the user does a direct Session.rollback() without a flush I couldn't find an event I could attach to know what changed an so what is going to be rolled back. Not sure if that can be achieved or a new event would be needed. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Lazy ForeignKey declaration
Looks good, just one curiosity, is there a reason for using "after_parent_attach" event instead of directly using _set_parent to register the LazyForeignKey for being resolved? 2014/1/13 Michael Bayer > > On Jan 13, 2014, at 1:19 PM, Alessandro Molina < > alessandro.mol...@gmail.com> wrote: > > Just noticed that SQLAlchemy 0.9 broke a LazyForeignKey class that was > provided by TurboGears tgext.pluggable to make possible to declare foreign > keys to models not yet defined at the time the ForeignKey was declared. > > The main use was something like: > > class User(DeclarativeBase): > __tablename__ = 'users' > > registration_id = Column(Integer, LazyForeignKey(lambda: > app_model.Registration.uid)) > > where app_model.Registration was available only at runtime and not when > User class was declared. > Also app_model.Registration table name was not known (as it dynamically > generated to avoid collision between tables of multiple apps plugged at > runtime), so it was not possible to declare the foreign key as a string > with the "tablename.column_name" syntax. > > LazyForeignKey was simply implemented as a subclass of ForeignKey which > provided a custom _colspec property which would resolve the given function > whenever it was first accessed: > https://github.com/TurboGears/tgext.pluggable/blob/master/tgext/pluggable/sqla/relations.py#L4 > > > This approach doesn't work anymore in SQLA 0.9 as _colspec gets accessed > by the ForeignKey constructor itself, calling the lambda at Column > declaration time. > > It seems to me that adapting the LazyForeignKey class to SQLA 0.9 would > require messing a lot more with SQLAlchemy internal code, which is > something I would like to avoid. > > What would be the suggested way to achieve the same feature on SQLA 0.9? > > > Well if the table name were known, then you wouldn’t need such a feature > in the first place as you can just put a string name in ForeignKey(). > > However, as this seems to be a case where a table + declared class is to > eventually refer to some other Table whose name is not known yet, the > ForeignKey() should not exist on the Column at all until that other Table > is available.A simple call to > User.__table__.append_constraint(ForeignKeyConstraint( put here>)) at the time this information is known would be the cleanest. > > to get at this you’d need to use events. First a way to make an XYZ() > object that can be placed inside of Column and know about it: > > > from sqlalchemy.schema import SchemaItem > from sqlalchemy import event > > class MyThing(SchemaItem): > def _set_parent(self, parent): > pass > > @event.listens_for(MyThing, "after_parent_attach") > def my_thing_attached(target, parent): > print("Attaching target %s to parent %s", target, parent) > > from sqlalchemy import Table, MetaData, Column, Integer > > Table('t', MetaData(), Column('x', Integer, MyThing())) > > Then you want to register your LazyForeignKey object in a registry that > will allow the callable to be invoked once your “table setup” system has > completed - you’d call the callable, construct a ForeignKeyConstraint, then > append it to the constraints of the parent column.table. > > the only private API in use here is the _set_parent() override. If I > change that API it would be something simple. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Lazy ForeignKey declaration
Just noticed that SQLAlchemy 0.9 broke a LazyForeignKey class that was provided by TurboGears tgext.pluggable to make possible to declare foreign keys to models not yet defined at the time the ForeignKey was declared. The main use was something like: class User(DeclarativeBase): __tablename__ = 'users' registration_id = Column(Integer, LazyForeignKey(lambda: app_model.Registration.uid)) where app_model.Registration was available only at runtime and not when User class was declared. Also app_model.Registration table name was not known (as it dynamically generated to avoid collision between tables of multiple apps plugged at runtime), so it was not possible to declare the foreign key as a string with the "tablename.column_name" syntax. LazyForeignKey was simply implemented as a subclass of ForeignKey which provided a custom _colspec property which would resolve the given function whenever it was first accessed: https://github.com/TurboGears/tgext.pluggable/blob/master/tgext/pluggable/sqla/relations.py#L4 This approach doesn't work anymore in SQLA 0.9 as _colspec gets accessed by the ForeignKey constructor itself, calling the lambda at Column declaration time. It seems to me that adapting the LazyForeignKey class to SQLA 0.9 would require messing a lot more with SQLAlchemy internal code, which is something I would like to avoid. What would be the suggested way to achieve the same feature on SQLA 0.9? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] subquery as column
Yes,I know, so simple... but it didn't work for me as far I didn't set the "label" for the internal select. I try it many times, but always without it; I didn't know it was mandatory. Thank you. > first_id_row = s.query(Row.id_row).\ > filter(Row.id_head == Head.id_head).\ > order_by(Row.id_row).\ > limit(1).label("first_id_row") > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/sQWv1yGoC8gJ. 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] subquery as column
I have a very simple case: two mapped classes, Head and Row, linked with the "id_head" id column. This is the primary key for the Head, while "id_row" is the primary key for the Row table. I'm not able to create the following subqueries: select HEAD.id_head, (select ROW.column_bla_bla from ROW where ROW.id_head=HEAD.id_head order by ROW.id_row limit 0,1) as a_column_from_row, (select ROW.id_row from ROW where ROW.id_head=HEAD.id_head order by ROW.id_row limit 0,1) as first_id_row from HEAD The subqueries are inside the selected columns. I'm using the last sqlalchemy version with MySql. Thanks for your help Alessandro PS: I can get the same result with a different subquery, but I don't like it because it seems to me more complex: a subquery get the max_row_id and the min_row_id for each head_id, then I join it with HEAD, ROW as ROW_A and ROW as ROW_B and I get the columns I want. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/VPIZtHYZzOoJ. 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 query against more databases
Hi all, I'm trying to use session to execute a query against two databases; is it possibile? Ex. sql: select db1.table1.col1, db2.table2.col2 from db1.table1 inner join db2.table2 on db1.table1.key = db2.table2.key With sessions: session.query(Table1).join(Table2, Table1.key==Table2.key).all() On internet I found old answers that say it is not possible, but it was the far 2007.. :-) Alessandro -- 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]: 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 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.
[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: [, 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 "" % (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.
[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(), ProcessState(OneToManyDP(School.sedi), , delete=False)]) all edges: [(SaveUpdateState(), ProcessState(OneToManyDP(School.sedi), , delete=False)), (ProcessState(OneToManyDP(School.sedi), , delete=False), SaveUpdateState()), (SaveUpdateState(), SaveUpdateState()), (SaveUpdateState(), SaveUpdateState()), (ProcessState(OneToManyDP(School.sedi), , delete=False), SaveUpdateState())] 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] .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.
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] 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] Re: session (autocommit/not autoflush) + begin & dirty
On Tue, Dec 15, 2009 at 05:34:16AM -0800, Nebur wrote: > If you like, I'd run your test in my environment (Py2.5,MySQL or > SQLite). Can you mail it or put it to something like pastebin ? thanks Nebur. It'll take quite a long time to isolate it. It's a test setup, it's not a test snipper thought But I don't think I have alternatives to solve it... 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] Re: session (autocommit/not autoflush) + begin & dirty
On Tue, Dec 15, 2009 at 04:56:39AM -0800, Nebur wrote: > Are you sure ? Did you check that really nothing happened in the DB ? > (Sorry for the distrust ;-) I'd expect myself to miss such a check > sometimes, e.g. when it's late in the evening...) Yes totally sure. I can repeat it in my setup. It's a testing setup. No one else is using the db. > And which SA version do you use ? 0.5.6 > I experience that SA 0.5.6 issues a correct sequence of: > BEGIN > UPDATE ... > COMMIT I do as well... normally... 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] 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([, , , ]) 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]: 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] 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...
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.
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.
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.
[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 "", line 49, in 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 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] 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 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 '' 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
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 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 '' 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: 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] 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] Re: insert and joined mappers
On Tue, May 05, 2009 at 06:01:27AM -0700, GHZ wrote: > > try : > > m = mapper(MyJoin, a_table.join(b_table), properties={ > 'a_id' : [Table_a.__table__.c.id, Table_b.__table__.c.a_id] > }) > > from: > http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables Thanks, that's it! Am I wong or this is just a way to express exactely the join condition? wouldn't it be possible to set it implicitly from the join condition? *:-) --~--~-~--~~~---~--~~ 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 on collection 'Entry.user', whose mapper does not inherit from that of 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] remove an object from a property deletes from session
Hi, in the following code new_film is added to a director film list and then removed, before committing. I just realized that this makes it vanish from the session. What's the rationale behind it? I fear I don't understand something becouse I would have thought it should stay in the session. >>> donn = sess.query(Director).filter_by(last_name='Donnersmak').one() >>> new_film = Movie() >>> sess.add(new_film) >>> donn.movies += [new_film] >>> donn.movies.remove(new_film) >>> print new_film in sess False 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] 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(
[sqlalchemy] puzzling setup with ForeignKey
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", line 54, in _init_items item._set_parent(self) File "/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/schema.py", line 929, in _se
[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 ### deleting obj session.delete: IdentitySet([]) 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 obj 0x88b6c4c obj 0x88b61cc No handlers could be found for logger "sqlalchemy.pool.QueuePool.0x...cb8c" Traceback (most recent call last): File "test.py", line 36, in 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, anon_1.ticket_project_status AS anon_1_ticket_project_status, anon_1.ticket_project_name AS anon_1_ticket_project_name, anon_1.ticket_project_description AS anon_1_tick
[sqlalchemy] (InterfaceError) connection already closed
Hi, i'm trying to understand how to cope with errors from the database and I don't completely understand the following behaviour. I try to delete a Project that has integrity constrains, so correctly the db complains and SA raises an OperationalError. Right now I'm not trying to understand how to *prevent* this. I just want to understand how to give a message to the user and restore a working situation. [I'm working with postgreSQL and SA 5.0rc4] def delete_obj(obj): print "### deleting obj", repr(obj) try: session.delete(obj) print "session.delete:", session.deleted session.commit() except Exception, e: print "Problemi...", e.message session.rollback() q = session.query(Project) prj = q.filter_by(id=18).one() print repr(prj) delete_obj(prj) print "session.delete:", session.deleted for obj in session: print "obj", hex(id(obj)), type(obj) print "Project", prj ### <<< this code raises the Interface error where is the problem? the connection (as I tend to believe) or the object 'prj'? What's the state in which is now the object 'prj'? What should I do to go back to the previous situation? What exacltly means InterfaceError - connection already closed. I simply issued a .rollback(), why should it be closed? How should I test if a connection is closed? and how should I open it again? Thaks in advance sandro *:-) # OUTPUT ### ### deleting obj session.delete: IdentitySet([]) Problemi... (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.delete: IdentitySet([]) obj 0x88a912c obj 0x88b52cc obj 0x88b59ec No handlers could be found for logger "sqlalchemy.pool.QueuePool.0x...bc2c" Project Traceback (most recent call last): File "test.py", line 24, in print "Project", prj File "/home/misc/src/hg/py/sqlkit-pub/sqlkit/db/utils.py", line 65, in __str__ format = get_description(self.__table__, attr='format') File "/home/misc/src/hg/py/sqlkit-pub/sqlkit/db/utils.py", line 226, in get_description return getattr(TableDescr(table), attr) File "/home/misc/src/hg/py/sqlkit-pub/sqlkit/db/utils.py", line 133, in __init__ self.description, dbformat = self.guess_description() File "/home/misc/src/hg/py/sqlkit-pub/sqlkit/db/utils.py", line 197, in guess_description description, format = get_description_from_sqlkit(table) File "/home/misc/src/hg/py/sqlkit-pub/sqlkit/db/utils.py", line 240, in get_description_from_sqlkit res_proxy = metadata.bind.execute(SQL, table=table.name) File "/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/engine/base.py", line 1202, in execute return connection.execute(statement, *multiparams, **params) 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 888, in _execute_text 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 928, in _handle_dbapi_exception self._autorollback() File "/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/engine/base.py", line 794, in _autorollback self._rollback_impl() File "/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/engine/base.py", line 738, in _rollback_impl self._handle_dbapi_exception(e, None, None, None, None) File "/home/misc/src/sqlalchemy/sqlalchemy-svn/lib/sqlalchemy/engine/base.py", line 912, in _handle_dbapi_exception raise exc.DBAPIError.instance(None, None, e) sqlalchemy.exc.InterfaceError: (InterfaceError) connection already closed None None -- 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: 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 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 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: 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: 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 -~--~~~~--~~--~--~---