python库处理xls文件【csv、pyhttp类似需求记录】

# https://www.cnblogs.com/superhin/p/11455240.html
# https://blog.csdn.net/birdflyinhigh/article/details/79685019
# https://www.cnblogs.com/xzjf/p/9339674.html
import csv
import pandas as pd
from urllib import request
import json
import copy
import time


# csv和xls的相互转换
def xls_to_csv():
    data_xls = pd.read_excel('image.xlsx', index_col=0)
    data_xls.to_csv('1.csv', encoding='utf-8')


def csv_to_xlsx_pd():
    csv = pd.read_csv('my.csv', encoding='utf-8')
    csv.to_excel('my.xlsx', sheet_name='data')


# csv转list
def csv_to_list():
    f = open("1.csv", 'r')
    csvreader = csv.reader(f)
    final_list = list(csvreader)
    return final_list



def fetch_data(url):
    req = request.Request(url)  # 请求url（GET请求）
    with request.urlopen(req) as f:  # 打开url请求（如同打开本地文件一样）
        return json.loads(f.read().decode('utf-8'))  # 读数据 并编码同时利用json.loads将json格式数据转换为python对象


def deal_list(csv_list):
    new_csv_list = []
    i = 0
    for row in csv_list:
        cur_item = []
        for each in row:
            cur_item.append(each)
            # 如果这个元素满足条件，在这里条件是一个http链接。
            if each[:4] == "http":     
                value = get_quality(each[each.rfind("url=") + 4:])
                cur_item.append(value)
        # 深拷贝
        new_csv_list.append(copy.deepcopy(cur_item))
        # 睡眠1s
        time.sleep(1)
        print("第" + str(i) + "行正在处理完成****************")
        i += 1
    return new_csv_list

# 串联整个流程
def write_to_csv():
    xls_to_csv()
    dest_csv = csv_to_list()
    rows = deal_list(dest_csv)
    with open('my.csv', 'w+', newline='') as csv_file:
        writer = csv.writer(csv_file)
        for row in rows:
            writer.writerow(row)


def get_quality(name):
    url = 'www.baidu.com?name' + name
    data = fetch_data(url)
    for v in data["result"].items():
        if "blur_quality" in v[1]:
            value = v[1]["blur_quality"]["blur"]
        else:
            value = "no value"
    return value