{"id":426,"date":"2018-03-14T16:10:47","date_gmt":"2018-03-14T08:10:47","guid":{"rendered":"http:\/\/www.max-shu.com\/blog\/?p=426"},"modified":"2018-03-14T16:13:10","modified_gmt":"2018-03-14T08:13:10","slug":"%e4%bd%bf%e7%94%a8python%e4%bb%8emysql%e7%94%9f%e6%88%90discuz%e7%9a%84excel%e6%8a%a5%e8%a1%a8","status":"publish","type":"post","link":"http:\/\/www.max-shu.com\/blog\/?p=426","title":{"rendered":"\u4f7f\u7528python\u4eceMySQL\u751f\u6210discuz\u7684excel\u62a5\u8868"},"content":{"rendered":"
\u8981\u6c42\uff1a\u6bcf\u5468\u4ecediscuz\u7684mysql\u6570\u636e\u5e93\u8bfb\u53d6\u6570\u636e\uff0c\u751f\u6210\u6240\u9700\u8981\u7684xecel\u62a5\u8868\uff0c\u6a21\u677f\u683c\u5f0f\u5982\u4e0b\uff1a<\/div>\n
\"\"<\/div>\n
\u540c\u65f6\u751f\u6210\u7edf\u8ba1\u56fe\uff1a<\/div>\n
\"\"<\/div>\n

 <\/p>\n

\u9644\uff0c\u4ee3\u7801\u5982\u4e0b\uff08\u9700\u8981\u6839\u636e\u4e0a\u9762\u7684\u8868\u683c\u5148\u505a\u597dexcel\u6a21\u677f\uff09\uff1a<\/b><\/div>\n
\n
#!\/usr\/bin\/env python<\/div>\n
# -*- coding: cp936 -*-<\/div>\n
__author__ = ‘Max Shu’<\/div>\n
<\/div>\n
import sys<\/div>\n
import string<\/div>\n
import os<\/div>\n
import time<\/div>\n
import datetime<\/div>\n
import struct<\/div>\n
import MySQLdb<\/div>\n
import openpyxl<\/div>\n
from openpyxl.styles import Style, PatternFill, Border, Side, Alignment, Protection, Font<\/div>\n
from openpyxl.charts import LineChart, Reference, Series<\/div>\n
from openpyxl.cell import coordinate_from_string, get_column_letter, get_column_interval, column_index_from_string<\/div>\n
<\/div>\n
class BCDISCUZ_Statistics():<\/div>\n
\u00a0 \u00a0 conn = None<\/div>\n
<\/div>\n
\u00a0 \u00a0 usersTotal = 0<\/div>\n
\u00a0 \u00a0 usersActivityTotal = 0<\/div>\n
\u00a0 \u00a0 usersZombieTotal = 0<\/div>\n
\u00a0 \u00a0 postsMaxOfSingleUser = 0<\/div>\n
\u00a0 \u00a0 threadsMaxOfSingleUser = 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 postsTotal = 0<\/div>\n
\u00a0 \u00a0 threadsTotal = 0<\/div>\n
\u00a0 \u00a0 postsMaxOfSingleThread = 0<\/div>\n
\u00a0 \u00a0 pvMaxOfSingleThread = 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 forumsTotal =0<\/div>\n
\u00a0 \u00a0 postsMaxOfSingleForum = 0<\/div>\n
\u00a0 \u00a0 threadsMaxOfSingleForum = 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def __init__(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.conn = MySQLdb.connect(host = “localhost”,<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 user = “root”,<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 passwd = “xxxxx”,<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 db = “discuz_ecloud”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “ERROR: Can’t connect to MySQL( localhost:3306 )”<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.conn = None<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return<\/div>\n
<\/div>\n
\u00a0 \u00a0 def __del__(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn != None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.conn.commit()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.conn.close()<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Print_All_Statistics_Value(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print ” “<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “usersTotal: %d” % self.usersTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “usersActivityTotal: %d” % self.usersActivityTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “usersZombieTotal: %d” % self.usersZombieTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “postsMaxOfSingleUser: %d” % self.postsMaxOfSingleUser<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “threadsMaxOfSingleUser: %d” % self.threadsMaxOfSingleUser<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print ” “<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “postsTotal: %d” % self.postsTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “threadsTotal: %d” % self.threadsTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “postsMaxOfSingleThread: %d” % self.postsMaxOfSingleThread<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “pvMaxOfSingleThread: %d” % self.pvMaxOfSingleThread<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print ” “<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “forumsTotal: %d” % self.forumsTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “postsMaxOfSingleForum: %d” % self.postsMaxOfSingleForum<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print “threadsMaxOfSingleForum: %d” % self.threadsMaxOfSingleForum<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 print ” “<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_All_Statistics_Value(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_Users_Total()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_Users_Activity_Total()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_Users_Zombie_Total()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_Posts_Max_Of_Single_User()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_Threads_Max_Of_Single_User()<\/div>\n
<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_Posts_Total()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_Threads_Total()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_Posts_Max_Of_Single_Thread()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_PV_Max_Of_Single_Thread()<\/div>\n
<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_Forums_Total()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_Posts_Max_Of_Single_Forum()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Get_Threads_Max_Of_Single_Forum()<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_Users_Total(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u7528\u6237\u6570”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT COUNT(*) FROM pre_common_member;”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
# \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0for row in result_set:<\/div>\n
# \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0print “%s” % (row[0])<\/div>\n
# \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0print “Number of rows returned: %d” % cursor.rowcount<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.usersTotal = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.usersTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_Users_Activity_Total(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u6d3b\u8dc3\u7528\u6237\u6570”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT COUNT(*) FROM pre_common_member_status WHERE lastvisit >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 WEEK));”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.usersActivityTotal = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.usersActivityTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_Users_Zombie_Total(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u50f5\u5c38\u7528\u6237\u6570”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT COUNT(*) FROM pre_common_member_status WHERE lastvisit <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 MONTH));”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.usersZombieTotal = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.usersZombieTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_Posts_Max_Of_Single_User(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u5355\u7528\u6237\u6700\u591a\u5e16\u5b50\u6570”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT MAX(posts) FROM pre_common_member_count;”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.postsMaxOfSingleUser = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.postsMaxOfSingleUser<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_Threads_Max_Of_Single_User(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u5355\u7528\u6237\u6700\u591a\u4e3b\u9898\u6570”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT MAX(threads) FROM pre_common_member_count;”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.threadsMaxOfSingleUser = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.threadsMaxOfSingleUser<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_Posts_Total(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u5e16\u5b50\u6570”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT COUNT(*) FROM pre_forum_post;”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.postsTotal = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.postsTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_Threads_Total(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u4e3b\u9898\u6570”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT COUNT(*) FROM pre_forum_thread;”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.threadsTotal = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.threadsTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_Posts_Max_Of_Single_Thread(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u5355\u4e3b\u9898\u6700\u591a\u5e16\u5b50\u6570”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT MAX(t1.c1) FROM (SELECT COUNT(*) AS c1 FROM pre_forum_post GROUP BY tid) AS t1;”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.postsMaxOfSingleThread = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.postsMaxOfSingleThread<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_PV_Max_Of_Single_Thread(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u5355\u4e3b\u9898\u6700\u5927PV\u503c”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT MAX(views) FROM pre_forum_thread;”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.pvMaxOfSingleThread = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.pvMaxOfSingleThread<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_Forums_Total(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u7248\u5757\u6570”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT COUNT(*) FROM pre_forum_forum WHERE STATUS=1 AND TYPE <> ‘group’;”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.forumsTotal = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.forumsTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_Posts_Max_Of_Single_Forum(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u5355\u7248\u5757\u6700\u591a\u5e16\u5b50\u6570”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT MAX(posts) FROM pre_forum_forum;”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.postsMaxOfSingleForum = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.postsMaxOfSingleForum<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Get_Threads_Max_Of_Single_Forum(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 “””\u5355\u7248\u5757\u6700\u591a\u4e3b\u9898\u6570”””<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if self.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 try:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor = self.conn.cursor ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.execute (“SELECT MAX(threads) FROM pre_forum_forum;”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 result_set = cursor.fetchall ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 cursor.close ()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 self.threadsMaxOfSingleForum = result_set[0][0]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return self.threadsMaxOfSingleForum<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 except MySQLdb.Error, e:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 print “Error %d: %s” % (e.args[0], e.args[1])<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return 0<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Write_Excel_File(self):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 fileDir = u”Excel\u5468\u62a5”<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 fileNamePre = u”\u8bba\u575b\u73b0\u7f51\u73af\u5883\u4e1a\u52a1\u7edf\u8ba1\u5468\u62a5-“<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 dateStr = time.strftime(‘%Y%m%d’, time.localtime())<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 # delete old file for today.<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 fileName = os.path.join(fileDir, fileNamePre+dateStr+u”.xlsx”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 fileName = fileName.encode(“cp936”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 if os.path.isfile(fileName):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 os.remove(fileName)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 # get newest file for before.<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 newestFileName = “”<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 oldFileDate = “”<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 fileDir = fileDir.encode(“cp936”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 for file in os.listdir(fileDir):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 fileDate = file[len(fileNamePre):file.index(“.xlsx”)]<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 if oldFileDate < fileDate:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 oldFileDate = fileDate<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 newestFileName = file<\/div>\n
<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 # open the newest file to operation.<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 border=Border(left=Side(border_style=’thin’, color=’FF000000′),<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 right=Side(border_style=’thin’, color=’FF000000′),<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 top=Side(border_style=’thin’, color=’FF000000′),<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 bottom=Side(border_style=’thin’, color=’FF000000′))<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 workBook = openpyxl.load_workbook(os.path.join(fileDir, newestFileName))<\/div>\n
<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet = workBook.get_sheet_by_name(u”Statistics”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 row = sheet.max_row + 1<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 # date<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘A’+str(row)).value = datetime.date.today()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘A’+str(row)).number_format = “yyyy\/mm\/dd”<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘A’+str(row)).style = Style(border=border, number_format=’yyyy\/mm\/dd’)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 # user<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘B’+str(row)).value = self.usersTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘B’+str(row)).style = Style(border=border)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘C’+str(row)).value = self.usersActivityTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘C’+str(row)).style = Style(border=border)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘D’+str(row)).value = self.usersZombieTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘D’+str(row)).style = Style(border=border)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘E’+str(row)).value = self.postsMaxOfSingleUser<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘E’+str(row)).style = Style(border=border)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘F’+str(row)).value = self.threadsMaxOfSingleUser<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘F’+str(row)).style = Style(border=border)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 # post<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘G’+str(row)).value = self.postsTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘G’+str(row)).style = Style(border=border)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘H’+str(row)).value = self.threadsTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘H’+str(row)).style = Style(border=border)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘I’+str(row)).value = self.postsMaxOfSingleThread<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘I’+str(row)).style = Style(border=border)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘J’+str(row)).value = self.pvMaxOfSingleThread<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘J’+str(row)).style = Style(border=border)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 # forum<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘K’+str(row)).value = self.forumsTotal<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘K’+str(row)).style = Style(border=border)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘L’+str(row)).value = self.postsMaxOfSingleForum<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘L’+str(row)).style = Style(border=border)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘M’+str(row)).value = self.threadsMaxOfSingleForum<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.cell(‘M’+str(row)).style = Style(border=border)<\/div>\n
<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 # chart operation<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheetChart = workBook.get_sheet_by_name(u”Chart”)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chartTopPos = 5<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chartInterval = 5<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chartLeftPos = 10<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chartWidth = 1200<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 realValueStartRow = 3<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 labels = Reference(sheet, (realValueStartRow, column_index_from_string(‘A’)), (sheet.max_row, column_index_from_string(‘A’)))<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 # No.1 chart<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 values = Reference(sheet, (realValueStartRow, column_index_from_string(‘B’)), (sheet.max_row, column_index_from_string(‘B’)))<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 series1 = Series(values, title=u”\u603b\u7528\u6237\u6570”, labels=labels)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chartHeight = 300<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Execl_Insert_Chart(sheetChart, u”\u603b\u7528\u6237\u6570”, chartLeftPos, chartTopPos, chartWidth, chartHeight, series1)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 # No.2 chart<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 values = Reference(sheet, (realValueStartRow, column_index_from_string(‘C’)), (sheet.max_row, column_index_from_string(‘C’)))<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 series1 = Series(values, title=u”\u6d3b\u8dc3\u7528\u6237\u6570”, labels=labels)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chartTopPos += (chartHeight + chartInterval)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Execl_Insert_Chart(sheetChart, u”\u6d3b\u8dc3\u7528\u6237\u6570”, chartLeftPos, chartTopPos, chartWidth, chartHeight, series1)<\/div>\n
<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 values = Reference(sheet, (realValueStartRow, column_index_from_string(‘G’)), (sheet.max_row, column_index_from_string(‘G’)))<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 series1 = Series(values, title=u”\u603b\u8d34\u6570”, labels=labels)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chartTopPos += (chartHeight + chartInterval)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Execl_Insert_Chart(sheetChart, u”\u603b\u8d34\u6570”, chartLeftPos, chartTopPos, chartWidth, chartHeight, series1)<\/div>\n
<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 values = Reference(sheet, (realValueStartRow, column_index_from_string(‘H’)), (sheet.max_row, column_index_from_string(‘H’)))<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 series1 = Series(values, title=u”\u603b\u4e3b\u9898\u6570”, labels=labels)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chartTopPos += (chartHeight + chartInterval)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 self.Execl_Insert_Chart(sheetChart, u”\u603b\u4e3b\u9898\u6570”, chartLeftPos, chartTopPos, chartWidth, chartHeight, series1)<\/div>\n
<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 # save today file<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 workBook.save(fileName)<\/div>\n
<\/div>\n
\u00a0 \u00a0 def Execl_Insert_Chart(self, sheet, title, left, top, width, height, *series):<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chart = LineChart()<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chart.title = title<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 for serial in series:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 chart.append(serial)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chart.drawing.left = left<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chart.drawing.top = top<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chart.drawing.width = width<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chart.drawing.height = height<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 chart.margin_left = width – 100<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 sheet.add_chart(chart)<\/div>\n
<\/div>\n
if __name__ == ‘__main__’:<\/div>\n
\u00a0 \u00a0 bcdiscuz = BCDISCUZ_Statistics()<\/div>\n
\u00a0 \u00a0 if bcdiscuz.conn == None:<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 exit(1)<\/div>\n
\u00a0 \u00a0 bcdiscuz.Get_All_Statistics_Value()<\/div>\n
\u00a0 \u00a0 bcdiscuz.Print_All_Statistics_Value()<\/div>\n
\u00a0 \u00a0 bcdiscuz.Write_Excel_File()<\/div>\n
\u00a0 \u00a0 bcdiscuz = None<\/div>\n
\u00a0 \u00a0 exit(0)<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\u8981\u6c42\uff1a\u6bcf\u5468\u4ecediscuz\u7684mysql\u6570\u636e\u5e93\u8bfb\u53d6\u6570\u636e\uff0c\u751f\u6210\u6240\u9700\u8981\u7684xecel\u62a5\u8868\uff0c\u6a21\u677f\u683c\u5f0f\u5982\u4e0b\uff1a \u540c\u65f6\u751f\u6210\u7edf\u8ba1\u56fe …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[63],"tags":[364,291,366,365,323,108,367],"class_list":["post-426","post","type-post","status-publish","format-standard","hentry","category-63","tag-discuz","tag-excel","tag-mariadb","tag-mysql","tag-openpyxl","tag-python","tag-367"],"views":1694,"_links":{"self":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/426","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=426"}],"version-history":[{"count":3,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/426\/revisions"}],"predecessor-version":[{"id":431,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/426\/revisions\/431"}],"wp:attachment":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=426"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=426"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=426"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}