Holding open a transaction across several web requests is in general a bad idea. HTTP is stateless - the requests could be spaced hours apart or not at all, leaving the transaction hanging open permanently. While the transaction is open, locks may be held, preventing concurrent activities upon the affected rows from proceeding.
That said, one simple way to do this would be to stow away the transaction-holding object, assuming its a Session(), in some kind of storage (most likely a dictionary) where it is accessed by each successive request. The full series of requests would need to take place in a single process. A more robust method, if you're on Postgresql or MySQL, would be to defer the commit of several individual transactions using two phase xids. This would enable a collection of per-connection transactions to participate in a larger "two phase" operation. Each request emits the PREPARE command with a distinct xid, storing the transaction's state on disk. Then COMMIT PREPARED is emitted for each. Docs on this for Postgresql are at http://www.postgresql.org/docs/current/static/sql-prepare-transaction.html . The SQLAlchemy Connection object can provide a conversation like this as follows: from sqlalchemy import * e = create_engine('postgresql://scott:tiger@localhost/test', echo=True) e.execute("drop table if exists foo") e.execute("create table foo(id integer)") c = e.connect() t1 = c.begin_twophase() c.execute("insert into foo (id) values (1)") t1.prepare() c.close() c = e.connect() t2 = c.begin_twophase() c.execute("insert into foo (id) values (2)") t2.prepare() c.close() c = e.connect() t3 = c.begin_twophase() c.execute("insert into foo (id) values (3)") t3.prepare() c.close() c = e.connect() for t in (t1, t2, t3): c.commit_prepared(t.xid, recover=True) print e.execute("select * from foo").fetchall() If using that with a Session, you'd need to embed the Session in the transaction using a scheme similar to the one described at http://www.sqlalchemy.org/docs/orm/session.html#joining-a-session-into-an-external-transaction (just not within the context of a unit test). On Feb 21, 2011, at 12:35 AM, Andrey Gladilin wrote: > I have a big RESTfull API in my python web application using Werkzeug > and SQLAlchemy. Could you advise some way to use a single transaction > across several http calls? > > Thanks! > > -- > 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.
