用Python将json数据装载到mysql

1、相关说明
此脚本可以将json数据解析之后装载到Mysql;暂未进行大批量装载方面的优化处理；由于json的字段定义太灵活，所以暂时未进行太自动化的处理，要装载的表名也需要先手动建好。
数据传入参数有：dbConfigName, tabName, jsonPath, fileName。
依赖的库有：MySQLdb、json，尤其MySQLdb需要事先安装好。

2、Python脚本及测试示例
/Users/nisj/PycharmProjects/BiDataProc/oldPythonBak/jsonData2mysql.py

# -*- coding=utf-8 -*-import MySQLdbimport warningsimport datetimeimport sysimport jsonreload(sys)sys.setdefaultencoding('utf8')warnings.filterwarnings("ignore")# target Database configtargetMysqlConfig_funnyai_data = {    # 'host': 'MysqlHostInnerIp',    'host': 'MysqlHostOuterIp',    'user': 'MysqlUser',    'passwd': 'MysqlPass',    'port': 6603,    'db': 'funnyai_data'}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, Nonedef json2mysql(dbConfigName, tabName, jsonPath, fileName):    conn, curr = getDB(dbConfigName)    curr.execute("truncate table {tabName};".format(tabName=tabName))    with open('{jsonPath}{fileName}.json'.format(jsonPath=jsonPath, fileName=fileName), 'r') as f:        for line in f:            lineDict=json.loads(line)            colDesc = ''            colValue = ''            for key in lineDict.keys():                colDesc = colDesc + key + ','                colValue = colValue + '\'' + lineDict[key] + '\','            insertSql = "insert into {tabName}({colDesc}) select {colValue} ;".format(tabName=tabName, colDesc=colDesc[:-1], colValue=colValue[:-1])            curr.execute(insertSql)    f.close()    curr.close()    conn.close()# Batch TestdbConfigName = 'targetMysqlConfig_funnyai_data'tabName = "xx_python_json"jsonPath = '/Users/nisj/Desktop/'fileName = 'mysql2json'json2mysql(dbConfigName, tabName, jsonPath, fileName)

3、测试表建表

drop table if exists xx_python_json;create table xx_python_json(uid varchar(20),name varchar(20),phone_num varchar(20),qq varchar(20),area varchar(20),created_time varchar(20));