Wikipedia:Database reports/Templates transcluded on the most pages/Configuration

mosttransclusions.py

edit
#! /usr/bin/env python
# Public domain; bjweeks, MZMcBride; 2008, 2016, 2018

import oursql
import wikitools

import settings

report_title = settings.rootpage + 'Templates with the most transclusions'

report_template = u'''\
Templates with the most transclusions, limited to the first 3,000 entries;
data as of <onlyinclude>~~~~~</onlyinclude>.

{| class="wikitable sortable" style="width:100%%; margin:auto;"
|- style="white-space:nowrap;"
! No.
! Template
! Uses
|-
%s
|}
'''

wiki = wikitools.Wiki(settings.apiurl)
wiki.login(settings.username, settings.password)

conn = oursql.connect(
    host=settings.host,
    db=settings.dbname,
    read_default_file='~/.my.cnf'
)
cursor = conn.cursor()
cursor.execute('''
/* mosttransclusions.py SLOW_OK */
SELECT
  tl_title,
  COUNT(*)
FROM templatelinks
WHERE tl_namespace = 10
GROUP BY tl_title
ORDER BY COUNT(*) DESC
LIMIT 3000;
''')

i = 1
output = []
for row in cursor.fetchall():
    tl_title = row[0].decode('utf-8').replace('_', ' ')
    tl_title = u'[[Template:%s|%s]]' % (tl_title, tl_title)
    uses = '{0:,}'.format(int(row[1]))
    table_row = u'''\
| %d
| %s
| %s
|-''' % (i, tl_title, uses)
    output.append(table_row)
    i += 1

report = wikitools.Page(wiki, report_title)
report_text = report_template % ('\n'.join(output))
report_text = report_text.encode('utf-8')
report.edit(report_text, summary=settings.editsumm, bot=1)

cursor.close()
conn.close()

crontab

edit
0 15 8,22 * * python ~/scripts/database-reports/mosttransclusions.py > /dev/null