Wikidata:Database reports/Popular items without claims/Configuration
The most recent version of this script can be found at Github: popular_without_claims. The script is run on Toolforge in the deltabot
tool account; Toolforge users usually have read-access to all scripts in that tool account.
A potentially outdated version is stored onwiki at User:DeltaBot/source/popular_without_claims in order to be permanently available and conveniently accessible; it is being displayed on this page with code formatting. Mind that the onwiki source code might have been slightly altered in order to prevent onlyinclude
directives from being effective, and that the wikitext parser im some situations messes up the formatted output of the transcluding page content including the source code.
#!/usr/bin/python
# -*- coding: UTF-8 -*-
# licensed under CC-Zero: https://creativecommons.org/publicdomain/zero/1.0
from os.path import expanduser
from time import strftime
import mariadb
import pywikibot as pwb
HEADER = """Update: <only""" + """include>{update_timestamp}</onlyinclude>
=='"`UNIQ--h-0--QINU`"'Items with the most links but without statements==
{{| class="wikitable sortable plainlinks" style="width:100%; margin:auto;"
|-
! Item !! # links
"""
MIDDLE = """|}
==Items with the most sitelinks but without statements==
{| class="wikitable sortable plainlinks" style="width:100%; margin:auto;"
|-
! Item !! # sitelinks
"""
FOOTER = """|}
[[Category:Database reports|Popular items without claims]]"""
TABLE_ROW = """|-
| {{qid}} || {cnt}
"""
QUERY_1 = """SELECT
CONVERT(pl_title USING utf8) AS qid,
COUNT(*) AS cnt
FROM
pagelinks
WHERE
pl_from_namespace=0
AND pl_namespace=0
AND pl_title IN (
SELECT
page_title
FROM
page
JOIN page_props ON page_id=pp_page
WHERE
pp_propname='wb-claims'
AND pp_value=0
)
GROUP BY
pl_title
ORDER BY
cnt DESC
LIMIT
100"""
QUERY_2 = """SELECT
CONVERT(page_title USING utf8) AS qid,
CONVERT(ppsitelinks.pp_value USING utf8) AS cnt
FROM
page_props AS ppclaims
JOIN page ON ppclaims.pp_page=page_id AND page_namespace=0 AND page_is_redirect=0
JOIN page_props AS ppsitelinks ON page_id=ppsitelinks.pp_page AND ppsitelinks.pp_propname='wb-sitelinks'
WHERE
ppclaims.pp_propname='wb-claims'
AND ppclaims.pp_value=0
ORDER BY
CAST(ppsitelinks.pp_value AS int) DESC
LIMIT
100"""
def make_report(db, query:str) -> str:
cursor = db.cursor(dictionary=True)
cursor.execute(query)
text = ''
for row in cursor:
qid = row.get('qid')
cnt = row.get('cnt')
if qid is None or cnt is None:
continue
text += TABLE_ROW.format(qid=qid, cnt=cnt)
cursor.close()
return text
def main() -> None:
db = mariadb.connect(
host='wikidatawiki.analytics.db.svc.wikimedia.cloud',
database='wikidatawiki_p',
default_file=f'{expanduser("~")}/replica.my.cnf',
)
report1 = make_report(db, QUERY_1)
report2 = make_report(db, QUERY_2)
db.close()
text = HEADER.format(update_timestamp=strftime('%Y-%m-%d %H:%M (%Z)')) + report1 + MIDDLE + report2 + FOOTER
page = pwb.Page(pwb.Site('wikidata', 'wikidata'), 'Wikidata:Database reports/Popular items without claims')
page.text = text
page.save(summary='Bot:Updating database report', minor=False)
if __name__=='__main__':
main()