import pywikibot
import pymysql
import os
import re

conn = pymysql.connect(
    host=os.environ['MYSQL_HOST'],
    user=os.environ['MYSQL_USERNAME'],
    password=os.environ['MYSQL_PASSWORD'],
    database='enwiki_p',
    charset='utf8',
)

with conn.cursor() as cur:
    cur.execute('use enwiki_p')
    cur.execute("SELECT CONCAT('[[:Category:', cl_to, ']]'), COUNT(*) FROM categorylinks WHERE cl_to IN ( SELECT page_title FROM page LEFT JOIN categorylinks ON page.page_id = categorylinks.cl_from WHERE cl_to = 'Container_categories') AND cl_type = 'page' GROUP BY cl_to ORDER BY COUNT(*) DESC")
    drafts = cur.fetchall()
#    print( drafts )

arred = []
for row in drafts:
    arred.append ( [ str( row[0] ).replace('_', ' '), row[1] ] )
#print( arred )
    
table = '{| class="wikitable sortable" \n ! Category !! Number of pages\n'
for row in range( 0, len(arred) ):
    table = table + '|-\n| ' + (str( arred[row][0] )[2:-1]) + ' || ' + str( arred[row][1]) + '\n'

table = table + '|}'
#print( table )

to_save = "Container categories that contain pages; data as of <onlyinclude>~~~~~</onlyinclude>. Updated by ~~~.\n\n" + table

site = pywikibot.Site('en', 'wikipedia')
page = pywikibot.Page(site, 'Wikipedia:Database reports/Polluted categories (3)')
page.text = to_save
#print( to_save )
page.save( summary = 'Task 30: Update database report', minor = False )

Sleeping for 7.5 seconds, 2019-07-25 18:56:00
Page [[en:Wikipedia:Database reports/Polluted categories (3)]] saved