Pyramid / SQLAlchemy trouble with joined models

I'm really new to Python & as new to Pyramid (this is the first thing I've written in Python) and am having trouble with a database query...

I have the following models (relevant to my question anyway):

MetadataRef (contains info about a given metadata type)
Metadata (contains actual metadata) -- this is a child of MetadataRef
User (contains users) -- this is linked to metadata. MetadataRef.model = 'User' and metadata.model_id = user.id

I need access to name from MetadataRef and value from Metadata.

Here's my code:

class User(Base):
    ...
    _meta = None

    def meta(self):
        if self._meta == None:
            self._meta = {}
            try:
                for item in DBSession.query(MetadataRef.key, Metadata.value).\
                    outerjoin(MetadataRef.meta).\
                    filter(
                        Metadata.model_id == self.id,
                        MetadataRef.model == 'User'
                    ):
                    self._meta[item.key] = item.value

            except DBAPIError:
                #@TODO: actually do something with this
                self._meta = {}
        return self._meta

The query SQLAlchemy is generating does return what I need (close enough anyway -- it needs to query model_id as part of the ON clause rather than the WHERE, but that's minor and I'm pretty sure I can figure that out myself):

SELECT metadata_refs.`key` AS metadata_refs_key, metadata.value AS metadata_value 
FROM metadata_refs LEFT OUTER JOIN metadata ON metadata_refs.id = metadata.metadata_ref_id 
WHERE metadata.model_id = %s AND metadata_refs.model = %s

However, when I access the objects I get this error:

AttributeError: 'KeyedTuple' object has no attribute 'metadata_value'

This leads me to think there's some other way I need to access it, but I can't figure out how. I've tried both .value and .metadata_value. .key does work as expected.

Any ideas?

Solution

You're querying separate attributes ("ORM-enabled descriptors" in SA docs):

DBSession.query(MetadataRef.key, Metadata.value)

in this case the query returns not full ORM-mapped objects, but a KeyedTuple, which is a cross between a tuple and an object with attributes corresponding to the "labels" of the fields.

So, one way to access the data is by its index:

ref_key = item[0]
metadata_value = item[1]

Alternatively, to make SA to use a specific name for column, you may use Column.label() method:

for item in DBSession.query(MetadataRef.key.label('ref_key'), Metadata.value.label('meta_value'))...
    self._meta[item.key] = item.meta_value

For debugging you can use Query.column_descriptions() method which will tell you the names of the columns returned by the query.