因工作需要,使用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))
输出结果: