Bikarhêner:Balyozxane/skrîpt/py/listeyasablonan.py

"""
Gets all redirected templates and their target titles along with number of use and saves in a json file. Output example:
"""

{
    "Str sub long": {
        "rd_title": "Str sub old",
        "num": 56839
    },
    "Find sources": {
        "rd_title": "Çavkanî bigere",
        "num": 48889
    },
    "If empty": {
        "rd_title": "Ifempty",
        "num": 35558
    },
    "Coord": {
        "rd_title": "Koord",
        "num": 33599
    },
    "WikidataCoord": {
        "rd_title": "WikidataKoord",
        "num": 32943
    },
    "Commonscat-b": {
        "rd_title": "Commonscat-biçûk",
        "num": 32566
    },
}

import pymysql
import os
import json

# Database connection details
# Note: If you are using Toolforge, you may ignore the database username and password
db_hostname_format = "kuwiki.analytics.db.svc.wikimedia.cloud"  # Hostname of the database server
db_port = 3306  # Port number for the database server
# db_username = ""  # Add your actual database username credential (if not using Toolforge)
# db_password = ""  # Add your actual database password credential (if not using Toolforge)
db_name_format = "kuwiki_p"  # Name of the target database
db_connect_file = "~/replica.my.cnf"  # path to the "my.cnf" file

# Create a connection to the database
connection = pymysql.connect(
    host=db_hostname_format,
    port=db_port,
    # user=db_username,
    # password=db_password,
    database=db_name_format,
    read_default_file=db_connect_file,  # "my.cnf" file contains user and password and read these parameters from under the [client] section.
    charset='utf8'
)

# Create a cursor
cursor = connection.cursor()

# Use the kuwiki_p database
cursor.execute("USE kuwiki_p;")

# query
query = """
select page_title, rd_title, count(tl_from) as num
from page
join linktarget on lt_title = page_title and lt_namespace = 10
join templatelinks on tl_target_id = lt_id
join redirect on rd_from = page_id
where page_namespace = 10
and page_is_redirect = 1
group by page_title
ORDER BY num DESC
"""

# Execute the query
cursor.execute(query)

# Fetch the results
results = cursor.fetchall()

# Close the cursor and the database connection
cursor.close()
connection.close()

# Construct a dictionary to hold the data
redirected_template_mappings = {}

if results:
    for result in results:
        ku_page_title = result[0].decode('utf-8').replace("_", " ")  # Assuming there's only one column in the result
        rd_title = result[1].decode('utf-8').replace("_", " ")
        num = result[2]
        redirected_template_mappings[ku_page_title] = {"rd_title": rd_title, "num": num}

    # Write the dictionary to a JSON file
    output_file_path = os.path.join(os.getenv('HOMEPATH', '/data/project/balyozbot/'), 'redirected_template_mappings.json')
    with open(output_file_path, 'w', encoding='utf-8') as output_file:
        json.dump(redirected_template_mappings, output_file, ensure_ascii=False, indent=4)
    print(f"Results written to {output_file_path}.")
else:
    print("No results found from the query.")