Hi *,
This is as much a question as it is a write up of the issue I am
fighting with. To summarize: my problem was caused by the sqlite3 python
module inserting commits automatically. I think it should not not that!!
today I ran into a problem with rolling back DDL requests to SQLite.
Interestingly, this works just fine from the sqlite3 command line
utility.
Here is a minimal example to illustrate:
-----
from sqlalchemy import engine
engine = create_engine("sqlite:///test.sqlite", echo=True)
conn = engine.connect()
txn = conn.begin():
conn.execute("create table demo (foo varchar, bar varchar)")
txn.rollback()
-----
I am actually using DDL instances for those requests and they are
creating triggers, but either way, nothing is rolled back.
The output is:
INFO sqlalchemy.engine.base.Engine.0x...f090 BEGIN
INFO sqlalchemy.engine.base.Engine.0x...f090 create table demo (foo
varchar, bar varchar)
INFO sqlalchemy.engine.base.Engine.0x...f090 ()
INFO sqlalchemy.engine.base.Engine.0x...f090 ROLLBACK
Typing the exactly same commands into the sqlite3 command line interface
shows that it is in fact rolling back fine.
I reproduced the same thing using the sqlite3 python bindings directly:
-----
from sqlite3 import connect
c = connect("test.sqlite")
c.execute("begin")
c.execute("create table demo (foo varchar, bar varchar)")
c.rollback()
-----
Same problem. Now, I can't really infer from the sqlite3 documentation
how transactions are managed. After a bit of experiment, I found out
that passing isolation_level=None to the sqlite3.connect function gives
me the expected behaviour: My changes are rolled back.
However, when passing this same option to create_engine, it has no
effect. Which does not surprise me given that the documentation at
http://www.sqlalchemy.org/docs/reference/dialects/sqlite.html only talks
about sending a pragma down to each sqlite connection, while the
misbehaviour seems to be caused by the sqlite3 module adding "commit"
instructions into the command stream.
More precisely, this code here automatically inserts a commit in front
of all commands send to sqlite which are not select, update, delete,
insert, replace:
http://code.python.org/hg/branches/release2.6-maint/file/7fa70e059572/Modules/_sqlite/cursor.c#l571
So, as a stop gap measure, how do I pass isolation_level=None to
sqlite3.connect via SA?
Thanks!
Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
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.
