catherinedevlin
Transaction Support
Does ipython-sql magic support transactions?
I've just run a couple of tests and it seems as if everything is being committed via an execute, rather than respecting BEGIN and COMMIT transaction blocks?
ref: http://docs.sqlalchemy.org/en/rel_0_8/core/connections.html
I started thinking how this might be handled, working from SQLAlchemy: Working with Engines and Connections as a crib.
Imagine the following example:
%%sql
DROP TABLE IF EXISTS transactiontest;
CREATE TABLE transactiontest(testval VARCHAR(20),id INT);
INSERT INTO transactiontest VALUES('test1',1);
INSERT INTO transactiontest VALUES('test2',2);
BEGIN;
UPDATE transactiontest SET testval='test2b' WHERE id=2;
ROLLBACK;
SELECT * FROM transactiontest;
The parsing should start off as normal with connection = engine.connect() and the first few lines being executed as normal via connection.execute(), but when we hit the BEGIN statement we need to invoke something like trans = connection.begin(). Further statements continue as per normal via connection.execute() in a try: block, with trans.rollback() as the exception.
Presumably, if we see a COMMIT or a ROLLBACK we also need to invoke trans.commit() or trans.rollback() as necessary.
I wonder if a cell should be defined as a transaction block by default (i.e. always finishing with trans.commit()), or whether the transaction should be left open to run across several cells? If the latter, it would perhaps be handy to allow an override in the opening line, eg %%sql -nocommit.
The next thing to worry about would be nested transaction blocks which would require further trans2=connection.begin() assignments? It would perhaps make sense to generate a stack or list of trans connections that additional levels of commit can be pushed onto and then commited or rolled back from?
I don't know sqlalchemy but is this strictly necessary? From my understanding their transaction .begin() is used for ORM purposes -- but you should still able to use BEGIN in your sql without it? I'm not sure why ipython-sql is preventing this..
For those still encountering this issue today, I've created a lightweight package for MSSQL/SSMS Jupyter cell and line magic: sqlcmd-magic. Give it a try!
