python爬虫数据连接MySQL数据库

TY✧

已于 2023-12-07 09:57:50 修改

阅读量638

点赞数 7

文章标签： python 数据库开发语言

于 2023-12-07 09:29:22 首次发布

本文链接：https://blog.csdn.net/2301_78233353/article/details/134820384

版权

一，爬虫数据到内存

import pymysql
import requests
from bs4 import BeautifulSoup
import pandas as pd
import os
import random
from time import sleep

from pymysql.cursors import Cursor

# 请求头
h1 = {
    'Cookie': 'bid=zBa9OSUKR3U; _pk_id.100001.4cf6=573e4774bfb06446.1701225708.; _pk_ses.100001.4cf6=1; ap_v=0,6.0; __utma=30149280.1110594257.1701225708.1701225708.1701225708.1; __utmb=30149280.0.10.1701225708; __utmz=30149280.1701225708.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); __utma=223695111.330245772.1701225708.1701225708.1701225708.1; __utmb=223695111.0.10.1701225708; __utmz=223695111.1701225708.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); __utmc=30149280; __utmc=223695111',
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
    'Accept-Encoding': 'gzip, deflate',
    'Host': 'movie.douban.com',
    'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/15.4 Safari/605.1.15',
    'Accept-Language': 'zh-CN,zh-Hans;q=0.9',
    'Referer': 'https://movie.douban.com/subject/35267224/?from=showing',
    'Connection': 'keep-alive'
}


def trans_star(v_str):
    """转换评论星级"""
    v_str = v_str[0]
    if v_str == 'allstar10':
        return '1星'
    elif v_str == 'allstar20':
        return '2星'
    elif v_str == 'allstar30':
        return '3星'
    elif v_str == 'allstar40':
        return '4星'
    elif v_str == 'allstar50':
        return '5星'
    else:
        return '未知'


def get_short(v_movie_id):
    """爬取短评数据"""
    for page in range(1, max_page + 1):  # 爬取前n页
        requests.packages.urllib3.disable_warnings()
        # 请求地址
        url = 'https://movie.douban.com/subject/{}/comments?start={}&limit=20&status=P&sort=new_score'.format(
            v_movie_id, (page - 1) * 20)
        # 发送请求
        response = requests.get(url, headers=h1, verify=False)
        print(response.status_code)
        # 解析页面数据
        soup = BeautifulSoup(response.text, 'html.parser')
        # 所有评论数据
        reviews = soup.find_all('div', {'class': 'comment'})
        print('开始爬取第{}页，共{}条评论'.format(page, len(reviews)))
        sleep(random.uniform(1, 2))
        # 定义空列表用于存放数据
        user_name_list = []  # 评论者昵称
        star_list = []  # 评论星级
        time_list = []  # 评论时间
        ip_list = []  # 评论者ip属地
        vote_list = []  # 有用数
        content_list = []  # 评论内容
        for review in reviews:
            # 评论者昵称
            user_name = review.find('span', {'class': 'comment-info'}).find('a').text
            user_name_list.append(user_name)
            # 评论星级
            star = review.find('span', {'class': 'comment-info'}).find_all('span')[1].get('class')
            star = trans_star(star)
            star_list.append(star)
            # 评论时间
            time2 = review.find('span', {'class': 'comment-time'}).text.strip()
            print('评论时间：', time2)
            time_list.append(time2)
            # 评论者IP属地
            ip = review.find('span', {'class': 'comment-location'}).text
            ip_list.append(ip)
            # 有用数
            vote = review.find('span', {'class': 'votes vote-count'}).text
            vote_list.append(vote)
            # 评论内容
            content = review.find('span', {'class': 'short'}).text
            content = content.replace(',', '，').replace(' ', '').replace('\n', '').replace('\t', '').replace('\r', '')
            content_list.append(content)
        df = pd.DataFrame(
            {
                '页码': page,
                '评论者昵称': user_name_list,
                '评论星级': star_list,
                '评论时间': time_list,
                '评论者IP属地': ip_list,
                '有用数': vote_list,
                '评论内容': content_list,
            }
        )

    

if __name__ == '__main__':
    # 电影id
    movie_id = '35556001'
    # 最大爬取页
    max_page = 1  # 最大为30页
    # 保存文件名
    result_file = '豆瓣短评_{}_前{}页.csv'.format(movie_id, max_page)
    # 如果csv文件存在，先删除之
    if os.path.exists(result_file):
        os.remove(result_file)
        print('结果文件存在，已删除: {}'.format(result_file))
    # 循环爬取短评
    get_short(movie_id)

二，连接mysql数据库

# host:地址    potr:端口     user:用户名    password:密码     db:数据库名     charset:编码
    db = pymysql.connect(host="172.22.67.216", port=3306, user="root", password="password", db="douban", charset="utf8mb4")

    cursor = db.cursor()  # 创建游标

三，通过pymysql创建数据表

1，先创建数据库

创建的字符集要跟加入代码的编码保持一致

2，创建数据表

3，输入下面的代码创建数据表

data_json =df.to_dict(orient='records')  #转换格式
        for dt in data_json:
            print(dt)
            sql = 'insert into douban_data values ("%s","%s","%s","%s","%s","%s","%s")' % (dt['页码'], dt['评论者昵称'], dt['评论星级'], dt['评论时间'], dt['评论者IP属地'], dt['有用数'], dt['评论内容'])
            cursor.execute(sql)  # 执行语句
            db.commit()

