统计车型提及率的小程序

因工作需要，使用python写了个统计车型提及率的小程序

python版本 3.5 

用到的库：sqlite3，xlwt，re

# -*- coding: utf-8 -*-

from __future__ import division
import sqlite3
import os
import xlwt
import re
import time

class CarNum:


    # 数据库文件路径
    fpath = r'F:\310.db3'
    # 保存excel文件路径
    epath = r'F:\cary.xls'
    # 车型列表路径
    cpath = r'F:\car.txt'
    # 计算月份的字典
    date_dic = {
        '一月份':" 发表于 LIKE '2016-1-%'",
        '二月份': " 发表于 LIKE '2016-2-%'",
        '三月份': " 发表于 LIKE '2016-3-%'",
        '四月份': " 发表于 LIKE '2016-4-%'",
        '五月份': " 发表于 LIKE '2016-5-%'",
        '六月份': " 发表于 LIKE '2016-6-%'",
        '七月份': " 发表于 LIKE '2016-7-%'",
        '八月份': " 发表于 LIKE '2016-8-%'",
        '九月份': " 发表于 LIKE '2016-9-%'",
        '十月份': " 发表于 LIKE '2016-10-%'",
        '十一月份': " 发表于 LIKE '2016-11-%'",
        '十二月份': " 发表于 LIKE '2016-12-%'"

    }

    #获取链接
    def GetConn(self):
        conn = sqlite3.connect(self.fpath)
        if conn:
            print('connect success')
        else:
            print('connect failure')
        return  conn
    def GetCarList(self):
        carlist=[]
        fp = open(CarNum.cpath)
        for car in fp.readlines():
            carlist.append(car.split())
        fp.close()
        return carlist

    def WipeReply(self,datalist):
        resultstr=''
        pattern="reply.*"
        for data in datalist:
            #print('------------------------------all data----------------------------')
            #print(data)
            if 'reply' in ''.join(data):
                #print('------------------------------reply data----------------------------')
                #print(re.findall(pattern, ''.join(data)))
                resultstr += ''.join(re.findall(pattern, ''.join(data)))
            else:
                #print('------------------------------not reply data----------------------------')
                #print(''.join(data))
                resultstr += ''.join(data)

        return resultstr

    def getCount(self,pattern,strall):
        num=strall.count(pattern)
        return num

    def write_excel(self,data_list):
        wb = xlwt.Workbook()
        ws = wb.add_sheet(u'工作表1')
        row = 0
        ws.write(row, 0, u'车型')
        ws.write(row, 1, u'月份')
        ws.write(row, 2, u'提及数')
        ws.write(row, 3, u'当月提及率')
        row = 1
        for result in data_list:
            col = 0
            for item in result:
                ws.write(row, col, item)
                col += 1
            row += 1

        wb.save(CarNum.epath)

if __name__ == '__main__':
    start = time.clock()
    carnum=CarNum()
    conn=carnum.GetConn()
    cur=conn.cursor()
    carlist=carnum.GetCarList()
    date_dic=carnum.date_dic
    relist=[]
    for key in date_dic:
        sql="select 内容 from Content where " +''.join(date_dic[key])
        resultstr=carnum.WipeReply(cur.execute(sql))
        #print(type(resultstr))
        total=0
        for car in carlist:
            num=carnum.getCount(''.join(car),resultstr)
            total+=num
        if total==0:
            total=1
        for car in carlist:
            num=carnum.getCount(''.join(car),resultstr)
            #listproducer=[car,key,num,round(num/total,4)]
            listproducer = [car, key, num, "%.2f%%" % (float(num / total)*100)]
            relist.append(listproducer)

    carnum.write_excel(relist)
    cur.close()
    conn.close()
    end = time.clock()
    print('------------------------------执行时间----------------------------')
    print("read: %f s" % (end - start))

输出结果：