SQLAlchemy introspect column type with inheritance

Question

Considering this code (and using SQLAlchemy 0.7.7):

class Document(Base):
    __tablename__ = 'document'
    __table_args__ = {
        'schema': 'app'
    }

    id = Column(types.Integer, primary_key=True)
    nom = Column(types.Unicode(256), nullable=False)
    date = Column(types.Date())

    type_document = Column(types.Enum('arrete', 'photographie',
        name='TYPES_DOCUMENT_ENUM'))
    __mapper_args__ = {'polymorphic_on': type_document}

class Arrete(Document):
    __tablename__ = 'arrete'
    __table_args__ = {
        'schema': 'app'
    }
    __mapper_args__ = {'polymorphic_identity': 'arrete'}

    id = Column(types.Integer, ForeignKey('app.document.id'), primary_key=True)
    numero_arrete = Column(types.Integer)
    date_arrete = Column(types.Date())

I can easily introspect column type for column defined in Arrete class with:

Arrete.__table__.c['date_arrete'].type

But this doesn’t work if I want to access, through the Arrete class, a column defined in Document class. (KeyError if I try to access c['date']).

Is there a way to get column type, regardless if the column is defined in the final class or in one of its parent?

zzzeek · Accepted Answer · 2012-07-25T15:44:46.863

33

The ORM has allowed you to define classes in an inheritance pattern that corresponds to a JOIN of two tables. This structure is full service, and can also be used to find out basic things like the types of attributes on columns, pretty much directly:

type = Arrete.date.property.columns[0].type

note that this is basically the same thing as the approach of trudging through __bases__, except you let Python's normal class mechanics do the work.

edited Jul 25 '12 at 15:44

answered Jul 24 '12 at 15:57

zzzeek

72,307
23
193
185

Huh, that's cleaner! Thanks! :) – tonio Jul 24 '12 at 16:43
2

@zzzeek is there a reason the `columns` attribute is a list? Are there situations where the first instance might not be the correct one? – ricekab Jan 13 '20 at 14:19
same curiosity as @ricekab – weaming Aug 18 '20 at 03:12

score 7 · Answer 2 · answered Jul 24 '12 at 14:58

7

You can explore the base classes...

def find_type(class_, colname):
    if hasattr(class_, '__table__') and colname in class_.__table__.c:
        return class_.__table__.c[colname].type
    for base in class_.__bases__:
        return find_type(base, colname)
    raise NameError(colname)

print find_type(Arrete, 'date_arrete')
print find_type(Arrete, 'date')

answered Jul 24 '12 at 14:58

gurney alex

13,247
4
43
57

I just add it as a @classmethod of my base class, and looks like to work just as expected. Thanks a lot! – tonio Jul 24 '12 at 15:13
1

the problem with this answer is that it unnecessarily pokes through `__bases__`, when simply accessing `Arrete.date` will do that work for you. SQLAlchemy of course can give you the column/datatype given a mapped attribute. – zzzeek Jul 24 '12 at 15:58

score 0 · Answer 3 · answered Jul 24 '12 at 14:29

0

You need the abstract special directive or the mixin pattern.

For a mixin, you'd use something like this:

class MyMixin(object):
    __tablename__ = 'document'
    __table_args__ = {
        'schema': 'app'
    }

    id = Column(types.Integer, primary_key=True)
    nom = Column(types.Unicode(256), nullable=False)
    date = Column(types.Date())

class Arrete(MyMixin, Base):
    __tablename__ = 'arrete'

    __mapper_args__ = {'polymorphic_identity': 'arrete'}

    foreign_id = Column(types.Integer, ForeignKey('app.document.id'), primary_key=True)
    numero_arrete = Column(types.Integer)
    date_arrete = Column(types.Date())


class Document(MyMixin, Base):
    __tablename__ = 'Document'
    type_document = Column(types.Enum('arrete', 'photographie',
        name='TYPES_DOCUMENT_ENUM'))
    __mapper_args__ = {'polymorphic_on': type_document}

Shared stuff goes in the mixin, non shared stuff in the sub classes.

answered Jul 24 '12 at 14:29

Spencer Rathbun

14,510
6
54
73

Looks like it will break some other code of my app for which `Arrete` has to extend `Document`. I don’t want the exact syntax of my example to work (for getting the column type), but why I really want, if possible, is to keep the database unchanged. – tonio Jul 24 '12 at 14:41
@tonio If Arrete is just extending document, then there should only be one table, called `arrete`, on the db. You can't extend it the way you are wanting, because each `__table__` is an object, with a unique entry in the `metadata`. Trying to share the `__table__` object across classes prevents each class from having different tables. You may need to rethink your db model. Perhaps more details? – Spencer Rathbun Jul 24 '12 at 14:57
Arrete was here as an example, I got more table extending document. And several table with inheritance in my application that I don’t want to change. Maybe I should have better explained my needs, thanks for your help anyway. – tonio Jul 24 '12 at 15:15

SQLAlchemy introspect column type with inheritance

3 Answers3

Linked