Re: [sqlalchemy] autocommit on for DDL

2011-01-27 Thread A.M. 

On Jan 26, 2011, at 7:47 PM, Michael Bayer wrote:

 
 On Jan 26, 2011, at 6:32 PM, A.M. wrote:
 
 Well, I spoke too soon :( What is the mistake in the following sample code 
 which causes the COMMITs to be emitted? Setting autocommit to either True or 
 False emits the same SQL. I think this is a case of staring at the same code 
 too long causing brain damage- thanks for your patience and help!
 
 from sqlalchemy.engine import create_engine
 from sqlalchemy.orm.session import sessionmaker
 from sqlalchemy.orm import scoped_session
 from sqlalchemy.schema import DDL,MetaData,Table
 
 engine = create_engine('postgresql://localhost/test',echo=True)
 session = scoped_session(sessionmaker(bind=engine))
 metadata = MetaData()
 metadata.bind = engine
 
 Table('test1',metadata)
 Table('test2',metadata)
 metadata.create_all()
 
 metadata.create_all() looks at the bind attribute, then uses it to execute 
 each DDL statement.   The bind here is an engine so it uses connectionless 
 execution.  connectionless execution is usually autocommit as documented 
 here:  
 http://www.sqlalchemy.org/docs/core/connections.html#connectionless-execution-implicit-execution
 
 to emit create_all() in a transaction:
 
 conn = engine.connect()
 with conn.begin():
   metadata.create_all(conn)

Ugh- thanks for being patient with a noob- I had erroneously assumed that the 
creation of a session would assume responsibility for transaction management 
like the zope transaction handler. It makes sense now that the session is 
exclusively specific to ORM management- the section Joining a Session into an 
External Transaction helped to clear things up- there is indeed some 
interaction between connection and session transactions.

Cheers,
M


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] autocommit on for DDL

2011-01-26 Thread A.M. 
Hello,

While working on a database test with nose, I dug into sqlalchemy 0.6.6 until I 
found these lines:

class DDLElement(expression.Executable, expression.ClauseElement):
Base class for DDL expression constructs.

_execution_options = expression.Executable.\
_execution_options.union({'autocommit':True})

In my nose test against postgresql, I emit a bunch of DDL, perform the test and 
then roll back the whole shebang which conveniently makes it look like I didn't 
touch the database. Obviously, the emitted commits were getting in my way, so I 
wrote this:

class NoCommitDDL(DDL):
def __init__(self,*args,**kw):
super(NoCommitDDL,self).__init__(*args,**kw)
unfrozen = dict(self._execution_options)
del unfrozen['autocommit']
self._execution_options = frozendict(unfrozen)

DDL = NoCommitDDL

I still feel like I am missing something though. I understand that PostgreSQL 
is perhaps one of few databases to allow for transaction-aware DDL, but why is 
a commit emitted for the DDL for any database, when the database makes it 
implied anyway?

Thanks.

Cheers,
M

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] autocommit on for DDL

2011-01-26 Thread Michael Bayer 

On Jan 26, 2011, at 5:16 PM, A.M. wrote:

 Hello,
 
 While working on a database test with nose, I dug into sqlalchemy 0.6.6 until 
 I found these lines:
 
 class DDLElement(expression.Executable, expression.ClauseElement):
Base class for DDL expression constructs.
 
_execution_options = expression.Executable.\
_execution_options.union({'autocommit':True})
 
 In my nose test against postgresql, I emit a bunch of DDL, perform the test 
 and then roll back the whole shebang which conveniently makes it look like I 
 didn't touch the database. Obviously, the emitted commits were getting in my 
 way, so I wrote this:
 
 class NoCommitDDL(DDL):
def __init__(self,*args,**kw):
super(NoCommitDDL,self).__init__(*args,**kw)
unfrozen = dict(self._execution_options)
del unfrozen['autocommit']
self._execution_options = frozendict(unfrozen)
 
 DDL = NoCommitDDL
 
 I still feel like I am missing something though. I understand that PostgreSQL 
 is perhaps one of few databases to allow for transaction-aware DDL, but why 
 is a commit emitted for the DDL for any database, when the database makes it 
 implied anyway?

