可能要求MysqL太多的人期望MysqL直接从查询中生成格式正确的json。相反,可以考虑生成更方便的内容,例如CSV(使用INTO OUTFILE '/path/to/output.csv' FIELDS TERMINATED BY ','
您已经知道的摘录),然后将结果转换为使用内置支持的语言(如python或PHP)的json。
python示例:
class Student(object):
'''The model, a plain, ol python class'''
def __init__(self, name, email, enrolled):
self.name = name
self.email = email
self.enrolled = enrolled
def __repr__(self):
return "<Student(%r, %r)>" % (self.name, self.email)
def make_dict(self):
return {'name': self.name, 'email': self.email}
import sqlalchemy
Metadata = sqlalchemy.MetaData()
students_table = sqlalchemy.Table('students', Metadata,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('name', sqlalchemy.String(100)),
sqlalchemy.Column('email', sqlalchemy.String(100)),
sqlalchemy.Column('enrolled', sqlalchemy.Date)
)
# connect the database. substitute the needed values.
engine = sqlalchemy.create_engine('MysqL://user:pass@host/database')
# if needed, create the table:
Metadata.create_all(engine)
# map the model to the table
import sqlalchemy.orm
sqlalchemy.orm.mapper(Student, students_table)
# Now you can issue queries against the database using the mapping:
non_students = engine.query(Student).filter_by(enrolled=None)
# and lets make some json out of it:
import json
non_students_dicts = ( student.make_dict() for student in non_students)
students_json = json.dumps(non_students_dicts)