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

Question

我试图用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和SQL server是完全不同的机器。他们实际上相距很远

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

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

'The SQL contains 56 parameter markers, but 1 parameters were supplied', '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)

Answer 1

有很多方法可以做到这一点，但我认为举个例子会有所帮助，你可能需要这样的东西：

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

或者本质上

db.execute(yourQueryHere, json_vals)