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/