User:YiFeiBot/~/pywikipedia/zhvoystat.py

#!/usr/bin/python
# -*- coding: utf-8 -*-
#
# Published by zhuyifei1999 (https://wikitech.wikimedia.org/wiki/User:Zhuyifei1999)
# under the terms of Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
# https://creativecommons.org/licenses/by-sa/3.0/

import os
import re
import wikipedia as pywikibot
import datetime
import MySQLdb

class Robot(object):
    def __init__(self):
        self.site = pywikibot.getSite("incubator", "incubator")
    def run(self):
        pages = self.query('SELECT COUNT(DISTINCT page_id) FROM page WHERE page_title LIKE "Wy/zh/%";')
        articles = self.query('SELECT COUNT(DISTINCT page_id) FROM page WHERE page_namespace = 0 AND page_is_redirect = 0 AND page_title LIKE "Wy/zh/%" AND NOT page_title LIKE "Wy/zh/Project:%";')
        edits_l = self.query('SELECT COUNT(DISTINCT rev_id) FROM revision INNER JOIN page ON page_id = rev_page WHERE page_title LIKE "Wy/zh/%";')
        edits_d = self.query('SELECT COUNT(DISTINCT ar_rev_id) FROM archive WHERE ar_title LIKE "Wy/zh/%";')
        users = self.query('SELECT COUNT(DISTINCT rev_user) FROM revision INNER JOIN page ON page_id = rev_page WHERE rev_user != 0 AND page_title LIKE "Wy/zh/%";')
        admins = self.query('SELECT COUNT(DISTINCT rev_user) FROM revision INNER JOIN page ON page_id = rev_page INNER JOIN user_groups ON ug_user = rev_user AND (ug_group = "sysop" OR ug_group = "test-sysop") WHERE rev_user != 0 AND page_title LIKE "Wy/zh/%";')
        time = (datetime.datetime.utcnow() - datetime.timedelta(days=30)).strftime('%Y%m%d%H%M%S')
        active = self.query('SELECT COUNT(DISTINCT rc_user_text) FROM recentchanges WHERE rc_title LIKE "Wy/zh/%" AND rc_user != 0 AND rc_bot = 0 AND (rc_log_type != "newusers" OR rc_log_type IS NULL) AND rc_timestamp >= ' + time + ';')
        ttext = pywikibot.Page(self.site, "Template:Wy/zh/SiteStats/t").get()
        otext = pywikibot.Page(self.site, "Template:Wy/zh/SiteStats").get()
        text = ttext % {'pages': pages, 'articles': articles, 'edits': edits_l + edits_d, 'users': users, 'admins': admins, 'active': active}
        if text != otext:
            text = ttext % {'pages': pages, 'articles': articles, 'edits': edits_l + edits_d + 1, 'users': users, 'admins': admins, 'active': active}
            pywikibot.Page(self.site, "Template:Wy/zh/SiteStats").put(text, "Robot: Update stats")
    def query(self, cmd):
        connection = MySQLdb.connect(
            host = "incubatorwiki.labsdb",
            db = "incubatorwiki_p",
            read_default_file = "~/replica.my.cnf"
        )
        cursor = connection.cursor()
        cursor.execute(cmd)
        result = cursor.fetchall()[0][0]
        return result

def main():
    pywikibot.handleArgs()
    bot = Robot()
    bot.run()

if __name__ == "__main__":
    try:
        main()
    finally:
        pywikibot.stopme()