py 采集流程整理

import urllib

import pandas as pd
import requests
from lxml import etree
import os
import csv
import time
import pymysql

# 全局取消证书验证
import ssl
ssl._create_default_https_context = ssl._create_unverified_context


# 获取链接
def get_url_list(num):
    all_num=0
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.139 Safari/537.36'}

    arr = range(1, num+1)
    print(arr)
    requests.adapters.DEFAULT_RETRIES = 5

    # request  持久化
    for i in arr:
        url_ = 'http://www.***.com/photo/list/?page={}'.format(i)
        res = requests.get(url_, headers=headers)
        res = etree.HTML(res.content.decode())
        nodes = res.xpath('//ul[@class="list-group"]/li/div/div/a')
        info = []
        k=0
        for node in nodes:
            item = {}
            title = node.xpath('//*[@id="pic-detail"]/div/div[2]/div[2]/ul/li/div/div/a/p/text()')[k]
            url = node.xpath('//*[@id="pic-detail"]/div/div[2]/div[2]/ul/li/div/div/a/@href')[k]
            img = node.xpath('//*[@id="pic-detail"]/div/div[2]/div[2]/ul/li/div/div/a/img/@data-backup')[k]
            item['title'] = title
            item['url'] = url
            item['pic'] = img
            item['pic_new'] = img.replace('http://img.***.com','http://oss1.wangmingchang.com/0bd86e854d29ca97c3510e774d9cd4d4/uploads',1)
            item['status'] = 0
            # info.append(item)
            print(item)
            db_insert(item)
            all_num = all_num+1
            k=k+1;
            # print("当前第" +  str(all_num) +"个")
        # write_to_file(info)
        print("====第"+ format(i)+"页已完成====")
        # time.sleep( 2 )
    print("OK")

# 保存数据
def write_to_file(info):
    with open('采集链接列表.csv','a',newline='') as f:
       fieldnames = ['title','url','img','status']
       writer = csv.DictWriter(f,fieldnames=fieldnames)
       writer.writeheader()
       try:
           writer.writerows(info)
       except:
           pass

# //清洗数据
def handle_data():
    data = pd.read_csv("采集链接列表-2.csv")  # 读取csv文件
    # print(data)                                #打印所有文件
    # print(data.head(5))  # 打印前5行
    # print(data.columns)  # 返回全部列名
    # print(data.shape)  # f返回csv文件形状(65, 5)
    # print(data.loc[1:2])  # 打印第1到2行
    # print(data.loc[2:4, ['title','time', 'url']])  # 打印行中特定列

    # df = pd.DataFrame(data)
    # print(df)
    # df['title'].drop_duplicates(keep='last')
    newdata = data.drop_duplicates(subset=['title', 'url'], keep='first')

    print(newdata);

    # 清洗后保存
    # df = newdata.to_csv('清洗去重后的数据.csv', sep=',', header=True, index=True)
    # print(df)


        # save_img(url)

# 保存图片
def save_img(img_url):
    #保存图片到磁盘文件夹 file_path中，默认为当前脚本运行目录下的 book\img文件夹
    file_path = os.path.dirname(img_url)
    opener = urllib.request.build_opener()
    opener.addheaders = [('User-Agent',
                          'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1941.0 Safari/537.36')]
    urllib.request.install_opener(opener)
    try:
        if not os.path.exists(file_path):
            print("创建目录...")
            os.makedirs(file_path)
        #获得图片后缀
        file_suffix = os.path.splitext(img_url)[1]
        file_path2 = os.path.splitext(img_url)[0]
        #拼接图片名（包含路径）
        filename = '{}{}'.format(file_path2,file_suffix)
       #下载图片，并保存到文件夹中
        urllib.request.urlretrieve(img_url,filename=filename)
        return 1
    except IOError as e:
        # return '文件操作失败',e
        return 0
    except Exception as e:
        # return '错误 ：',e
        return 0
# 操作数据库
def db_insert(data):
    # print("==执行数据库插入==")
    # 选择TEST数据库，连接MySQL
    db = pymysql.connect(host="localhost", port=3306, user="root", password="",db="py_test", charset="utf8")
    cursor = db.cursor()
    is_cz = db_is_cz(db,data)
    if(is_cz==1):
        # 这里的sql语句不能用%或者+号作为连接符，否则会报错
        # params表需事先创建
        sql = 'INSERT INTO list(title, pic,pic_new, url, status) VALUES (%s, %s, %s, %s,%s)'
        parm = (data['title'], data['pic'], data['pic_new'], data['url'], data['status'])
        try:
            db.ping(reconnect=True)# 避免无法连接数据
            cursor.execute(sql, parm)
            db.commit()
            print("ok")
        except:
            db.rollback()
            print("error")
        db.close()
    else:
        print("数据已存在..")
#     判断数据是否存在
def db_is_cz(db,data):
    try:
        with db.cursor() as cursor:
            sql = 'select * from list where title= %s and url= %s and pic= %s'
            parm = (data['title'], data['url'], data['pic'])
            db.ping(reconnect=True)
            cursor.execute(sql,parm)
            result=cursor.fetchone()
            if(result):
                return 0
            else:
                return 1
    except:
        db.rollback()
        print("error")
    # finally:
        db.close()
    # 转换成DataFrame格式
    # df = pd.DataFrame(result)
    # print(df)

def test():
    # for i in range(1, 5):
    #     print(i)
    #     # 1
    #     # 2
    #     # 3
    #     # 4
    return 0

if __name__ == '__main__':
    # handle_data()
    # ret = save_img('http://img.***.com/production/uploads/image/2020/02/16/20200216833483_mpGroi.jpg')
    # print(ret)
    # str = 'http://img.***.com/production/uploads/image/2020/02/16/20200216833483_mpGroi.jpg'
    # str = str.replace('http://img.***.com/',' ')
    # print(str)
    # db_cz()
    list = get_url_list(5)




    # test()