01-yxb.py
import time
from YDM import YDMHttp
import requests
import time
import pymysql
import logging
import json
from file_handle import wite_to_excel_text
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import smtplib
logging.basicConfig(
level=logging.INFO, # 定义输出到文件的log级别，大于此级别的都被输出
format='%(asctime)s %(filename)s %(levelname)s : %(message)s', # 定义输出log的格式
datefmt='%Y-%m-%d %H:%M:%S', # 时间
filename='yibao.log', # log文件名
filemode='a') # 写入模式“w”或“a”
class yyb(object):
def __init__(self):
self.db = pymysql.connect(host='localhost', port=3306, database='cfda', user='root', password='root',
charset='utf8')
self.cursor = self.db.cursor()
self.headers = {
"user-agent": "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36", }
self.url = 'http://code.nhsa.gov.cn:8000/jbzd/public/toStdOperationTreeList.html'
self.sess = requests.Session()
# 邮箱设置
# 设置发件服务器地址
self.host = 'smtp.163.com'
# 设置发件服务器端口号。注意，这里有SSL和非SSL两种形式
self.port = 465
# 设置发件邮箱，一定要自己注册的邮箱
self.sender = '17682303516@163.com'
# 设置发件邮箱的密码，163邮箱的授权码，等会登陆会用到
self.pwd = 'yu17682303516'
# 设置邮件接收人，可以是扣扣邮箱
self.receiver0 = '1064145110@qq.com'
# self.receiver0 = 'caifu.yu@kangcenet.com'
# self.receiver1 = '54400407@qq.com'
self.main()
def login(self):
image = self.sess.get(
url='https://www.1xuebao.com/randCodeImage?a={}'.format(int(time.time() * 1000)), headers=self.headers,
verify=False)
with open(r'./code.jpg', 'wb') as f:
f.write(image.content)
filename = r'./code.jpg'
codetype = 1004
# 超时时间，秒
timeout = 60
ydm = YDMHttp()
cid, code_result = ydm.run(filename, codetype, timeout)
code_result = input('请输入验证码:')
data = {
"userName": "18069859702",
"password": "18069859702",
"randCode": code_result,
"autologin": "false",
}
self.sess.post(url='https://www.1xuebao.com/loginController.do?checkuser', data=data,
headers=self.headers, verify=False).content.decode('utf-8')
def data_area(self):
data = {
}
html = self.sess.post(url='https://www.1xuebao.com/planController.do?infoForAddPlan', data=data,
headers=self.headers, verify=False).content.decode('utf-8')
data_json = json.loads(html)
time.sleep(1)
return data_json['companys']
def data_shop(self, id):
data = {
"companyid": int(id),
}
html = self.sess.post(url='https://www.1xuebao.com/planController.do?getStores', data=data,
headers=self.headers, verify=False).content.decode('utf-8')
data_json = json.loads(html)
time.sleep(1)
return data_json['stores']
def execls(self):
data = self.data_area()
head_data_area = ['id', '投放连锁名称', '是否新增(1为新增)']
records_area = []
for i, j in enumerate(data):
whether = 0
num = self.cursor.execute('select id from yxb_area where ids={}'.format(j['id']))
if not num:
self.cursor.execute('insert into yxb_area(ids, names, createtime) values({}, "{}", "{}")'.format(j['id'], j['text'], time.strftime("%Y-%m-%d %X", time.localtime())))
self.db.commit()
whether = 1
record = []
record.append(i + 1)
record.append(j['text'])
record.append(whether)
records_area.append(record)
try:
data_shop = self.data_shop(j['id'])
for p in data_shop:
print('p:', p)
num = self.cursor.execute('select id from xyb_data_shop where store_id="{}"'.format(p['store_id']))
if not num:
list_key = []
list_lalues = []
for key, lalues in p.items():
list_key.append(key)
list_lalues.append("'" + str(lalues).replace("'", "‘") + "'")
# 拼接sql语句
insert_sql = 'insert into xyb_data_shop({}, companym, createtime) values({}, "{}", "{}")'.format(
', '.join(list_key),
', '.join(list_lalues), j['text'], time.strftime("%Y-%m-%d %X", time.localtime()))
try:
self.cursor.execute(insert_sql)
self.db.commit()
except Exception as e:
print('insert_sql:', insert_sql)
print(e)
except:
print('错误的是', j['id'], j['text'])
logging.info('错误的是:{},{}'.format(j['id'], j['text']))
n = len(records_area)
# 设置生成文件所在路径
download_url = './'
# excel文件名称
table_name = '投放连锁名称区域'
# 写入数据到excel中
wite_to_excel_text(n, head_data_area, records_area, download_url, table_name)
# 邮箱设置---------------------------
# 设置邮件正文，这里是支持HTML的
body = '<h1> 投放连锁名称.xls </h1>'
# 设置正文为符合邮件格式的HTML内容
msg = MIMEText(body, 'html')
message = MIMEMultipart()
# 设置邮件标题
message['subject'] = ' 药学宝投放连锁名称 '
# 设置发送人
message['from'] = self.sender
# 设置接收人
message['to'] = self.receiver0
message.attach(msg)
# 构造附件1，传送当前目录下的 filename 文件
filename = '投放连锁名称.xls'
att1 = MIMEText(open(filename, 'rb').read(), 'base64', 'utf-8')
att1["Content-Type"] = 'application/octet-stream'
# 这里的filename可以任意写，写什么名字，邮件中显示什么名字
att1["Content-Disposition"] = 'attachment; filename="' + filename + '"'
print('attachment; filename="' + filename + '"')
message.attach(att1)
try:
# 注意！如果是使用SSL端口，这里就要改为SMTP_SSL
s = smtplib.SMTP_SSL(self.host, self.port)
# 登陆邮箱
s.login(self.sender, self.pwd)
# 发送邮件！
s.sendmail(self.sender, self.receiver0, message.as_string())
# 发送第二人邮件
# s.sendmail(self.sender, self.receiver1, message.as_string())
print('邮件发送成功')
except smtplib.SMTPException:
print('邮件发送失败')
def main(self):
self.login()
self.execls()
if __name__ == "__main__":
yyb()
02-file_handle.py
import xlwt, datetime
from xlwt import *
import xlsxwriter
# 写入excel文件函数
def wite_to_excel(n, head_data, records, download_url, table_name):
# 获取时间戳
timestr = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
# 工作表
wbk = xlwt.Workbook(style_compression=2, encoding='utf-8')
sheet1 = wbk.add_sheet('sheet1', cell_overwrite_ok=True)
# 写入表头
for filed in range(0, len(head_data)):
sheet1.write(0, filed, head_data[filed], excel_head_style())
# 写入数据记录
for row in range(1, n + 1):
for col in range(0, len(head_data)):
sheet1.write(row, col, records[row - 1][col], excel_record_style())
# 设置默认单元格宽度
sheet1.col(col).width = 256 * 15
wbk.save(download_url + table_name + '.xls')
return timestr
# 写入excel文件函数
def wite_to_excel_text(n, head_data, records, download_url, table_name):
# 获取时间戳
timestr = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
# 工作表
wbk = xlsxwriter.Workbook(download_url + table_name + '.xls')
sheet1 = wbk.add_worksheet('sheet1')
# 写入表头
for filed in range(0, len(head_data)):
sheet1.write(0, filed, head_data[filed])
# 写入数据记录
for row in range(1, n + 1):
for col in range(0, len(head_data)):
sheet1.write(row, col, records[row - 1][col])
# 设置默认单元格宽度
# sheet1.col(col).width = 256 * 15
# wbk.save(download_url + table_name + '.xls')
wbk.close()
return timestr
# 定义导出文件表头格式
def excel_head_style():
# 创建一个样式
style = XFStyle()
# 设置背景色
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = Style.colour_map['light_green'] # 设置单元格背景色
style.pattern = pattern
# 设置字体
font0 = xlwt.Font()
font0.name = u'微软雅黑'
font0.bold = True
font0.colour_index = 0
font0.height = 240
style.font = font0
# 设置文字位置
alignment = xlwt.Alignment() # 设置字体在单元格的位置
alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平方向
alignment.vert = xlwt.Alignment.VERT_CENTER # 竖直方向
style.alignment = alignment
# 设置边框
borders = xlwt.Borders() # Create borders
borders.left = xlwt.Borders.THIN # 添加边框-虚线边框
borders.right = xlwt.Borders.THIN # 添加边框-虚线边框
borders.top = xlwt.Borders.THIN # 添加边框-虚线边框
borders.bottom = xlwt.Borders.THIN # 添加边框-虚线边框
style.borders = borders
return style
# 定义导出文件记录格式
def excel_record_style():
# 创建一个样式
style = XFStyle()
# 设置字体
font0 = xlwt.Font()
font0.name = u'微软雅黑'
font0.bold = False
font0.colour_index = 0
font0.height = 200
style.font = font0
# 设置文字位置
alignment = xlwt.Alignment() # 设置字体在单元格的位置
alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平方向
alignment.vert = xlwt.Alignment.VERT_CENTER # 竖直方向
style.alignment = alignment
# 设置边框
borders = xlwt.Borders() # Create borders
borders.left = xlwt.Borders.THIN # 添加边框-虚线边框
borders.right = xlwt.Borders.THIN # 添加边框-虚线边框
borders.top = xlwt.Borders.THIN # 添加边框-虚线边框
borders.bottom = xlwt.Borders.THIN # 添加边框-虚线边框
style.borders = borders
return style
03-YDM.py
# coding:utf-8
import json, time, requests
class YDMHttp:
apiurl = 'http://api.yundama.com/api.php'
username = ''
password = ''
appid = ''
appkey = ''
def __init__(self):
# 用户名
username = '17682303516'
# 密码
password = 'yu1064145110'
# 软件ＩＤ，开发者分成必要参数。登录开发者后台【我的软件】获得！
appid = 5942
# 软件密钥，开发者分成必要参数。登录开发者后台【我的软件】获得！
appkey = 'd0b1f02e797d4470c1eb3d1d0590592b'
self.username = username
self.password = password
self.appid = str(appid)
self.appkey = appkey
def request(self, fields, files=[]):
response = self.post_url(self.apiurl, fields, files)
response = json.loads(response)
return response
def balance(self):
data = {'method': 'balance', 'username': self.username, 'password': self.password, 'appid': self.appid,
'appkey': self.appkey}
response = self.request(data)
if (response):
if (response['ret'] and response['ret'] < 0):
return response['ret']
else:
return response['balance']
else:
return -9001
def login(self):
data = {'method': 'login', 'username': self.username, 'password': self.password, 'appid': self.appid,
'appkey': self.appkey}
response = self.request(data)
if (response):
if (response['ret'] and response['ret'] < 0):
return response['ret']
else:
return response['uid']
else:
return -9001
def upload(self, filename, codetype, timeout):
data = {'method': 'upload', 'username': self.username, 'password': self.password, 'appid': self.appid,
'appkey': self.appkey, 'codetype': str(codetype), 'timeout': str(timeout)}
file = {'file': filename}
response = self.request(data, file)
if (response):
if (response['ret'] and response['ret'] < 0):
return response['ret']
else:
return response['cid']
else:
return -9001
def result(self, cid):
data = {'method': 'result', 'username': self.username, 'password': self.password, 'appid': self.appid,
'appkey': self.appkey, 'cid': str(cid)}
response = self.request(data)
return response and response['text'] or ''
def decode(self, filename, codetype, timeout):
cid = self.upload(filename, codetype, timeout)
if (cid > 0):
for i in range(0, timeout):
result = self.result(cid)
if (result != ''):
return cid, result
else:
time.sleep(1)
return -3003, ''
else:
return cid, ''
def report(self, cid):
data = {'method': 'report', 'username': self.username, 'password': self.password, 'appid': self.appid,
'appkey': self.appkey, 'cid': str(cid), 'flag': '0'}
response = self.request(data)
if (response):
return response['ret']
else:
return -9001
def post_url(self, url, fields, files=[]):
for key in files:
files[key] = open(files[key], 'rb')
res = requests.post(url, files=files, data=fields)
return res.text
def run(self, filename, codetype, timeout):
uid = self.login()
cid, result = self.decode(filename, codetype, timeout)
return cid, result
######################################################################
if __name__ == "__main__":
# 图片文件
filename = 'code.jpg'
# 验证码类型，# 例：1004表示4位字母数字，不同类型收费不同。请准确填写，否则影响识别率。在此查询所有类型 http://www.yundama.com/price.html
codetype = 1004
# 超时时间，秒
timeout = 60
ydm = YDMHttp()
cid, result = ydm.run(filename, codetype, timeout)
print(result)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
