Open main menu

Wikidata:Database reports/Deleted Wikidata entities that are still linked/Configuration

< Wikidata:Database reports‎ | Deleted Wikidata entities that are still linked
#!/usr/bin/python
# -*- coding: UTF-8 -*-
#licensed under CC-Zero: https://creativecommons.org/publicdomain/zero/1.0

import MySQLdb
import pywikibot
import time

site = pywikibot.Site('wikidata','wikidata')

header = 'Update: <onlyinclude>{0}</onlyinclude>\n{{| class="wikitable sortable plainlinks" style="width:100%%; margin:auto;"\n|-\n! Entity !! count\n'
footer = '|}\n\n[[Category:Database reports|Deleted Wikidata entities that are still linked]]'
table_row = '|-\n| {{{{{0}|{1}}}}} || [{{{{fullurl:Special:WhatLinksHere/{1}}}}} {2}]\n'

query = 'SELECT pl_title, COUNT(*) FROM pagelinks LEFT JOIN page ON pl_title = page_title AND pl_namespace = page_namespace WHERE pl_from_namespace IN (0, 120, 146) AND pl_namespace IN (0, 120, 146) AND page_id IS NULL GROUP BY pl_title ORDER BY COUNT(*) DESC, pl_namespace, pl_title'

def makeReport(db,query):
	cursor = db.cursor()
	cursor.execute(query)
	text = ''
	for val, cnt in cursor:
		text += table_row.format(val[0],val,('{:,}'.format(cnt)))
	return text

def main():
	page = pywikibot.Page(site,'Wikidata:Database reports/Deleted Wikidata entities that are still linked')
	db = MySQLdb.connect(host="wikidatawiki.labsdb",db="wikidatawiki_p", read_default_file="~/replica.my.cnf")
	report = makeReport(db,query)
	text = header.format(time.strftime("%H:%M, %d %B %Y (%Z)")) + report + footer
	page.put(text.decode('UTF-8'),comment='Bot:Updating database report',minorEdit=False)

if __name__ == "__main__":
	main()