时间戳

首页 > 解决方案 > 无法将此 JSON 数据插入 SQL Server？

python - 无法将此 JSON 数据插入 SQL Server？

问题描述

我正在尝试使用 python 代码将以下 JSON 数据插入到 SQL Server 上的表中。

('{"ITEMCODE":"A00975605","DISPLAYREVISIONNO":1,"ITEMNAME":"ABCDEFGHIJKLMN","ITEMNAME_ID":"~0000PCLAZ","ITEMTYPE":2,"ITEMTYPEEX":0,"PRODUCTTYPE":12,"LOTTYPE":"S1","EDITIONNOTYPE":0,"ISINVENTORY":2,"HOSTMANAGETYPE":-1,"SHIPMENTJUDGETYPE":null,"UNITVOLUME":1000,"TAREWEIGHT":0,"DEFAULTRECIPECODE":null,"ISQUALITYCHECK":1,"ISALLOCATEMORELOT":0,"ISINSERTLOTCHECK":-1,"RECEIPTVOLUMELIMIT":"10","RECEIPTZONECODE":"FS01","WAREHOUSEZONECODE":null,"SHIPMENTZONECODE":null,"LABELCODE":null,"THEME":null,"ARTICLENO":null,"ARTICLENAME":null,"ARTICLENAME_ID":null,"PACKAGEINDICATOR":null,"PRODUCTIONFLOWNO":null,"RECEIPTFLOWNO":null,"TESTFLOWNO":null,"RECORDREVIEWFLOWNO":null,"SHIPMENTGMPFLOWNO":null,"SHIPMENTGQPFLOWNO":null,"NOTE":null,"NOTE_ID":null,"STRENGTHUNITFLAG":0,"MEDICINEDIVISION":null,"EVALUATIONCLASS":"6200","SAMPLINGVALUEFORLIST":null,"STOCKVALUEFORLIST":null,"FILLINGVOLUME":null,"FILLINGVOLUMEMAXIMUMLIMIT":null,"FILLINGVOLUMEMINIMUMLIMIT":null,"FILTRATIONTYPE":null,"STERILIZATIONTYPE":null,"BOTTLEWASHING":null,"STRENGTHUNITFLAGEX":0,"DEFAULTMAKERCODE":"H75776","PRODUCTCODEFORBCD ":null,"STOCKCONDITIONCODE":5,"METALUSE":null,"AUTOALLOCFLAG":1,"INNERPACKAGEVOLUME ":1000,"AREACODE":"A1","STANDARDSTRENGTHUNITRATIO":1}',)
('{"ITEMCODE":"A00975612","DISPLAYREVISIONNO":1,"ITEMNAME":"BCDEFGHIJKLMNO","ITEMNAME_ID":"~0000PCLHS","ITEMTYPE":2,"ITEMTYPEEX":0,"PRODUCTTYPE":12,"LOTTYPE":"S1","EDITIONNOTYPE":0,"ISINVENTORY":2,"HOSTMANAGETYPE":-1,"SHIPMENTJUDGETYPE":null,"UNITVOLUME":1000,"TAREWEIGHT":0,"DEFAULTRECIPECODE":null,"ISQUALITYCHECK":1,"ISALLOCATEMORELOT":0,"ISINSERTLOTCHECK":-1,"RECEIPTVOLUMELIMIT":"10","RECEIPTZONECODE":"FS01","WAREHOUSEZONECODE":null,"SHIPMENTZONECODE":null,"LABELCODE":null,"THEME":null,"ARTICLENO":null,"ARTICLENAME":null,"ARTICLENAME_ID":null,"PACKAGEINDICATOR":null,"PRODUCTIONFLOWNO":null,"RECEIPTFLOWNO":null,"TESTFLOWNO":null,"RECORDREVIEWFLOWNO":null,"SHIPMENTGMPFLOWNO":null,"SHIPMENTGQPFLOWNO":null,"NOTE":null,"NOTE_ID":null,"STRENGTHUNITFLAG":0,"MEDICINEDIVISION":null,"EVALUATIONCLASS":"6200","SAMPLINGVALUEFORLIST":null,"STOCKVALUEFORLIST":null,"FILLINGVOLUME":null,"FILLINGVOLUMEMAXIMUMLIMIT":null,"FILLINGVOLUMEMINIMUMLIMIT":null,"FILTRATIONTYPE":null,"STERILIZATIONTYPE":null,"BOTTLEWASHING":null,"STRENGTHUNITFLAGEX":0,"DEFAULTMAKERCODE":"H75776","PRODUCTCODEFORBCD ":null,"STOCKCONDITIONCODE":5,"METALUSE":null,"AUTOALLOCFLAG":1,"INNERPACKAGEVOLUME ":1000,"AREACODE":"A1","STANDARDSTRENGTHUNITRATIO":1}',)
('{"ITEMCODE":"A00977746","DISPLAYREVISIONNO":1,"ITEMNAME":"CDEFGHIJKLMNOP","ITEMNAME_ID":"~0000PCJZN","ITEMTYPE":2,"ITEMTYPEEX":0,"PRODUCTTYPE":12,"LOTTYPE":"S1","EDITIONNOTYPE":0,"ISINVENTORY":2,"HOSTMANAGETYPE":-1,"SHIPMENTJUDGETYPE":null,"UNITVOLUME":1000,"TAREWEIGHT":0,"DEFAULTRECIPECODE":null,"ISQUALITYCHECK":1,"ISALLOCATEMORELOT":0,"ISINSERTLOTCHECK":-1,"RECEIPTVOLUMELIMIT":"10","RECEIPTZONECODE":"FS01","WAREHOUSEZONECODE":null,"SHIPMENTZONECODE":null,"LABELCODE":null,"THEME":null,"ARTICLENO":null,"ARTICLENAME":null,"ARTICLENAME_ID":null,"PACKAGEINDICATOR":null,"PRODUCTIONFLOWNO":null,"RECEIPTFLOWNO":null,"TESTFLOWNO":null,"RECORDREVIEWFLOWNO":null,"SHIPMENTGMPFLOWNO":null,"SHIPMENTGQPFLOWNO":null,"NOTE":null,"NOTE_ID":null,"STRENGTHUNITFLAG":0,"MEDICINEDIVISION":null,"EVALUATIONCLASS":"6200","SAMPLINGVALUEFORLIST":null,"STOCKVALUEFORLIST":null,"FILLINGVOLUME":null,"FILLINGVOLUMEMAXIMUMLIMIT":null,"FILLINGVOLUMEMINIMUMLIMIT":null,"FILTRATIONTYPE":null,"STERILIZATIONTYPE":null,"BOTTLEWASHING":null,"STRENGTHUNITFLAGEX":0,"DEFAULTMAKERCODE":"H75776","PRODUCTCODEFORBCD ":null,"STOCKCONDITIONCODE":5,"METALUSE":null,"AUTOALLOCFLAG":1,"INNERPACKAGEVOLUME ":1000,"AREACODE":"A1","STANDARDSTRENGTHUNITRATIO":1}',)
('{"ITEMCODE":"A00977753","DISPLAYREVISIONNO":1,"ITEMNAME":"DEFGHIJKLMNOPQ","ITEMNAME_ID":"~0000PCJZP","ITEMTYPE":2,"ITEMTYPEEX":0,"PRODUCTTYPE":12,"LOTTYPE":"S1","EDITIONNOTYPE":0,"ISINVENTORY":2,"HOSTMANAGETYPE":-1,"SHIPMENTJUDGETYPE":null,"UNITVOLUME":1000,"TAREWEIGHT":0,"DEFAULTRECIPECODE":null,"ISQUALITYCHECK":1,"ISALLOCATEMORELOT":0,"ISINSERTLOTCHECK":-1,"RECEIPTVOLUMELIMIT":"10","RECEIPTZONECODE":"FS01","WAREHOUSEZONECODE":null,"SHIPMENTZONECODE":null,"LABELCODE":null,"THEME":null,"ARTICLENO":null,"ARTICLENAME":null,"ARTICLENAME_ID":null,"PACKAGEINDICATOR":null,"PRODUCTIONFLOWNO":null,"RECEIPTFLOWNO":null,"TESTFLOWNO":null,"RECORDREVIEWFLOWNO":null,"SHIPMENTGMPFLOWNO":null,"SHIPMENTGQPFLOWNO":null,"NOTE":null,"NOTE_ID":null,"STRENGTHUNITFLAG":0,"MEDICINEDIVISION":null,"EVALUATIONCLASS":"6200","SAMPLINGVALUEFORLIST":null,"STOCKVALUEFORLIST":null,"FILLINGVOLUME":null,"FILLINGVOLUMEMAXIMUMLIMIT":null,"FILLINGVOLUMEMINIMUMLIMIT":null,"FILTRATIONTYPE":null,"STERILIZATIONTYPE":null,"BOTTLEWASHING":null,"STRENGTHUNITFLAGEX":0,"DEFAULTMAKERCODE":"H75776","PRODUCTCODEFORBCD ":null,"STOCKCONDITIONCODE":5,"METALUSE":null,"AUTOALLOCFLAG":1,"INNERPACKAGEVOLUME ":1000,"AREACODE":"A1","STANDARDSTRENGTHUNITRATIO":1}',)
('{"ITEMCODE":"A00977760","DISPLAYREVISIONNO":1,"ITEMNAME":"EFGHIJKLMNOPQR","ITEMNAME_ID":"~0000PCJZR","ITEMTYPE":2,"ITEMTYPEEX":0,"PRODUCTTYPE":12,"LOTTYPE":"S1","EDITIONNOTYPE":0,"ISINVENTORY":2,"HOSTMANAGETYPE":-1,"SHIPMENTJUDGETYPE":null,"UNITVOLUME":1000,"TAREWEIGHT":0,"DEFAULTRECIPECODE":null,"ISQUALITYCHECK":1,"ISALLOCATEMORELOT":0,"ISINSERTLOTCHECK":-1,"RECEIPTVOLUMELIMIT":"10","RECEIPTZONECODE":"FS01","WAREHOUSEZONECODE":null,"SHIPMENTZONECODE":null,"LABELCODE":null,"THEME":null,"ARTICLENO":null,"ARTICLENAME":null,"ARTICLENAME_ID":null,"PACKAGEINDICATOR":null,"PRODUCTIONFLOWNO":null,"RECEIPTFLOWNO":null,"TESTFLOWNO":null,"RECORDREVIEWFLOWNO":null,"SHIPMENTGMPFLOWNO":null,"SHIPMENTGQPFLOWNO":null,"NOTE":null,"NOTE_ID":null,"STRENGTHUNITFLAG":0,"MEDICINEDIVISION":null,"EVALUATIONCLASS":"6200","SAMPLINGVALUEFORLIST":null,"STOCKVALUEFORLIST":null,"FILLINGVOLUME":null,"FILLINGVOLUMEMAXIMUMLIMIT":null,"FILLINGVOLUMEMINIMUMLIMIT":null,"FILTRATIONTYPE":null,"STERILIZATIONTYPE":null,"BOTTLEWASHING":null,"STRENGTHUNITFLAGEX":0,"DEFAULTMAKERCODE":"H75776","PRODUCTCODEFORBCD ":null,"STOCKCONDITIONCODE":5,"METALUSE":null,"AUTOALLOCFLAG":1,"INNERPACKAGEVOLUME ":1000,"AREACODE":"A1","STANDARDSTRENGTHUNITRATIO":1}',)

