Python3.0 我的DailyReport 脚本(三) 数据库发送模块

其实不会用Python,跟风装了Python3.0,看了几天自带的Manual,写个日报的脚本玩玩,不用不要紧,一用感觉还挺好用的

 

#!/usr/bin/env python
#coding=utf-8
#author:haozes
#sqlite数据库操作
import sqlite3
import datetime
import time
from ExcelHelper import *
import re

class DBHelper:
    def __init__(self,dbFile):
        self.conn=sqlite3.connect(dbFile)
        if self._hasTable('report')==False:
            self._initDB()
        
    def cur(self):
        return self.conn.cursor();
    
    def close(self):
        self.conn.close()
        
    def execute(self,sql):
        c=self.cur().execute(sql)
        fetch = c.fetchall()
        self.conn.commit()
        c.close()
        return fetch;
    
    def addReport(self,report):
        sql="select * from report where date(reportDate)='{0}'"
        sql=sql.format(report.get('reportDate').strftime("%Y-%m-%d"))
        result=self.execute(sql)
        if len(result)>0:
            return False
        else:
            sql="insert into report (reportDate,work,percent,hourSpent,tommorrowPlan,question)values('{0}','{1}',{2},{3},'{4}','{5}')"
            sql=sql.format(report.get('reportDate'),report.get('work'),report.get('percent'),report.get('hourSpent'),report.get('tommorrowPlan'),report.get('question'))
            print(sql)
            self.execute(sql)  
            return True
        
    def delReport(self,report):
        sql="delete from report where date(reportDate)='{0}'"
        sql=sql.format(report.get('reportDate').strftime("%Y-%m-%d"))
        result=self.execute(sql)
        
    def updateReport(self,report):
        sql="update  report set isSend=1 where date(reportDate)='{0}'"
        sql=sql.format(report.get('reportDate').strftime("%Y-%m-%d"))
        self.execute(sql)
        
    def selectByMonth(self,inputDate):
        #输入年和月份
        d=time.strptime(inputDate,"%Y-%m")
        n=datetime.datetime(d[0],d[1]+1,d[2])
        #月末
        n=n-datetime.timedelta(days=1)
        
        sql="select * from report where reportDate>='{0}' and reportDate<='{1}'"
        sql=sql.format(time.strftime("%Y-%m-%d",d),n.strftime("%Y-%m-%d"))
        print(sql)
        result=self.execute(sql)
        #print(result)
        return result
    
    def selectNotSendReport(self):
        sql="select * from report where isSend=0"   
        result=self.execute(sql)
        #print(result)
        return result
    
    def _initDB(self):
        sql='''
        CREATE TABLE [report] (
          [reportDate] DATETIME NOT NULL, 
          [work] CHAR(3000), 
          [percent] INT, 
          [hourSpent] INT, 
          [tommorrowPlan] CHAR(3000), 
          [question] CHAR(4000), 
          [isSend] INT DEFAULT 0, 
          CONSTRAINT [sqlite_autoindex_report_1] PRIMARY KEY ([reportDate]) ON CONFLICT ABORT)
        '''
        self.execute(sql)
        
    def _hasTable(self,tblName):
        sql='''SELECT [Name] FROM [SQLITE_MASTER] WHERE ([type] = 'table') OR ([type] = 'view')'''  
        tbl=self.execute(sql)
        #create a tuple
        t=tblName,
        if t in tbl:
            return True
        else:
            print("can't find:"+tblName +"and will be created")
            return False
            
if __name__ == "__main__":  
    #选择导出某月的日报
    db=DBHelper(r'dailyReport.db3')
    xlsName=''
    while re.match(r"(?:^(19|20)[0-9]{2}[- /.](0[1-9]|1[012])\b)\Z", xlsName, re.DOTALL | re.IGNORECASE)==None:
        xlsName=input('请输入要导出的月份(格式如:2009-09):')
    rows=db.selectByMonth(xlsName)
    if len(rows)<1:
        print('该月无数据,按任意键程序退出!')
        inpu('')
        exit()
        
    dir=os.getcwd()
    xlsFile=os.path.join(dir,xlsName+".xlsx")    
    if os.path.exists(xlsFile):
        if(input('文件已存在,删除?(Y/N):').strip().lower()=='y'):
            os.remove(xlsFile) 
    xls=ExcelHelper(xlsFile)
    xls.activateSheet('Sheet1');
    
    #设置表头及样式开始
    xls.setCell(1,1,'日期','Sheet1')
    xls.mergeCell(1,1,2,1)
    xls.setCell(1,2,'工作日报','Sheet1')
    xls.mergeCell(1,2,1,3)
    xls.mergeCell(1,2,1,4)
    xls.setCell(1,5,'明日计划','Sheet1')
    xls.mergeCell(1,5,2,5)
    xls.setCell(1,6,'待解决问题','Sheet1')
    xls.mergeCell(1,6,2,6)
    xls.setCell(2,2,'工作内容','Sheet1')
    xls.setCell(2,3,'完成情况','Sheet1')
    xls.setCell(2,4,'估计用时','Sheet1')
    xls.activeSheet().Rows(1).RowHeight=30
    xls.activeSheet().Rows(2).RowHeight=30
    xls.activeSheet().Columns(1).ColumnWidth=8
    xls.activeSheet().Columns(2).ColumnWidth=62
    xls.activeSheet().Columns(3).ColumnWidth=11
    xls.activeSheet().Columns(4).ColumnWidth=8
    xls.activeSheet().Columns(5).ColumnWidth=40
    xls.activeSheet().Columns(6).ColumnWidth=28
    xls.activeSheet().Range("A1:F2").Font.Bold = True
    xls.activeSheet().Range("A1:F2").Font.Size = 11
    xls.activeSheet().Range("A1:F2").Font.Color = win32api.RGB(55, 96, 145)
    xls.activeSheet().Range("A1:F2").HorizontalAlignment = -4108
    #设置表头及样式结束    
    
    for i in range(0,len(rows)):
        xls.setCell(i+3,1,str(rows[i][0])[0:10],'Sheet1')
        xls.setCell(i+3,2,rows[i][1],'Sheet1')
        xls.setCell(i+3,3,rows[i][2],'Sheet1')
        xls.setCell(i+3,4,rows[i][3],'Sheet1')
        xls.setCell(i+3,5,rows[i][4],'Sheet1')
        xls.setCell(i+3,5,rows[i][5],'Sheet1')
    xls.save()
    xls.close()
    print('报表已生成!')