Q:

Rearrange columns in SQLAlchemy Select object

Question
How can I reorder the columns in a SQLAlchemy query object?
Example
Given a SQLAlchemy core Select object representing the following query:
>>> print s
SELECT sum(accounts.amount) AS amount_sum, accounts.name 
FROM accounts GROUP BY accounts.name
 LIMIT :param_1

I want to form the a Select object with the exact same structure, but with the order of the columns moved around
SELECT accounts.name,  sum(accounts.amount) AS amount_sum
FROM accounts GROUP BY accounts.name
 LIMIT :param_1

What I've tried that doesn't work
I want to do this without expanding the SQL significantly.  I've tried doing with with with_only_columns and it nests the query, which, again, I'd like to avoid
>>> print s.with_only_columns(list(s.columns)[::-1])
SELECT name, amount_sum 
FROM (SELECT sum(accounts.amount) AS amount_sum, accounts.name AS name 
FROM accounts GROUP BY accounts.name
 LIMIT :param_1) GROUP BY accounts.name
 LIMIT :param_2

Edit
To be clear the example above is just an example.  I'm looking for a general solution to do this on arbitrary queries.  Ideally the function would consume a Select object and a list of column names in the proper order.
Edit 2
Code to create s.  Again, I'm looking for a solution to a general problem, not simply a code snippet that fixes the ordering below explicitly.
import sqlalchemy

metadata = sqlalchemy.MetaData()

t = sqlalchemy.Table('accounts', metadata,
             sqlalchemy.Column('name', sqlalchemy.String),
             sqlalchemy.Column('amount', sqlalchemy.Integer),
             sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
             )

s = (sqlalchemy.select([sqlalchemy.sql.functions.sum(t.c.amount).label('sum'),    
                         t.c.name])
        .group_by(t.c.name)
        .limit(10))

print s

SELECT sum(accounts.amount) AS sum, accounts.name 
FROM accounts GROUP BY accounts.name
 LIMIT :param_1

A:

You use with_only_columns but the columns you place into it must come from that list that was sent to the select() originally, and not the exported columns of the select itself.
You either have to hold onto these columns externally, or get at them via the select.inner_columns accessor.
The session below illustrates the various behaviors.  The ".c." collection on any selectable represents "the columns we can SELECT from", which is why using "someselect.c.somecol" implies you're selecting FROM that select statement, just like a table.
>>> from sqlalchemy import select
>>> from sqlalchemy.sql import table, column

>>> t = table('t', column('a'), column('b'), column('c'))

>>> stmt1 = select([t])
>>> print(stmt1)
SELECT t.a, t.b, t.c FROM t

>>> print(stmt1.with_only_columns([t.c.b, t.c.a]))
SELECT t.b, t.a FROM t

>>> print(stmt1.with_only_columns([stmt1.c.b, stmt1.c.a]))
SELECT b, a FROM (SELECT t.a AS a, t.b AS b, t.c AS c FROM t)

>>> stmt1_cols = dict((c.key, c) for c in stmt1.inner_columns)

>>> stmt1_cols
{'a': <sqlalchemy.sql.elements.ColumnClause at 0x102d0e090; a>, 'c': <sqlalchemy.sql.elements.ColumnClause at 0x102de7c50; c>, 'b': <sqlalchemy.sql.elements.ColumnClause at 0x10047bb50; b>}

>>> dict(t.c)
{'a': <sqlalchemy.sql.elements.ColumnClause at 0x102d0e090; a>, 'c': <sqlalchemy.sql.elements.ColumnClause at 0x102de7c50; c>, 'b': <sqlalchemy.sql.elements.ColumnClause at 0x10047bb50; b>}

>>> assert stmt1_cols == dict(t.c)

>>> print(stmt1.with_only_columns([stmt1_cols['b'], stmt1_cols['a']]))
SELECT t.b, t.a FROM t

