Can SQLAlchemy execute multiple select statements in a single round trip?

[Sean Lynch]

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).

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

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.

[Michael Bayer]

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.
 
 

[Claudio Freire]

On Thu, May 23, 2013 at 3:32 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:
> 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.

In postgres, it could be implemented with Async I/O and multiple
cursors, but sadly Async is something of a global pool configuration,
not something you can turn on/off per call.

[Sean Lynch]

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).

[Charlie Clark]

Am 23.05.2013, 21:03 Uhr, schrieb Sean Lynch <tech...@gmail.com>:

> 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).

Support isn't limited to MySQLdb but as the feature is not detailed in the
Python DB-API, each driver does it differently if at all. Eg.
mysql-connector has the format

cursor.execute(sql, multi=True)

You can always get to the underlying connection and cursor as Mike
explained to me last week:

conn = sql.session.connection().connection
cursor = conn.cursor()
# do your magic

For an overview of what databases support what degree of functionality you
can do worse than check the mxODBC documentation which provides detailed
information for the most common backends and a unified Python interface to
them.

http://www.egenix.com/products/python/mxODBC/doc/

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Michael Bayer]

IMHO stuffing async calls and such in an attempt to get two statements to go at once is deeply beyond all lines of diminishing returns :).

[Michael Bayer]

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 ?

[Sean Lynch]

Not within one of my SQLAlchemy apps, but I have an NHibernate application where the database and application servers are in different data centers (out of my control) and thus using .future() calls saves a good bit I/O time.  After seeing the ActiveRecord::Futures project show up on https://github.com/languages/Ruby, I was curious if SQLAlchemy had a similar feature / capability.  

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/-8dIb-VX1tI/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Claudio Freire]

On Thu, May 23, 2013 at 4:21 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:
>> In postgres, it could be implemented with Async I/O and multiple
>> cursors, but sadly Async is something of a global pool configuration,
>> not something you can turn on/off per call.
>
> IMHO stuffing async calls and such in an attempt to get two statements to go at once is deeply beyond all lines of diminishing returns :).

If it means making all of SA async, totally. If it could be done only
for those queries, it would be wonderful. But it's not so.

[Charlie Clark]

Am 23.05.2013, 21:50 Uhr, schrieb Sean Lynch <tech...@gmail.com>:

> Not within one of my SQLAlchemy apps, but I have an NHibernate
> application
> where the database and application servers are in different data centers
> (out of my control) and thus using .future() calls saves a good bit I/O
> time. After seeing the ActiveRecord::Futures project show up on
> https://github.com/languages/Ruby, I was curious if SQLAlchemy had a
> similar feature / capability.

I'm not sure how related this is to your problem but ActiveRecord needs
something like this because it has a very poor model with lots of I/O to
the database, SQLAlchemy gives you the flexibility to decide how you want
your queries processed.

With a persistent connection I wouldn't have thought it made much
difference where the servers are.