Don Dwiggins wrote: > > I've started playing somewhat seriously with the SQL expression subset > of SA as a replacement for some direct SQL code (and as a possible first > step to heavier use of SA). As part of this, I took a query containing > several selects, which naturally returns several result sets. In my > current code (using pyodbc), I use cursor.nextset to collect all the > results. (I'm collecting several lists that will be used to populate > combo boxes in the front end. I could use several queries, but I > thought "why make multiple trips to the DB?") > > I tried this with SA, using text(), putting the result object into a > variable. I was able to fetchall() to get the first set, but the object > lacks a "nextset". Fooling around, I found that I could do > "result.cursor.cursor.nextset()", but attempting to fetchall dies; > apparently, the underlying cursor has been closed. > > I have no specific request at this point, except to suggest an extension > to ResultProxy to handle this part of the DBAPI cursor API as well. For > my purposes, I'll either live with multiple SA execute's or just drop > down to pyodbc for this.
well, second request in the past week or so to support nextset(). Right now ResultProxy has an autoclose feature such that as long as you consume its results, you don't have to issue a close() to immediately close it. If you don't trigger autoclose, and don't close(), the cursor/connection will close when the resultproxy is garbage collected. However its not always easy to ensure that the result is GC'ed before some other connection attempts to acquire a lock on the table - a big example is postgresql-related unit tests that try to DROP TABLE after the test is done. This issue is 100 times worse on Jython which does not feature synchronous gc. So the basic idea of autoclose is critical. So here one thing that would have to happen is, autoclose needs to call cursor.nextset(), if available, to ensure that it returns None. A simple dialect flag will tell us if nextset() is available or not. Right off I worry about the prospect of nextset() adding additional network traffic which in virtually all cases would be wasted. I would consider requiring a create_engine() option to enable this check if this is the case. The second thing is, and I don't know the behavior here, is that ResultProxy needs a working cursor.description in order to fetch results. This means that cursor.nextset() needs to faithfully rewrite its cursor.description when called to match the new result set, and ResultProxy needs to re-establish its internal dictionaries around this new description. I hope pyodbc does this but I wouldn't be completely surprised if they don't. Finally, SQLA's own execution facilities certainly aren't aware of execute() involving multiple statements, i.e. that of stringing together three select() constructs for example, and its unlikely this feature would be considered. Most DBAPIs do not support multiple result sets and there's no clear advantage to stringing together several SELECT constructs on a semicolon versus executing them each distinctly. DBAPIs may also prohibit even allowing multiple statements in a single string since its an extremely common and 18-wheeler-sized security hole. The pyodbc dialect does use nextset() internally to overcome a particular quirk it has regarding "select scope_identity()", but that's because its required for the workaround. Otherwise I've yet to see any clear advantages to their usage, except for the usage of stored procedures which return them. It has been argued that such SPs increase efficiency by pulling multiple result sets in one go, which is fine. But at the execution side its still just one statement, not a string of them. > > Thanks for listening, > -- > Don Dwiggins > Advanced Publishing Technology > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
