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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
