python插入数据库怎么操作？请给出完整代码

Question

主要问题在于整理好数据格式并插入数据库。难度不高，主要是我是刚接触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 json
import mysql.connector

def 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)

我想要达到的结果

把数据插入到数据库中。

Answer 1

传参不应该用字典，应该用元组，最后还丢了con.commit()，先给你改了一下，但是还有个问题，第二个表中的crm_mco_billdocument_id有唯一约束，只能插入一条，你得看看你定得数据解析规则是否有问题

import json
import mysql.connector
 
def insert_data(order_detail):
    #config = {'host': 'localhost','user': 'root','password': '123456','port': 3306,'database': 'mushi','charset': 'utf8'}
    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=(order_detail['data']['orderdetail']['crm_mco_billdocument_id'],
        order_detail['data']['orderdetail']['crm_mco_linkman'],
        order_detail['data']['orderdetail']['crm_mco_phone'],
        order_detail['data']['orderdetail']['crm_mco_address'],
        order_detail['data']['orderdetail']['crm_mco_seller'],
        order_detail['data']['orderdetail']['crm_mco_operatedate'],
        order_detail['data']['orderdetail']['crm_mco_inoutspecies'],
        order_detail['data']['orderdetail']['LinkMan'],
        order_detail['data']['orderdetail']['hr_fty_name'],
        order_detail['data']['orderdetail']['isCoupon'],
        order_detail['data']['orderdetail']['ROW_NUMBER'],
        order_detail['data']['orderdetail']['total_num'],
        order_detail['data']['orderdetail']['total_tons'],
        order_detail['data']['orderdetail']['total_money'],
        )
        cursor.execute(sql_query_orderdetail,order_detailmk)
        con.commit()

        sql_query_zenglist=("INSERT INTO `mushi`.`zenglist` (`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`)"
        " VALUES (%s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s)"
        )
        zenglists = order_detail['data']['zenglist']
        for zenglist in zenglists:
            order_zenglistmk=(order_detail['data']['orderdetail']['crm_mco_billdocument_id'],
            zenglist['productid'],
            zenglist['appellation'],
            zenglist['crm_lco_amount'],
            zenglist['crm_lco_famount'],
            zenglist['crm_lco_summoney'],
            zenglist['crm_lco_price'],
            zenglist['crm_lco_fprice'],
            zenglist['spec'],
            zenglist['productunitname'],
            zenglist['productType'],
            zenglist['nature'],
            zenglist['crm_lco_isgift'],
            zenglist['pstr8'],
            zenglist['ROW_NUMBER']
            )
            cursor.execute(sql_query_zenglist,order_zenglistmk)
            con.commit()

        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 (%s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s, %s, %s, %s,%s, %s, %s, %s)"
        )
        ordergoods = order_detail['data']['ordergoods']
        for k,v in ordergoods.items():
            order_ordergoodsmk=(order_detail['data']['orderdetail']['crm_mco_billdocument_id'],
            v['data']['productid'],#发现有问题，不会做了。
            v['data']['appellation'],
            v['data']['crm_lco_amount'],
            v['data']['crm_lco_famount'],
            v['data']['crm_lco_summoney'],
            v['data']['crm_lco_price'],
            v['data']['crm_lco_fprice'],
            v['data']['spec'],
            v['data']['productunitname'],
            v['data']['productType'],
            v['data']['nature'],
            v['data']['crm_lco_isgift'],
            v['data']['pstr8'],
            v['data']['ROW_NUMBER'],
            v['zeng_crm_lco_amount'],
            v['zeng_crm_lco_famount'],
            v['zeng_goods'],
            )
            cursor.execute(sql_query_ordergoods,order_ordergoodsmk)
            con.commit()
    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)