python实现通用json导入到mysql
import json from Db import Db db = Db() ''' 参考配置文件 { "file": "test.json", "path": "data", "table": "tk_bill", "comitcount": 50, "map": { "trade_id": "trade_id", "tk_status": "tk_status", "item_title": "item_title", "alipay_total_price": "alipay_total_price", "tk_paid_time": "tk_paid_time", "pub_share_pre_fee": "pub_share_pre_fee", "subsidy_fee": "subsidy_fee", "tk_settletime": "tk_settletime" } } ''' def loadJsonFromFile(file): ''' 加载配置 :return: ''' with open(file) as f: return json.load(f) def getData(jsonObj, path): return jsonObj[path] if __name__ == '__main__': importConfig = loadJsonFromFile("jsonmap.conf") # 获取要导入的表名 tableName = importConfig["table"] comitcount = importConfig["comitcount"] cols = [] colsidx = [] for k, v in importConfig["map"].items(): cols.append(k) colsidx.append(v) sqlpre = "insert into %s(%s) values " % (tableName, ",".join(cols)) jsonobj = loadJsonFromFile(importConfig["file"]) # 获取列表部分 pathArr = [] if importConfig["path"] != "": pathArr = importConfig["path"].split(".") for key in pathArr: jsonobj = getData(jsonobj, key) vlist = [] for i, data in enumerate(jsonobj): print("处理第%d行" % (i + 1)) temlist = []; for k in colsidx: s = "'%s'" % data[k].replace("'", "''") temlist.append(s) vlist.append("(%s)" % ",".join(temlist)) if i % comitcount == 0: sql = sqlpre + ",".join(vlist) db.execSql(sql) vlist.clear() continue if len(vlist) > 0: sql = sqlpre + ",".join(vlist) db.execSql(sql) vlist.clear() print("处理完成")