从零开始--03-数据存储

数据存储

前面已经实现了数据的下载和解析，接下来，我们需要把数据存储到数据库中，将来写接口的时候，可以直接读取我们自己的数据库获取数据，顺便练习一下python的数据库操作。

准备工作

数据库，这里我们选择使用mysql
数据库管理工具，我们选择免费的DBeaver
PyMySQL

数组处理

我们精简一下数据，修改后的实体类如下

class VideoBean:
    """
    视频数据
    """
    def __init__(self, data):
        self.id = data["id"]
        self.title = data["title"]
        self.poster = data["poster"]
        self.source_name = data["source_name"]
        self.play_url = data["play_url"]
        self.playcnt = data["playcnt"]
        self.mthid = data["mthid"]
        self.mthpic = data["mthpic"]
        self.threadId = data["threadId"]
        self.duration = data["duration"]
        self.comment_id = data["comment_id"]
        self.publish_time = data["publish_time"]
        self.new_cate_v2 = data["new_cate_v2"]
        self.like = data["like"]
        self.fmlike = data["fmlike"]
        self.comment = data["comment"]
        self.fmcomment = data["fmcomment"]
        self.fmplaycnt = data["fmplaycnt"]
        self.fmplaycnt_2 = data["fmplaycnt_2"]
        self.outstand_tag = data["outstand_tag"]

    def __repr__(self):
        return "<Video>[%s]" % self.id

数据获取

数据获取部分，我们依旧使用之前数据下载章节的代码即可

存储

需要先创建一个数据库

CREATE DATABASE `video` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

获取数据库连接

db = pymysql.connect(host='192.168.3.30', port=3306, user='root', password='123456', database="video", charset="utf8")
cursor = db.cursor()

创建表

video_sql = """
CREATE TABLE IF NOT EXISTS video(_id INTEGER PRIMARY KEY AUTO_INCREMENT,id VARCHAR(30) NOT NULL,title VARCHAR(300) NOT 
NULL,poster VARCHAR(300) NOT NULL,source_name VARCHAR(300) NOT NULL,play_url VARCHAR(300) NOT NULL,playcnt VARCHAR(40) NOT 
NULL,mthid VARCHAR(30) NOT NULL,mthpic VARCHAR(300) NOT NULL,threadId VARCHAR(30) NOT NULL,duration VARCHAR(30) NOT NULL,
comment_id VARCHAR(30) NOT NULL,publish_time VARCHAR(30) NOT NULL,new_cate_v2 VARCHAR(30) NOT NULL,`like` VARCHAR (40) 
NOT NULL,fmlike VARCHAR(30) NOT NULL,comment VARCHAR(30) NOT NULL,fmcomment VARCHAR(30) NOT NULL,fmplaycnt VARCHAR(30) 
NOT NULL,fmplaycnt_2 VARCHAR(30) NOT NULL,outstand_tag VARCHAR(30) NOT NULL)"""
cursor.execute(video_sql)

抓取数据并存储

我们希望抓取更多分类的数据，所以会准备一个视频分类的list，之后遍历去查询获取。

video_categories = ["recommend", "yingshi", "yinyue", "vlog", "youxi", "gaoxiao", "zongyi", "yule", "dongman",
                    "shenghuo", "guangchangwu", "meishi", "chongwu", "sannong", "junshi", "shehui", "tiyu", "keji",
                    "shishang", "qiche", "qinzi", "jiaoyu", "wenhua", "lvyou", "miaodong"]
for i in range(10):
    for item in video_categories:
        videos = GetVideoData.get_video_data(item)
        save(videos, cursor, db)
        print("{} 已下载".format(item))
        time.sleep(1)

完整的代码

import pymysql
import GetVideoData
import time


def save(videos, cursor, db):
    if videos is None:
        print("videos 为null")
        return
    if len(videos) == 0:
        print("videos 长度为0")
        return
    if cursor is None:
        print("cursor 为null")
        return
    if db is None:
        print("db 为null")
        return
    insert_sql = """INSERT INTO video(`id`,`title`,`poster`,`source_name`,`play_url`,
  `playcnt`,`mthid`,`mthpic`,`threadId`,`duration`,`comment_id`,`publish_time`,`new_cate_v2`,
  `like`,`fmlike`,`comment`,`fmcomment`,`fmplaycnt`,`fmplaycnt_2`,`outstand_tag`) values ( '{}', '{}', '{}', '{}', '{}',
   {}, '{}', '{}', '{}', '{}','{}','{}','{}',{}, '{}', '{}', '{}', '{}', '{}', '{}')
  """
    for video in videos:
        # 先查询是否存在，如果不存在就插入，存在就跳过
        cursor.execute("select count(*) from video where `id` = {};".format(video.id))
        res = cursor.fetchone()
        if res[0] > 0:
            print(video)
            continue
        cursor.execute(
            insert_sql.format(video.id, video.title, video.poster, video.source_name, video.play_url, video.playcnt,
                              video.mthid, video.mthpic, video.threadId, video.duration,
                              video.comment_id, video.publish_time, video.new_cate_v2,
                              video.like,
                              video.fmlike, video.comment, video.fmcomment, video.fmplaycnt, video.fmplaycnt_2,
                              video.outstand_tag))
        db.commit()


db = pymysql.connect(host='192.168.3.30', port=3306, user='root', password='123456', database="video", charset="utf8")
cursor = db.cursor()
cursor.execute("SELECT VERSION()")
video_sql = """
CREATE TABLE IF NOT EXISTS video(_id INTEGER PRIMARY KEY AUTO_INCREMENT,id VARCHAR(30) NOT NULL,title VARCHAR(300) NOT 
NULL,poster VARCHAR(300) NOT NULL,source_name VARCHAR(300) NOT NULL,play_url VARCHAR(300) NOT NULL,playcnt VARCHAR(40) NOT 
NULL,mthid VARCHAR(30) NOT NULL,mthpic VARCHAR(300) NOT NULL,threadId VARCHAR(30) NOT NULL,duration VARCHAR(30) NOT NULL,
comment_id VARCHAR(30) NOT NULL,publish_time VARCHAR(30) NOT NULL,new_cate_v2 VARCHAR(30) NOT NULL,`like` VARCHAR (40) 
NOT NULL,fmlike VARCHAR(30) NOT NULL,comment VARCHAR(30) NOT NULL,fmcomment VARCHAR(30) NOT NULL,fmplaycnt VARCHAR(30) 
NOT NULL,fmplaycnt_2 VARCHAR(30) NOT NULL,outstand_tag VARCHAR(30) NOT NULL)
"""
# 创建数据库
cursor.execute(video_sql)

# 抓取并存入数据
video_categories = ["recommend", "yingshi", "yinyue", "vlog", "youxi", "gaoxiao", "zongyi", "yule", "dongman",
                    "shenghuo", "guangchangwu", "meishi", "chongwu", "sannong", "junshi", "shehui", "tiyu", "keji",
                    "shishang", "qiche", "qinzi", "jiaoyu", "wenhua", "lvyou", "miaodong"]
for i in range(10):
    for item in video_categories:
        videos = GetVideoData.get_video_data(item)
        save(videos, cursor, db)
        print("{} 已下载".format(item))
        time.sleep(1)

db.close()