[sqlalchemy] Re: 0.4 and server-side cursors
looks like it won't work using psycopg2's named cursor implementation, at least not without some patching. it seems like psycopg should fill in cursor.description after it issues the DECLARE, but it doesn't. you can do it using one normal (non-named) cursor in SA using the method above, but it would probably require some significant work, since you'd have to transform SELECT statements into FETCH statements. anyway, thanks for the fix! On Sep 26, 8:01 pm, Michael Bayer [EMAIL PROTECTED] wrote: 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] 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 -~--~~~~--~~--~--~---
