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_from IN (select page_id from page where page_namespace = 118) and cl_to not like '%AfC%' and cl_to not like '%raft%' and cl_to not like '%Pages%' and cl_to not like '%pages%' and cl_to not like '%edirect%' and cl_to not like '%CS1%' and cl_to not like '%deletion%' and cl_to not like '%rticles%' and cl_to not like '%emplate%' and cl_to not like '%with%' and cl_to not like '%tracking%' and cl_to not like '%nfobox%' 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 !! Drafts\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 = "Categories that contain pages in the (main) namespace and the draft namespaces; data as of <onlyinclude>~~~~~</onlyinclude>. Updated by ~~~.\n\n" + table

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