SQLAlchemy クエリ実行結果のObjectを辞書(dict) に変換

Runtime Inspection API — SQLAlchemy 2.0 Documentation にメソッドを用意する
辞書(dict)を作成する方法

from sqlalchemy import inspect

def object_to_dict(obj):
    return {
        c.key: getattr(obj, c.key)
        for c in inspect(obj).mapper.column_attrs
    }

https://oboe2uran.hatenablog.com/entry/2025/01/11/124754
ここで書いた database.py を使って

import database
from sqlalchemy import text
from sqlalchemy import inspect
from itemdto import Itemwork

with database.getSession() as session:
    sql = "SELECT * FROM items WHERE id = 1"
    res = session.query(Itemwork).from_statement(text(sql)).first()
    itemdict = object_to_dict(res)
    # {'id': 1, 'user_name': 'A', 'task_name': 'A1', 'point': 1, 'create_time': datetime.datetime(2024, 4, 12, 21, 8, 55, 55000)}

これだと、常に def object_to_dict(obj) を用意することになる。

外側で object_to_dict() メソッドを用意するのではなく、
ORMでマッピングされるクラスで辞書(dict)に変換するメソッドを用意する
itemdto.py

import sqlalchemy as db
from sqlalchemy.orm import declarative_base
import datetime
from sqlalchemy import inspect
from sqlalchemy.ext.hybrid import hybrid_property

class Itemwork(declarative_base()):
    __tablename__ = 'itemwork'
    id  = db.Column(db.INT, nullable=False, primary_key=True)
    user_name = db.Column(db.String(120), nullable=False)
    task_name = db.Column(db.String(60), nullable=False)
    point = db.Column(db.INT, nullable=True)
    create_time = db.Column(db.TIMESTAMP, nullable=False)

    def __init__(self,id:int, user_name:str, task_name:str, point:int, create_time=datetime.datetime.now()):
        self.id = id
        self.user_name = user_name
        self.task_name = task_name
        self.point = point
        self.create_time = create_time

    def to_dict(self) -> {}:
        dict_ = {}
        for key in self.__mapper__.c.keys():
            if not key.startswith('_'):
                dict_[key] = getattr(self, key)
        for key, prop in inspect(self.__class__).all_orm_descriptors.items():
            if isinstance(prop, hybrid_property):
                dict_[key] = getattr(self, key)
        return dict_

クエリ実行する側がすっきりする。

import database
from sqlalchemy import text
from itemdto import Itemwork

with database.getSession() as session:
    sql = "SELECT * FROM items ORDER BY id ASC"
    results = session.query(Itemwork).from_statement(text(sql)).all()
    for row in results:
        itemdict = row.to_dict()

{'id': 1, 'user_name': 'A', 'task_name': 'A1', 'point': 1, 'create_time': datetime.datetime(2024, 4, 12, 21, 8, 55, 55000)}
{'id': 2, 'user_name': 'B', 'task_name': 'B1', 'point': 10, 'create_time': datetime.datetime(2025, 1, 11, 11, 52, 58, 241098)}
{'id': 3, 'user_name': 'C', 'task_name': 'C1', 'point': 20, 'create_time': datetime.datetime(2025, 1, 11, 11, 53, 29, 135697)}