[okfn-help] sqlalchemy model sessions

Fri Mar 14 14:27:55 GMT 2008

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 
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).

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.

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 
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.... ;-)

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

Best wishes,

John.

Rufus Pollock wrote:
> John Bywater wrote:
>> Hi Rufus,
>>
>> I'm having trouble with sessions and threads.....
>
> We need to be a little careful here. The setup we're using should 
> provide threadlocal sessions:
>
> <http://www.sqlalchemy.org/docs/04/session.html#unitofwork_contextual>
>
> Also vdm makes heavy use of this setup so if we start messing about 
> too much with the session there will be problems.
>
>> Do you mind if we move to having explicit sessions in microfacts? It 
>> would seem to solve the problem I'm experiencing where sqlalchemy 
>> bitches that my object:
>>
>> "'X' is already attached to session 'Y' (this is 'Z')"
>>
>> http://www.mail-archive.com/sqlalchemy-users@lists.sourceforge.net/msg03361.html 
>>
>>
>> I regenerate this problem when I create from my web browser a second 
>> thread or a second factlet after having previously restarted paster. 
>> That is, when the create mode class makes a call to 
>> session.save(new_entity) only the first create request is successful, 
>> and succeeding requests all fail with this error. With the call is 
>> not made, the created object is never persistent, but there is no error.
>
> This seems a pylons issue. It should be clearing the session 
> (model.session.clear at the start of each run). I implemented 
> sqlalchemy into pylons following:
>
> <http://docs.pythonweb.org:8080/display/pylonscookbook/SQLAlchemy+0.4+for+people+in+a+hurry> 
>
>
> We should check this stuff first (perhaps we aren't clearing the 
> (model) session as we should be). In fact just looking i notice it is 
> marked as deprecated but at least it indicates what i did and where 
> you can find the sqlalc setup code:
>
> myapp/config/environment.py
>
>> Is the current model session config the Pylons project initial 
>> default, ie what paster pastes? We're having problems, so let's make 
>> it explicit?
>
> I'm not clear what should be explicit but if you mean we should clear 
> the session ourselves then that may be necessary.
>
> ~rufus
>
>
>