如果我可以通过执行 SQL 语句来解决这个问题，我会很高兴。因为我们用python的Application server和SQL Server是完全不同的机器。他们实际上相距甚远。

尝试下面的代码，我遇到了一个错误。谁能给我一些建议？

def jsonINSERT(_cn, _cur, jdata):
    SQL = """
    INSERT INTO TSTTBL VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """
    _cur.executemany(SQL, jdata)
    _cn.commit()
    return

'SQL 包含 56 个参数标记，但提供了 1 个参数', 'HY000'




我最终喜欢上了这个。

def jsonINSERT(_cn, _cur, sql, jdata):
    _cur.executemany(sql, jdata)
    _cn.commit()
    return


#From Oracle
fetch_dat = cur.fetchall()

#To take tuple off
fetch_dat = [json.loads(t[0]) for t in fetch_dat]

#To declare columns for the query
columns = tuple(fetch_dat[0].keys())
params = ['?'  for _ in range(len(columns))]
    
#To declare value for the query
data = [tuple(e.values()) for e in fetch_dat]


#Composing the sql!
sql = 'INSERT INTO TSTTBL ({}) VALUES ({})'.format(','.join(columns), ','.join(params))
    
jsonINSERT(mscn, mscur, sql, data)

标签： pythonjsonsql-server

解决方案

有很多方法可以解决这个问题，但我认为一个例子会有所帮助，你可能需要这样的东西：

db.execute("INSERT INTO `t1` VALUES (%s)", json_vals)

或者本质上，

db.execute(yourQueryHere, json_vals)

推荐阅读