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)
with conn.cursor() as cur:
    cur.execute('use zhwiki_p')
    cur.execute("""
        SELECT pr_page, page_namespace, page_title, pr_expiry
        FROM page_restrictions
        LEFT JOIN page ON pr_page = page_id
        WHERE pr_type = "edit"
            AND pr_level = "autoconfirmed"
            AND pr_expiry != "infinity"
            AND pr_expiry >= {}
            AND page_namespace IN (0, 4)
        ORDER BY pr_expiry DESC, page_title ASC

    """.format(d.strftime('%Y%m%d000000')))
    res = cur.fetchall()
text = """* [https://paws-public.wmflabs.org/paws-public/User:Xiplus/long%20time%20protected%20ariticle.ipynb 來源]
*長期 = 終止期限超過{}

{{| class="wikitable sortable"
!頁面!!保護日誌!!期限""".format(d.strftime('%-m月%-d日'))
for row in res:
    pid = str(row[0])
    ns = str(row[1])
    title = row[2].decode()
    expiry = str(row[3].decode())
    text += "\n|-\n"
    text += "|[[Special:Redirect/page/"+pid+"|{{subst:#ifeq:"+ns+"|0||{{subst:ns:"+ns+"}}:}}"+title+"]]"
    text += "||[{{fullurl:Special:日志/protect|page={{subst:#ifeq:"+ns+"|0||{{subst:ns:"+ns+"}}:}}"+title+"}} 保護日誌]"
    text += "||" + expiry
text += "\n|}"
page = pywikibot.Page(site, "User:Xiplus/被長期半保護的條目")
page.text = text
page.save(summary="更新", minor=False)
Sleeping for 19.1 seconds, 2019-09-10 02:48:02
Page [[zh:User:Xiplus/被長期半保護的條目]] saved