# 前後端串接前 DB query 處理
想藉由flask SqlAlchemy查詢並將tabla轉json格式輸出

嘗試以ORM方式取得column name 再將 row 中的 value一起組成dict

data = PaymentInfo.query.limit(3).all()
#取得column name
my_query = PaymentInfo.__table__.columns
schema_name = [column.key for column in PaymentInfo.__table__.columns]
my_list = []

for row in data:
    my_list.append([dict(zip(schema_name, row))])

print(my_list)

#### TypeError: 'PaymentInfo' object is not iterable

解法

from sqlalchemy.ext.declarative import DeclarativeMeta

class AlchemyEncoder(json.JSONEncoder):

    def default(self, obj):
        if isinstance(obj.__class__, DeclarativeMeta):
            # an SQLAlchemy class
            fields = {}
            for field in [x for x in dir(obj) if not x.startswith('_') and x != 'metadata']:
                data = obj.__getattribute__(field)
                try:
                    json.dumps(data) # this will fail on non-encodable values, like other classes
                    fields[field] = data
                except TypeError:
                    fields[field] = None
            # a json-encodable dict
            return fields

        return json.JSONEncoder.default(self, obj)

## 理解套件 sqlalchemy.ext.declarative 使用
解決問題原理與過程
＊
＊

## reference
https://stackoverflow.com/questions/1958219/how-to-convert-sqlalchemy-row-object-to-a-python-dict
https://stackoverflow.com/questions/5022066/how-to-serialize-sqlalchemy-result-to-json
https://stackoverflow.com/questions/15175339/sqlalchemy-what-is-declarative-base
https://medium.com/@alanhamlett/part-1-sqlalchemy-models-to-json-de398bc2ef47
https://mmas.github.io/sqlalchemy-serialize-json