User:YiFeiBot/~/pywikipedia/com_metacat_count.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 re
import MySQLdb
import wikipedia as pywikibot
from emstop import emstop #self-made emergency stop library

class CountMetacatRobot(object):
    def __init__(self):
        self.site = pywikibot.getSite()
        self.emstop = emstop(self.site, 6)
    def getNumber(self, name, include=None):
        cmd = u"""\
SELECT *
FROM page"""

        if include:
            cmd += u"\nINNER JOIN templatelinks"
            cmd += u"\nON tl_from = page_id"
            cmd += u"\nAND tl_namespace = 10"
            cmd += u'\nAND tl_title = "%s"' % include

        cmd += u'''
WHERE page_title LIKE "%s_by_%s"
AND page_namespace = 14''' % (u"%", name.replace(u" ", "_"))

        connection = MySQLdb.connect(
            host = "commonswiki.labsdb",
            db = "commonswiki_p",
            read_default_file = "~/replica.my.cnf"
        )
        cursor = connection.cursor()
        cursor.execute(cmd.encode('utf-8'))
        return len(cursor.fetchall())

    def run(self):
        self.page = pywikibot.Page(self.site, "Commons:List of meta category criteria")
        self.text_o = self.page.get()
        text_n = re.sub(r"\| \{\{Search for meta cats by\|(.+?)(\|.+)?\}\}",\
        (lambda reobj: u"| {{Search for meta cats by|%s|%d|tagged=%d|redirect=%d}}"\
        % (reobj.group(1), self.getNumber(reobj.group(1)),\
        self.getNumber(reobj.group(1), ur"MetaCat"),\
        self.getNumber(reobj.group(1), ur"Category_redirect"))), self.text_o)
        #self.page = pywikibot.Page(self.site, "User:YiFeiBot/sandbox")
        if self.emstop(): self.page.put(text_n, comment="Robot: Update report")

def main():
    bot =CountMetacatRobot()
    bot.run()

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