[Python] sqlalchemy 輸出json格式

sqlalchemy 輸出json格式

前言：

剛好有人發問，寫了一個範例，

做個記錄一下，也許以後自己會用到 XD

範例：

http://pastie.org/8595769

# -*- coding: utf-8 -*-
import hashlib
import sqlalchemy
from sqlalchemy import Column, Integer, String, ForeignKey, DateTime, Table
from sqlalchemy.schema import MetaData
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Bundle
from sqlalchemy.orm import lazyload
from sqlalchemy.orm.session import Session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = sqlalchemy.create_engine('mysql+mysqldb://user:passwd@host/your_db?charset=utf8')
connection = engine.connect()
session = Session(bind=connection)

my_declarative_base = lambda cls: declarative_base(cls=cls)

@my_declarative_base
class Base(object):
    @property
    def columns(self):
        return [ c.name for c in self.__table__.columns ]

    @property
    def columnitems(self):
        return dict([ (c, getattr(self, c)) 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
        
class User(Base):
    __tablename__ = 'user'
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8'
    }

    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    username = Column(String(64))
    password = Column(String(256))
    address_id = Column(Integer, ForeignKey('user_address.id'))
    address = relationship("Address", lazy="joined")

    def __init__(self, name, username, password):
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()

    def __repr__(self):
       return u"[User('%s','%s', '%s')]" % \
           (self.name, self.username, self.password)


class Address(Base):
    __tablename__ = 'user_address'
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8'
    }

    id = Column(Integer, primary_key=True)
    address = Column(String(256), nullable=False)

    def __init__(self, address):
        self.address = address

    def __repr__(self):
        return u"[Address('%s')]" % self.address
        
        

# insert test data...
"""
Base.metadata.create_all(engine)

user_1 = User("user1", "username1", "password_1")
user_2 = User("user2", "username2", "password_2")
address_1 = Address(u'臺灣凱達格蘭大道')
user_1.address = address_1
user_2.address = address_1
session.add(user_1)
session.add(user_2)
session.commit()
"""

for r in session.query(User).options(lazyload('address')):
    print r.rec_json('address')


"""
Output Result:

{'username': 'username1', 'address_id': 17L, 'name': 'user1', 'address': {'id': 17L, 'address': u'\u81fa\u7063\u51f1\u9054\u683c\u862d\u5927\u9053'},
 'password': '3833b3a1c69cf71a31d86cb5bb4d3866789b4d1e', 'id': 33L}
{'username': 'username2', 'address_id': 17L, 'name': 'user2', 'address': {'id': 17L, 'address': u'\u81fa\u7063\u51f1\u9054\u683c\u862d\u5927\u9053'},
 'password': '148dfdc3c539d35004cb808ca84e17ff962af744', 'id': 34L}

"""

參考資料(Refs)：

1. http://chimerhapsody.blogspot.tw/2013/09/python-sqlalchemy-orm-part-3-end.html
2. http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html
3. http://blogs.gnome.org/danni/2013/03/07/generating-json-from-sqlalchemy-objects/