On May 23, 2013, at 2:10 PM, Sean Lynch <techni...@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+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.
