SA doesn't update table

[Geo]

I have a query to join another two querys which are written as
subqueries:

paid_120_count = session.query(Capital_invest.member_id,
func.count().label("count")).\
join(Product,
Capital_invest.prod_id==Product.prodid).\
filter(Product.price*payback_pc-
Capital_invest.capital_payback<=0).\

group_by(Capital_invest.member_id).subquery()

buy_product_count = session.query(Capital_invest.member_id,
func.count().label("count")).\
group_by(Capital_invest.member_id).subquery()

x_members = session.query(Distributor).\
join(paid_120_count,
paid_120_count.c.member_id==Distributor.id).\
join(buy_product_count,
buy_product_count.c.member_id==Distributor.id).\

filter(paid_120_count.c.count==buy_product_count.c.count).\
filter(Distributor.quali_bonus==True)

So the distributor is the center table joins two queries. The Query
returns data without problem. But i can't update the result data
subsequently, for example

for member in x_members:
member.name ="xxxxx"

The sqlalchemy just simply do nothing. I have to do this:

for member in x_members:
member.name ="xxxxx"

dist = session.query(Distributor).get(member.id)
dist.name = "xxxxx"

Is there something I missed for getting this issue?

Thanks for someone pointing out the clue.

[Michael Bayer]

did you close out the Session before you iterated through x_members ? This test will ensure you're doing things correctly:

for member in x_members:
dist = session.query(Distributor).get(member.id)
assert dist is member

the identity map will ensure they are the same instance, *if* you are operating on x_members as present in the Session. If the Session got closed before you iterated, you'd need to not do that.

[Geo]

I'm not sure if my code has closed the session, the context is:

import transaction
x_members = session.query(Distributor).join....

try:
   transaction.begin()
   for member in x_members:
        .....
except:
  transaction.abort()
BTW, I'm using pyramid framework, which is using the following statement to init the session:

DBSession = scoped_session(sessionmaker(
                             extension=ZopeTransactionExtension()))

[Michael Bayer]

It's likely you're starting with a new Session when begin() is called, but this depends on the workings of ZopeTransactionExtension and I'd ask on their list about it.

You shouldn't have any calls to begin/abort/try/except when using Pyramid though, all of that should be happening behind the scenes. A framework isn't worth much if you need to be hand-coding defensive transaction blocks in application code.

[Geo]

Well, my case is a bit different. I'm writing a nightly running batch script. And this script is not running inside pyramid context, which means it is not the model that called from the framework. Instead, I arrange it to run by system cron. But, I'm trying to utilize the pyramid environment settings, like the development.ini and production.ini to get the connection string and the contextual/thread-local session management object ZopeTransactionExtension. I'm not sure if this is the best practice of doing in this way, may be I should just use the plain session object. For my understanding, it's the framework's responsibility to commit or abort session if using the thread-local session. That is way I manually put them in the code. So anyway I would like to know the reason that why the SA doesn't do anything in this case. And I was suspecting it is the reason of the complex joins in usage, because I have other code that doing things in the same way, they are just some simple single table queries, so....

[Michael Bayer]

On Dec 1, 2011, at 4:29 PM, Geo wrote:

> And I was suspecting it is the reason of the complex joins in usage, because I have other code that doing things in the same way,

the structure of the Query/ SELECT statement that gets you back some data has no connection on how that data behaves later on. Your original case seems a simple case of the session being closed prematurely.

[Geo]

Ok I found the solution, just move the first query into the transaction body:

import transaction

try:
   transaction.begin()
   x_members = session.query(Distributor)...