Python实现农历生日提醒功能

前言：

某天突发奇想，想要实现一个农历生日提醒。这个时候有了如下的大概思路：

获取农历 --> 匹配 --> 提醒

发现实现这个需求最主要的在获取农历这一块，后边两个不是什么巨大挑战。遂查阅一番资料，发现数据库可以实现阳历转阴历功能。在数据库这一块，我是在是小菜，看不懂存储过程。蛋疼！直接放弃改用其他思路。思考了许久，想到可以通过爬虫爬取现成的日历信息，存库再进行匹配。所以，现在思路如下：

爬取农历存库 --> 匹配姓名表 --> 提醒

一、爬取日历网站

刚开始是按照每年一个表的思路去爬，代码及注释如下：

首先是建表：

（文件名：reptile\CreateDb.py）

# -*- coding:utf-8 -*-import MySQLdbimport os#将敏感信息写入环境变量 通过export去设置valueMYSQLDB_HOST=os.environ.get('MYSQLDB_HOST')MYSQLDB_USER=os.environ.get('MYSQLDB_USER')MYSQLDB_PASSWD=os.environ.get('MYSQLDB_PASSWD')db = MySQLdb.connect(    host=MYSQLDB_HOST,    port=3306,    user=MYSQLDB_USER,    passwd = MYSQLDB_PASSWD,    db='Calendar',    charset = "utf8",    )    cursor = db.cursor()#数据库插入def Insert_mysql(sql):    cursor.execute(sql)    db.commit()    #数据库查询def Inquire_mysql(sql):    cursor.execute(sql)    request = cursor.fetchall()    return requestif __name__ == "__main__":    st_sql = "show tables;"    cursor.execute(st_sql)    request = cursor.fetchall()    for year in range(1900,2050):        if year in request:            print "[*]%d is in database!"        else:            print "[!]%s No in the Database,create now." % year            ct_sql = """CREATE TABLE `%d` (                `DAY`  date NOT NULL ,                `WEEK`  varchar(50) NULL ,                `CONSTELLATON`  varchar(50) NULL ,                `FESTIVAL`  varchar(24) NULL ,                `YEAR`  varchar(24) NULL ,                `LUNARCALENDAR`  varchar(60) NULL ,                `LUNNAR`  varchar(24) NULL ,                `ERSHIBASU`  varchar(24) NULL ,                `JIAZI`  varchar(24) NULL ,                PRIMARY KEY (`DAY`)                )                ENGINE=InnoDB                DEFAULT CHARACTER SET=utf8                ;""" % year            try:                cursor.execute(ct_sql)                db.commit()                print "[.] %s OK!" % year            except:                print "Error:unable to fecth data"    db.close()

效果：

接着爬取：

（文件名：reptile\Spider.py）

#coding:utf-8import re,urllib2from bs4 import BeautifulSoupfrom urllib import urlencodeclass SiteData:    def __init__(self,url):        self.Url = url            def Data(self):         #伪装头        values = {'Accept':'text/html,application/xhtml+xml,application/xml;q=0.9,p_w_picpath/w        ebp,*/*;q=0.8',        'Accept-Language':'zh-CN,zh;q=0.8',        'User-Agent':'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like         Gecko) Chrome/46.0.2490.86 Safari/537.36'        }                SiteUrl = urllib2.Request(self.Url,headers=values)        WebSite = urllib2.urlopen(SiteUrl)        HtmlData = WebSite.read()        WebSite.close()        return HtmlData                    def Find(self):        #BeautifulSoup筛选出我要的内容        HtmlData = self.Data()        HtmlSoup = BeautifulSoup(HtmlData,"html5lib")        FindALL = HtmlSoup.find_all("a")        List=[]        for i in FindALL:            filter = i.encode('gb2312').replace(' ',' ').replace(';','')            if "#" in filter:                ReplaceText = filter.replace('","",ReplaceText)                Extract1 = re.sub(r"[0-9]+\:[0-9]+","",Extract2)                Extract  = Extract1.replace('   0-','').replace('">','')                List.append(Extract)            else:                pass        return List

开始爬取：

（文件名：reptile\Run.py）

