python定时操作mysql统计数据

# -*- coding: UTF-8 -*-

import schedule
import time
import pymysql
import datetime
import json

#数据库封装方法
class MysqlHelp(object):
    config={
        'host':'localhost',
        'user':'root',
        'password':'123456',
        'db':'wwp',
        'charset':'utf8'

   }
    def __init__(self):
        self.connection=None
        self.cursor=None
    #该函数用来从数据库表中查询一行数据
    def getOne(self,sql,*args):
        try:
            self.connection = pymysql.connect(**MysqlHelp.config)
            self.cursor=self.connection.cursor()
            self.cursor.execute(sql,args)
            return self.cursor.fetchone()
        except Exception as ex:
            print(ex,ex)
        finally:
            self.close()
    def close(self):
        if(self.cursor):
            self.cursor.close()
        if(self.connection):
            self.connection.close()

    #该函数用来从数据库表中查询多行数据
    def getList(self,sql,*args):
        try:
            self.connection = pymysql.connect(**MysqlHelp.config)
            self.cursor=self.connection.cursor()
            self.cursor.execute(sql,args)
            return self.cursor.fetchall()
        except Exception as ex:
            print(ex,ex)
        finally:
            self.close()
    #该函数用来插入一行数据
    def insert(self,sql):
        try:
            self.connection = pymysql.connect(**MysqlHelp.config)
            self.cursor=self.connection.cursor()
            self.cursor.execute(sql)
            self.connection.commit()
            return self.cursor.fetchone()
        except Exception as ex:
            print(ex,ex)
        finally:
            self.close()

    def close(self):
        if(self.cursor):
            self.cursor.close()
        if(self.connection):
            self.connection.close()

date = datetime.datetime.now()
year = date.year
month = date.month
end = datetime.date(year, month, 1) - datetime.timedelta(days=1)
enddate = str(end.year)+"-"+str(end.month)+"-"+str(end.day)+" 23:59:59"
if month == 1:
    year = year-1
    month=12
else:
    month=month-1
startdate = str(year)+"-"+str(month)+"-"+"1"+" 00:00:01"

def shipjob():
    if date.day == 1:
        print("船舶统计")
        helper=MysqlHelp()
        # 查询所有船舶语句
        shipsql = "SELECT code FROM ship_info" 
        # 执行SQL语句, 获取所有记录列表
        shiplist = helper.getList(shipsql)
        for row in shiplist:
            shipcode = str(row[0])
            #获得计划次数
            applicationsql='''SELECT * FROM  plan WHERE 
            (start_time between "%s" and "%s" OR end_time between "%s" and "%s" OR (start_time <= "%s" AND end_time >= "%s") ) 
            and ship_info_code = "%s"'''%(startdate,enddate,startdate,enddate,startdate,enddate,shipcode)
            applicationlist = helper.getList(applicationsql)
            #计划数量
            applicationnum = len(applicationlist)
            if applicationnum > 0:
                #插入船舶统计
                workssql='''INSERT INTO `wwp`.`reports`(`id`, `statisticsyear`, `planttime`, `repairtime`, `inspectiontime`, `basicistypecode`, `statisticsmonth`)
                VALUES (null,  "%s年",%s, %s, %s, '船舶统计',  "%s月");'''%("",year,applicationnum,0,0,month)
                helper.insert(workssql)
    else:
         print("船舶日期："+str(date.day))      

schedule.every().day.at("00:01").do(shipjob)
print("I'm working...")while True:
    schedule.run_pending()
    time.sleep(1)