import pywikibot
import re
import os
import pymysql
from datetime import datetime, timedelta
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"
)
d = datetime.today() - timedelta(days=30)
print(d.strftime('%Y%m%d000000'))
20191012000000
with conn.cursor() as cur:
    cur.execute('use zhwiki_p')
    cur.execute("""
        SELECT user_name, ug_group, `count`
        FROM
        (
          SELECT log_actor, COUNT(*) AS `count`
          FROM logging
          WHERE log_type = 'patrol'
            AND log_namespace = 0
            AND log_timestamp > {}
           GROUP BY log_actor
        ) logging

        LEFT JOIN actor
        ON log_actor = actor_id

        LEFT JOIN (
          SELECt *
          FROM user_groups
          WHERE ug_group IN ('sysop', 'patroller')
        ) user_groups
        ON actor_user = ug_user

        LEFT JOIN user
        ON actor_user = user_id
        ORDER BY `count` DESC
    """.format(d.strftime('%Y%m%d000000')))
    res = cur.fetchall()
text = """==條目巡查統計==
* [https://paws-public.wmflabs.org/paws-public/User:Xiplus/patrol%20stats.ipynb 來源]
* 統計範圍:條目,{} 至 ~~~~~

{{| class="wikitable sortable"
! 用戶名 !! 群組 !! 巡查數量""".format(d.strftime('%-Y年%-m月%-d日'))
for row in res:
    user_name = row[0].decode()
    group = row[1].decode() if row[1] is not None else ''
    cnt = row[2]
    text += "\n|-\n"
    text += ("| {0} |"
            +"| {1} |"
            +"| [https://zh.wikipedia.org/wiki/Special:Log?type=patrol&user={{{{urlencode:{0}}}}} {2}]").format(
                user_name, group, cnt)
text += "\n|}"
# print(text)
page = pywikibot.Page(site, "User:Xiplus/巡查統計")
page.text = text
page.save(summary="更新巡查統計", minor=False)
Sleeping for 46.3 seconds, 2019-11-11 06:27:23
Page [[zh:User:Xiplus/巡查統計]] saved