[sqlalchemy] Re: Nested query generates invalid SQL on PostgreSQL
On Sep 26, 2007, at 9:28 AM, Barry Hart wrote: My application has a table of item prices by week. A record is inserted into a week whenever there is a new, different price. To find the current price, you have to look backwards in time to the most recent record. I've written some code using SqlAlchemy 0.3.10 to generate a query like this. The resulting query fails on PostgreSQL because the innermost query generated by SQLAlchemy is missing a FROM clause. (SQLite seems happy with it, however.) Is this a bug in SqlAlchemy? if youre missing a FROM clause, thats a bug. this is usually caused by over-correlation of a select (in 0.4, this shouldnt happen..i.e. the bug should be fixed). try setting correlate=False on the nested select() statement. Individual correlations to the outside you can then set explicitly using the correlate() method on the select() object. in version 0.4, the API for correlation is a little different and the correlate(), instead of changing the current select(), generates a new select() (also correlate=False is deprecated, calling correlate (None) turns off auto-correlation). --~--~-~--~~~---~--~~ 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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
On Sep 25, 2007, at 7:30 PM, m h wrote: Have updated the ticket with my fix change the dbapi_type of OracleText from CLOB to NUMBER I'm not sure if this breaks others code the bug is, the type of the bind parameters should be coming out as VARCHAR, not CLOB. NUMBER is definitely wrong. perchance, is the type of the column cal_dim.c.adwkenddt a CLOB ? (i.e. String with no length?) you didnt put the table on the ticket so i actually cannot test it (its an incomplete test case). just add a length and the whole thing should work. --~--~-~--~~~---~--~~ 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: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
On 9/26/07, Michael Bayer [EMAIL PROTECTED] wrote: On Sep 25, 2007, at 7:30 PM, m h wrote: Have updated the ticket with my fix change the dbapi_type of OracleText from CLOB to NUMBER I'm not sure if this breaks others code the bug is, the type of the bind parameters should be coming out as VARCHAR, not CLOB. NUMBER is definitely wrong. perchance, is the type of the column cal_dim.c.adwkenddt a CLOB ? (i.e. String with no length?) you didnt put the table on the ticket so i actually cannot test it (its an incomplete test case). just add a length and the whole thing should work. Nope, not a CLOB, it's a DATE field. --~--~-~--~~~---~--~~ 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: r3507 breaks here
On Sep 25, 2007, at 12:15 PM, [EMAIL PROTECTED] wrote: anyway, all 10328 (joined) cases pass, have a nice day. svilen ive changed my approach on this one to what i should have done in the 1st place. try out 3518. --~--~-~--~~~---~--~~ 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] 0.4 and server-side cursors
It seems that something changed in 0.4 that causes server-side cursors (in postgresql) to fail. I'm issuing the DECLARE/FETCH commands manually through connection.execute, not using server_side_cursors in the dialect, since I only want certain queries to use them. I verified that this works on the rel_0_3 branch. Here's a minimal test case: from sqlalchemy import * engine = create_engine( 'postgres://user:[EMAIL PROTECTED]:port/db' ) conn = engine.connect() conn.execute( DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM table ) res = conn.execute( FETCH FORWARD 64 FROM test_cur ) for row in res: print row This results in a InterfaceError: cursor already closed exception when iterating over the resultproxy. Any ideas? Dan --~--~-~--~~~---~--~~ 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: r3507 breaks here
On Wednesday 26 September 2007 20:09:10 Michael Bayer wrote: On Sep 25, 2007, at 12:15 PM, [EMAIL PROTECTED] wrote: anyway, all 10328 (joined) cases pass, have a nice day. svilen ive changed my approach on this one to what i should have done in the 1st place. try out 3518. ok too now, with no special naming. --~--~-~--~~~---~--~~ 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: 0.4 and server-side cursors
cursor.description isn't available after the DECLARE (analogous to conn.cursor(x)), but is available after a FETCH (analogous to a cur.fetchXXX()). If you want to get rid of the buffering resultproxy, you can do a FETCH ABSOLUTE 0 FROM cursor_name - that will make cursor.description available, and leave the SS cursor positioned before the first row. On Sep 26, 4:34 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Sep 26, 2007, at 3:52 PM, Dan Watson wrote: It seems that something changed in 0.4 that causes server-side cursors (in postgresql) to fail. I'm issuing the DECLARE/FETCH commands manually through connection.execute, not using server_side_cursors in the dialect, since I only want certain queries to use them. I verified that this works on the rel_0_3 branch. Here's a minimal test case: from sqlalchemy import * engine = create_engine( 'postgres://user:[EMAIL PROTECTED]:port/db' ) conn = engine.connect() conn.execute( DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM table ) res = conn.execute( FETCH FORWARD 64 FROM test_cur ) for row in res: print row This results in a InterfaceError: cursor already closed exception when iterating over the resultproxy. one thing I'd note is that this approach, well it *shouldn't* go very far in 0.3, because when SS cursors are used, psycopg2 does not have cursor.description available until the first row is fetched, and ResultProxy immediately fetches cursor.description; this is why we made a separate buffered ResultProxy which only takes effect when server_side_cursors=True just to deal with this. but im not familiar with FETCH FORWARD 64 and it seems to be prefetching the description field somehow ? is there a way to combine this with a regular SELECT so that we wouldnt need the buffered result handle ? in 0.4, the error is just because your statement doesnt start with SELECT, which results in it just grabbing cursor.rowcount and closing immediately; this removes the need to explicitly close all INSERT/UPDATE/DELETE statements which otherwise can hang open and leave things locked. FETCH is added to the regexp in r3521. --~--~-~--~~~---~--~~ 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: 0.4 and server-side cursors
On Sep 26, 2007, at 5:31 PM, Dan Watson wrote: cursor.description isn't available after the DECLARE (analogous to conn.cursor(x)), but is available after a FETCH (analogous to a cur.fetchXXX()). If you want to get rid of the buffering resultproxy, you can do a FETCH ABSOLUTE 0 FROM cursor_name - that will make cursor.description available, and leave the SS cursor positioned before the first row. also i should note that we do eventually want to have more options on Connection to control things like this; transaction isolation, cursor behavior, etc. just need to decide on an API for it. if you need this feature sooner rather than later its not a big deal to add (somethihng like connection.set_options(named_cursors=True) ). --~--~-~--~~~---~--~~ 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: 0.4 and server-side cursors
On Sep 26, 2007, at 5:31 PM, Dan Watson wrote: cursor.description isn't available after the DECLARE (analogous to conn.cursor(x)), but is available after a FETCH (analogous to a cur.fetchXXX()). If you want to get rid of the buffering resultproxy, you can do a FETCH ABSOLUTE 0 FROM cursor_name - that will make cursor.description available, and leave the SS cursor positioned before the first row. hm does that require a second cursor ? its giving me cant execute twice on a named cursor. if so, the current approach we have is probably more efficient... --~--~-~--~~~---~--~~ 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] SQLAlchemy 0.4beta6 released !!
This should hopefully be the last of the beta releases before 0.4.0 is released. The big change in this one is that the ORM Session is now *weak referencing* by default, with an option to turn on the old strong referencing behavior. This means that objects in the session get cleared out automatically when they fall out of scope within your application. Objects which have pending changes on them will not get cleared until the session is flushed. Theres also some refinements to how mappers compile, and it should be much less likely that you'd get stuck with an uncompiled mapper. When defining mappers in an inheritance relationship, the superclass mapper needs to be defined before the corresponding subclass mappers (which should be how everyone is doing it anyway...I think). Other than that you can still define mappers in whatever order regardless of relations to each other. Besides those, we have a lot of bug fixes and some more enhancements and speed improvements. We mainly want to see that this one is stable, works in everyone's environment as well or better than all the other betas, and then we should be ready to go. Download 0.4 beta6 at: http://www.sqlalchemy.org/download.html changelog (also at http://www.sqlalchemy.org/CHANGES) : 0.4.0beta6 -- - The Session identity map is now *weak referencing* by default, use weak_identity_map=False to use a regular dict. The weak dict we are using is customized to detect instances which are dirty and maintain a temporary strong reference to those instances until changes are flushed. - Mapper compilation has been reorganized such that most compilation occurs upon mapper construction. This allows us to have fewer calls to mapper.compile() and also to allow class-based properties to force a compilation (i.e. User.addresses == 7 will compile all mappers; this is [ticket:758]). The only caveat here is that an inheriting mapper now looks for its inherited mapper upon construction; so mappers within inheritance relationships need to be constructed in inheritance order (which should be the normal case anyway). - added FETCH to the keywords detected by Postgres to indicate a result-row holding statement (i.e. in addition to SELECT). - Added full list of SQLite reserved keywords so that they get escaped properly. - Tightened up the relationship between the Query's generation of eager load aliases, and Query.instances() which actually grabs the eagerly loaded rows. If the aliases were not specifically generated for that statement by EagerLoader, the EagerLoader will not take effect when the rows are fetched. This prevents columns from being grabbed accidentally as being part of an eager load when they were not meant for such, which can happen with textual SQL as well as some inheritance situations. It's particularly important since the anonymous aliasing of columns uses simple integer counts now to generate labels. - Removed parameters argument from clauseelement.compile(), replaced with column_keys. The parameters sent to execute() only interact with the insert/update statement compilation process in terms of the column names present but not the values for those columns. Produces more consistent execute/executemany behavior, simplifies things a bit internally. - Added 'comparator' keyword argument to PickleType. By default, mutable PickleType does a deep compare of objects using their dumps() representation. But this doesn't work for dictionaries. Pickled objects which provide an adequate __eq__() implementation can be set up with PickleType(comparator=operator.eq) [ticket:560] - Added session.is_modified(obj) method; performs the same history comparison operation as occurs within a flush operation; setting include_collections=False gives the same result as is used when the flush determines whether or not to issue an UPDATE for the instance's row. - Added schema argument to Sequence; use this with Postgres /Oracle when the sequence is located in an alternate schema. Implements part of [ticket:584], should fix [ticket:761]. - Fixed reflection of the empty string for mysql enums. - Changed MySQL dialect to use the older LIMIT offset, limit syntax instead of LIMIT l OFFSET o for folks using 3.23. [ticket:794] - Added 'passive_deletes=all' flag to relation(), disables all nulling-out of foreign key attributes during a flush where the parent object is deleted. - Column defaults and onupdates, executing inline, will add parenthesis for subqueries and other parenthesis-requiring expressions - The behavior of String/Unicode types regarding that they auto- convert to TEXT/CLOB when no length is present now occurs *only* for an exact type of String or Unicode with no arguments. If you use VARCHAR or NCHAR (subclasses of String/Unicode) with no length,
[sqlalchemy] Re: SQLAlchemy 0.4beta6 released !!
Dear Micheal, Does this mean that with web apps since the session is now weak referencing that we will no longer have to call Session.remove() to clear out Sessions? Specifically I'm referencing what Mike Orr wrote in the pylonscookbook. Jose Michael Bayer wrote: This should hopefully be the last of the beta releases before 0.4.0 is released. The big change in this one is that the ORM Session is now *weak referencing* by default, with an option to turn on the old strong referencing behavior. This means that objects in the session get cleared out automatically when they fall out of scope within your application. Objects which have pending changes on them will not get cleared until the session is flushed. Theres also some refinements to how mappers compile, and it should be much less likely that you'd get stuck with an uncompiled mapper. When defining mappers in an inheritance relationship, the superclass mapper needs to be defined before the corresponding subclass mappers (which should be how everyone is doing it anyway...I think). Other than that you can still define mappers in whatever order regardless of relations to each other. Besides those, we have a lot of bug fixes and some more enhancements and speed improvements. We mainly want to see that this one is stable, works in everyone's environment as well or better than all the other betas, and then we should be ready to go. Download 0.4 beta6 at: http://www.sqlalchemy.org/download.html changelog (also at http://www.sqlalchemy.org/CHANGES) : 0.4.0beta6 -- - The Session identity map is now *weak referencing* by default, use weak_identity_map=False to use a regular dict. The weak dict we are using is customized to detect instances which are dirty and maintain a temporary strong reference to those instances until changes are flushed. - Mapper compilation has been reorganized such that most compilation occurs upon mapper construction. This allows us to have fewer calls to mapper.compile() and also to allow class-based properties to force a compilation (i.e. User.addresses == 7 will compile all mappers; this is [ticket:758]). The only caveat here is that an inheriting mapper now looks for its inherited mapper upon construction; so mappers within inheritance relationships need to be constructed in inheritance order (which should be the normal case anyway). - added FETCH to the keywords detected by Postgres to indicate a result-row holding statement (i.e. in addition to SELECT). - Added full list of SQLite reserved keywords so that they get escaped properly. - Tightened up the relationship between the Query's generation of eager load aliases, and Query.instances() which actually grabs the eagerly loaded rows. If the aliases were not specifically generated for that statement by EagerLoader, the EagerLoader will not take effect when the rows are fetched. This prevents columns from being grabbed accidentally as being part of an eager load when they were not meant for such, which can happen with textual SQL as well as some inheritance situations. It's particularly important since the anonymous aliasing of columns uses simple integer counts now to generate labels. - Removed parameters argument from clauseelement.compile(), replaced with column_keys. The parameters sent to execute() only interact with the insert/update statement compilation process in terms of the column names present but not the values for those columns. Produces more consistent execute/executemany behavior, simplifies things a bit internally. - Added 'comparator' keyword argument to PickleType. By default, mutable PickleType does a deep compare of objects using their dumps() representation. But this doesn't work for dictionaries. Pickled objects which provide an adequate __eq__() implementation can be set up with PickleType(comparator=operator.eq) [ticket:560] - Added session.is_modified(obj) method; performs the same history comparison operation as occurs within a flush operation; setting include_collections=False gives the same result as is used when the flush determines whether or not to issue an UPDATE for the instance's row. - Added schema argument to Sequence; use this with Postgres /Oracle when the sequence is located in an alternate schema. Implements part of [ticket:584], should fix [ticket:761]. - Fixed reflection of the empty string for mysql enums. - Changed MySQL dialect to use the older LIMIT offset, limit syntax instead of LIMIT l OFFSET o for folks using 3.23. [ticket:794] - Added 'passive_deletes=all' flag to relation(), disables all nulling-out of foreign key attributes during a flush where the parent object is deleted. - Column defaults and onupdates, executing inline, will
