SQL Alchemy Get Name Of Column

Question

If you have an entity in SQL Alchmey you created can you at a later part get the name you specified of the column to use in a custom statement.

For example here is the first part of a data entity:

class ReportMetadataEntity(Base, ReportMetadata):
    __tablename__ = "reports_metadata"
    __mapper_args__ = {"eager_defaults": True}

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, name="id")
    application_customer_id = Column(
        String,
        nullable=False,
        name="application_customer_id",
    )

Is there a way to call ReportMetadataEntity.id.name or some other attribute to get back the string "id"?

Thanks

Answer 1

Use the Runtime Inspection API, which will return a mapper when passed an ORM model class:

>>> from sqlalchemy import inspect
>>>
>>> insp = inspect(ReportMetadataEntity)
>>> insp.columns.id.name
'id'
>>> # insp.c.id.name is equivalent

It's possible for the column's name in the database and in the class to be different, for example:

foo = Column(String, name='bar')  # "foo" in the class, "bar" in the database.

The mapper will access the column through its name in the class:

>>> insp.columns.foo.name
'bar'

By constrast, accessing via the underlying table requires that the name of the column in the database be used:

>>> tbl = MyModel.__table__
>>> tbl.c.foo.name
Traceback (most recent call last):
  ...
KeyError: 'foo'
>>> 
>>> tbl.c.bar.name
'bar'

Answer 2

I recently did this in a project I'm working on, here is the pertinent logic:

# Only the important lines are included

class Engine(object):
    def __init__(self, connection_string: str = "postgresql:///postgres"):
        self.base = automap_base()
        self.engine = create_engine(connection_string, convert_unicode=True)
        self.base.prepare(self.engine, reflect=True)
        self.tables = self.base.classes

    def get_table(self, name: str = None):
        for table in self.tables:
            if table.__name__ == name:
                return table

    def get_by_column_value(self, table_name, column_name, column_value):
        table = self.get_table(name=table_name)
        column = table.__table__.c[column_name]
        result = self.session.query(table).filter(column == column_value).all()
        return result

So, if you have the table, you can list the columns names with:

columns = ReportMetadataEntity.__table__.c
print(columns)