import pywikibot
import re
import os
import pymysql
site = pywikibot.Site('zh', 'wikipedia')
site.login()
host = os.environ['MYSQL_HOST']
user = os.environ['MYSQL_USERNAME']
password = os.environ['MYSQL_PASSWORD']
conn = pymysql.connect(
    host=host,
    user=user,
    password=password,
    charset="utf8"
)
with conn.cursor() as cur:
    cur.execute('use zhwiki_p')
    cur.execute("""
        SELECT rc_namespace, page_is_redirect, COUNT(*) AS `count`
        FROM recentchanges
        LEFT JOIN page
        ON rc_namespace = page_namespace AND rc_title = page_title
        WHERE rc_new = 1
          AND rc_patrolled = 0
          AND page_is_redirect IS NOT NULL
        GROUP BY rc_namespace, page_is_redirect
    """)
    res = cur.fetchall()
cnttable = {}
for row in res:
    ns = row[0]
    redirect = row[1]
    cnt = row[2]
    if ns not in cnttable:
        cnttable[ns] = {0: 0, 1:0}
    cnttable[ns][redirect] = cnt
print(cnttable)
{0: {0: 373, 1: 1730}, 1: {0: 1075, 1: 2}, 2: {0: 408, 1: 23}, 3: {0: 188, 1: 4}, 4: {0: 50, 1: 9}, 5: {0: 20, 1: 0}, 7: {0: 57, 1: 0}, 9: {0: 1, 1: 0}, 10: {0: 3139, 1: 60}, 11: {0: 42, 1: 1}, 12: {0: 0, 1: 2}, 13: {0: 1, 1: 0}, 14: {0: 422, 1: 1}, 15: {0: 422, 1: 0}, 100: {0: 48, 1: 3}, 101: {0: 1, 1: 0}, 118: {0: 119, 1: 3}, 119: {0: 16, 1: 1}, 828: {0: 65, 1: 0}, 829: {0: 1, 1: 0}}
text = """==未巡查頁面統計==
* [https://paws-public.wmflabs.org/paws-public/User:Xiplus/unpatrolled%20page%20count.ipynb 來源]

{| class="wikitable sortable"
! 命名空間 !! 數量 !! 含重定向數量"""
for ns in cnttable:
    text += "\n|-\n"
    text += ("| {0} ({{{{NS|2={0}}}}}) |"
            +"| [https://zh.wikipedia.org/wiki/Special:Newpages?namespace={0}&hidepatrolled=1&hideredirs=1 {1}] |"
            +"| [https://zh.wikipedia.org/wiki/Special:Newpages?namespace={0}&hidepatrolled=1&hideredirs=0 {2}]").format(
                ns, cnttable[ns][0], cnttable[ns][0]+cnttable[ns][1])
text += "\n|}"
# print(text)
page = pywikibot.Page(site, "User:Xiplus/未巡查頁面統計")
page.text = text
page.save(summary="更新未巡查頁面統計", minor=False)
Sleeping for 26.4 seconds, 2019-09-10 02:48:10
Page [[zh:User:Xiplus/沙盒6]] saved