Thanks Michael, that solves my problem. On Mon, Sep 12, 2011 at 3:18 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
> > On Sep 12, 2011, at 5:50 PM, Geo wrote: > > > I have a tested update ... from.. statement, like so: > > UPDATE...FROM syntax, which is non-standard, isn't built into SQLAlchemy > right now. You'd need to either convert this to use a correlated SELECT > in the WHERE clause, stick to the SQL string, or use a custom @compiles > construct - for that we have an example, associated with the trac ticket > involving an add of this feature, at > http://www.sqlalchemy.org/trac/attachment/ticket/1944/enhance2.py . The > construct there might work as it is right now but I haven't tested it. > > > > > > > > > update distributors set lead_bonus = lead_bonus + a.sum_amt > > from ( > > select target_member as id, sum(amount) as sum_amt > > from bonus_gen_history > > where bonus_type=2 and sub_type=1 and source_member in > > (select id > > from distributors > > where (extract(epoch from (now()-reg_time))/60>363)) > > group by target_member > > ) a > > where a.id=distributors.id > > > > I'm having problem of constructing this statement from sqlalchemy: > > > > pass_pending_period = session.query(Distributor.id).filter((func.now()- > > Distributor.reg_time)/3600>72).subquery() > > > > sum_amt = session.query(Bonus_gen_history.target_member, > > func.sum(Bonus_gen_history.amount).label('sm')).\ > > > > filter(Bonus_gen_history.bonus_type==BonusType.sponsorBonus).\ > > filter(Bonus_gen_history.tran_type==TranType.addPd).\ > > > > filter(Bonus_gen_history.source_member.in_(pass_pending_period)). > > group_by(Bonus_gen_history.target_member).subquery() > > > > > > q = session.query(Distributor).update({Distributor.sponsor_bonus: > > Distributor.sponsor_bonus+(sum_amt.c.sm)}) > > > > I keep getting the following error message: > > > > sqlalchemy.exc.ProgrammingError: (ProgrammingError) missing FROM- > > clause entry for table "anon_1" > > LINE 1: ...s SET sponsor_bonus=(distributors.sponsor_bonus + > > anon_1.sm) > > ^ > > 'UPDATE distributors SET sponsor_bonus=(distributors.sponsor_bonus + > > anon_1.sm)' {} > > > > And I can't figure out how to add the "FROM-clause" in the above > > query. > > > > Can somebody help me out? > > > > > > -- > > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
