Hi - We've been using sqlalchemy (0.5) at work for some new scripts, but I've run up against this problem a couple of times, and I'm wondering if there's a way to structure my code differently to avoid it.
We have some jobs which need to update various column values in the symbol table based on a data file. The general structure of the code is: 1 - grab all the Symbol objects which might need to change (along with some other bits and pieces) in one query 2 - scan through the parsed file, matching file rows to the relevant symbols, 3 - for each row, apply updates to the matched symbol's data if necessary, and write that back to the database Each symbol update is done in its own transaction to avoid inconsistent data being written out if there's a bug which crashes the process halfway through making changes to a specific symbol. The problem I have is as follows. Since I'm committing each change, when I start updating the attributes of the next Symbol object it realises that there's been a commit since it was retrieved, and it requeries to get its latest data. This means that instead of doing one query to get all of the Symbols upfront, and then one for each update (which is what I'd do if it was raw sql), it ends up doing another individual query for each symbol. This means that the job takes much longer to run. I understand why this behaviour is correct in general, but in this specific case I know that nothing else is updating the symbols in any relevant way (since only this job does). Is there any way to tell sqlalchemy not to requery the objects retrieved as a result of query? One solution I found was to do a session.expunge_all after the initial retrieval of the symbols, and then do a session.add when I need to write out a symbol. This avoids the requerying. It required contorting my code a little, but also it means that the Symbol object is disconnected from the session - I can't get related objects any more (for reporting errors, for example). So it's not ideal. Is there somthing better that I'm missing? Thanks xtian -- 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.
