Re: [sqlalchemy] Can SQLAlchemy execute multiple select statements in a single round trip?
Am 23.05.2013, 21:50 Uhr, schrieb Sean Lynch techni...@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. 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 -- 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.
[sqlalchemy] Can SQLAlchemy execute multiple select statements in a single round trip?
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. -- 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.
Re: [sqlalchemy] Can SQLAlchemy execute multiple select statements in a single round trip?
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
Re: [sqlalchemy] Can SQLAlchemy execute multiple select statements in a single round trip?
On Thu, May 23, 2013 at 3:32 PM, Michael Bayer mike...@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. -- 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.
Re: [sqlalchemy] Can SQLAlchemy execute multiple select statements in a single round trip?
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.comjavascript: . Visit
Re: [sqlalchemy] Can SQLAlchemy execute multiple select statements in a single round trip?
On May 23, 2013, at 2:37 PM, Claudio Freire klaussfre...@gmail.com wrote: On Thu, May 23, 2013 at 3:32 PM, Michael Bayer mike...@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. 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 :). -- 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.
Re: [sqlalchemy] Can SQLAlchemy execute multiple select statements in a single round trip?
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
Re: [sqlalchemy] Can SQLAlchemy execute multiple select statements in a single round trip?
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. On Thu, May 23, 2013 at 3:22 PM, Michael Bayer mike...@zzzcomputing.comwrote: 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-futureshttp://ayende.com/blog/3979/nhibernate-futures) or ActiveRecord::Futures (https://github.com/leoasis/** 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/ 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-statementshttp://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)
Re: [sqlalchemy] Can SQLAlchemy execute multiple select statements in a single round trip?
On Thu, May 23, 2013 at 4:21 PM, Michael Bayer mike...@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. -- 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.
