Re: [sqlalchemy] sqlalchemy does not enforce foreign key constraint
On May 23, 2013, at 07:16 , Shyam Purkayastha shyam.g...@gmail.com wrote: I am trying to play with the sql alchemy ORM based db definition with an inmemory sqlite db. SQLite does not enforce foreign key constraints unless you manually tell it to. You can do that with a PRAGMA foreign_keys=ON statement. See section two of http://www.sqlite.org/foreignkeys.html for the exact details. Wichert. -- 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] distinct on
|Hi all, I wondered if it is possible to execute a partial distinct in sqlalchemy. The following query works in oracle and postgresql: select distinct col1, first_value(col2) over (partition by col1 order by col2 asc) from tmp; How can I do such query in sqlalchemy? Thanks for any help. j | -- 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] distinct on
On 05/23/2013 04:42 AM, jo wrote: |Hi all, I wondered if it is possible to execute a partial distinct in sqlalchemy. The following query works in oracle and postgresql: select distinct col1, first_value(col2) over (partition by col1 order by col2 asc) from tmp; How can I do such query in sqlalchemy? Thanks for any help. j Yes, it is entirely possible. Something like this should do the trick (not tested): from sqlalchemy import select, func from sqlalchemy.sql.expression import over q = select([tmp.c.id.distinct(), over(func.first_value(tmp.c.cid), partition_by=tmp.c.id, order_by=tmp.c.name.asc())]) print(q) SELECT DISTINCT user.id, first_value(user.cid) OVER (PARTITION BY user.id ORDER BY user.name ASC) AS anon_1 FROM user This chapter of the documentation will help with these features and much more: http://docs.sqlalchemy.org/en/latest/core/expression_api.html -- 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] implementing implicit scalar collections
Hi, I've just implemented support for scalar collections for Spyne. (In Spyne terms that's sql serialization of an array of primitives). Seems to be working fine so far. The question is: Is the association proxy the only (read/write) way of doing this? It requires the child table to be mapped, which requires the child table to have a primary key, which is sometimes completely useless. I also have to create another implicit attribute so that the association proxy can fetch the value off of it. Here's the relevant bit: https://github.com/plq/spyne/blob/master/spyne/util/sqlalchemy.py#L563 Here's its test: https://github.com/plq/spyne/blob/master/spyne/test/test_sqlalchemy.py#L917 Setting both columns as primary keys breaks the test: https://gist.github.com/plq/5630698#file-spyne-patch column_property is read-only, so I can't use it. Any suggestions? Another question, instead of deleting, it seems to be updating foreign keys to null and re-inserting data. Why not just delete the old values? Best regards, Burak -- 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] feel dirty, is there a better way?
Hi All,
I have a mixin defined like this:
def add_exclude_constraint(mapper, class_):
table = class_.__table__
elements = [('period', '')]
for col in table.primary_key.columns:
if col.name!='period':
elements.append((col, '='))
table.append_constraint(ExcludeConstraint(*elements))
class Temporal(object):
@declared_attr
def __table_args__(cls):
listen(cls, 'instrument_class', add_exclude_constraint)
return (
CheckConstraint(period != 'empty'::tsrange),
)
period = Column(DateTimeRange(), nullable=False, primary_key=True)
That listen call is the source of the dirty feeling...
What's the right way to do this?
Chris
--
Simplistix - Content Management, Batch Processing Python Consulting
- http://www.simplistix.co.uk
--
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] how to use bind_expression to build an express like 'convert(bit, @value)?
On May 23, 2013, at 1:29 AM, Anthony Kong anthony.hw.k...@gmail.com wrote:
Hi, all,
We are upgrading our application to use SA0.8.0. For reason outside our
control, some XP workstations are using old sybase ODBC driver and we cannot
upgrade them as of yet.
The sybase driver will cause this problem in the following situation:
1) we have a table that have a field of type 'bit' to hold boolean value
(i.e. 0 for false and 1 for true)
2) we need to select subset data out of this table based on this flag
When we run something like
session.query(SubProduct).filter(Product.isTradeable==True)
we get this error message:
DatabaseError: (DatabaseError) Msg 257, Level 16, State 1, Line 1
Implicit conversion from datatype 'CHAR' to 'BIT' is not allowed. Use the
CONVERT function to run this query.
Msg 257, Level 16, State 1, Line 1
Implicit conversion from datatype 'CHAR' to 'BIT' is not allowed. Use the
CONVERT function to run this query.
'SELECT SUBPRODUCT ... WHERE Product.id_tradeable = @id_tradeable_1'
{'@id_tradeable_1': '1'}
I am trying to put in place a workaround: If I can convert the sql statement
to something like so
'SELECT SUBPRODUCT ... WHERE Product.id_tradeable = convert(bit,
@id_tradeable_1)' {'@id_tradeable_1': '1'}
I believe I can avoid the sybase SQL exception regardless of client driver
version
So I implemented this class
import sqlalchemy.types as satypes
from sqlalchemy import func
from sqlalchemy.sql.expression import type_coerce
class SybaseBit( satypes.TypeDecorator):
impl = satypes.Boolean
def bind_expression(self, bindvalue):
# bindvalue = type_coerce(bindvalue, satypes.Integer)
return func.convert(bit, bindvalue)
It failed because 'bit' is not defined there. If I put in a string 'bit', the
generated sql is very close to what I want.
So my question is:
What is the proper way to implement this?
you can get the string bit in there using sqlalchemy.literal_column('bit').
--
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] 0.8.0 engine.base.Connection._safe_close_cursor
Hi, this method references self.connection._logger. Is self.connection._logger guaranteed to exist? -- 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] 0.8.0 engine.base.Connection._safe_close_cursor
while there's no plans to remove it, it's underscored, so that means it could be removed in a future release. On May 23, 2013, at 10:08 AM, Grant McKenzie grant.r.mcken...@gmail.com wrote: Hi, this method references self.connection._logger. Is self.connection._logger guaranteed to exist? -- 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.
[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.
[sqlalchemy] Creating an index if it doesn't exist
How might I convince SQLAlchemy 0.7.9 to create a newly added index on a table
in the event that the index doesn't already exist? This new index is created as
a member of __table_args__; it is not instantiated with index=True. I have many
such index objects to create.
Calling create_all() doesn't appear to issue CREATEs for a table if it already
exists. I think that's because the optional checkfirst parameter defaults to
True.
Setting checkfirst=False breaks because trying to create an already existing DB
object is an error, and most of my DB already exists:
ModelBase.metadata.create_all(checkfirst=False)
ProgrammingError: (ProgrammingError) relation organism already exists
'\nCREATE TABLE organism (\n\tid SERIAL NOT NULL, \n\tname VARCHAR NOT
NULL, \n\tPRIMARY KEY (id)\n)\n\n' {}
I did check stackoverflow, and their unsatisfactory advice was simply to cut
and paste the generated DDL:
http://stackoverflow.com/questions/14419299/adding-indexes-to-sqlalchemy-models-after-table-creation
In a perfect world, I'd like a way to drop a new index onto a column with the
assurance that SQLAlchemy will create it as needed. In my case, this DB is just
a big testing data set, so there is no need for formal migrations.
If this feature doesn't exist, I would settle for a function that would take a
table and conditionally create any missing pieces (specifically indexes).
As a last resort, is the correct way to do this simply to call Index.create for
each index I make?
MB
--
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] Creating an index if it doesn't exist
On 05/23/2013 09:06 PM, Mike Bissell wrote:
How might I convince SQLAlchemy 0.7.9 to create a newly added index on a table
in the event that the index doesn't already exist? This new index is created as
a member of __table_args__; it is not instantiated with index=True. I have many
such index objects to create.
Calling create_all() doesn't appear to issue CREATEs for a table if it already
exists. I think that's because the optional checkfirst parameter defaults to
True.
Setting checkfirst=False breaks because trying to create an already existing DB
object is an error, and most of my DB already exists:
ModelBase.metadata.create_all(checkfirst=False)
ProgrammingError: (ProgrammingError) relation organism already exists
'\nCREATE TABLE organism (\n\tid SERIAL NOT NULL, \n\tname VARCHAR NOT
NULL, \n\tPRIMARY KEY (id)\n)\n\n' {}
I did check stackoverflow, and their unsatisfactory advice was simply to cut
and paste the generated DDL:
http://stackoverflow.com/questions/14419299/adding-indexes-to-sqlalchemy-models-after-table-creation
In a perfect world, I'd like a way to drop a new index onto a column with the
assurance that SQLAlchemy will create it as needed. In my case, this DB is just
a big testing data set, so there is no need for formal migrations.
If this feature doesn't exist, I would settle for a function that would take a
table and conditionally create any missing pieces (specifically indexes).
As a last resort, is the correct way to do this simply to call Index.create for
each index I make?
MB
I would suggest event.listen with a DDL event:
1- first a routine to check if the objects exists (vendors providing the
create if not exists idiom could help here, too):
def should_create(ddl, target, connection, state, **kw):
http://www.sqlalchemy.org/docs/core/schema.html;
row = connection.execute(select relname from pg_class where
relname='{0}'.format(state)).scalar()
return not bool(row)
2- then an event that will be execute when the time is right:
event.listen(Base.metadata, after_create, DDL(create index
lower_value_ix on
dummy_table(lower(value)).execute_if(callable_=should_create,
state=lower_value_ix))
These chapters should be of help:
http://docs.sqlalchemy.org/en/rel_0_7/core/events.html?highlight=after_create#sqlalchemy.events.DDLEvents.after_create
http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#customizing-ddl
--
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.