四，通过pymysql写入数据记录

运行结果如下：

mysql结果如下：

完整代码：

import pymysql
import requests
from bs4 import BeautifulSoup
import pandas as pd
import os
import random
from time import sleep

from pymysql.cursors import Cursor

# 请求头
h1 = {
    'Cookie': 'bid=zBa9OSUKR3U; _pk_id.100001.4cf6=573e4774bfb06446.1701225708.; _pk_ses.100001.4cf6=1; ap_v=0,6.0; __utma=30149280.1110594257.1701225708.1701225708.1701225708.1; __utmb=30149280.0.10.1701225708; __utmz=30149280.1701225708.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); __utma=223695111.330245772.1701225708.1701225708.1701225708.1; __utmb=223695111.0.10.1701225708; __utmz=223695111.1701225708.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); __utmc=30149280; __utmc=223695111',
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
    'Accept-Encoding': 'gzip, deflate',
    'Host': 'movie.douban.com',
    'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/15.4 Safari/605.1.15',
    'Accept-Language': 'zh-CN,zh-Hans;q=0.9',
    'Referer': 'https://movie.douban.com/subject/35267224/?from=showing',
    'Connection': 'keep-alive'
}


def trans_star(v_str):
    """转换评论星级"""
    v_str = v_str[0]
    if v_str == 'allstar10':
        return '1星'
    elif v_str == 'allstar20':
        return '2星'
    elif v_str == 'allstar30':
        return '3星'
    elif v_str == 'allstar40':
        return '4星'
    elif v_str == 'allstar50':
        return '5星'
    else:
        return '未知'


def get_short(v_movie_id):
    """爬取短评数据"""
    for page in range(1, max_page + 1):  # 爬取前n页
        requests.packages.urllib3.disable_warnings()
        # 请求地址
        url = 'https://movie.douban.com/subject/{}/comments?start={}&limit=20&status=P&sort=new_score'.format(
            v_movie_id, (page - 1) * 20)
        # 发送请求
        response = requests.get(url, headers=h1, verify=False)
        print(response.status_code)
        # 解析页面数据
        soup = BeautifulSoup(response.text, 'html.parser')
        # 所有评论数据
        reviews = soup.find_all('div', {'class': 'comment'})
        print('开始爬取第{}页，共{}条评论'.format(page, len(reviews)))
        sleep(random.uniform(1, 2))
        # 定义空列表用于存放数据
        user_name_list = []  # 评论者昵称
        star_list = []  # 评论星级
        time_list = []  # 评论时间
        ip_list = []  # 评论者ip属地
        vote_list = []  # 有用数
        content_list = []  # 评论内容
        for review in reviews:
            # 评论者昵称
            user_name = review.find('span', {'class': 'comment-info'}).find('a').text
            user_name_list.append(user_name)
            # 评论星级
            star = review.find('span', {'class': 'comment-info'}).find_all('span')[1].get('class')
            star = trans_star(star)
            star_list.append(star)
            # 评论时间
            time2 = review.find('span', {'class': 'comment-time'}).text.strip()
            print('评论时间：', time2)
            time_list.append(time2)
            # 评论者IP属地
            ip = review.find('span', {'class': 'comment-location'}).text
            ip_list.append(ip)
            # 有用数
            vote = review.find('span', {'class': 'votes vote-count'}).text
            vote_list.append(vote)
            # 评论内容
            content = review.find('span', {'class': 'short'}).text
            content = content.replace(',', '，').replace(' ', '').replace('\n', '').replace('\t', '').replace('\r', '')
            content_list.append(content)
        df = pd.DataFrame(
            {
                '页码': page,
                '评论者昵称': user_name_list,
                '评论星级': star_list,
                '评论时间': time_list,
                '评论者IP属地': ip_list,
                '有用数': vote_list,
                '评论内容': content_list,
            }
        )

    # 定义sql语句
        data_json =df.to_dict(orient='records')  #转换格式
        for dt in data_json:
            print(dt)
            sql = 'insert into douban_data values ("%s","%s","%s","%s","%s","%s","%s")' % (dt['页码'], dt['评论者昵称'], dt['评论星级'], dt['评论时间'], dt['评论者IP属地'], dt['有用数'], dt['评论内容'])
            cursor.execute(sql)  # 执行语句
            db.commit()

if __name__ == '__main__':
    # host:地址    potr:端口     user:用户名    password:密码     db:数据库名     charset:编码
    db = pymysql.connect(host="172.22.67.216", port=3306, user="root", password="password", db="douban", charset="utf8mb4")

    cursor = db.cursor()  # 创建游标
    # 电影id
    movie_id = '35556001'
    # 最大爬取页
    max_page = 1  # 最大为30页
    # 保存文件名
    result_file = '豆瓣短评_{}_前{}页.csv'.format(movie_id, max_page)
    # 如果csv文件存在，先删除之
    if os.path.exists(result_file):
        os.remove(result_file)
        print('结果文件存在，已删除: {}'.format(result_file))
    # 循环爬取短评
    get_short(movie_id)