On Dec 1, 2010, at 5:50 AM, Ralph Heinkel wrote:
Hi everybody,
we have the problem that transactions embedded in
'with Session.begin()'
are not committing statements made in raw connections. However
'with engine.begin()' (in 0.5.8)
works fine.
The reason we need to use Session.begin() is that we want to cover
transactions over multiple engines. Raw connections are needed because we
have to run oracle plsql functions and hence need to call 'cursor.call(...)'.
Below you find a simple example. It doesn't call a stored procedure, but the
problem is the same.
By using 'strategy=threadlocal' I would have assumed that I always operate on
the same low level db connection. This seems to be true with
'engine.begin()', but not with 'session.begin()'.
Any idea why?
Thanks for your help,
The Session.begin() statement doesn't touch any of its engines until it
accesses one of them in order to procure a connection, so engine.execute()
isn't related to that interaction, hence threadlocal not really of use when
the Session is used to manage transactions. See
http://www.sqlalchemy.org/docs/core/connections.html#using-the-threadlocal-execution-strategy
for details.
Here you'd use Session.execute() and Session.connection() to get at the
Connection you'd normally get from engine.contextual_connect()
(http://www.sqlalchemy.org/docs/orm/session.html#using-sql-expressions-with-sessions)
.execute() and connection() accept a mapper argument for the case where
individual engines are associated with individual mappers, and both ultimately
call get_bind() which you can override via subclass if desired to accept other
kinds of arguments.
Ralph
ps: Why did the 'engine.begin()' context manager disappear in 0.6.x?
---
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
engine = create_engine('sqlite:///s.db', strategy='threadlocal', echo=True)
Session = scoped_session(sessionmaker(autoflush=True, autocommit=True))
engine.execute('create table testtable (id int, name varchar(20))')
engine.execute(insert into testtable (id, name) values (1, 'otto'))
engine.execute(insert into testtable (id, name) values (2, 'gustav'))
with Session.begin():# does not work
#with engine.begin():# does work !
engine.execute(update testtable set name='ottox28' where id=1)
# the next line returns a low level DBAPI connection obj:
raw_conn = engine.contextual_connect().connection
cur = raw_conn.cursor()
cur.execute(update testtable set name='gustav2' where id=2)
(if you look into s.db with sqlite3 record id=2 is still 'gustav')
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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 sqlalch...@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.
