Hi Conor, you were totally right in that I have to close the session, and then the objects relating to my InnoDB can be re-queried and they have what I expect. This is kind of a pain, as I'd like to be able to update those objects continuously. I've modified my code so the session closes when I complete every request to the webserver. It should work well once I add in the code to all my tests to re-acquire the objects.
thanks for your help, Conrad On Sep 29, 12:05 pm, Conor <[email protected]> wrote: > 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.
