Search code examples
pythonsqlalchemygroup-concat

SQLAlchemy: Access data of group_concat

I'm in the process of learning python, and maybe my following question sounds a bit dumb, but I can't find an answer myself:

In my code, I've got the following query:

jobs = session.query(Ecordov, Ecordovadr, func.group_concat(Ecordovadr.ooaname1)).outerjoin(Ecordovadr).filter(Ecordov.oovpudate.like('2015-07-02')).group_by(Ecordov.oovorder).order_by(desc(Ecordov.oovputimev))

which gets evaluated to:

SELECT ecordov.oovkey AS ecordov_oovkey, ecordov.oovorder AS ecordov_oovorder, ecordov.oovpudate AS ecordov_oovpudate, ecordov.oovputimev AS ecordov_oovputimev, ecordovadr.ooakey AS ecordovadr_ooakey, ecordovadr.ooaname1 AS ecordovadr_ooaname1, ecordovadr.ooaorder AS ecordovadr_ooaorder, group_concat(ecordovadr.ooaname1) AS group_concat_1 
FROM ecordov 
LEFT OUTER JOIN ecordovadr ON ecordov.oovorder = ecordovadr.ooaorder 
GROUP BY ecordov.oovorder 
ORDER BY ecordov.oovputimev DESC

Running this directly against mysql works as intended and gives me the expected results.

Also, doing something like 

for x in jobs:
    x.Ecordov.oovorder

in my python code gives me the correct results.

However, I've got no clue how to access the data of the group_concat funtion. I've tried variations of 

for x in jobs:
    x.group.concat1

but all I get is:

AttributeError: 'result' object has no attribute 'group'

Could anyone point me into the correct direction? It seems, I can't figure it out myself.

Thanks in advance and best regards!

Solution

  • func.group_concat(Ecordovadr.ooaname1) creates an anonymous field in the result set, and is thus not accessible by name.

    To access it by name, the label must be specified like this:

    func.group_concat(Ecordovadr.ooaname1).label('ooanames')

    This will evaluate to the following sql:

    group_concat(ecordovadr.ooaname1) AS 'ooanames'

    Then, it can be referred as:

    for x in jobs:
    x.ooanames

    If the label is not specified, then the field is still individually accessible positionally, i.e.

    for x in jobs:
    x[7] # unless I miscounted from your example