您现在的位置：程式師世界 >> 編程語言 > >> 更多編程語言 >> Python

python插入數據庫怎麼操作？請給出完整代碼

編輯：Python

主要問題在於整理好數據格式並插入數據庫。難度不高，主要是我是剛接觸python，想趕緊完成任務，不想試了。數據庫的地址已經給出，可以自行登錄查看數據庫格式進行測試。謝謝大家幫忙。

問題相關代碼，請勿粘貼截圖

原始json格式：
{
"msg": "ok",
"status": 0,
"data": {
"orderdetail": {
"bak": "",
"crm_mco_billdocument_id": "CO2207010002",
"crm_mco_linkman": "",
"crm_mco_phone": "1399999999",
"crm_mco_address": "通遼市通遼市村",
"crm_mco_seller": "業務員姓名",
"crm_mco_operatedate": "2022-07-01 09:51:22",
"crm_mco_inoutspecies": "15",
"LinkMan": "聯系人姓名",
"hr_fty_name": "發貨工廠",
"isCoupon": "0",
"ROW_NUMBER": "1",
"total_num": 210,
"total_tons": 10.500000000000002,
"total_money": 36050
},
"ordergoods": {
"CF00032-0004": {
"data": {
"productid": "CF00032-0004",
"appellation": "產品名稱",
"crm_lco_amount": "40.0000",
"crm_lco_famount": "2.0000",
"crm_lco_summoney": "8700.0000",
"crm_lco_price": "217.50000000",
"crm_lco_fprice": "217.50000000",
"spec": "50kg/包",
"productunitname": "包",
"productType": "1",
"nature": "噸",
"crm_lco_isgift": "",
"pstr8": "產品代號",
"ROW_NUMBER": "1"
},
"zeng_crm_lco_amount": "2.0000",
"zeng_crm_lco_famount": ".1000",
"zeng_goods": "產品代號",
"productunitname": "包",
"nature": "噸"
},
"CF00035-0004": {
"data": {
"productid": "CF00035-0004",
"appellation": "產品名稱",
"crm_lco_amount": "120.0000",
"crm_lco_famount": "6.0000",
"crm_lco_summoney": "19800.0000",
"crm_lco_price": "165.00000000",
"crm_lco_fprice": "165.00000000",
"spec": "50kg/包",
"productunitname": "包",
"productType": "1",
"nature": "噸",
"crm_lco_isgift": "",
"pstr8": "產品代號2",
"ROW_NUMBER": "3"
},
"zeng_crm_lco_amount": "6.0000",
"zeng_crm_lco_famount": ".3000",
"zeng_goods": "產品代號2",
"productunitname": "包",
"nature": "噸"
},
"CF00039-0004": {
"data": {
"productid": "CF00039-0004",
"appellation": "產品名稱3",
"crm_lco_amount": "20.0000",
"crm_lco_famount": "1.0000",
"crm_lco_summoney": "3800.0000",
"crm_lco_price": "190.00000000",
"crm_lco_fprice": "190.00000000",
"spec": "50kg/包",
"productunitname": "包",
"productType": "1",
"nature": "噸",
"crm_lco_isgift": "",
"pstr8": "產品代號3",
"ROW_NUMBER": "5"
},
"zeng_crm_lco_amount": "1.0000",
"zeng_crm_lco_famount": ".0500",
"zeng_goods": "產品代號",
"productunitname": "包",
"nature": "噸"
},
"CF00221-0001": {
"data": {
"productid": "CF00221-0001",
"appellation": "產品代號",
"crm_lco_amount": "20.0000",
"crm_lco_famount": "1.0000",
"crm_lco_summoney": "3750.0000",
"crm_lco_price": "187.50000000",
"crm_lco_fprice": "187.50000000",
"spec": "50kg/包",
"productunitname": "包",
"productType": "1",
"nature": "噸",
"crm_lco_isgift": "",
"pstr8": "產品代號",
"ROW_NUMBER": "7"
},
"zeng_crm_lco_amount": "1.0000",
"zeng_crm_lco_famount": ".0500",
"zeng_goods": "產品代號",
"productunitname": "包",
"nature": "噸"
}
},
"zenglist": [{
"productid": "DG00001-0001",
"appellation": "產品代號產品代號",
"crm_lco_amount": "1.0000",
"crm_lco_famount": ".0000",
"crm_lco_summoney": ".0000",
"crm_lco_price": ".00000000",
"crm_lco_fprice": ".00000000",
"spec": "-",
"productunitname": "件",
"productType": "6",
"nature": "0",
"crm_lco_isgift": "是",
"pstr8": "產品代號產品代號",
"ROW_NUMBER": "9"
}]
}
}

