用Python将mysql数据导出成json的方法
1、相关说明
此脚本可以将Mysql的数据导出成Json格式,导出的内容可以进行select查询确定。
数据传入参数有:dbConfigName,selectSql,jsonPath,fileName。
依赖的库有:MySQLdb、json,尤其MySQLdb需要事先安装好。
2、Python脚本及测试示例
/Users/nisj/PycharmProjects/BiDataProc/oldPythonBak/mysqlData2json.py
#-*-coding=utf-8-*- importMySQLdb importwarnings importdatetime importsys importjson 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) defgetDB(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("SETNAMESutf8"); curr.execute("USE%s"%dbConfig['db']); returnconn,curr exceptMySQLdb.Error,e: print"MysqlError%d:%s"%(e.args[0],e.args[1]) returnNone,None defmysql2json(dbConfigName,selectSql,jsonPath,fileName): conn,curr=getDB(dbConfigName) curr.execute(selectSql) datas=curr.fetchall() fields=curr.description column_list=[] forfieldinfields: column_list.append(field[0]) withopen('{jsonPath}{fileName}.json'.format(jsonPath=jsonPath,fileName=fileName),'w+')asf: forrowindatas: result={} forfieldIndexinrange(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() #BatchTest dbConfigName='mysqlDb_config' selectSql="SELECTuid,name,phone_num,qq,area,created_timeFROMmatch_applywherematch_id=83orderbycreated_timedesc;" jsonPath='/Users/nisj/Desktop/' fileName='mysql2json' mysql2json(dbConfigName,selectSql,jsonPath,fileName)
以上这篇用Python将mysql数据导出成json的方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持毛票票。