Several databases offer transactional DDL including Postgresql.SQLA doesn't 
differentiate between these backends - it emits COMMIT after any statement that 
it considers to be an autocommit statement.  autocommit means, commit this 
statement after completion, if a transaction is not in progress.

From this it follows that if you'd like to emit several DDL statements in a 
transaction, the usage is no different for DDL expressions than for any other 
kind of DML statement (i.e insert/update/delete).   Use 
connection.begin()/transaction.commit() as documented at:

http://www.sqlalchemy.org/docs/core/connections.html#using-transactions 
.

autocommit is described right after that:


http://www.sqlalchemy.org/docs/core/connections.html#understanding-autocommit

Also the public API for _execution_options is the execution_options() 
generative call: 


http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=execution_options#sqlalchemy.sql.expression.Executable.execution_options
 .

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] autocommit on for DDL

2011-01-26 Thread A.M. 

On Jan 26, 2011, at 5:45 PM, Michael Bayer wrote:
 
 
 From this it follows that if you'd like to emit several DDL statements in a 
 transaction, the usage is no different for DDL expressions than for any other 
 kind of DML statement (i.e insert/update/delete).   Use 
 connection.begin()/transaction.commit() as documented at:
 
   http://www.sqlalchemy.org/docs/core/connections.html#using-transactions 
 .
 
 autocommit is described right after that:
 
   
 http://www.sqlalchemy.org/docs/core/connections.html#understanding-autocommit
 
 Also the public API for _execution_options is the execution_options() 
 generative call: 
 
   
 http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=execution_options#sqlalchemy.sql.expression.Executable.execution_options
  .

Thanks for the prodding- I figured out my bug. Here is sample code that 
demonstrates a little surprise. 

First, this code that emits:
BEGIN (implicit)
SELECT 1
ROLLBACK

=
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.schema import DDL

engine = create_engine('postgresql://localhost/test',echo=True)
session = scoped_session(sessionmaker(bind=engine))

DDL(SELECT 1).execute(bind=session)
session.rollback()
=

and here is the buggy code which emits:
SELECT 1
COMMIT

from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.schema import DDL

engine = create_engine('postgresql://localhost/test',echo=True)
session = scoped_session(sessionmaker(bind=engine))

DDL(SELECT 1).execute(bind=engine)
session.rollback()
=

Can you spot the difference? The DDL in the first code is bound to the session 
and the latter code mistakenly binds to the engine for execution resulting in 
two different execution paths. In hindsight, I guess it makes sense, but it 
certainly was not easy to find...

Cheers,
M

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] autocommit on for DDL

2011-01-26 Thread A.M. 
Well, I spoke too soon :( What is the mistake in the following sample code 
which causes the COMMITs to be emitted? Setting autocommit to either True or 
False emits the same SQL. I think this is a case of staring at the same code 
too long causing brain damage- thanks for your patience and help!

from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.schema import DDL,MetaData,Table

engine = create_engine('postgresql://localhost/test',echo=True)
session = scoped_session(sessionmaker(bind=engine))
metadata = MetaData()
metadata.bind = engine

Table('test1',metadata)
Table('test2',metadata)
metadata.create_all()

2011-01-26 18:27:48,284 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select 
version()
2011-01-26 18:27:48,284 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
2011-01-26 18:27:48,286 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select 
current_schema()
2011-01-26 18:27:48,286 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
2011-01-26 18:27:48,288 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select 
relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where 
n.nspname=current_schema() and lower(relname)=%(name)s
2011-01-26 18:27:48,288 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {'name': 
u'test1'}
2011-01-26 18:27:48,290 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select 
relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where 
n.nspname=current_schema() and lower(relname)=%(name)s
2011-01-26 18:27:48,290 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {'name': 
u'test2'}
2011-01-26 18:27:48,291 INFO sqlalchemy.engine.base.Engine.0x...6cd0 
CREATE TABLE test1 (
)


2011-01-26 18:27:48,291 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
2011-01-26 18:27:48,292 INFO sqlalchemy.engine.base.Engine.0x...6cd0 COMMIT
2011-01-26 18:27:48,293 INFO sqlalchemy.engine.base.Engine.0x...6cd0 
CREATE TABLE test2 (
)


2011-01-26 18:27:48,293 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
2011-01-26 18:27:48,294 INFO sqlalchemy.engine.base.Engine.0x...6cd0 COMMIT

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] autocommit on for DDL

