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 <javascript:>> > 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 <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com<javascript:> > . > 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.
