Features like this are difficult to justify as they would vastly complicate the internals and add lots of new bugs and issues for an exceedingly small benefit. Have you identified a real speed issue with some particular series of statements ?
On May 23, 2013, at 3:03 PM, Sean Lynch <techni...@gmail.com> wrote: > Thank you for the very detailed reply. I know with NHibernate a lot of their > drivers don't support it and under the hood it will fall back to executing > them immediately when a .future() is placed. Maybe SQLAlchemy could do > something similar based on the support of the current DBAPI (which at this > point looks to just be MySQLdb). > > On Thursday, May 23, 2013 2:32:04 PM UTC-4, Michael Bayer wrote: > > On May 23, 2013, at 2:10 PM, Sean Lynch <tech...@gmail.com> wrote: > >> Does SQLAlchemy have any builtin support to execute multiple SELECT >> statements in a single round trip to the database, similar to NHibernate's >> .future() call (http://ayende.com/blog/3979/nhibernate-futures) or >> ActiveRecord::Futures (https://github.com/leoasis/activerecord-futures). > > not currently, no, this is not something that most DBAPI implementations have > support for. DBAPI does have specified support for multiple result sets, as > when a stored procedure returns multiple result sets; SQLAlchemy has a long > standing feature request to add support for this which includes part of a > patch, but there's been little demand for this feature since it tends to be > specific to stored procedures. Most DBAPIs do not implement this feature, > the main exception being the SQL Server DBAPIs and apparently MySQLdb (but > notably not oursql). > >> >> I came across the SQLAlchemy-Future project >> (http://lunant.github.io/SQLAlchemy-Future/), but it appears to just spawn a >> new thread for each query so it doesn't block the normal flow, instead of >> batching multiple queries together into a single trip. >> >> I've seen examples in SQLAlchemy on how to do this for INSERTs, but not for >> SELECT queries. >> http://docs.sqlalchemy.org/ru/latest/core/tutorial.html#executing-multiple-statements > > that's not quite the same thing. DBAPI has a feature whereby you can pass a > statement once and send a list of parameter sets. The DBAPI can then > optimize as it is able to, how to invoke that single statement for all the > parameter lists given. This usually means that the DBAPI creates a prepared > statement which it then executes once for each parameter set. It is a lot > faster than calling execute() repeatedly via the Python call, but not as fast > as if just one round trip were made to the database. > >> If not, it would be nice to call .future() or .promise() on instead of a ` >> sqlalchemy.orm.query.Query` instance instead of .all() to batch multiple >> queries and have them executed in a single round trip. The way NHibernate >> works is it will execute all the queries called with .future() when an >> attempt is made to access the results of one of the query's results. So if >> you've called .future() on 5 queries, but start to access the results from >> the 3 query before .future() was called on the remaining 2 queries, it will >> make 2 round trips. > > its not something DBAPI has consistent support for, a few backends allow > joining of statements with semicolons like SQL server, but for the most > prominently used systems like Postgresql and SQLite, it's not generally > possible. > > The test below illustrates DBAPI support for this feature, only MySQLdb > supports it (not OurSQL): > > def test(conn, stmt="select 1; select 2"): > cursor = conn.cursor() > try: > cursor.execute(stmt) > print cursor.fetchall() > cursor.nextset() > except Exception, e: > print e > else: > print cursor.fetchall() > > > import MySQLdb > conn = MySQLdb.connect(user="scott", passwd="tiger", db="test") > print "\nMySQLdb\n---------" > test(conn) > > import oursql > conn = oursql.connect(user="scott", passwd="tiger", db="test") > print "\noursql\n---------" > test(conn) > > import psycopg2 > conn = psycopg2.connect(user="scott", password="tiger", database="test") > print "\npsycopg2\n---------" > test(conn) > > import sqlite3 > conn = sqlite3.connect(":memory:") > print "\nsqlite\n---------" > test(conn) > > import kinterbasdb > conn = kinterbasdb.connect(dsn="/Users/classic/foo.fdb", user="scott", > password="tiger") > print "\nfirebird\n--------" > test(conn, "select 1 FROM rdb$database; select 2 FROM rdb$database") > > MySQLdb > --------- > ((1L,),) > ((2L,),) > > oursql > --------- > (1064, "You have an error in your SQL syntax; check the manual that > corresponds to your MySQL server version for the right syntax to use near > 'select 2' at line 1", None) > > psycopg2 > --------- > [(2,)] > not supported by PostgreSQL > > sqlite > --------- > You can only execute one statement at a time. > > firebird > -------- > (-104, 'isc_dsql_prepare: \n Dynamic SQL Error\n SQL error code = -104\n > Token unknown - line 1, column 29\n select') > 'kinterbasdb.Cursor' object has no attribute 'nextset' > > > > > >> >> -- >> 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+...@googlegroups.com. >> To post to this group, send email to sqlal...@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> > > > -- > 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?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
