json同mysql python_python实现通用json导入到mysql

importjsonfrom Db importDb

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"

}

}‘‘‘

defloadJsonFromFile(file):‘‘‘加载配置

:return:‘‘‘with open(file) as f:returnjson.load(f)defgetData(jsonObj, path):returnjsonObj[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 inpathArr:

jsonobj=getData(jsonobj, key)

vlist=[]for i, data inenumerate(jsonobj):print("处理第%d行" % (i + 1))

temlist=[];for k incolsidx:

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("处理完成")