pandas 对Excel 的 读取与写入, python time threading定时触发任务

pandas 对Excel 的 读取与写入

#-*-coding:utf-8-*-

import requests
import json
import urllib3
from  selenium  import webdriver
import xlwt
import pandas as pd
from urllib import parse



requests.packages.urrlib3.disable_warnings()

tools = {}
time_handle = 1
auth_info = ""

def login_in():
	"""
	先自动登录，再获取另一个页面的 token
	"""
	user = "xxx"
	psd = "yyy"
	driver = webdriver.Chrome()
	driver.get("https://www.login.com")
	user_element = driver.find_element_by_xpath('//*[@id="uid"]')
	psd_element = driver.find_element_by_xpath('//*[@id="password"]')
	submit_element = driver.find_element_by_xpath('//*[@id="page-input-holder-pwd"]/form/div/input')
	
	user_element.send_keys(user)
	psd_element .send_keys(psd)
	submit_element .click()

	driver.get("https://www.xxx.com/auth/api/v1/token")
	body = driver.find_element_by_xpath('/html/body')
	body_json = json.loads(body.text)
	global auth_info
	auth_info = body_json["data"]
	print(auth_info)
	return auth_info

def read_excel():
	"""
	读取Excel，返回 URL、project_id
	"""
	df = pd.read_csv("info.csv", usecols=[0,1,2,3],index_col=0, names=None, 	header=None)
	csv_info_list = df.values.tolist()
	del csv_info_list[0]
	return csv_info_list


def get_template_name(csv_info_list):
	"""
	通过 server_id 获取 project 信息和 模板配置信息
	"""
	auth_info = login_in()
	header = {
	'Authorization':auth_info
	}
	shell_list = []
	for one_list in csv_info_list:
		httpurl = one_list[0]
		project_id= one_list[1]
		template = one_list[2]
		url_encode = parse.quote(httpurl)
		
		get_tool_api = "https:www.xxx.com/Gateconfig/api/v1/getConfig?projectid=%srepoType=xx&repoURL=%s&templateName=%s"
		single_acccess_url = (get_tool_api % (str(project_id), url_encode , template ))

		get_access_data = requests.get(single_acccess_url , headers=header, verify=False)
		get_single_access_data =get_access_data .json()[" data "]
		if get_single_access_data :
			for tool in get_single_access_data["Gateconfig"]
				if tool[" isSelected "] == True:
					if tool["tool"] == "py27":
						py27 = []
						py27_shell = tool["buildScript"]
						py27.append(httpurl )
						py27.append(py27_shell)
						py27.append(template )
						shell_list.append(py27)
						
	head = ["project_url","shell","template"]
	summary_pd = pd.DateFrame(shell_list,columns=head)
	tools.updata({"shell_list_all": summary_pd })  # 输出的  Excel 文件中的 sheet 页 名称
	global time_handle
	# 请求的数据较多，会导致token过期，当 time_handle = 0, 此时 整个数据已经获取完成，时间线程 正常退出
	time_handle = 0

# 定时函数
from datetime import datetime 
from threading import Timer

def Time_threading(inc):	
	"""   设置定数触发  """
	print(datetime.now(), "更新页面token")
	auth_info = login_in()
	t = Timer(inc,Time_threading, (inc,))
	if time_handle == 1:
		t.satrt()
	else:
		t.cancel() 

if __name == '__main__':
	Time_threading(1200)  # 20 min  获取一次 token
	get_template_name(read_excel)

	writer = pd.ExcelWriter("test.xlsx")   #  最终输出的  Excel 文件
	for single_tool in tools:
		tools[single_tool].to_excel(writer, sheet_name=single_tool)
	writer.save()