如何将MySQL数据库导出为JSON? [英] How to export a MySQL database to JSON?

问题描述

我有兴趣将值的子集从MySQL数据库导出到磁盘上的JSON格式的文件中.

I am interested in exporting a subset of values from a MySQL database into a JSON-formatted file on disk.

我找到了一个链接，该链接讨论了执行此操作的可能方法: http://www.thomasfrank. se/mysql_to_json.html

I found a link that talks about a possible way to do this: http://www.thomasfrank.se/mysql_to_json.html

...但是当我使用该页面中的方法时，它似乎可以工作，但有两个问题:

... but when I use the method from that page, it seems to work but with two problems:

1)它仅返回大约15个结果，最后一个突然中断(不完整).当我以SELECT name, email FROM students WHERE enrolled IS NULL身份运行它时，我的标准查询返回大约4000个结果 但是当我以以下方式运行它时:

1) It only returns around 15 results, with the last one abruptly cut off (incomplete). My standard query for this returns around 4000 results when I just run it as SELECT name, email FROM students WHERE enrolled IS NULL But when I run it as:

SELECT 
     CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{name:'",name,"'"),
               CONCAT(",email:'",email,"'}")
          )
     ,"]") 
AS json FROM students WHERE enrolled IS NULL;

...如链接中所述，它仅返回(如我所述)15个结果. (一开始，我对照应该得到的4000来检查了这些结果，而这15个结果与4000的前15个结果相同)

... as described in the link, it only returns (as I mentioned) 15 results. (fwiw, I checked these results against the 4000 I'm supposed to get, and these 15 are the same as the first 15 of the 4000)

2)当我在该查询的末尾添加INTO OUTFILE '/path/to/jsonoutput.txt' FIELDS TERMINATED BY ','时，实际文件中似乎包含转义"字符.因此，逗号最终看起来像是"\"，而显然我只希望逗号不带有\.

2) There seem to be "escape" characters included in the actual file when I add INTO OUTFILE '/path/to/jsonoutput.txt' FIELDS TERMINATED BY ',' to the end of that query. So commas end up looking like '\,' when obviously I would just like to have the commas without the \.

关于如何从MySQL获取正确的JSON输出的任何想法? (使用此方法还是其他方法)?

Any ideas on how to get proper JSON output from MySQL? (Either using this method, or some other method)?

谢谢！

推荐答案

MySQL可能要求太多，以至于不能期望它直接从查询中生成格式正确的json.相反，可以考虑生成更方便的内容，例如CSV(使用您已经知道的INTO OUTFILE '/path/to/output.csv' FIELDS TERMINATED BY ','代码段)，然后将结果转换为使用内置支持的语言(如python或php)的json.

It may be asking too much of MySQL to expect it to produce well formed json directly from a query. Instead, consider producing something more convenient, like CSV (using the INTO OUTFILE '/path/to/output.csv' FIELDS TERMINATED BY ',' snippet you already know) and then transforming the results into json in a language with built in support for it, like python or php.

编辑 python示例，使用优质的SQLAlchemy:

Edit python example, using the fine SQLAlchemy:

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)

这篇关于如何将MySQL数据库导出为JSON?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！