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
CodePudding user response:
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'
CodePudding user response:
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)