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: 516, 1: 1558}, 1: {0: 16, 1: 0}, 2: {0: 104, 1: 46}, 3: {0: 36, 1: 34}, 4: {0: 3, 1: 29}, 6: {0: 1, 1: 0}, 8: {0: 3, 1: 0}, 10: {0: 689, 1: 47}, 11: {0: 2, 1: 0}, 12: {0: 19, 1: 26}, 14: {0: 660, 1: 0}, 15: {0: 1, 1: 0}, 100: {0: 1, 1: 0}, 101: {0: 2, 1: 0}, 118: {0: 121, 1: 0}, 119: {0: 5, 1: 2}, 828: {0: 9, 1: 0}}
text = """==未巡查頁面統計==
* [https://paws-public.wmflabs.org/paws-public/User:Xiplus/unpatrolled%20page%20count.ipynb 由Xiplus脚本支持]

{| 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|}"

page = pywikibot.Page(site, "Draft:沙盒")
page.text = text
page.save(summary="更新未巡查页面统计", minor=False)
{0: {0: 789, 1: 1368}, 1: {0: 211, 1: 7}, 2: {0: 420, 1: 31}, 3: {0: 441, 1: 9}, 4: {0: 0, 1: 14}, 7: {0: 53, 1: 0}, 10: {0: 1537, 1: 28}, 11: {0: 29, 1: 1}, 12: {0: 1, 1: 1}, 14: {0: 1495, 1: 0}, 15: {0: 29, 1: 0}, 100: {0: 4, 1: 0}, 101: {0: 2, 1: 0}, 118: {0: 88, 1: 2}, 119: {0: 7, 1: 0}, 828: {0: 6, 1: 0}, 829: {0: 1, 1: 0}}
Sleeping for 4.0 seconds, 2019-11-09 06:49:38
Page [[zh:Draft:沙盒]] saved