一.Python3的json模块介绍
在数据处理的过程中，经常遇到json格式的，那么python里面是如何处理json格式的数据呢?
这里就要提到python的json模块了。
json.dumps()和json.loads()是json格式处理函数（可以这么理解，json是字符串）
- json.dumps()函数是将一个Python数据类型列表进行json格式的编码（可以这么理解，json.dumps()函数是将字典转化为字符串）
- json.loads()函数是将json格式数据转换为字典（可以这么理解，json.loads()函数是将字符串转化为字典）
image.png
image.png
二.Python json模块实例
2.1 json与字典格式相互转换
代码:
import json
dict1 = {'age':16} #这是一个字典
print("原来的类型:",type(dict1))
str_json = json.dumps(dict1) #将字典转换为json（字符串）
print("经过json.dumps()转换后类型变为：",type(str_json))
dict2 = json.loads(str_json) #将json（字符串）转换为字典
print("经过json.loads()转换后类型变为：",type(dict2))
测试记录:
E:\python\learn_python1\venv\Scripts\python.exe E:/python/learn_python1/json_test3.py
原来的类型: <class 'dict'>
经过json.dumps()转换后类型变为： <class 'str'>
经过json.loads()转换后类型变为： <class 'dict'>
Process finished with exit code 0
2.2 将json数据落地到实体表
现在需要将json数据落地到实体的表
mysql端建表语句
create table prod_main(id int,name varchar(100));
create table prod_sub1(id int,name varchar(100),prod_main_id int);
create table prod_sub2(id int,name varchar(100),prod_sub1_id int);
python代码:
import pymysql, datetime
import json
json_data = [
{
"id": 1,
"name": "男士",
"productTypeDtos": [
{
"id": 23,
"name": "上装",
"productTypeDtos": [
{
"id": 33,
"name": "毛衣"
},
{
"id": 34,
"name": "T恤"
},
{
"id": 35,
"name": "衬衫"
},
{
"id": 36,
"name": "卫衣"
},
{
"id": 37,
"name": "背心"
},
{
"id": 38,
"name": "针织衫"
},
{
"id": 39,
"name": "POLO衫"
}
]
},
{
"id": 24,
"name": "裤装",
"productTypeDtos": [
{
"id": 40,
"name": "背带裤"
},
{
"id": 41,
"name": "休闲裤"
},
{
"id": 42,
"name": "短裤"
},
{
"id": 43,
"name": "运动裤"
},
{
"id": 44,
"name": "哈伦裤"
},
{
"id": 45,
"name": "九分裤"
},
{
"id": 46,
"name": "七分裤"
},
{
"id": 47,
"name": "西裤"
},
{
"id": 48,
"name": "牛仔裤"
}
]
},
{
"id": 25,
"name": "外套",
"productTypeDtos": [
{
"id": 49,
"name": "皮衣"
},
{
"id": 50,
"name": "风衣"
},
{
"id": 51,
"name": "夹克"
},
{
"id": 52,
"name": "便西"
},
{
"id": 53,
"name": "马甲"
},
{
"id": 54,
"name": "大衣"
},
{
"id": 55,
"name": "羽绒服"
}
]
}
]
},
{
"id": 2,
"name": "女士",
"productTypeDtos": [
{
"id": 26,
"name": "上装",
"productTypeDtos": [
{
"id": 56,
"name": "T恤"
},
{
"id": 57,
"name": "衬衫"
},
{
"id": 58,
"name": "上衣"
},
{
"id": 59,
"name": "卫衣"
},
{
"id": 60,
"name": "雪纺衫"
},
{
"id": 61,
"name": "针织衫"
},
{
"id": 62,
"name": "背心"
}
]
},
{
"id": 27,
"name": "裤装",
"productTypeDtos": [
{
"id": 63,
"name": "打底裤"
},
{
"id": 64,
"name": "休闲裤"
},
{
"id": 65,
"name": "牛仔裤"
},
{
"id": 66,
"name": "短裤"
},
{
"id": 67,
"name": "连体裤"
},
{
"id": 68,
"name": "七分裤"
},
{
"id": 69,
"name": "九分裤"
},
{
"id": 70,
"name": "哈伦裤"
}
]
},
{
"id": 29,
"name": "裙装",
"productTypeDtos": [
{
"id": 71,
"name": "连衣裙"
},
{
"id": 72,
"name": "半身裙"
},
{
"id": 73,
"name": "套裙"
},
{
"id": 74,
"name": "迷你短裙"
},
{
"id": 75,
"name": "礼服"
}
]
},
{
"id": 28,
"name": "外套",
"productTypeDtos": [
{
"id": 76,
"name": "风衣"
},
{
"id": 77,
"name": "便西"
},
{
"id": 78,
"name": "夹克"
},
{
"id": 79,
"name": "马甲"
},
{
"id": 80,
"name": "斗篷"
},
{
"id": 81,
"name": "大衣"
},
{
"id": 82,
"name": "羽绒服"
},
{
"id": 83,
"name": "皮草"
}
]
},
{
"id": 30,
"name": "内衣",
"productTypeDtos": [
{
"id": 84,
"name": "内衣"
}
]
}
]
},
{
"id": 3,
"name": "童装",
"productTypeDtos": [
{
"id": 31,
"name": "男童",
"productTypeDtos": [
{
"id": 85,
"name": "T恤"
},
{
"id": 86,
"name": "外套"
},
{
"id": 87,
"name": "卫衣"
},
{
"id": 88,
"name": "衬衫"
},
{
"id": 89,
"name": "裤子"
}
]
},
{
"id": 32,
"name": "女童",
"productTypeDtos": [
{
"id": 90,
"name": "连衣裙"
},
{
"id": 91,
"name": "T恤"
},
{
"id": 92,
"name": "卫衣"
},
{
"id": 93,
"name": "背心"
},
{
"id": 94,
"name": "礼服"
},
{
"id": 95,
"name": "雪纺衫"
},
{
"id": 96,
"name": "衬衫"
},
{
"id": 97,
"name": "裤子"
},
{
"id": 98,
"name": "半身裙"
},
{
"id": 99,
"name": "外套"
}
]
}
]
}
]
sql1 = "insert into prod_main(id ,name) values ('%s', '%s')"
sql2 = "insert into prod_sub1(id ,name ,prod_main_id) values ('%s', '%s', '%s')"
sql3 = "insert into prod_sub2(id ,name , prod_sub1_id) values ('%s', '%s', '%s')"
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='abc123',db='zqs')
cursor = conn.cursor()
for i1 in json_data:
#print(i)
#print(i1['id'])
#print(i1['name'])
cursor.execute(sql1 % (i1['id'],i1['name']))
for i2 in i1['productTypeDtos']:
#print(i2['id'])
#print(i2['name'])
cursor.execute(sql2 % (i2['id'], i2['name'],i1['id']) )
for i3 in i2['productTypeDtos']:
#print(i3['id'])
#print(i3['name'])
cursor.execute(sql3 % (i3['id'], i3['name'], i2['id']) )
conn.commit()
# 关闭连接
cursor.close()
conn.close()
Python解析json落地成mysql表简直不要太方便
mysql表查看数据:
mysql> select * from prod_main;
+------+--------+
| id | name |
+------+--------+
| 1 | 男士 |
| 2 | 女士 |
| 3 | 童装 |
+------+--------+
3 rows in set (0.00 sec)
mysql> select * from prod_sub1;
+------+--------+--------------+
| id | name | prod_main_id |
+------+--------+--------------+
| 23 | 上装 | 1 |
| 24 | 裤装 | 1 |
| 25 | 外套 | 1 |
| 26 | 上装 | 2 |
| 27 | 裤装 | 2 |
| 29 | 裙装 | 2 |
| 28 | 外套 | 2 |
| 30 | 内衣 | 2 |
| 31 | 男童 | 3 |
| 32 | 女童 | 3 |
+------+--------+--------------+
10 rows in set (0.00 sec)
mysql> select * from prod_sub2 limit 20;
+------+-----------+--------------+
| id | name | prod_sub1_id |
+------+-----------+--------------+
| 33 | 毛衣 | 23 |
| 34 | T恤 | 23 |
| 35 | 衬衫 | 23 |
| 36 | 卫衣 | 23 |
| 37 | 背心 | 23 |
| 38 | 针织衫 | 23 |
| 39 | POLO衫 | 23 |
| 40 | 背带裤 | 24 |
| 41 | 休闲裤 | 24 |
| 42 | 短裤 | 24 |
| 43 | 运动裤 | 24 |
| 44 | 哈伦裤 | 24 |
| 45 | 九分裤 | 24 |
| 46 | 七分裤 | 24 |
| 47 | 西裤 | 24 |
| 48 | 牛仔裤 | 24 |
| 49 | 皮衣 | 25 |
| 50 | 风衣 | 25 |
| 51 | 夹克 | 25 |
| 52 | 便西 | 25 |
+------+-----------+--------------+
20 rows in set (0.00 sec)
mysql>
参考:
1.https://docs.python.org/zh-cn/3.6/library/json.html
2.https://www.jianshu.com/p/1ad6d267d177
3.https://www.cnblogs.com/jeavy/p/9503553.html
4.https://blog.csdn.net/qq_35889665/article/details/94565884