我的解答思路和嘗試過的方法

import jsonimport mysql.connectordef insert_data(order_detail): config = {
'host': '111.231.146.150','user': 'mushi','password': 'mushi','port': 16988,'database': 'mushi','charset': 'utf8'} try: con=mysql.connector.connect(**config) cursor = con.cursor() except mysql.connector.error as e: print('數據庫連接錯誤!{}'.format(e)) try:#數據庫插入 sql_query_orderdetail=("INSERT INTO `mushi`.`orderdetail` (`crm_mco_billdocument_id`, \ `crm_mco_linkman`, `crm_mco_phone`, `crm_mco_address`, `crm_mco_seller`, `crm_mco_operatedate`, `crm_mco_inoutspecies`, `LinkMan`,\ `hr_fty_name`, `isCoupon`, `ROW_NUMBER`, `total_num`, `total_tons`, `total_money`)" " VALUES (%s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s, %s, %s, %s)" ) order_detailmk={
'crm_mco_billdocument_id':order_detail['data']['orderdetail']['crm_mco_billdocument_id'], 'crm_mco_linkman':order_detail['data']['orderdetail']['crm_mco_linkman'], 'crm_mco_phone':order_detail['data']['orderdetail']['crm_mco_phone'], 'crm_mco_address':order_detail['data']['orderdetail']['crm_mco_address'], 'crm_mco_seller':order_detail['data']['orderdetail']['crm_mco_seller'], 'crm_mco_operatedate':order_detail['data']['orderdetail']['crm_mco_operatedate'], 'crm_mco_inoutspecies':order_detail['data']['orderdetail']['crm_mco_inoutspecies'], 'LinkMan':order_detail['data']['orderdetail']['LinkMan'], 'hr_fty_name':order_detail['data']['orderdetail']['hr_fty_name'], 'isCoupon':order_detail['data']['orderdetail']['isCoupon'], 'ROW_NUMBER':order_detail['data']['orderdetail']['ROW_NUMBER'], 'total_num':order_detail['data']['orderdetail']['total_num'], 'total_tons':order_detail['data']['orderdetail']['total_tons'], 'total_money':order_detail['data']['orderdetail']['total_money'], } sql_query_ordergoods="INSERT INTO `mushi`.`ordergoods` (`crm_mco_billdocument_id`, `productid`, `appellation`, `crm_lco_amount`, `crm_lco_famount`, `crm_lco_summoney`, `crm_lco_price`, `crm_lco_fprice`, `spec`, `productunitname`, `productType`, `nature`, `crm_lco_isgift`, `pstr8`, `ROW_NUMBER`, `zeng_crm_lco_amount`, `zeng_crm_lco_famount`, `zeng_goods`) VALUES ('從從', '', '', NULL, '', NULL, '', '321', NULL, '', NULL, '', '', NULL, '', '', '', '');" order_ordergoodsmk={
'crm_mco_billdocument_id':order_detail['data']['orderdetail']['crm_mco_billdocument_id'], 'productid':order_detail['ordergoods']['CF00000000XX']['data']['productid'],#發現有問題，不會做了。 'appellation':order_detail['data']['orderdetail']['crm_mco_phone'], 'crm_lco_amount':order_detail['data']['orderdetail']['crm_mco_address'], 'crm_lco_famount':order_detail['data']['orderdetail']['crm_mco_seller'], 'crm_lco_summoney':order_detail['data']['orderdetail']['crm_mco_operatedate'], 'crm_lco_price':order_detail['data']['orderdetail']['crm_mco_inoutspecies'], 'crm_lco_fprice':order_detail['data']['orderdetail']['LinkMan'], 'spec':order_detail['data']['orderdetail']['hr_fty_name'], 'productunitname':order_detail['data']['orderdetail']['isCoupon'], 'productType':order_detail['data']['orderdetail']['ROW_NUMBER'], 'nature':order_detail['data']['orderdetail']['total_num'], 'crm_lco_isgift':order_detail['data']['orderdetail']['total_tons'], 'pstr8':order_detail['data']['orderdetail']['total_money'], 'ROW_NUMBER':order_detail['data']['orderdetail']['total_money'], 'zeng_crm_lco_amount':order_detail['data']['orderdetail']['total_money'], 'zeng_crm_lco_famount':order_detail['data']['orderdetail']['total_money'], 'zeng_goods':order_detail['data']['orderdetail']['total_money'], } cursor.execute(sql_query_orderdetail,order_detailmk) cursor.execute(sql_query_ordergoods,order_ordergoodsmk) except mysql.connector.Error as e: print('語句執行錯誤!{}'.format(e)) finally: cursor.close() con.close() text = """{'msg': '請求無效', 'status': 404, 'data': []}""" text = text.replace('\'','\"') a= json.loads(text) print(a['status'])#程序開始jsontext="""{"msg":"ok","status":0,"data":{"orderdetail":{"bak":"","crm_mco_billdocument_id":"CO2207010002","crm_mco_linkman":"","crm_mco_phone":"1399999999","crm_mco_address":"通遼市通遼市村","crm_mco_seller":"業務員姓名","crm_mco_operatedate":"2022-07-01 09:51:22","crm_mco_inoutspecies":"15","LinkMan":"聯系人姓名","hr_fty_name":"發貨工廠","isCoupon":"0","ROW_NUMBER":"1","total_num":210,"total_tons":10.500000000000002,"total_money":36050},"ordergoods":{"CF00032-0004":{"data":{"productid":"CF00032-0004","appellation":"產品名稱","crm_lco_amount":"40.0000","crm_lco_famount":"2.0000","crm_lco_summoney":"8700.0000","crm_lco_price":"217.50000000","crm_lco_fprice":"217.50000000","spec":"50kg\/包","productunitname":"包","productType":"1","nature":"噸","crm_lco_isgift":"","pstr8":"產品代號","ROW_NUMBER":"1"},"zeng_crm_lco_amount":"2.0000","zeng_crm_lco_famount":".1000","zeng_goods":"產品代號","productunitname":"包","nature":"噸"},"CF00035-0004":{"data":{"productid":"CF00035-0004","appellation":"產品名稱","crm_lco_amount":"120.0000","crm_lco_famount":"6.0000","crm_lco_summoney":"19800.0000","crm_lco_price":"165.00000000","crm_lco_fprice":"165.00000000","spec":"50kg\/包","productunitname":"包","productType":"1","nature":"噸","crm_lco_isgift":"","pstr8":"產品代號2","ROW_NUMBER":"3"},"zeng_crm_lco_amount":"6.0000","zeng_crm_lco_famount":".3000","zeng_goods":"產品代號2","productunitname":"包","nature":"噸"},"CF00039-0004":{"data":{"productid":"CF00039-0004","appellation":"產品名稱3","crm_lco_amount":"20.0000","crm_lco_famount":"1.0000","crm_lco_summoney":"3800.0000","crm_lco_price":"190.00000000","crm_lco_fprice":"190.00000000","spec":"50kg\/包","productunitname":"包","productType":"1","nature":"噸","crm_lco_isgift":"","pstr8":"產品代號3","ROW_NUMBER":"5"},"zeng_crm_lco_amount":"1.0000","zeng_crm_lco_famount":".0500","zeng_goods":"產品代號","productunitname":"包","nature":"噸"},"CF00221-0001":{"data":{"productid":"CF00221-0001","appellation":"產品代號","crm_lco_amount":"20.0000","crm_lco_famount":"1.0000","crm_lco_summoney":"3750.0000","crm_lco_price":"187.50000000","crm_lco_fprice":"187.50000000","spec":"50kg\/包","productunitname":"包","productType":"1","nature":"噸","crm_lco_isgift":"","pstr8":"產品代號","ROW_NUMBER":"7"},"zeng_crm_lco_amount":"1.0000","zeng_crm_lco_famount":".0500","zeng_goods":"產品代號","productunitname":"包","nature":"噸"}},"zenglist":[{"productid":"DG00001-0001","appellation":"產品代號產品代號","crm_lco_amount":"1.0000","crm_lco_famount":".0000","crm_lco_summoney":".0000","crm_lco_price":".00000000","crm_lco_fprice":".00000000","spec":"-","productunitname":"件","productType":"6","nature":"0","crm_lco_isgift":"是","pstr8":"產品代號產品代號","ROW_NUMBER":"9"}]}}"""jsontext = jsontext.replace('\'','\"')order_detail = json.loads(jsontext)insert_data(order_detail)