[okfn-help] sqlalchemy model sessions

Mon Mar 17 11:19:10 GMT 2008

John Bywater wrote:
> I've just committed some changes that make it all work again, following 
> this page:
> 
> http://www.sqlalchemy.org/docs/04/session.html
> 
> The main issue for me was that the created object needs committing to 
> the database before you get an id. You can do this by Session.commit() 
> but I'm not sure if you can rollback() afterwards. There are tests that 

You definitely do *not* need to commit to get an id you just need to do 
a flush (we discussed this a little last week and you can see me making 
exactly these changes in  and as shown by changes I made to the modes 
code last week [1]).

[1]:<http://knowledgeforge.net/microfacts/trac/changeset/30>

> fail when the entity isn't committed, as it doesn't have an id. We might 
> need to manage things a bit more carefully than just a general 
> non-commit and general rollback (I think).

No that should be fine if you use flush rather than commit. If we really 
need to use commit we might, for those tests, just move to explicit 
removal of any data created.

> Certainly, I don't think we want to nest Pylons controller transactions 
> across the WSGI interface. In other words, I think that in the 
> controller test case setup() and teardown() methods we can expect to 
> find construction and destruction of domain object fixtures that are 
> fully committed to the database, so that the controller session can work 
> and be tested in exactly the same way as it will run in production.

That should be achievable using the flush methods but as I said if that 
isn't possible we can do explicit removal.

> For example, if we are testing an entity get request, then we want to 
> have an entity committed to the database before the test. And then we 

We are using the term 'commit' in 2 different ways here :)

a) Object is no longer just in memory but has been put into the DB (sql 
insert/update) has been run

b) We have committed the current db transaction (if there is one).

Clearly a) needs to be done if you want e.g. the id and that is only 
generated by the db (of course one solution would be to move id creation 
away from the db but that is another story). But b) is only necessary if 
you really need to be managing transactions.

> want to delete it afterwards. Otherwise there isn't an id to refer to. 
> So we need to put those various delete() steps back in to the teardown() 
> methods of the tests I wrote.... ;-)

I don't think so as I hope is clear above. Reading those commit messages 
can sometimes be useful :)

> The tests run through and thread create/update works in the browser, but 
> the tests leave a lot of bits in the database. :-)

OK but if we move back to wrapping the unittests in a transaction that 
problem should go away again I hope.

~rufus