引言
在现代数据处理流程中，JSON作为轻量级数据交换格式被广泛应用。本教程将详细讲解如何通过Python将JSON格式的数据高效写入MySQL数据库，涵盖从环境配置到数据落地的全流程。
一、环境准备
所需工具
- Python 3.8+
- MySQL 8.0+
- Python库：
第一步 pip install pymysql 第二步 在 Django 项目的 __init__.py 中添加： import pymysql pymysql.install_as_MySQLdb()数据库配置
创建目标数据库和表（示例）：
CREATE DATABASE json_db;
USE json_db;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
二、实现流程
三、代码实现
1. 示例JSON数据（jiukuaijiu.json）
[
{
"category": "\u7537\u88c5",
"goods": [
]
},
{
"category": "\u5973\u88c5",
"goods": [
{
"sizes": [
[
"150",
"150"
],
[
"160",
"160"
],
[
"165",
"165"
],
[
"170",
"170"
]
],
"goods_oldprice": "499.00",
"goods_desc": "\u68a6\u5a1c\u4e16\u5bb62017\u5973\u5f0f\u65b0\u6b3e\u4fee\u8eab\u4e2d\u957f\u6b3e\u6bdb\u9886\u65f6\u5c1a\u663e\u7626\u6b27\u7f8e\u6c14\u8d28\u7fbd\u7ed2\u670dA88",
"colors": [
[
"\u7ea2\u8272",
"/media/color/hong_Dm4fQ6U.jpg"
],
[
"\u7eff\u8272",
"/media/color/lv_cTMJg2K.jpg"
],
[
"\u9ec4\u8272",
"/media/color/huang_bvCMlhn.jpg"
],
[
"\u9ed1\u8272",
"/media/color/hei_rFOWelp.jpg"
]
],
"goodsname": "90\u7ed2\u5927\u6bdb\u9886\u4fdd\u6696\u7fbd\u7ed2\u670d",
"goods_price": "99.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_mpwtoGA.jpg",
"/media/2_UuQkY4b.jpg",
"/media/3_ViMgWv6.jpg",
"/media/4_BDmgdFv.jpg",
"/media/5_ozWIsej.jpg",
"/media/6_Pny8yTQ.jpg",
"/media/7_K4tB09L.jpg",
"/media/8_60MJMwS.jpg",
"/media/9_8YomGSk.jpg",
"/media/10_vonnLjk.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "100.00",
"goods_desc": "\u79cb\u88c5\u65f6\u5c1a\u5370\u82b1\u590d\u53e4\u65f6\u5c1a\u4f11\u95f2\u4e24\u4ef6\u5957\u88c5\u88d9\u5b50",
"colors": [
[
"\u767d\u8272",
"/media/color/bai_mt1VrH5.jpg"
],
[
"\u4e09\u53f7\u8272",
"/media/color/san.jpg"
]
],
"goodsname": "\u79cb\u65f6\u5c1a\u5370\u82b1\u4e24\u4ef6\u5957\u88c5\u88d9",
"goods_price": "35.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_DNiW0D5.jpg",
"/media/2_eHi0Rix.jpg",
"/media/3_2e1cWcs.jpg",
"/media/4_D0ck80V.jpg",
"/media/5_bxMyxv5.jpg",
"/media/6_Z4j72Ft.jpg",
"/media/7_3QbFC0z.jpg",
"/media/8_OQGcrwL.jpg"
]
]
]
},
{
"sizes": [
[
"\u5747\u7801",
"\u5747\u7801"
]
],
"goods_oldprice": "369.00",
"goods_desc": "\u65b0\u6b3e\u97e9\u7248\u524d\u77ed\u540e\u957f \u4fa7\u5f00\u53c9\u5bbd\u677e\u5706\u9886 \u7eaf\u8272\u5927\u7801\u5957\u5934\u9488\u7ec7\u886b\u5916\u5957",
"colors": [
[
"\u7ea2\u8272",
"/media/color/hong_y7wQwRw.jpg"
],
[
"\u9ec4\u8272",
"/media/color/huang_NKcgOlT.jpg"
],
[
"\u7d2b\u8272",
"/media/color/zi_umktLLD.jpg"
]
],
"goodsname": "\u97e9\u7248\u4fa7\u5f00\u53c9\u5bbd\u677e\u6bdb\u8863",
"goods_price": "39.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_QoLgPlj.jpg",
"/media/2_lHY8mE9.jpg",
"/media/3_GiFc4gk.jpg",
"/media/4_IBO3QkF.jpg",
"/media/5_NjssJjH.jpg",
"/media/6_TJDGChY.jpg",
"/media/7_Sv0tWHZ.jpg",
"/media/8_MDhSM1I.jpg",
"/media/9_BUoWkrL.jpg",
"/media/10_k9f1eEK.jpg"
]
]
]
},
{
"sizes": [
[
"M",
"M"
]
],
"goods_oldprice": "296.00",
"goods_desc": "2017\u79cb\u5b63\u65b0\u6b3e\u4e0a\u8863\u6f6e \u7b11\u8138\u5b66\u9662\u98ce\u9488\u7ec7\u80cc\u5fc3\u65e0\u8896\u5957\u5934\u6bdb\u8863\u9a6c\u7532\u5973\u6625\u79cb",
"colors": [
[
"\u84dd\u8272",
"/media/color/lan_BKQdpOj.jpg"
]
],
"goodsname": "\u65e0\u8896\u5957\u5934\u6bdb\u8863\u9a6c\u7532\u5973\u79cb",
"goods_price": "39.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_ORTzeTY.jpg",
"/media/2_RbgTYId.jpg",
"/media/3_1CyOSyR.jpg",
"/media/4_0490Pq4.jpg",
"/media/5_gk51Yc1.jpg",
"/media/6_MNo76Wb.jpg",
"/media/7_4JyLPNO.jpg",
"/media/8_VX32aT2.jpg",
"/media/9_x8mkplo.jpg",
"/media/10_1OVaH2E.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "229.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u65b0\u6b3e\u7ea2\u8272\u539f\u5bbfbf\u98ce\u725b\u4ed4\u5c0f\u5916\u5957\u5973",
"colors": [
[
"\u7ea2\u8272",
"/media/color/hong_zHPXVqY.jpg"
]
],
"goodsname": "\u7ea2\u8272\u539f\u5bbfbf\u98ce\u5c0f\u5916\u5957",
"goods_price": "69.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_0IilSTS.jpg",
"/media/2_dU6RLKQ.jpg",
"/media/3_eLYKeSJ.jpg",
"/media/4_mwUqQ7u.jpg",
"/media/5_AOfqMfX.jpg",
"/media/6_LE1Qg19.jpg",
"/media/7_7RVZFif.jpg",
"/media/8_W5zUBfp.jpg",
"/media/9_Py3cDKv.jpg",
"/media/10_w9OXfoC.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
]
],
"goods_oldprice": "199.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u65b0\u6b3e\u4e0d\u89c4\u5219\u6bdb\u8fb9\u5587\u53ed\u957f\u88e4\u725b\u4ed4\u88e4",
"colors": [
[
"\u84dd\u8272",
"/media/color/lan_IThSO4Z.jpg"
]
],
"goodsname": "\u4e0d\u89c4\u5219\u6bdb\u8fb9\u5587\u53ed\u725b\u4ed4\u88e4",
"goods_price": "65.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_f8GP2Js.jpg",
"/media/2_mewqiym.jpg",
"/media/3_T3najRK.jpg",
"/media/4_Zn7OFjf.jpg",
"/media/5_z6JRqPe.jpg",
"/media/6_96JhJlq.jpg",
"/media/7_eNtOUP3.jpg",
"/media/8_B13UoeN.jpg"
]
]
]
},
{
"sizes": [
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "259.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u65b0\u6b3e\u5bbd\u677e\u77ed\u6b3e\u65f6\u5c1a\u7ee3\u82b1\u725b\u4ed4\u5916\u5957\u5973",
"colors": [
[
"\u84dd\u8272",
"/media/color/lan_ELubwxG.jpg"
]
],
"goodsname": "\u5bbd\u677e\u77ed\u6b3e\u725b\u4ed4\u5916\u5957",
"goods_price": "79.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_bmqDR3N.jpg",
"/media/2_72CrUqv.jpg",
"/media/3_FVVk5kE.jpg",
"/media/4_LNKrlRN.jpg",
"/media/5_qHsL809.jpg",
"/media/6_EXgGQLK.jpg",
"/media/7_lJD2O84.jpg",
"/media/8_TM4De6X.jpg",
"/media/9_gLF6TGo.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "199.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u65b0\u6b3e\u6c14\u8d28\u65f6\u5c1a\u6536\u8170\u663e\u7626\u8fde\u8863\u88d9\u5973",
"colors": [
[
"\u7eff\u8272",
"/media/color/lv_H5hkmmq.jpg"
],
[
"\u7ea2\u8272",
"/media/color/hong_4FhJTrJ.jpg"
],
[
"\u84dd\u8272",
"/media/color/lan_7rd4yDs.jpg"
]
],
"goodsname": "\u6c14\u8d28\u6536\u8170\u663e\u7626\u8fde\u8863\u88d9",
"goods_price": "69.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_VJ7ZAQ6.jpg",
"/media/2_w4cxzhY.jpg",
"/media/3_U55lgsP.jpg",
"/media/4_EzoxL21.jpg",
"/media/5_Rewgb01.jpg",
"/media/6_ze76f9K.jpg",
"/media/7_6Wq1Bgx.jpg",
"/media/8_R2uMfUz.jpg",
"/media/9_D7uVd9z.jpg",
"/media/10_0RIywDD.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "199.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u65b0\u6b3e\u9488\u7ec7\u8896\u65f6\u5c1a\u62fc\u63a5\u6bdb\u5462\u6253\u5e95\u88d9",
"colors": [
[
"\u7070\u8272",
"/media/color/hui.jpg"
],
[
"\u84dd\u8272",
"/media/color/lan.jpg"
],
[
"\u7ea2\u8272",
"/media/color/hong_UO2LzHh.jpg"
],
[
"\u9ed1\u8272",
"/media/color/hei_cOOnNKI.jpg"
]
],
"goodsname": "\u9488\u7ec7\u8896\u62fc\u63a5\u6bdb\u5462\u6253\u5e95\u88d9",
"goods_price": "69.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_mfc1bTY.jpg",
"/media/2_GohidDO.jpg",
"/media/3_mFj1A5X.jpg",
"/media/4_H23ayWL.jpg",
"/media/5_SGzSxZZ.jpg",
"/media/6_li3hJqN.jpg",
"/media/7_lK1M9SF.jpg",
"/media/8_7L4e40W.jpg",
"/media/9_TW7TlmY.jpg",
"/media/10_woLP7jo.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "299.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u65b0\u6b3e\u7b80\u7ea6\u53cc\u6392\u6263\u5047\u4e24\u4ef6\u8fde\u8863\u88d9\u5973",
"colors": [
[
"\u9ed1\u8272",
"/media/color/hei_kYyKPNv.jpg"
]
],
"goodsname": "\u53cc\u6392\u6263\u5047\u4e24\u4ef6\u8fde\u8863\u88d9",
"goods_price": "69.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_oeshNKk.jpg",
"/media/2_iQ7dNj1.jpg",
"/media/3_8WGGwE5.jpg",
"/media/4_m2QcMFM.jpg",
"/media/5_m3BYUyr.jpg",
"/media/6_mJhhcKP.jpg",
"/media/7_NKEyR9K.jpg",
"/media/8_gRR4RHz.jpg",
"/media/9_YhUmuWF.jpg",
"/media/10_8H4hKoc.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "199.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u65b0\u6b3e\u4fee\u8eab\u5305\u81c0\u857e\u4e1d\u6253\u5e95\u88d9\u8fde\u8863\u88d9",
"colors": [
[
"\u7eff\u8272",
"/media/color/lv_sxlJZTq.jpg"
],
[
"\u767d\u8272",
"/media/color/bai_B7tmsjh.jpg"
],
[
"\u9ed1\u8272",
"/media/color/hei_sNOao2p.jpg"
]
],
"goodsname": "\u4fee\u8eab\u5305\u81c0\u857e\u4e1d\u6253\u5e95\u88d9",
"goods_price": "69.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_Rqdz7U7.jpg",
"/media/2_wyGJ4ta.jpg",
"/media/3_NNxcobt.jpg",
"/media/4_LOhSXlh.jpg",
"/media/5_OEepnkc.jpg",
"/media/6_om0yrNS.jpg",
"/media/7_va0yr9Y.jpg",
"/media/8_egWx7Pl.jpg",
"/media/9_JJPOi3d.jpg",
"/media/10_yXoBnL0.jpg"
]
]
]
},
{
"sizes": [
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "259.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u65b0\u6b3e\u4fee\u8eab\u663e\u7626\u683c\u5b50\u6253\u5e95\u88d9\u8fde\u8863\u88d9",
"colors": [
[
"\u6a58\u8272",
"/media/color/ju.jpg"
],
[
"\u7d2b\u8272",
"/media/color/zi.jpg"
]
],
"goodsname": "\u4fee\u8eab\u663e\u7626\u683c\u5b50\u6253\u5e95\u88d9",
"goods_price": "69.90",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_EPbLlsh.jpg",
"/media/2_CG1HTi2.jpg",
"/media/3_LrJ1TCJ.jpg",
"/media/4_ppO44fs.jpg",
"/media/5_3tIgCS6.jpg",
"/media/6_KN6SOts.jpg",
"/media/7_7vLJg1T.jpg",
"/media/8_BaXff43.jpg",
"/media/9_AexcCbE.jpg",
"/media/10_yG5bZ6x.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "199.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u65b0\u6b3e\u6761\u7eb9\u65f6\u5c1a\u663e\u7626\u7f51\u7eb1\u8fde\u8863\u88d9\u5973",
"colors": [
[
"\u6591\u9a6c\u8272",
"/media/color/ban_fSqFE03.jpg"
],
[
"\u9ed1\u8272",
"/media/color/hei_ZyxMfgc.jpg"
]
],
"goodsname": "\u6761\u7eb9\u663e\u7626\u7f51\u7eb1\u8fde\u8863\u88d9",
"goods_price": "69.90",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_X1vUHuX.jpg",
"/media/2_rQvH5hc.jpg",
"/media/3_T5aiJXo.jpg",
"/media/4_VYfCSBP.jpg",
"/media/5_KdbwcPL.jpg",
"/media/6_7stuZRI.jpg",
"/media/7_WB2mznP.jpg",
"/media/8_7HnFJUe.jpg",
"/media/9_HN634xt.jpg",
"/media/10_Gs7AS8Z.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "255.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u65b0\u6b3e\u65f6\u5c1a\u663e\u7626\u857e\u4e1d\u4e2d\u957f\u8fde\u8863\u88d9\u5973",
"colors": [
[
"\u767d\u8272",
"/media/color/bai.jpg"
],
[
"\u9ed1\u8272",
"/media/color/hei_goVnbhs.jpg"
]
],
"goodsname": "\u663e\u7626\u857e\u4e1d\u4e2d\u957f\u8fde\u8863\u88d9",
"goods_price": "69.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_qhSeLSP.jpg",
"/media/2_T1EYNmq.jpg",
"/media/3_M5MK3Rp.jpg",
"/media/4_Qsb0wFi.jpg",
"/media/5_8L7yPar.jpg",
"/media/6_Xaip1y1.jpg",
"/media/7_MEUf1z0.jpg",
"/media/8_KtL5Rj0.jpg",
"/media/9_yFn3P2g.jpg",
"/media/10_TmP9DXa.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "299.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u65b0\u6b3e\u65f6\u5c1a\u4fee\u8eab\u4e24\u4ef6\u5957\u88c5\u8fde\u8863\u88d9\u5973",
"colors": [
[
"\u7eff\u8272",
"/media/color/lv_eFlravj.jpg"
],
[
"\u7ea2\u8272",
"/media/color/hong_nwQGdMd.jpg"
],
[
"\u68d5\u8272",
"/media/color/zong_v7JDaAc.jpg"
]
],
"goodsname": "\u65f6\u5c1a\u4fee\u8eab\u4e24\u4ef6\u5957\u88c5\u88d9",
"goods_price": "68.90",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_YVRqAfG.jpg",
"/media/2_rJP2AdH.jpg",
"/media/3_gQdONuG.jpg",
"/media/4_7T0yj4F.jpg",
"/media/5_DI2p1Wl.jpg",
"/media/6_CmqXZLW.jpg",
"/media/7_XOIrJSq.jpg",
"/media/8_ZqFUlsq.jpg",
"/media/9_CwxVXnR.jpg",
"/media/10_gn66t3j.jpg"
]
]
]
},
{
"sizes": [
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "199.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u6761\u7eb9\u65f6\u5c1a\u9488\u7ec7\u5305\u81c0\u6253\u5e95\u88d9\u8fde\u8863\u88d9",
"colors": [
[
"\u6591\u9a6c\u8272",
"/media/color/ban.jpg"
]
],
"goodsname": "\u6761\u7eb9\u9488\u7ec7\u5305\u81c0\u8fde\u8863\u88d9",
"goods_price": "65.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_v57UFo5.jpg",
"/media/2_nT3X3Gi.jpg",
"/media/3_C9361FH.jpg",
"/media/4_KPjviHC.jpg",
"/media/5_j9H6NUg.jpg",
"/media/6_0yvtHsK.jpg",
"/media/7_OyIqVRu.jpg",
"/media/8_zaePCtI.jpg",
"/media/9_zDqfKRE.jpg",
"/media/10_o3nc5fU.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "259.00",
"goods_desc": "\u8bd7\u8d6b\u59ff\u79cb\u7cfb\u5e26\u663e\u7626\u5bbd\u677e\u65f6\u5c1a\u5957\u88c5\u9614\u817f\u88e4\u5973",
"colors": [
[
"\u9ed1\u8272",
"/media/color/hei_3vOTo3s.jpg"
],
[
"\u7eff\u8272",
"/media/color/lv.jpg"
],
[
"\u9ec4",
"/media/color/huang.jpg"
]
],
"goodsname": "\u7cfb\u5e26\u663e\u7626\u5bbd\u677e\u5957\u88c5",
"goods_price": "69.90",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_vXCGxCI.jpg",
"/media/2_54gKgQP.jpg",
"/media/3_eNxYhCr.jpg",
"/media/4_BprQkfJ.jpg",
"/media/5_qsc95lP.jpg",
"/media/6_UAGLwbX.jpg",
"/media/7_F6hIwWB.jpg",
"/media/8_a2kJabS.jpg",
"/media/9_SZ0wMpy.jpg",
"/media/10_MeWXSKs.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
]
],
"goods_oldprice": "124.00",
"goods_desc": "\u65b0\u6b3e\u5927\u7801\u5973\u88c5\u857e\u4e1d\u886b\u97e9\u7248\u4fee\u8eabV\u9886\u957f\u8896\u6253\u5e95\u886b\u7f51\u7eb1\u9542\u7a7a\u4e0a\u8863",
"colors": [
[
"\u9ed1\u8272",
"/media/color/hei_JeBWGjF.jpg"
]
],
"goodsname": "\u79cb\u5b63V\u9886\u9542\u7a7a\u857e\u4e1d\u886b",
"goods_price": "28.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_NMUUNXC.jpg",
"/media/2_Ouot4Pr.jpg",
"/media/3_Gz1jfea.jpg",
"/media/4_I75CkJ3.jpg",
"/media/5_o83wrz9.jpg",
"/media/6_a6urQrM.jpg",
"/media/7_Wj7Dhuj.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "199.00",
"goods_desc": "\u79cb\u51ac\u65f6\u5c1a\u767e\u642d\u9ad8\u8170PU\u76ae\u9614\u817f\u77ed\u88e4\u5973\u6253\u5e95\u76ae\u88e4",
"colors": [
[
"\u9ed1\u8272",
"/media/color/hei_LfSKcUV.jpg"
]
],
"goodsname": "\u677e\u7d27\u8170PU\u76ae\u77ed\u88e4",
"goods_price": "19.90",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_hYKWHNU.jpg",
"/media/2_HG8qNBO.jpg",
"/media/3_aDwC2Ql.jpg",
"/media/4_Rj4l1L8.jpg",
"/media/5_23MIkpl.jpg",
"/media/6_q3Kpy4T.jpg",
"/media/7_9VDJMAo.jpg",
"/media/8_aIKJvFt.jpg",
"/media/9_InpWyDb.jpg",
"/media/10_5jDjWzb.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "199.00",
"goods_desc": "\u79cb\u51ac\u65f6\u5c1a\u9ad8\u8170\u523a\u7ee3PU\u76ae\u77ed\u88d9\u5973\u6253\u5e95\u534a\u8eab\u88d9",
"colors": [
[
"\u9ed1\u8272",
"/media/color/hei_1d7yrVm.jpg"
]
],
"goodsname": "\u9ad8\u8170\u523a\u7ee3PU\u76ae\u77ed\u88d9",
"goods_price": "29.90",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_elE8eIp.jpg",
"/media/2_4vn9XVx.jpg",
"/media/3_D1iL18X.jpg",
"/media/4_i3s0HY6.jpg",
"/media/5_fjAcCOl.jpg",
"/media/6_ECsXNEg.jpg",
"/media/7_7pnv5eG.jpg",
"/media/8_dDvDZt3.jpg",
"/media/9_e0PyyUN.jpg",
"/media/10_w27K7NE.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "199.00",
"goods_desc": "\u79cb\u88c5\u65b0\u6b3e\u5973\u88c5\u6bdb\u5462\u77ed\u5916\u5957\u5973 \u65f6\u5c1a\u4fee\u8eab\u77ed\u6b3e\u5462\u5b50\u5c0f\u897f\u88c5\u6f6e",
"colors": [
[
"\u9ed1\u8272",
"/media/color/hei.jpg"
],
[
"\u7ea2\u8272",
"/media/color/hongse.jpg"
]
],
"goodsname": "\u4fee\u8eab\u77ed\u6b3e\u5462\u5b50\u5c0f\u5916\u5957",
"goods_price": "39.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1_lCofK1Q.jpg",
"/media/2_fN7fXqT.jpg",
"/media/3_sQci5N1.jpg",
"/media/4_t8f1jLp.jpg",
"/media/5_pnAqZij.jpg",
"/media/6_Jyvt0gV.jpg",
"/media/7_88GAaTo.jpg",
"/media/8_dwmGQq1.jpg",
"/media/9_IVxniTc.jpg",
"/media/10_MBQOH1D.jpg"
]
]
]
},
{
"sizes": [
[
"S",
"S"
],
[
"M",
"M"
],
[
"L",
"L"
],
[
"XL",
"XL"
]
],
"goods_oldprice": "256.00",
"goods_desc": "\u4e2d\u957f\u6b3e\u53cc\u9762\u5462\u6bdb\u5462\u5916\u5957",
"colors": [
[
"\u9ed1\u8272",
"/media/color/h_2og4uJv.jpg"
],
[
"\u7ea2\u8272",
"/media/color/hong.jpg"
],
[
"\u68d5\u8272",
"/media/color/zong.jpg"
]
],
"goodsname": "\u4e2d\u957f\u6b3e\u53cc\u9762\u5462\u6bdb\u5462\u5916\u5957",
"goods_price": "33.00",
"specs": [
[
"\u53c2\u6570\u89c4\u683c",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_03.png"
]
],
[
"\u6574\u4f53\u6b3e\u5f0f",
[
"/static/img/%E8%AF%A6%E6%83%85%E9%A1%B5_06.png"
]
],
[
"\u6a21\u7279\u5b9e\u62cd",
[
"/media/1.jpg",
"/media/2.jpg",
"/media/3.jpg",
"/media/4.jpg",
"/media/5.jpg",
"/media/6.jpg",
"/media/7.jpg",
"/media/8.jpg",
"/media/9.jpg",
"/media/10.jpg"
]
]
]
}
]
},
{
"category": "\u978b\u5b50",
"goods": [
]
},
{
"category": "\u7bb1\u5305",
"goods": [
]
},
{
"category": "\u6bcd\u5a74",
"goods": [
]
},
{
"category": "\u7f8e\u5986",
"goods": [
]
},
{
"category": "\u5c45\u5bb6",
"goods": [
]
},
{
"category": "\u5bb6\u7eba",
"goods": [
]
},
{
"category": "\u6587\u4f53",
"goods": [
]
},
{
"category": "\u7f8e\u98df",
"goods": [
]
},
{
"category": "\u6570\u7801",
"goods": [
]
},
{
"category": "\u7535\u5668",
"goods": [
]
},
{
"category": "\u5185\u8863",
"goods": [
]
},
{
"category": "\u88c5\u9970",
"goods": [
]
}
]
2. Python处理脚本
from collections.abc import Sized
from django.db.transaction import atomic
from goods.models import Category, Goods, Size, Color, GoodDetail, GoodDetailName, Inventory
@atomic
def test_model():
with open('utils/jiukuaijiu.json') as fr:
import json
datas = json.loads(fr.read())
for data in datas:
cate = Category.objects.create(cname=data['category'])
_goods = data['goods']
for goods in _goods:
good = Goods.objects.create(
gname=goods['goodsname'],gdesc=goods['goods_desc'],
price=goods['goods_price'],oldprice=goods['goods_oldprice'],
category=cate
)
sizes = []
for _size in goods['sizes']:
if Size.objects.filter(sname=_size[0]).count()==1:
size = Size.objects.get(sname=_size[0])
else:
size = Size.objects.create(sname=_size[0])
sizes.append(size)
colors = []
for _color in goods['colors']:
color = Color.objects.create(colorname=_color[0],colorurl=_color[1])
colors.append(color)
for _spec in goods['specs']:
goodsdet = GoodDetailName.objects.create(gdname=_spec[0])
for img in _spec[1]:
GoodDetail.objects.create(goods=good,gdname=goodsdet,gdurl=img)
for c in colors:
for s in sizes:
Inventory.objects.create(count=100,goods=good,color=c,size=s)
def deleteall():
Category.objects.filter().delete()
Color.objects.filter().delete()
Size.objects.filter().delete()
json文件要与代码在同一层次，否则运行会有路径问题。
在控制台输入这两段代码，发现json数据导入成功
from utils.loaddata import *
test_model()
四、关键技术点
1. 数据映射关系
| JSON字段 | MySQL字段 | 数据类型 |
|---|---|---|
| name | name | VARCHAR |
| VARCHAR | ||
| age | age | INT |
2. 批量插入优势
使用executemany()方法比单条插入效率提升显著：
插入耗时 ∝ 1 n ( n = 批量大小 ) \text{插入耗时} \propto \frac{1}{n} \quad (n=\text{批量大小}) 插入耗时∝n1(n=批量大小)
3. 错误处理机制
- 使用try-except捕获数据库异常
- finally确保连接关闭
- 事务提交(commit)保障数据一致性
五、执行结果验证
SELECT * FROM users;
| id | name | age | created_at | |
|---|---|---|---|---|
| 1 | 张三 | zhangsan@example.com | 28 | 2023-08-20 10:25:31 |
| 2 | 李四 | lisi@example.com | 32 | 2023-08-20 10:25:31 |
六、常见问题解决
- 中文乱码问题：
conn.set_charset_collation('utf8mb4') - 数据类型转换：
- JSON数字 → MySQL INT/DECIMAL
- JSON布尔 → MySQL TINYINT(1)
- 连接超时处理：
db_config['pool_size'] = 5 db_config['pool_timeout'] = 30
七、扩展应用
- 动态表结构生成
- 实时JSON流处理
- 与APIs集成实现自动化数据管道
最佳实践：生产环境建议使用SQLAlchemy等ORM工具，增强代码可维护性。
总结
通过本教程，您已掌握：
- JSON数据解析技巧
- Python-MySQL高效交互
- 批量数据处理方法
- 异常处理机制