On Mar 8, 2012, at 2:33 AM, yannack wrote:
>
> I would like to write this in a sensible SQLA-oriented way, but I seem
> to face various challenges; anyone have an idea how to:
> - use PGSQL window functions
> - update using a custom "from" clause: can I use my models here or do
> I have to use an expression?
> - do both at once
> Thanks a lot,
> best regards,
> Yannick
we support all those constructs (you need SQLA 0.7.5 for UPDATE..FROM) and I
worked up an example that should pretty much create that query:
s = Session()
subq = s.query(
Scoring,
ScoreCard.player_index,
func.rank().over(
order_by=[
Scoring.extra.asc(),
ScoreCard.player_index.desc()
]).label('rank')
).select_from(Scoring).join("extra_scorecard").\
filter(Scoring.weekstart=='hi').\
order_by(Scoring.extra.asc(),
ScoreCard.player_index.desc()).subquery("ordered_table")
upd = Scoring.__table__.update().\
values(extra_rank=subq.c.rank).\
where(Scoring.scoring_id==subq.c.id)
s.execute(upd)
>
> --
> 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.
