python使用mysql
链接mysql做了个小练习:爬取 http://wufazhuce.com 上的问题,描述和答案,存到本地的数据库里。
数据表结构:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE `questions` ( `title` varchar(2000) DEFAULT NULL, `description` varchar(200) DEFAULT NULL, `answers` varchar(2000) DEFAULT NULL, `url` varchar(2000) DEFAULT NULL, `daynum` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# author: import pymysql.cursors import requests from bs4 import BeautifulSoup con = pymysql.connect(host='192.168.86.130', user='root', password='letmein', db='0603simon', port=3306, charset='utf8') # with con.cursor() as cur: # sql = 'show tables' # result = cur.execute(sql) # print(result) # exit() cur = con.cursor() for p_num in range(1, 1872): # for p_num in range(8,9): url = 'http://wufazhuce.com/question/%s' % p_num response = requests.get(url=url) response.encoding = response.apparent_encoding soup = BeautifulSoup(response.text, features="html.parser") # print(soup) tar = soup.find('div', class_='one-cuestion') # print(tar) if not tar: print('not tar') sql = '''insert into questions (title,description,answers,url,daynum) values('404','404','404','%s','%s')''' % ( url, p_num) print(sql) result = cur.execute(sql) con.commit() print('执行结果:' + str(result)) continue title = tar.find('h4').text.strip() # print (title) # exit() desc = soup.find('div', class_='cuestion-contenido').text.strip() # print(desc) ans = soup.find_all('div', class_='cuestion-contenido') for index, answer in enumerate(ans): if index == 0: continue # print(answer.text.strip()) answer = answer.text.strip() # print(url) if answer.__len__() > 1800: answer = answer[0:1800] sql = '''insert into questions (title,description,answers,url,daynum) values(%s,%s,%s,'%s','%s')''' % ( con.escape(title), con.escape(desc), con.escape(answer), url, p_num) print(sql) result = cur.execute(sql) con.commit() print('执行结果:' + str(result)) cur.close() con.close()
邮箱的使用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# coding:utf-8 def get_question(): obj = {} import requests from bs4 import BeautifulSoup import time first_num = 2593 first_date = time.mktime(time.strptime('2019-09-08', '%Y-%m-%d')) now_date = time.mktime(time.localtime()) cur_num = first_num + (int((now_date - first_date) / (24 * 60 * 60))) print(cur_num) url = 'http://wufazhuce.com/question/%s' % cur_num response = requests.get(url=url) response.encoding = response.apparent_encoding soup = BeautifulSoup(response.text, features="html.parser") tar = soup.find('div', class_='one-cuestion') if not tar: print('not tar') obj['title'] = '' return obj title = tar.find('h4').text.strip() desc = soup.find('div', class_='cuestion-contenido').text.strip() ans = soup.find_all('div', class_='cuestion-contenido') answer = '' for index, answer2 in enumerate(ans): if index == 0: continue answer = answer2.text.strip() # obj = {'title': '', 'desc': '', 'answer': ''} obj['title'] = title obj['desc'] = desc obj['answer'] = answer return obj def send_email(title, desc, content): import smtplib # 加载smtplib模块 from email.mime.text import MIMEText from email.utils import formataddr from email.mime.application import MIMEApplication from email.mime.image import MIMEImage from email.mime.multipart import MIMEMultipart sender = 'xxxxx@163.com' # 发件人邮箱账号 receive = 'xxxxx@qq.com' # 收件人邮箱账号 passwd = 'xxxxx' mailserver = 'smtp.163.com' port = '25' try: msg = MIMEMultipart('related') msg['From'] = formataddr(["sender", sender]) # 发件人邮箱昵称、发件人邮箱账号 msg['To'] = formataddr(["receiver", receive]) # 收件人邮箱昵称、收件人邮箱账号 msg['Subject'] = title # 文本信息 # txt = MIMEText('this is a test mail', 'plain', 'utf-8') # msg.attach(txt) # 附件信息 # attach = MIMEApplication(open("D:\xx\\tool\pycharm\\1.csv").read()) # attach.add_header('Content-Disposition', 'attachment', filename='1.csv') # msg.attach(attach) # 正文显示图片 body = '''【描述】:%s/br 【回答】:%s''' % (desc, content) text = MIMEText(body, 'html', 'utf-8') msg.attach(text) server = smtplib.SMTP(mailserver, port) # 发件人邮箱中的SMTP服务器,端口是25 server.login(sender, passwd) # 发件人邮箱账号、邮箱密码 server.sendmail(sender, receive, msg.as_string()) # 发件人邮箱账号、收件人邮箱账号、发送邮件 server.quit() print('success') except Exception as e: print(e) def main_to(): obj = get_question() res = 0 if obj['title']: send_email(obj['title'], obj['desc'], obj['answer']) res = 1 else: res = 0 return res from threading import Timer import time timer_interval = 1 def delayrun(): print('running') first_time = 3 * 60 * 60 time.sleep(first_time) print('先歇一下') t = Timer(timer_interval, delayrun) t.start() wait_time = 60 while True: print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))) res = main_to() if res: wait_time = 60 * 60 * 24 else: wait_time = 60 time.sleep(wait_time)