#coding:utf-8import timefrom CreateDb import db,cursor    #将数据库操作的一些方法加进来from Spider import SiteData       #将爬虫操作的方法加进来def ListMark(list):    LIST = []    items = list.split('\n')    List = [items[1],items[2],items[3],items[4],items[6],items[7],items[8]]    for i in List:        item,value = i.split(':')        LIST.append(value)    return LIST    def get_item(text):    item,value = text.split(' ')    return itemdef get_value(text):    item,value = text.split(' ')    return valuedef UTF(text):        TEXT = text.decode('gbk').encode('utf8')    return TEXT    if __name__ == '__main__':    for year in range(1900,2050):        sql = "select DAY from Calendar.%s order by DAY desc limit 1" % year        cursor.execute(sql)        request = cursor.fetchall()        print "[!]process %s" % year        try:            Month = str(request).split(',')[1]        except:            Month = str(request)            if Month == "()":                print  "[!]No data in Table! Crawling now.."                for m in range(1,13):                    url = "http://www.nongli.com/item3/rili_%s-%s.htm" % (year,m)                    OpenSite = SiteData(url)                    returnList = OpenSite.Find()                    for i in returnList:                        List = ListMark(i)                        Day = get_item(List[0])                        Week = get_value(List[0])                        Constellation = List[1]                        Festival = List[2]                        Year = List[3][0:4]                        Lunarcalendar = List[3][4:]                        Lunnar = List[4]                        Ershibasu = List[5]                        Jiazi = List[6]                        Insert_sql = "insert into Calendar.%s values('%s','%s','%s','%s','%s','%s','%s','%s','%s')" \                          % (year,Day,UTF(Week),UTF(Constellation),UTF(Festival),\                            UTF(Year),UTF(Lunarcalendar),UTF(Lunnar),UTF(Ershibasu),UTF(Jiazi))                        try:                            cursor.execute(Insert_sql)                            db.commit()                            print "[ ]Insert %s data ..." % Day                        except:                            print "[!]Insert %s Error!!!" % Day                            db.rollback()                         time.sleep(0.1)        try:            if Month == 12:                print "[ ] Have data in Table."            else:                NodataMoth = range(int(Month)+1,13)                for M in NodataMoth:                    url = "http://www.nongli.com/item3/rili_%s-%s.htm" % (year,M)                    OpenSite = SiteData(url)                    returnList = OpenSite.Find()                    for i in returnList:                        List = ListMark(i)                        Day = get_item(List[0])                        Week = get_value(List[0])                        Constellation = List[1]                        Festival = List[2]                        Year = List[3][0:4]                        Lunarcalendar = List[3][4:]                        Lunnar = List[4]                        Ershibasu = List[5]                        Jiazi = List[6]                        Insert_sql = "insert into Calendar.%s values('%s','%s','%s','%s','%s','%s','%s','%s','%s')" \                          % (year,Day,UTF(Week),UTF(Constellation),UTF(Festival),\                            UTF(Year),UTF(Lunarcalendar),UTF(Lunnar),UTF(Ershibasu),UTF(Jiazi))                        try:                            cursor.execute(Insert_sql)                            print "[ ]Insert %s data ..." % Day                            db.commit()                        except:                            print "[!]Insert %s Error!!!" % Day                            db.rollback()                        time.sleep(0.1)         except:            print "[!]Unknown Error!"    db.close()

效果：

平均1条6条数据 从1900-2049年需要2个半小时以上时间。

爬完仔细一下，一个表才365条记录，1900-2049才150年 完全可以合成一张表。

遂，写脚本，将所有表合体，现在所有日期数据都在数据库里了：

二、将想要提醒的人加入一张表，用于匹配

姓名表比较简单，主要有姓名，性别，日期，农历日期。一些星座等其他信息可以根据需要自己添加。一个字段一个字段添加太过蛋疼，可以用数据库触发器自动填充一些，但是。。我！不！会！遂，写了个脚本用于添加：

（文件名：add_use.py）

#coding:utf-8import re,sysfrom reptile.CreateDb import Insert_mysql,Inquire_mysqldef UTF(text):    TEXT = text.decode('UTF-8').encode('UTF-8')    return TEXT    #新增用户的方法  def add(Name,Sex,Birthday):        #自动新增ID，匹配最后一个ID，新ID是最后一个ID+1    Last_id_sql = "SELECT Tb_use.USE_ID FROM Calendar.`Tb_use` \    ORDER BY Tb_use.USE_ID DESC LIMIT 1"    Last_id = Inquire_mysql(Last_id_sql)    try:        m = re.search(r"[0-9]+",str(Last_id))        n = m.group()        id = int(n)+1    except:        id = 1    #如果填入为1 则为男性    if Sex == "1":        SEX = "男"    else:        SEX = "女"            birthday_sql = "SELECT Calendar.Lunarcalendar FROM Calendar WHERE \Calendar.TB_DAY = '%s';" % Birthday               #匹配日历表 自动填充农历信息    Lunarcalendar = Inquire_mysql(birthday_sql)    Lunar = Lunarcalendar[0][0].encode('UTF-8')    Insert_sql ="INSERT INTO `Tb_use` (`USE_ID`,`NAME`,`SEX`,`TB_DAY`,\        `LUNARCALENDAR`) VALUES ('%s','%s','%s','%s','%s');" % \        (id,UTF(Name),UTF(SEX),Birthday,Lunar)    Insert_mysql(Insert_sql)    print "[!]新增新记录，编号为%s\n姓名：%s  性别:%s \n生日：%s 农历：%s" \          %(id,Name,SEX,Birthday,Lunar)#删除用户的方法可以根据新增用户方法去修改，这里pass   def DEL(self):    pass#将方法映射到字典def run(type,Name=None,Sex=None,Birthday=None):    RUN = {        'add':lambda:add(Name,Sex,Birthday),        'del':lambda:DEL()    }    return RUN[type]()        if __name__ == "__main__":         #实现类似命令行交互式效果     while True:        echo = raw_input(">>")        Split = echo.split(' ')               if echo == "exit":            print "Exit!"            break        elif echo == "":            pass                    #如果输入信息正确，则运行方法        elif Split[0] == "add" or Split[0] == "del":            try:                a = run(Split[0],Split[1],Split[2],Split[3])            except:                pass                        else:            print "Please Use 'add' to Add user."

