Timothy

程式碼:

import unittest
from json import JSONEncoder
from json import dumps
from sqlalchemy import *
from sqlalchemy.orm import create_session
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
from sqlalchemy.orm import contains_eager, joinedload
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import DeclarativeMeta
from json import JSONEncoder

class DateTimeEncoder(JSONEncoder):  #JSON格式定義
    def default(self, obj):
        if hasattr(obj, 'isoformat'):
            return obj.isoformat()
        elif isinstance(obj, decimal.Decimal):
            return float(obj)
        elif isinstance(obj, ModelState):
            return None
        else:
            return json.JSONEncoder.default(self, obj)

my_declarative_base = lambda cls: declarative_base(cls=cls)

@my_declarative_base
class Base(object):  #改寫Object 主要做Json輸出使用
    @property
    def columns(self):
        return [ c.name for c in self.__table__.columns ]

    @property
    def columnitems(self):
        return dict([ (c, dumps(getattr(self, c),cls= DateTimeEncoder)) for c in self.columns ])

    def __repr__(self):
        return u'{}({})'.format(self.__class__.__name__, self.columnitems)

    def to_json(self):
        return self.columnitems

    def rec_json(self, key_str):
        result = self.columnitems
        result[key_str] = getattr(self, key_str).columnitems

        return result


engine = create_engine('mysql://root:@localhost:3306/test', echo=False)  #建立資料庫連線
etadata = MetaData(bind=engine)

class new_table(Base):      #定義 Table
     __table__ = Table('new_table', etadata, autoload=True)    

session = create_session(bind=engine) #建立Session
     
allres =  session.query(new_table).all()  #查詢資料表所有資料

#print allres.column_descriptions
for s in allres:
    print s.to_json()  #JSON字串輸出

結果: