Python SQLAlchemy mapping column names to attributes and vice versa

Question

I have a problem where I need to access a MS SQL DB, so naturally it's naming convention is different: TableName(Id, ColumnName1, ColumnName2, ABBREVIATED,...)

The way I got the model constructed in Python:

class TableName(Base):
    __tablename__ = 'TableName'
    
    id = Column('Id', BigInteger, primary_key=True, autoincrement=True)
    order_id = Column('OrderId', BigInteger, ForeignKey('Orders.Id'), nullable=False)
    column_name1 = Column('ColumnName1', String(50), nullable=True)
    column_name2 = Column('ColumnName2', String(50), nullable=True)
    abbreviated= Column('ABBREVIATED', String(50), nullable=False)
    ...

Then, I have a Repository:

class TableNameRepository:
    def __init__(self, connection_string: str):
        self.engine = create_engine(connection_string)
        self.Session = sessionmaker(bind=self.engine)

    def get_entry(self, order_id: int, column_name1: str) -> Optional[TableName]:
        with self.Session() as session:
            return session.query(TableName).filter_by(
                order_id=order_id, 
                column_name1=column_name1
            ).first() 

This works, however, now to my actual problem:

I have a template text file with placeholders using the SQL column names, meaning there are Id, ColumnName1, ColumnName2, ABBREVIATED strings and I need to replace them with values from the model.

Here is how I tried to approach this:

retrieved_table_entity = self.repository.get_entry(order_id, something)
attr_to_column = {
  column.key: column.name
  for column in retrieved_table_entity.__table__.columns
}

The problem is that doesnt work: It still creates keys that look like: {'Id': 'Id', 'OrderId': 'OrderId', 'ColumnName1': 'ColumnName1', ...}

And so, the next step which is:

            for attr_name, column_name in attr_to_column.items():
                value = getattr(retrieved_table_entity, attr_name, None)
                logging.debug(f"Accessing {attr_name}: {value}")
                reg_dict[column_name] = '' if value is None else str(value)

Doesnt work because the keys are not the python keys, in debug mode testing, if I manually do like: getattr(retrieved_table_entity, 'id'), it returns the value.

To summarize: I need a way to map, so that I have key/value dictionary like:

{
Id: 1
OrderID: 123
ColumnName1: 'SomeValue'
}

And then I could simply to soemthing like self.template.format(**keys_to_values_dict)

I have tried consulting with LLMs, and they keep circling around not suggesting a way to do this, I dont want to manually map the model again in this service function.

I also reviewed the Mapping API and cant find something relevant: https://docs.sqlalchemy.org/en/14/orm/mapping_api.html

For reference, my current dependencies:

sqlalchemy==2.0.36
pymssql==2.2.11 

Answer 1

Untested but something like this:

from sqlalchemy import inspect
old_to_new = {c.name: k for k, c in inspect(TableName).columns.items()}

https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.Mapper.columns

https://docs.sqlalchemy.org/en/20/orm/mapping_styles.html#inspection-of-mapper-objects