How does one reference a custom field from sqlalchemy when rendering a template?

Question

I am trying to print the value of a func.count() call, but I'm having trouble figuring out how it is named once I'm inside my mako template. I tried to pprint and dict the variables at the debugger line but they're returned as objects, so I'm not sure where to go from here.

Here's the code for the view I'm executing:

    TodayList = DBSession.query(
    func.count(ExtendedCDR.uniqueid),ExtendedCDR,ExtensionMap)
    .filter(or_(
         ExtensionMap.exten == ExtendedCDR.extension,
         ExtensionMap.prev_exten == ExtendedCDR.extension))
    .filter(ExtendedCDR.start > func.curdate())
    .group_by(ExtendedCDR.extension)
    .order_by(func.count(ExtendedCDR.uniqueid).desc())
    .all()

When this code runs, the debug for the sql query returns:

2012-11-09 19:16:10,392 INFO [sqlalchemy.engine.base.Engine][Dummy-3] SELECT count(cdr_extended.uniqueid) AS count_1, cdr_extended.uniqueid AS cdr_extended_uniqueid, cdr_extended.start AS cdr_extended_start, cdr_extended.end AS cdr_extended_end, cdr_extended.clid AS cdr_extended_clid, cdr_extended.src AS cdr_extended_src, cdr_extended.dst AS cdr_extended_dst, cdr_extended.dcontext AS cdr_extended_dcontext, cdr_extended.channel AS cdr_extended_channel, cdr_extended.dstchannel AS cdr_extended_dstchannel, cdr_extended.lastapp AS cdr_extended_lastapp, cdr_extended.lastdata AS cdr_extended_lastdata, cdr_extended.duration AS cdr_extended_duration, cdr_extended.billsec AS cdr_extended_billsec, cdr_extended.disposition AS cdr_extended_disposition, cdr_extended.amaflags AS cdr_extended_amaflags, cdr_extended.accountcode AS cdr_extended_accountcode, cdr_extended.userfield AS cdr_extended_userfield, cdr_extended.extension AS cdr_extended_extension, concat(concat(ext_map.FName, %s), ext_map.LName) AS anon_1, ext_map.exten AS ext_map_exten, ext_map.FName AS ext_map_FName, ext_map.LName AS ext_map_LName, ext_map.login_name AS ext_map_login_name, ext_map.isManager AS ext_map_isManager, ext_map.prev_exten AS ext_map_prev_exten FROM cdr_extended, ext_map WHERE (ext_map.exten = cdr_extended.extension OR ext_map.prev_exten = cdr_extended.extension) AND cdr_extended.start > curdate() GROUP BY cdr_extended.extension ORDER BY count(cdr_extended.uniqueid) DESC

Here's my mako template code:

        % for topentry in TopToday:
                
                        ${topentry.ExtensionMap.AssociateName}
                        ${topentry.count_1}
                
        % endfor

However, Mako throws an exception that ${topentry.count_1} doesn't exist, though one would assume it would given the DEBUG output above.

Does anyone know how I'd go about finding what that count_1 is named according to python/Mako?

Answer 1

You should be able to name the return values of your query using .label(). For example, DBSession.query(func.count(ExtendedCDR.unique_id).label('count'), ...).