2011-01-26 Thread Michael Bayer 

On Jan 26, 2011, at 6:10 PM, A.M. wrote:

 
 Thanks for the prodding- I figured out my bug. Here is sample code that 
 demonstrates a little surprise. 
 
 First, this code that emits:
 BEGIN (implicit)
 SELECT 1
 ROLLBACK
 
 =
 from sqlalchemy.engine import create_engine
 from sqlalchemy.orm.session import sessionmaker
 from sqlalchemy.orm import scoped_session
 from sqlalchemy.schema import DDL
 
 engine = create_engine('postgresql://localhost/test',echo=True)
 session = scoped_session(sessionmaker(bind=engine))
 
 DDL(SELECT 1).execute(bind=session)

that's some surprise - a Session was never intended to be used as a bind and 
that argument on DDL.execute() is documented as expecting a Connection or 
Engine.  Its kind of just coincidence it happens to call .execute() on the 
thing it gets and it works.Dynamic typing FTW I guess


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] autocommit on for DDL

2011-01-26 Thread Michael Bayer 

On Jan 26, 2011, at 6:32 PM, A.M. wrote:

 Well, I spoke too soon :( What is the mistake in the following sample code 
 which causes the COMMITs to be emitted? Setting autocommit to either True or 
 False emits the same SQL. I think this is a case of staring at the same code 
 too long causing brain damage- thanks for your patience and help!
 
 from sqlalchemy.engine import create_engine
 from sqlalchemy.orm.session import sessionmaker
 from sqlalchemy.orm import scoped_session
 from sqlalchemy.schema import DDL,MetaData,Table
 
 engine = create_engine('postgresql://localhost/test',echo=True)
 session = scoped_session(sessionmaker(bind=engine))
 metadata = MetaData()
 metadata.bind = engine
 
 Table('test1',metadata)
 Table('test2',metadata)
 metadata.create_all()

metadata.create_all() looks at the bind attribute, then uses it to execute 
each DDL statement.   The bind here is an engine so it uses connectionless 
execution.  connectionless execution is usually autocommit as documented here:  
http://www.sqlalchemy.org/docs/core/connections.html#connectionless-execution-implicit-execution

to emit create_all() in a transaction:

conn = engine.connect()
with conn.begin():
metadata.create_all(conn)



 
 2011-01-26 18:27:48,284 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select 
 version()
 2011-01-26 18:27:48,284 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
 2011-01-26 18:27:48,286 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select 
 current_schema()
 2011-01-26 18:27:48,286 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
 2011-01-26 18:27:48,288 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select 
 relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where 
 n.nspname=current_schema() and lower(relname)=%(name)s
 2011-01-26 18:27:48,288 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {'name': 
 u'test1'}
 2011-01-26 18:27:48,290 INFO sqlalchemy.engine.base.Engine.0x...6cd0 select 
 relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where 
 n.nspname=current_schema() and lower(relname)=%(name)s
 2011-01-26 18:27:48,290 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {'name': 
 u'test2'}
 2011-01-26 18:27:48,291 INFO sqlalchemy.engine.base.Engine.0x...6cd0 
 CREATE TABLE test1 (
 )
 
 
 2011-01-26 18:27:48,291 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
 2011-01-26 18:27:48,292 INFO sqlalchemy.engine.base.Engine.0x...6cd0 COMMIT
 2011-01-26 18:27:48,293 INFO sqlalchemy.engine.base.Engine.0x...6cd0 
 CREATE TABLE test2 (
 )
 
 
 2011-01-26 18:27:48,293 INFO sqlalchemy.engine.base.Engine.0x...6cd0 {}
 2011-01-26 18:27:48,294 INFO sqlalchemy.engine.base.Engine.0x...6cd0 COMMIT
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.