1.先在mysql库创建图片信息表。
DROP TABLE IF EXISTS s_photo_info;
CREATE TABLE IF NOT EXISTS s_photo_info
(
id INTEGER COMMENT '主键'
,title VARCHAR(50) COMMENT '图片标题'
,url_adress VARCHAR(100) COMMENT 'url地址'
,type1 VARCHAR(10) COMMENT '图片分类'
)DEFAULT CHARSET utf8;
COMMENT '图片信息'
;
2.python代码如下:
# !/usr/bin/python3
import pymysql
import json
import requests
# Python 字典类型转换为 JSON 对象
response = requests.get('https://api.apiopen.top/api/getImages?page=0&size=5')
json_str = json.dumps(response.json())
# print("Python 原始数据:", repr(response.json()))
# print("JSON 对象:", json_str)
# 将 JSON 对象转换为 Python 字典
data1 = json.loads(json_str)
# print("data2['result']: ", data1['result'])
json_str1 = json.dumps(data1['result'])
data2 = json.loads(json_str1)
print("data2['list]: ", data2['list'])
json_str2 = json.dumps(data2['list'])
data3 = json.loads(json_str2)
print("json_str2 de 1: ", data3[0]['id'])
row = len(data3)
print("row: ", row)
# 打开数据库连接
db = pymysql.connect(host='localhost',
user='root',
password='123123',
database='test')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 循环json内容
count = 0
while count < row:
print (count, " 小于 row")
# SQL 插入语句
sql = "INSERT INTO S_PHOTO_INFO(ID,TITLE, URL_ADRESS, TYPE1) \
VALUES (%s,'%s','%s','%s')" % \
(data3[count]['id'],data3[count]['title'],data3[count]['url'],data3[count]['type'])
cursor.execute(sql)
# 提交到数据库执行
db.commit()
count = count + 1
else:
print("完成循环!")
# 关闭数据库连接
db.close()
3.mysql库s_photo_info表数据如下: