SQLAlchemy introspection of declarative classes

Question

I'm writing a small sqlalchemy shim to export data from a MySQL database with some lightweight data transformations—mostly changing field names. My current script works fine but requires me to essentially describe my model twice—once in the class declaration and once as a list of field names to iterate over.

I'm trying to figure out how to use introspection to identify properties on row-objects that are column accessors. The following works almost perfectly:

for attr, value in self.__class__.__dict__.iteritems():
    if isinstance(value, sqlalchemy.orm.attributes.InstrumentedAttribute):
        self.__class__._columns.append(attr)

except that my to-many relation accessors are also instances of sqlalchemy.orm.attributes.InstrumentedAttribute, and I need to skip those. Is there any way to distinguish between the two while I am inspecting the class dictionary?

Most of the documentation I'm finding on sqlalchemy introspection involves looking at metadata.table, but since I'm renaming columns, that data isn't trivially mappable.

Answer 1

The Mapper of each mapped entity has an attribute columns with all column definitions. For example, if you have a declarative class User you can access the mapper with User.__mapper__ and the columns with:

list(User.__mapper__.columns)

Each column has several attributes, including name (which might not be the same as the mapped attribute named key), nullable, unique and so on...

Answer 2

I'd still like to see an answer to this question, but I've worked around it by name-mangling the relationship accessors (e.g. '_otherentity' instead of 'otherentity') and then filtering on the name. Works fine for my purposes.

Answer 3

An InstrumentedAttribute instance has an an attribute called impl that is in practice a ScalarAttributeImpl, a ScalarObjectAttributeImpl, or a CollectionAttributeImpl.

I'm not sure how brittle this is, but I just check which one it is to determine whether an instance will ultimately return a list or a single object.