you're seeing the behavior of the engine as involves a SQLite :memory: database, which only exists in the state of one DBAPI connection at a time and therefore is impossible to use for testing transactional concurrency.
http://www.sqlalchemy.org/docs/dialects/sqlite.html#threading-pooling-behavior This is slightly out of date: SingletonThreadPool only applies to :memory: databases , so with file-based you're OK http://www.sqlalchemy.org/trac/wiki/FAQ#IamusingmultipleconnectionswithaSQLitedatabasetypicallytotesttransactionoperationandmytestprogramisnotworking On Sep 1, 2011, at 8:26 AM, Ben Hearsum wrote: > Hi all, > > I've been writing some tests for an application that makes use of > transactions and came across a strange issue: the Transactions in my > tests seemed to be committed before commit() was called. After some > head scratching I reduced the problem to a minimal test case and found > that everything works as I expected it to if I used a real file, but > not if I use an in-memory database. > > Based on my new understanding of in-memory databases I _think_ this > makes sense, because there's no real way to "lock" them or have > multiple, separate, connections -- but I would really appreciate > someone confirming this, or otherwise explaining what's going on here. > > Here's my test case and the output of it for an in-memory and a real > file database: > [] bhearsum@voot:~/tmp$ cat test2.py > #!/usr/bin/env python > > from sqlalchemy import Table, Column, Integer, MetaData, create_engine > > import sys > engine = create_engine('sqlite:///%s' % sys.argv[1]) > metadata = MetaData(engine) > t = Table('test', metadata, Column('foo', Integer), Column('bar', > Integer)) > t2 = Table('test2', metadata, Column('baz', Integer), Column('crap', > Integer)) > metadata.create_all() > t.insert().execute(dict(foo=1, bar=100)) > t.insert().execute(dict(foo=2, bar=200)) > > conn = metadata.bind.connect() > trans = conn.begin() > conn.execute(t.update(values=dict(bar=500)).where(t.c.foo==1)) > print engine.execute(t.select()).fetchall() > trans.commit() > [] bhearsum@voot:~/tmp$ python test2.py :memory: > [(1, 500), (2, 200)] > [] bhearsum@voot:~/tmp$ python test2.py newdb.db > [(1, 100), (2, 200)] > > -- > 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.
