使用python统计csdn博客一段时间内的访问量

转载请注明来源：http://blog.csdn.net/imred
这篇文章需要与上一篇结合来看，这篇文章的程序基于上一篇程序获取的数据。实现很简单，就是使用SQL查询一段时间内访问量的差值，展示出来就可以了。界面使用的是tkinter，截图如下：

程序如下：

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

import os
import sqlite3
import tkinter
#上篇文章的程序命名为blog_stat_server.py
import blog_stat_server
from tkinter import ttk

DB_PATH = os.path.join(os.path.dirname(__file__), 'blog_stat.db')

SQL_QUERY_TIME = "SELECT id, time FROM t_time ORDER BY time DESC"
SQL_QUERY_DELTA = "SELECT id, title, IFNULL(( \
        SELECT view \
        FROM t_view \
        WHERE aid=t_article.id AND tid=?) - ( \
        SELECT view \
        FROM t_view \
        WHERE aid=t_article.id AND tid=?), ( \
        SELECT view \
        FROM t_view \
        WHERE aid=t_article.id AND tid=?)) delta \
        FROM t_article \
        WHERE id IN ( \
        SELECT aid \
        FROM t_view \
        WHERE tid = ?) \
        ORDER BY delta DESC"

class MyError(Exception):
    def __init__(self, value):
        self.value = value
    def __str__(self):
        return repr(self.value)

def getIdTimeList():
    try:
        conn = sqlite3.connect(DB_PATH)
        try:
            cursor = conn.cursor()
            cursor.execute(SQL_QUERY_TIME)
            idTimeList = cursor.fetchall()
            return idTimeList
        finally:
            cursor.close()
        conn.commit()
    finally:
        conn.close()

def getStartEndTimeList(idTimeList):
    listLength = len(idTimeList)

    if listLength <= 1:
        raise MyError("Too few records")

    startTimeList = []
    endTimeList = []

    for i in range(listLength):
        if i == 0:
            endTimeList.append(idTimeList[i][1])
        elif i == listLength - 1:
            startTimeList.append(idTimeList[i][1])
        else:
            startTimeList.append(idTimeList[i][1])
            endTimeList.append(idTimeList[i][1])

    return startTimeList, endTimeList

def queryDelta():
    global tkstrStart
    global tkstrEnd
    global idTimeList
    global tree

    strStartTime = tkstrStart.get()
    strEndTime = tkstrEnd.get()

    length = len(idTimeList)

    strStartId = "-1"
    strEndId = "-1"

    for i in range(length):
        if strStartTime == idTimeList[i][1]:
            strStartId = idTimeList[i][0]
            break

    for i in range(length):
        if strEndTime == idTimeList[i][1]:
            strEndId = idTimeList[i][0]
            break

    print(strStartId)
    print(strEndId)

    try:
        conn = sqlite3.connect(DB_PATH)
        try:
            cursor = conn.cursor()
            cursor.execute(SQL_QUERY_DELTA, (strEndId, strStartId, strEndId, strEndId))
            idTitleDeltaList = cursor.fetchall()
            print(idTitleDeltaList)
        finally:
            cursor.close()
        conn.commit()
    finally:
        conn.close()

    length = len(idTitleDeltaList)
    tree.delete(*tree.get_children())
    for i in range(length):
        tree.insert("", i, values = (idTitleDeltaList[i][0], idTitleDeltaList[i][1], idTitleDeltaList[i][2]))

def refreshView():
    blog_stat_server.main()
    refreshTimeList()

def refreshTimeList():
    global idTimeList
    global comboStart
    global comboEnd
    idTimeList = getIdTimeList()
    startTimeList, endTimeList = getStartEndTimeList(idTimeList)
    comboStart["values"] = startTimeList
    comboEnd["values"] = endTimeList
    comboStart.current(0)
    comboEnd.current(0)

idTimeList = getIdTimeList()

top = tkinter.Tk()
top.title("blog_stat")

top.columnconfigure(0, weight=1)
top.rowconfigure(5, weight=1)

ttk.Label(top, text = "开始时间").grid(row = 0, column = 2)
ttk.Label(top, text = "结束时间").grid(row = 2, column = 2)

tkstrStart = tkinter.StringVar()
tkstrEnd = tkinter.StringVar()

comboStart = ttk.Combobox(top, textvariable = tkstrStart, state = "readonly")
comboEnd = ttk.Combobox(top, textvariable = tkstrEnd, state = "readonly")

comboStart.grid(row = 1, column = 2)
comboEnd.grid(row = 3, column = 2)

ttk.Button(top, text = "查询", command=queryDelta).grid(row = 4, column = 2)
ttk.Button(top, text = "刷新", command=refreshView).grid(row = 5, column = 2, sticky = (tkinter.N))

tree = ttk.Treeview(top, show = "headings")
tree.grid(row = 0, column = 0, rowspan = 6, sticky = (tkinter.N, tkinter.S, tkinter.E, tkinter.W))
tree["columns"] = ("id", "title", "delta", "empty")
tree.column("id", width = 40, anchor = "center")
tree.column("delta", width = 80, anchor = "center")
tree.heading("id", text = "ID")
tree.heading("title", text = "标题")
tree.heading("delta", text = "访问量")

scroll = tkinter.Scrollbar(top)
scroll.grid(row = 0, column = 1, rowspan = 6, sticky = (tkinter.N, tkinter.S))
scroll.config(command = tree.yview)

# 在scroll定义后再设置
tree["yscrollcommand"] = scroll.set

def main():
    refreshTimeList()

    top.mainloop()

if __name__ == "__main__":
    main()

本文采用 CC-BY 协议进行授权