SQLAlchemy: Is there a way to return the Column(comment=) instead of the name of the column in an AP-CodePudding

Assume a simple schema defined as:

# Base -> class which allows inherit of declarative_base

class Monkey(Base): 
    __tablename__ = "monkey"

    monkey_id = Column(BIGINT(20), primary_key=True, nullable=False, comment="PowerMonkey")

Lets then assume we have a simple GET API request:

# pseudocode 

@app.get("/{monkey_id}")
async def get_monkey():
     returns the data row where monkey has searched id in request

Is there a way to get:

{
"PowerMonkey" : 1
}

Instead of

{
"monkey_id" : 1
}

as the response body?

CodePudding user response：

You can access the comment via the mapper, using the class:

from sqlalchemy import inspect

...

mapper = inspect(Monkey)
comment = mapper.columns['monkey_id'].comment

or an instance of the class:

mapper = inspect(Monkey()).mapper
comment = mapper.columns['monkey_id'].comment

However it's up to you to construct an API response using the comment's value, or to configure tools like Marshmallow or Pydantic to do so. Here's how you might do it using Marshmallow:

from marshmallow_sqlalchemy import SQLAlchemySchema
from marshmallow import fields

class MarshmallowMonkeySchema(SQLAlchemySchema):
    class Meta:
        model = Monkey

    monkey_id = fields.Method('get_id')

    def get_id(self, obj):
        return sa.inspect(obj).mapper.columns['monkey_id'].comment

with Session() as s:
    q = sa.select(Monkey)
    m = s.scalars(q).first()

    response = MarshmallowMonkeySchema().dump(m)
    print(response)