On 09/29/2010 01:47 PM, Conradaroma wrote: > Hi, > > I am currently trying to move my DB tables over to InnoDB from MyISAM. > I am having timing issues with requests and cron jobs that are running > on the server that is leading to some errors. I am quite sure that > transaction support will help me with the problem. I am therefore > transitioning to InnoDB. > > I have a suite of tests which make calls to our webservices REST API > and receive XML responses. The test suite is fairly thorough, and it's > written in Python and uses SQLAlchemy to query information from the > database. When I change the tables in the system from MyISAM to InnoDB > however, the tests start failing. However, the tests aren't failing > because the system isn't working, they are failing because the ORM is > not correctly querying the rows from the database I am testing on. > when I step through the code I see the correct results, but the ORM is > not returning the correct results at all. > > Basic flow is: > > class > UnitTest(unittest.TestCase): > def setUp(self): > # Create a test object in DB that gets affected by the web > server > testObject = > Obj(foo='one') > self.testId = testObject.id > > > session.add(testObject) > > session.commit() > > def tearDown(self): > # Clean up after the test > testObject = > session.query(Obj).get(self.testId) > > > session.delete(testObject) > session.commit() > > def test_web_server(self): > # Ensure the initial state of the > object. > objects = session.query(Obj).get(self.testId) > assert objects.foo == > 'one' > > # This will make a simple HTTP get call on an url that will > modify the DB > response = server.request.increment_foo(self.testId) > > # This one fails, the object still has a foo of > 'one' > # When I stop here in a debugger though, and look at the > database, > # The row in question actually has the correct value in the > database. > # ???? > objects = > session.query(Obj).get(self.testId) > assert objects.foo == 'two' > Using MyISAM tables to store the object and this test will pass. > However, when I change to InnoDB tables, this test will not pass. What > is more interesting is that when I step through the code in the > debugger, I can see that the datbase has what I expect, so it's not a > problem in the web server code. I have tried nearly every combination > of expire_all, autoflush, autocommit, etc. etc, and still can't get > this test to pass. > > I can provide more info if necessary. > > Thanks, Conrad > >
Although you have expunged everything from the unit test's session, it is still holding onto a DB connection that is in a transaction that began in your "Ensure the initial state of the object" block. Since the server made its DB changes after the transaction started, the DB connection will not see those changes. You need to call "session.close()" just before your "This one fails" block. This will cause the session to expunge all objects and rollback any transaction it is in, and the next query will start in a new transaction which will see the changes. The reason (I assume) that setting autocommit=True did not work is that it SQLAlchemy only autocommits when it detects an INSERT/UPDATE/DELETE-type query. -Conor -- 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.
