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"
)
cur = conn.cursor()
cur.execute('use zhwiki_p')
0
# get all files
cur.execute("""
    SELECT page_title
    FROM page
    WHERE page_namespace = 6
""")
res = cur.fetchall()
allfiles = [v[0].decode() for v in res]
print(allfiles[:10])
print(len(allfiles))
['"A-TEEN_.jpg".jpg', '"A_Day_in_the_Life"_SAMPLE.ogg', '"Butch"_van_Breda_Kolff.jpg', '"Justice,_My_Foot!"_DVD_cover.jpg', '"Lucy_in_the_Sky_..."_SAMPLE.ogg', '"Sgt._Pepper"_Inner_Gatefold.jpg', '"Sgt._Pepper\'s..."_SAMPLE.ogg', '"When_I\'m_Sixty-Four"_SAMPLE.ogg', '&_ayu.png', "'Batman_Assault_on_Arkham'_cover.jpeg.jpg"]
53694
 
text = """* 本頁列出被無限期半保護的條目,參見[https://paws-public.wmflabs.org/paws-public/User:Xiplus/inf%20protected%20ariticle.ipynb 程式碼]
* 產生時間:<onlyinclude>~~~~~</onlyinclude>

{| class="wikitable sortable"
!頁面!!保護日誌"""
for row in res:
    pid = str(row[0])
    ns = str(row[1])
    title = row[2].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 += "\n|}"
page = pywikibot.Page(site, "Wikipedia:資料庫報告/被無限期半保護的條目")
page.text = text
# page.save(summary="產生列表", minor=False)
Sleeping for 17.8 seconds, 2019-10-27 01:19:55
Page [[zh:Wikipedia:資料庫報告/被無限期半保護的條目]] saved