sql - How to export a MySQL database to JSON? -

i interested in exporting subset of values mysql database json-formatted file on disk.

i found link talks possible way this: http://www.thomasfrank.se/mysql_to_json.html

... when use method page, seems work 2 problems:

1) returns around 15 results, last 1 abruptly cut off (incomplete). standard query returns around 4000 results when run select name, email students enrolled null when run as:

select       concat("[",           group_concat(                concat("{name:'",name,"'"),                concat(",email:'",email,"'}")           )      ,"]")  json students enrolled null; 

... described in link, returns (as mentioned) 15 results. (fwiw, checked these results against 4000 i'm supposed get, , these 15 same first 15 of 4000)

2) there seem "escape" characters included in actual file when add into outfile '/path/to/jsonoutput.txt' fields terminated ',' end of query. commas end looking '\,' when have commas without \.

any ideas on how proper json output mysql? (either using method, or other method)?

thanks!

it may asking of mysql expect produce formed json directly query. instead, consider producing more convenient, csv (using into outfile '/path/to/output.csv' fields terminated ',' snippet know) , transforming results json in language built in support it, python or php.

edit python example, using fine sqlalchemy:

class student(object):     '''the model, 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('name', sqlalchemy.string(100)),         sqlalchemy.column('enrolled', sqlalchemy.date)     )  # connect database.  substitute needed values. engine = sqlalchemy.create_engine('mysql://user:pass@host/database')  # if needed, create table: metadata.create_all(engine)  # map model table import sqlalchemy.orm sqlalchemy.orm.mapper(student, students_table)  # can issue queries against database using mapping: non_students = engine.query(student).filter_by(enrolled=none)  # , lets make json out of it: import json non_students_dicts = ( student.make_dict() student in non_students) students_json = json.dumps(non_students_dicts)