用Python将mysql数据导出成json的方法
程序员文章站
2023-11-11 17:54:58
1、相关说明
此脚本可以将mysql的数据导出成json格式,导出的内容可以进行select查询确定。
数据传入参数有:dbconfigname, selectsql,...
1、相关说明
此脚本可以将mysql的数据导出成json格式,导出的内容可以进行select查询确定。
数据传入参数有:dbconfigname, selectsql, jsonpath, filename。
依赖的库有:mysqldb、json,尤其mysqldb需要事先安装好。
2、python脚本及测试示例
/users/nisj/pycharmprojects/bidataproc/oldpythonbak/mysqldata2json.py
# -*- coding=utf-8 -*- import mysqldb import warnings import datetime import sys import json reload(sys) sys.setdefaultencoding('utf8') warnings.filterwarnings("ignore") mysqldb_config = { 'host': 'mysqlhostip', 'user': 'mysqluser', 'passwd': 'mysqlpass', 'port': 50512, 'db': 'tv_event' } today = datetime.date.today() yesterday = today - datetime.timedelta(days=1) tomorrow = today + datetime.timedelta(days=1) def getdb(dbconfigname): dbconfig = eval(dbconfigname) try: conn = mysqldb.connect(host=dbconfig['host'], user=dbconfig['user'], passwd=dbconfig['passwd'], port=dbconfig['port']) conn.autocommit(true) curr = conn.cursor() curr.execute("set names utf8"); curr.execute("use %s" % dbconfig['db']); return conn, curr except mysqldb.error, e: print "mysql error %d: %s" % (e.args[0], e.args[1]) return none, none def mysql2json(dbconfigname, selectsql, jsonpath, filename): conn, curr = getdb(dbconfigname) curr.execute(selectsql) datas = curr.fetchall() fields = curr.description column_list = [] for field in fields: column_list.append(field[0]) with open('{jsonpath}{filename}.json'.format(jsonpath=jsonpath, filename=filename), 'w+') as f: for row in datas: result = {} for fieldindex in range(0, len(column_list)): result[column_list[fieldindex]] = str(row[fieldindex]) jsondata=json.dumps(result, ensure_ascii=false) f.write(jsondata + '\n') f.close() curr.close() conn.close() # batch test dbconfigname = 'mysqldb_config' selectsql = "select uid,name,phone_num,qq,area,created_time from match_apply where match_id = 83 order by created_time desc;" jsonpath = '/users/nisj/desktop/' filename = 'mysql2json' mysql2json(dbconfigname, selectsql, jsonpath, filename)
以上这篇用python将mysql数据导出成json的方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。
下一篇: python安装twisted的问题解析