import pywikibot
import re
import os
import pymysql
LANG = 'zh'
FAMILY = 'wiktionary'
DBNAME = 'zhwiktionary'
# FAMILY = 'wikivoyage'
# DBNAME = 'zhwikivoyage'
# FAMILY = 'wikinews'
# DBNAME = 'zhwikinews'
site = pywikibot.Site(LANG, FAMILY)
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 {}_p'.format(DBNAME))
    cur.execute("""
        SELECT page_title
        FROM page
        WHERE page_namespace = 8
        ORDER BY page_title ASC
    """)
    res = cur.fetchall()
result = {}
langs = ['', 'zh', 'zh-hans', 'zh-cn', 'zh-my', 'zh-sg', 'zh-hant', 'zh-hk', 'zh-mo', 'zh-tw']
for row in res:
    title = row[0].decode()
    titleprefix = title.split('/')[0]
    titlesuffix = title.split('/')[-1] if len(title.split('/'))>1 else ''
    if titleprefix not in result:
        result[titleprefix] = {}
        for lang in langs:
            result[titleprefix][lang] = 0
        result[titleprefix]['talk'] = 0
    if titlesuffix in langs:
        result[titleprefix][titlesuffix] = 1
with conn.cursor() as cur:
    cur.execute('use {}_p'.format(DBNAME))
    cur.execute("""
        SELECT page_title
        FROM page
        WHERE page_namespace = 9
        ORDER BY page_title ASC
    """)
    res = cur.fetchall()
for row in res:
    title = row[0].decode()
    titleprefix = title.split('/')[0]
    if titleprefix in result:
        result[titleprefix]['talk'] = 1
text = '''{| id="interface-message-table" class="wikitable"
|-
! title
'''
for lang in langs:
    if lang == '':
        continue
    text += '! {}\n'.format(lang)
for title in result:
    text += '|-\n'
    text += '| [[MediaWiki:{0}|{0} {1}]]'.format(title, result[title][''])
    if result[title]['talk']:
        text += ' ([[MediaWiki talk:{0}|talk]])'.format(title)
    text += '\n'
    for lang in langs:
        if lang == '':
            continue
        text += '| [[MediaWiki:{}/{}|{}]]\n'.format(title, lang, result[title][lang])
text += '|}'
page = pywikibot.Page(site, 'User:Xiplus/介面訊息列表')
page.text = text
page.save(summary='列出介面訊息')
Page [[wiktionary:zh:User:Xiplus/介面訊息列表]] saved