Re: [sqlalchemy] Re: Non-deterministic insertion order
On Oct 2, 2012, at 5:01 PM, George Sakkis wrote: > On Oct 2, 10:39 pm, Michael Bayer wrote: > >> On Oct 2, 2012, at 4:29 PM, George Sakkis wrote: >> >>> I had the impression that the Session’s unit of work figures out >>> automatically the potential object dependencies and makes sure that >>> the insertion order is consistent to a topological sort but apparently >>> I misunderstood. I postedhttps://gist.github.com/3822855as an >>> example. >> >>> In the first case (object_lists = [authors, books]), first a bunch of >>> Authors is committed and then a bunch of Books. As expected this >>> always succeeds. The next two cases though where authors and books are >>> commited at once may or may not succeed. Why it doesn't use the >>> topological sort of the tables and why it is not deterministic? >> >> on a quick glance I see no usage of relationship(). The topological sort is >> based on the relationship() paths built between mappers, not the foreign >> keys. > > Ah, I see. Out of curiosity, why it's not consistent across runs (i.e. > always fail)? > > Thanks for the super prompt reply! if I had to guess it would be because the topological sort relies upon Python sets and therefore dictionary ordering, which is non-deterministic. -- 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] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior
I'm not 100% sure that's what was going on (it was hard to get a debug shell open at the moment of the error, as opposed to the assertion much later), but switching those setattr() calls to set_committed_value() certainly fixed it. I admit I don't understand the session internals well enough to grok what you're saying about resetting session.dirty or it being a deeper bug I've uncovered. If I can be of any assistance with that please let me know. Regardless, for this project it is no longer an issue as objects are no longer getting dirtied by setattr() calls in the mapper extension. Thanks for your help in pointing me down the right track; I'm pushing out a new version of SQLAlchemy-ORM-tree momentarily. Cheers, Mark On Tue, Oct 2, 2012 at 1:38 PM, Michael Bayer wrote: > oh, I know what you're doing, you're modifying the attributes of objects > that aren't even involved - so yes, the flush normally doesn't go finding > those, and set_committed_value() would be your workaround for now. > > However, I can modify flush to do this "reset" for everything that's in > "dirty", rather than just what it knows to have changed.I'd have to > think about this as I'm not sure it's appropriate. > > > > On Oct 2, 2012, at 4:28 PM, Mark Friedenbach wrote: > > Indeed, session.dirty is non-empty within after_flush_postexec(). > > I'm working on a fix for sqlalchemy-orm-tree first before I can think > about doing a (smaller) regression test. Besides, it now occurs to me that > in some cases I might be setting attributes on objects in the session but > outside of the flush plan (child nodes of a parent that gets moved around, > for example). That could legitimately cause an undesired 2nd > flush. `set_committed_value` looks like what I want to fix that. > > On Tue, Oct 2, 2012 at 1:08 PM, Michael Bayer wrote: > >> >> On Oct 2, 2012, at 4:05 PM, Michael Bayer wrote: >> >> >In theory it would be only .new and .dirty that might have state >> after the flush completes. >> >> correction, ".new and .deleted" lists that might have any state. >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Non-deterministic insertion order
On Oct 2, 10:39 pm, Michael Bayer wrote: > On Oct 2, 2012, at 4:29 PM, George Sakkis wrote: > > > I had the impression that the Session’s unit of work figures out > > automatically the potential object dependencies and makes sure that > > the insertion order is consistent to a topological sort but apparently > > I misunderstood. I postedhttps://gist.github.com/3822855as an > > example. > > > In the first case (object_lists = [authors, books]), first a bunch of > > Authors is committed and then a bunch of Books. As expected this > > always succeeds. The next two cases though where authors and books are > > commited at once may or may not succeed. Why it doesn't use the > > topological sort of the tables and why it is not deterministic? > > on a quick glance I see no usage of relationship(). The topological sort is > based on the relationship() paths built between mappers, not the foreign keys. Ah, I see. Out of curiosity, why it's not consistent across runs (i.e. always fail)? Thanks for the super prompt reply! George -- 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] Non-deterministic insertion order
On Oct 2, 2012, at 4:29 PM, George Sakkis wrote: > I had the impression that the Session’s unit of work figures out > automatically the potential object dependencies and makes sure that > the insertion order is consistent to a topological sort but apparently > I misunderstood. I posted https://gist.github.com/3822855 as an > example. > > In the first case (object_lists = [authors, books]), first a bunch of > Authors is committed and then a bunch of Books. As expected this > always succeeds. The next two cases though where authors and books are > commited at once may or may not succeed. Why it doesn't use the > topological sort of the tables and why it is not deterministic? on a quick glance I see no usage of relationship(). The topological sort is based on the relationship() paths built between mappers, not the foreign keys. -- 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] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior
oh, I know what you're doing, you're modifying the attributes of objects that aren't even involved - so yes, the flush normally doesn't go finding those, and set_committed_value() would be your workaround for now. However, I can modify flush to do this "reset" for everything that's in "dirty", rather than just what it knows to have changed.I'd have to think about this as I'm not sure it's appropriate. On Oct 2, 2012, at 4:28 PM, Mark Friedenbach wrote: > Indeed, session.dirty is non-empty within after_flush_postexec(). > > I'm working on a fix for sqlalchemy-orm-tree first before I can think about > doing a (smaller) regression test. Besides, it now occurs to me that in some > cases I might be setting attributes on objects in the session but outside of > the flush plan (child nodes of a parent that gets moved around, for example). > That could legitimately cause an undesired 2nd flush. `set_committed_value` > looks like what I want to fix that. > > On Tue, Oct 2, 2012 at 1:08 PM, Michael Bayer > wrote: > > On Oct 2, 2012, at 4:05 PM, Michael Bayer wrote: > > >In theory it would be only .new and .dirty that might have state after > > the flush completes. > > correction, ".new and .deleted" lists that might have any state. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Non-deterministic insertion order
I had the impression that the Session’s unit of work figures out automatically the potential object dependencies and makes sure that the insertion order is consistent to a topological sort but apparently I misunderstood. I posted https://gist.github.com/3822855 as an example. In the first case (object_lists = [authors, books]), first a bunch of Authors is committed and then a bunch of Books. As expected this always succeeds. The next two cases though where authors and books are commited at once may or may not succeed. Why it doesn't use the topological sort of the tables and why it is not deterministic? Thanks! George -- 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] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior
Indeed, session.dirty is non-empty within after_flush_postexec(). I'm working on a fix for sqlalchemy-orm-tree first before I can think about doing a (smaller) regression test. Besides, it now occurs to me that in some cases I might be setting attributes on objects in the session but outside of the flush plan (child nodes of a parent that gets moved around, for example). That could legitimately cause an undesired 2nd flush. `set_committed_value` looks like what I want to fix that. On Tue, Oct 2, 2012 at 1:08 PM, Michael Bayer wrote: > > On Oct 2, 2012, at 4:05 PM, Michael Bayer wrote: > > >In theory it would be only .new and .dirty that might have state > after the flush completes. > > correction, ".new and .deleted" lists that might have any state. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior
On Oct 2, 2012, at 4:05 PM, Michael Bayer wrote: >In theory it would be only .new and .dirty that might have state after the > flush completes. correction, ".new and .deleted" lists that might have any state. -- 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] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior
On Oct 2, 2012, at 3:49 PM, Mark Friedenbach wrote: > Well it's hard to boil it down to a specific test case, as it affects the > underlying assumptions that went into the design of ORM-tree. Here's an > explanation of what I'm doing, and perhaps you can tell me if I'm (ab)using > the API correctly: > > The meat of the code is a mapper extension whose insert, update, and delete > hooks execute SQL expressions directly to update the nested-interval tree > parameters. For efficiency I use the SQL expression layer and then manually > update the working set of ORM objects to reflect the new state. > > In essence: > > connection.execute(...update in sql expression language...) > for obj in session.identity_map: > ...same update, as python... > > (The session.identity_map is accessible to the mapper extension because it > was tucked away as a hidden attribute in the object in a session extension > before_flush handler.) > > As far as I can tell, the update to the session objects is now triggering a > 2nd flush, even though the purpose of the update was to refresh the objects > with their current database values. On the 2nd flush the SQL expression > updates get executed again, resulting in a corrupt database. > > Any red flags in what I'm doing? Basically, when the flush is finished, session.dirty, session.new and session.deleted should be empty. If you add a quick after_flush_postexec() event, and inside the event assert that these are all empty, then you shouldn't get any subsequent flush. If session.dirty is acquiring state due to attribute set events, as I see a lot of setattr() going on, you can set attributes without establishing history using set_committed_value(): http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#sqlalchemy.orm.attributes.set_committed_value . However, if that's in fact what's happening, I might need to look into that because the "dirty" state of attributes should be unconditionally reset before the flush completes. I just did a test to confirm that, so I'd be curious to see a test (unless I can come up with one here) that illustrates plain setattr() of a column-based attribute causing a second flush. In theory it would be only .new and .dirty that might have state after the flush completes. -- 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] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior
I forgot to mention, this is picked up by SQLAlchemy-ORM-tree's unit tests, if you want to see the failure: git clone git://github.com/monetizeio/sqlalchemy-orm-tree.git cd sqlalchemy-orm-tree && make check On Tue, Oct 2, 2012 at 12:49 PM, Mark Friedenbach wrote: > Well it's hard to boil it down to a specific test case, as it affects the > underlying assumptions that went into the design of ORM-tree. Here's an > explanation of what I'm doing, and perhaps you can tell me if I'm (ab)using > the API correctly: > > The meat of the code is a mapper extension whose insert, update, and > delete hooks execute SQL expressions directly to update the nested-interval > tree parameters. For efficiency I use the SQL expression layer and then > manually update the working set of ORM objects to reflect the new state. > > In essence: > > connection.execute(...update in sql expression language...) > for obj in session.identity_map: > ...same update, as python... > > (The session.identity_map is accessible to the mapper extension because it > was tucked away as a hidden attribute in the object in a session extension > before_flush handler.) > > As far as I can tell, the update to the session objects is now triggering > a 2nd flush, even though the purpose of the update was to refresh the > objects with their current database values. On the 2nd flush the SQL > expression updates get executed again, resulting in a corrupt database. > > Any red flags in what I'm doing? > > > All the relevant code is in this file: > > > https://github.com/monetizeio/sqlalchemy-orm-tree/blob/master/sqlalchemy_tree/orm.py > > > > On Tue, Oct 2, 2012 at 12:07 PM, Michael Bayer > wrote: > >> >> On Oct 2, 2012, at 2:14 PM, Mark Friedenbach wrote: >> >> SQLAlchemy 0.7.9 seems to have broken SQLAlchemy-ORM-tree ( >> http://pypi.python.org/pypi/SQLAlchemy-ORM-tree/). Specifically, >> SQLAlchemy-ORM-Tree has a dependency on flush behavior prior to the fix for >> #2566. I'm currently investigating a way to detect (and ignore) the 2nd >> flush. >> >> But more generally I'm wondering what motivated #2566 in the first place? >> It has huge compatibility implications for anyone doing tricky things with >> flush and insert/update/delete events. >> >> >> #2566 was a serious issue. If dirty state remains in the session after a >> flush(), then calling commit() had the effect that *two COMMITs are >> emitted*, meaning, one COMMIT, then a brand new transaction, then another >> one, and then any dirty state left by that second commit would just be >> garbage. The commit() call flushes all remaining dirty state and it is >> essential that the session is clean after a commit occurs.2566's fix >> should only effect when commit() is called. >> >> Feel free to send me a test case showing a valid usage that is broken by >> this change. >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior
Well it's hard to boil it down to a specific test case, as it affects the underlying assumptions that went into the design of ORM-tree. Here's an explanation of what I'm doing, and perhaps you can tell me if I'm (ab)using the API correctly: The meat of the code is a mapper extension whose insert, update, and delete hooks execute SQL expressions directly to update the nested-interval tree parameters. For efficiency I use the SQL expression layer and then manually update the working set of ORM objects to reflect the new state. In essence: connection.execute(...update in sql expression language...) for obj in session.identity_map: ...same update, as python... (The session.identity_map is accessible to the mapper extension because it was tucked away as a hidden attribute in the object in a session extension before_flush handler.) As far as I can tell, the update to the session objects is now triggering a 2nd flush, even though the purpose of the update was to refresh the objects with their current database values. On the 2nd flush the SQL expression updates get executed again, resulting in a corrupt database. Any red flags in what I'm doing? All the relevant code is in this file: https://github.com/monetizeio/sqlalchemy-orm-tree/blob/master/sqlalchemy_tree/orm.py On Tue, Oct 2, 2012 at 12:07 PM, Michael Bayer wrote: > > On Oct 2, 2012, at 2:14 PM, Mark Friedenbach wrote: > > SQLAlchemy 0.7.9 seems to have broken SQLAlchemy-ORM-tree ( > http://pypi.python.org/pypi/SQLAlchemy-ORM-tree/). Specifically, > SQLAlchemy-ORM-Tree has a dependency on flush behavior prior to the fix for > #2566. I'm currently investigating a way to detect (and ignore) the 2nd > flush. > > But more generally I'm wondering what motivated #2566 in the first place? > It has huge compatibility implications for anyone doing tricky things with > flush and insert/update/delete events. > > > #2566 was a serious issue. If dirty state remains in the session after a > flush(), then calling commit() had the effect that *two COMMITs are > emitted*, meaning, one COMMIT, then a brand new transaction, then another > one, and then any dirty state left by that second commit would just be > garbage. The commit() call flushes all remaining dirty state and it is > essential that the session is clean after a commit occurs.2566's fix > should only effect when commit() is called. > > Feel free to send me a test case showing a valid usage that is broken by > this change. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] live access to postgis database to use in ExtJS, OpenLayers, etc
On Oct 2, 2012, at 2:26 PM, Gery . wrote: > > thanks for the email, it seems that you know a lot about this stuff, it'd be > great if you could share what you know so in that way we will learn from you, > but as expected you won't do it, your "Sorry I can't be more help," is very > clear as always. But hey anyway, I appreciate that you demostrate how you > really are. If I'm reading this correctly, this is uncalled for - Simon is an incredibly helpful person, and on the SQLAlchemy list you'll get all the help you need in using SQLAlchemy and relational databases. For general Python assistance you can try IRC channel #python, the comp.lang.python lists, and StackOverflow. > > > > __ > Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO > es necesario. > Think green - keep it on the screen. Do NOT print if it is NOT necessary. > Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie > NICHT, wenn es NICHT notwendig ist. > > > > From: si...@simonking.org.uk > > Date: Tue, 2 Oct 2012 14:43:21 +0100 > > Subject: Re: [sqlalchemy] live access to postgis database to use in ExtJS, > > OpenLayers, etc > > To: sqlalchemy@googlegroups.com > > > > On Mon, Oct 1, 2012 at 11:38 PM, Gery . wrote: > > > > > > thanks but I want to use that live access to search at first through > > > ExtJS/GeoExtJS/OpenLayers and through them there is only a url available > > > (protocol HTTP), I also need to get the data as GeoJSON, so I think > > > GeoAlchemy might not be the right solution, I think. If I'm wrong, please > > > I'd love some points about it, thanks. > > > > > > > It sounds like you are trying to do at least 6 quite complicated > > things all at once, without really understanding any of them. This > > will not be easy. > > > > 1. The client side of your application is presumably written in > > Javascript and HTML, using javascript libraries such as ExtJS and > > OpenLayers. You need to fully understand how these work. > > > > 2. The application will then make HTTP requests to a web server. You > > need to understand at least the basics of HTTP. > > > > 3. The web server might be a single python script, or it could be > > something running behind Apache. You need to understand your web > > server. > > > > 4. The server side of your application might be using any of a number > > of libraries to connect to the web server (such as > > Django/Pyramid/Flask/cgi/mod_wsgi etc.). You need to understand > > whatever mechanism your application is using to speak HTTP. > > > > 5. Your application can use SQLAlchemy and GeoAlchemy to retrieve data > > from postgis into Python data structures. You will need to understand > > postgis, SQLAlchemy, GeoAlchemy and Python. > > > > 6. Your application can then convert those Python data structures into > > GeoJSON. You will need to understand GeoJSON. > > > > The SQLAlchemy mailing list can help you with exactly one part of this > > (step 5). SQLAlchemy (and GeoAlchemy) is perfectly capable of querying > > multiple tables and retrieving results. But how you accept the HTTP > > request, and how you pass the results back, are completely outside the > > scope of this list and I'm afraid you are unlikely to find much help > > here with it. > > > > Sorry I can't be more help, > > > > Simon > > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group at > > http://groups.google.com/group/sqlalchemy?hl=en. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior
On Oct 2, 2012, at 2:14 PM, Mark Friedenbach wrote: > SQLAlchemy 0.7.9 seems to have broken SQLAlchemy-ORM-tree > (http://pypi.python.org/pypi/SQLAlchemy-ORM-tree/). Specifically, > SQLAlchemy-ORM-Tree has a dependency on flush behavior prior to the fix for > #2566. I'm currently investigating a way to detect (and ignore) the 2nd flush. > > But more generally I'm wondering what motivated #2566 in the first place? It > has huge compatibility implications for anyone doing tricky things with flush > and insert/update/delete events. #2566 was a serious issue. If dirty state remains in the session after a flush(), then calling commit() had the effect that *two COMMITs are emitted*, meaning, one COMMIT, then a brand new transaction, then another one, and then any dirty state left by that second commit would just be garbage. The commit() call flushes all remaining dirty state and it is essential that the session is clean after a commit occurs.2566's fix should only effect when commit() is called. Feel free to send me a test case showing a valid usage that is broken by this change. -- 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] live access to postgis database to use in ExtJS, OpenLayers, etc
thanks for the email, it seems that you know a lot about this stuff, it'd be great if you could share what you know so in that way we will learn from you, but as expected you won't do it, your "Sorry I can't be more help," is very clear as always. But hey anyway, I appreciate that you demostrate how you really are. __ Piensa en el medio ambiente - mantenlo en la pantalla. NO lo imprimas si NO es necesario. Think green - keep it on the screen. Do NOT print if it is NOT necessary. Denken Sie an die Umwelt - bewahren Sie es auf dem Bildschirm. Drucken Sie NICHT, wenn es NICHT notwendig ist. > From: si...@simonking.org.uk > Date: Tue, 2 Oct 2012 14:43:21 +0100 > Subject: Re: [sqlalchemy] live access to postgis database to use in ExtJS, > OpenLayers, etc > To: sqlalchemy@googlegroups.com > > On Mon, Oct 1, 2012 at 11:38 PM, Gery . wrote: > > > > thanks but I want to use that live access to search at first through > > ExtJS/GeoExtJS/OpenLayers and through them there is only a url available > > (protocol HTTP), I also need to get the data as GeoJSON, so I think > > GeoAlchemy might not be the right solution, I think. If I'm wrong, please > > I'd love some points about it, thanks. > > > > It sounds like you are trying to do at least 6 quite complicated > things all at once, without really understanding any of them. This > will not be easy. > > 1. The client side of your application is presumably written in > Javascript and HTML, using javascript libraries such as ExtJS and > OpenLayers. You need to fully understand how these work. > > 2. The application will then make HTTP requests to a web server. You > need to understand at least the basics of HTTP. > > 3. The web server might be a single python script, or it could be > something running behind Apache. You need to understand your web > server. > > 4. The server side of your application might be using any of a number > of libraries to connect to the web server (such as > Django/Pyramid/Flask/cgi/mod_wsgi etc.). You need to understand > whatever mechanism your application is using to speak HTTP. > > 5. Your application can use SQLAlchemy and GeoAlchemy to retrieve data > from postgis into Python data structures. You will need to understand > postgis, SQLAlchemy, GeoAlchemy and Python. > > 6. Your application can then convert those Python data structures into > GeoJSON. You will need to understand GeoJSON. > > The SQLAlchemy mailing list can help you with exactly one part of this > (step 5). SQLAlchemy (and GeoAlchemy) is perfectly capable of querying > multiple tables and retrieving results. But how you accept the HTTP > request, and how you pass the results back, are completely outside the > scope of this list and I'm afraid you are unlikely to find much help > here with it. > > Sorry I can't be more help, > > Simon > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior
SQLAlchemy 0.7.9 seems to have broken SQLAlchemy-ORM-tree (http://pypi.python.org/pypi/SQLAlchemy-ORM-tree/). Specifically, SQLAlchemy-ORM-Tree has a dependency on flush behavior prior to the fix for #2566. I'm currently investigating a way to detect (and ignore) the 2nd flush. But more generally I'm wondering what motivated #2566 in the first place? It has huge compatibility implications for anyone doing tricky things with flush and insert/update/delete events. -- 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/-/nJU2GFvsATYJ. 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] Double many-to-many relation filter headache
On Oct 2, 2012, at 6:28 AM, Vlad K. wrote: > On 10/01/2012 04:48 PM, Michael Bayer wrote: >> If I understand correctly, this isn't accurate. Your query needs the usage >> of query.join(), and this usage is not related to whether or not you supply >> a complete entity to select from. > > > I wasn't using explicit .join(), except an outerjoin on a one-to-one relation > with images table (which by default is lazyloaded). I thought SQLA would > figure it out itself based on foreign keys, as it usually does. Maybe that's > wrong, maybe I should be explicit, but so far it worked (when entire > instances of declarative_base were given to session.query()). > > I forgot to mention, and this could be the cause of perceived problems, that > I was listing columns of more than one model in the session.query(). Maybe > that confused it. > > What happened was that the query returned a list of named tuples for each of > the rows returned by the database, while I was expecting only one instance of > the main model (see below). > > >> I'm not sure what "won't automatically process inner joins" means, does that >> mean, you get an error message, or you're forced to call .join() as opposed >> to some other system of joining two entites, or what. > > I mean the cartesian cross-product in queries with many-to-many relations. > SQLA would "automatically" recognize the queried models and construct the > model instances and their relationships properly. > > E.g. if I query for single model that has X related in a many-to-many > fashion, the query would return X number of rows, like with .joinedload(), > but SQLA would return only one instance of the model, and its related > property filled with a list of X instances of that model, i.e it would > automatically process the joins and create a relationship tree in the session. > > I hope I'm making sense here. :) > > I guess I should make a minimal example case for this, and will do as time > permits. I have a hunch what you're doing here. There is no "automatic" recognition of queried models and rendering of joins based on that, with the exception of joined eager loading which is something different. What there is, is that if you query for whole entities only, the Query will *de-duplicate* entries as rows are received. This is also so that strategies like joined eager loading allow the lead entry to be returned uniquely. So that would mean, you're getting many, many duplicate rows due to plain cartesian product, and the deduping of entities is hiding it. turn on echo='debug' on your create_engine() to see what's going on, then you'd probably want to start using join() to get the actual SQL you're looking for. -- 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] Alembic bulk_insert when primary key is a String
Great, works as expected now.
Not sure I understand it completely - I guess calling `table` with an
existing table links to it and provides access to whatever columns you
specified. Have to read up some more.
Thanks again for the help.
On Monday, October 1, 2012 10:39:43 PM UTC-5, Michael Bayer wrote:
>
> yeah you just need to create table() there with the columns you want as
> well:
>
> proc_table = table('generalprocedures_lu', column('procedure', String))
> op.bulk_insert(proc_table, ...)
>
>
> On Oct 1, 2012, at 10:49 PM, Christopher Loverich wrote:
>
> Hello.* Loving* alembic & sqlachemy - really great libraries. Just having
> a bit of trouble with something:
>
> op.create_table('generalprocedures_lu',
> sa.Column('procedure', sa.String(length=80), nullable=False),
> sa.PrimaryKeyConstraint('procedure')
> )
>
> op.bulk_insert(table('generalprocedures_lu'),
> [
> {'procedure':u'Fetoscopic guided laser photocoagulation'}
> ],
> )
>
>
> The above fails with:
> sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column
> "procedure" violates not-null constraint
> 'INSERT INTO generalprocedures_lu DEFAULT VALUES'* {}*
>
> If I attempt to insert multiple rows, alembic seems to try and insert
> multiple empty {}'s. Also, *confused* about the 'DEFAULT' argument.
>
> My apologies if I'm doing something very wrong here (ott single column
> lookup table ^^), but I"m not sure what to try as a work-around. I
> attempted using a traditional lookup table with an integer primary key, but
> got the same results. If I leave out the bulk insert, I can manually put in
> values. Database is Postgres 9.2 (if relevant). Let me know if I need to
> give a more complete example.
>
>
> --
> 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/-/baybV1cO4ecJ.
> To post to this group, send email to sqlal...@googlegroups.com
> .
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/lZzxXelyEtoJ.
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] live access to postgis database to use in ExtJS, OpenLayers, etc
On Mon, Oct 1, 2012 at 11:38 PM, Gery . wrote: > > thanks but I want to use that live access to search at first through > ExtJS/GeoExtJS/OpenLayers and through them there is only a url available > (protocol HTTP), I also need to get the data as GeoJSON, so I think > GeoAlchemy might not be the right solution, I think. If I'm wrong, please > I'd love some points about it, thanks. > It sounds like you are trying to do at least 6 quite complicated things all at once, without really understanding any of them. This will not be easy. 1. The client side of your application is presumably written in Javascript and HTML, using javascript libraries such as ExtJS and OpenLayers. You need to fully understand how these work. 2. The application will then make HTTP requests to a web server. You need to understand at least the basics of HTTP. 3. The web server might be a single python script, or it could be something running behind Apache. You need to understand your web server. 4. The server side of your application might be using any of a number of libraries to connect to the web server (such as Django/Pyramid/Flask/cgi/mod_wsgi etc.). You need to understand whatever mechanism your application is using to speak HTTP. 5. Your application can use SQLAlchemy and GeoAlchemy to retrieve data from postgis into Python data structures. You will need to understand postgis, SQLAlchemy, GeoAlchemy and Python. 6. Your application can then convert those Python data structures into GeoJSON. You will need to understand GeoJSON. The SQLAlchemy mailing list can help you with exactly one part of this (step 5). SQLAlchemy (and GeoAlchemy) is perfectly capable of querying multiple tables and retrieving results. But how you accept the HTTP request, and how you pass the results back, are completely outside the scope of this list and I'm afraid you are unlikely to find much help here with it. Sorry I can't be more help, Simon -- 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] Double many-to-many relation filter headache
On 10/01/2012 04:48 PM, Michael Bayer wrote: If I understand correctly, this isn't accurate. Your query needs the usage of query.join(), and this usage is not related to whether or not you supply a complete entity to select from. I wasn't using explicit .join(), except an outerjoin on a one-to-one relation with images table (which by default is lazyloaded). I thought SQLA would figure it out itself based on foreign keys, as it usually does. Maybe that's wrong, maybe I should be explicit, but so far it worked (when entire instances of declarative_base were given to session.query()). I forgot to mention, and this could be the cause of perceived problems, that I was listing columns of more than one model in the session.query(). Maybe that confused it. What happened was that the query returned a list of named tuples for each of the rows returned by the database, while I was expecting only one instance of the main model (see below). I'm not sure what "won't automatically process inner joins" means, does that mean, you get an error message, or you're forced to call .join() as opposed to some other system of joining two entites, or what. I mean the cartesian cross-product in queries with many-to-many relations. SQLA would "automatically" recognize the queried models and construct the model instances and their relationships properly. E.g. if I query for single model that has X related in a many-to-many fashion, the query would return X number of rows, like with .joinedload(), but SQLA would return only one instance of the model, and its related property filled with a list of X instances of that model, i.e it would automatically process the joins and create a relationship tree in the session. I hope I'm making sense here. :) I guess I should make a minimal example case for this, and will do as time permits. -- .oO V Oo. Work Hard, Increase Production, Prevent Accidents, and Be Happy! ;) -- 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.