三、匹配后发送提醒

(文件名:Run.py)

#coding:utf-8import time,json,smtplib,osfrom reptile.CreateDb import Insert_mysql,Inquire_mysqlfrom email.mime.text import MIMETextfrom email.MIMEMultipart import MIMEMultipartfrom email.MIMEBase import MIMEBase#今天的日期Today = time.strftime('%Y-%m-%d',time.localtime(time.time()))#从数据库从获取今天的IDToday_Sql = "SELECT Calendar.ID FROM `Calendar` WHERE TB_DAY='%s';" % TodayTodayID = Inquire_mysql(Today_Sql)[0][0]#这个方法用于获取今天日期def Get_today(id):    Sql = "SELECT Calendar.TB_DAY FROM `Calendar` WHERE ID='%s';" % id    Today = Inquire_mysql(Sql)[0][0]    return Today#用于获取今天有哪些小伙伴生日    def Today_Birthday(id):    Text = []    Today_Sql = "SELECT Calendar.LUNARCALENDAR FROM `Calendar` WHERE ID='%s'" % id    TodayLunar = Inquire_mysql(Today_Sql)[0][0]    Today_Birthday_sql = "SELECT Tb_use.NAME FROM `Tb_use` WHERE LUNARCALENDAR='%s';" % TodayLunar    Today_Birthday = Inquire_mysql(Today_Birthday_sql)    if str(Today_Birthday) == '()':        Text =  []    else:        for i in range(0,len(Today_Birthday)):            Text.append(Today_Birthday[i][0])       if Text == []:        Str = ""    else:        Str = '\t'.join(i.encode('utf-8') for i in Text)    return Str#用于获取7天内有哪些小伙伴生日    def Week_Birthday():    Text = []    for item in range(TodayID,TodayID+7):        if Today_Birthday(item) == "":            pass        else:            Text.append("%s : %s" % (Get_today(item),Today_Birthday(item)))    Str = '\n'.join(i for i in Text)    return Str    #发邮件提醒def Mail(Subject,text):        MAIL_USER=os.environ.get('MAIL_USER')    MAIL_PASSWD=os.environ.get('MAIL_PASSWD')    sender = 'cctv<你的邮箱@qq.com>'    receiver = ['发送到@139.com']    subject = Subject    smtpserver = 'smtp.mail.qq.com'    msg = MIMEMultipart('alternative')    msg['Subject'] = subject    html = text    part = MIMEText(html,'html','utf-8')    msg.attach(part)    smtp = smtplib.SMTP()    smtp.connect('smtp.mail.qq.com')    smtp.login(MAIL_USER,MAIL_PASSWD)    smtp.sendmail(sender,receiver,msg.as_string())    smtp.quit()    if __name__ == '__main__':      #获取今天星期    a = time.localtime()    Time = time.strftime("%w",a)        #今天生日的小伙伴    tb_text = Today_Birthday(TodayID)    #7天内生日的小伙伴    wb_text = Week_Birthday()        #如果今天有人生日就发邮件    if tb_text == '':        pass    else:        Mail('TodayBirthday',tb_text)            #每周一查一下，如果未来7天内有人生日就发邮件    if Time == '1':        if wb_text == '':            pass        else:            Mail('WeekBirthday',wb_text)    else:        pass

最后，加入任务计划

（文件名：Run.sh）

#!/bin/bashexport MYSQLDB_USER=****export MYSQLDB_PASSWD=****export MYSQLDB_HOST=****export MAIL_PASSWD=****export MAIL_USER=****#我是在虚拟环境下的，所以要用虚拟环境的路径运行`/home/ubuntu/class/env/bin/python /home/ubuntu/class/LunarBirthday/Run.py`

加入任务计划，大功告成！