Bikarhêner:Balyozxane/skrîpt/py/listeyabikarhêneran.py

import pywikibot
import pymysql
import datetime

bot_name = "User:Balyozxane/skrîpt/py/listeyabikarhêneran.py"
# Database connection details
db_hostname_format = "kuwiki.analytics.db.svc.wikimedia.cloud"
db_port = 3306
db_name_format = "kuwiki_p"
db_connect_file = "~/replica.my.cnf"

# Create a connection to the database
connection = pymysql.connect(
    host=db_hostname_format,
    port=db_port,
    database=db_name_format,
    read_default_file=db_connect_file,
    charset='utf8'
)

# Create a cursor
cursor = connection.cursor()

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

# First Query: Get top non-bot users
query_1 = """
SELECT 
    ROW_NUMBER() OVER (ORDER BY user.user_editcount DESC) AS row_number,
    user.user_name,
    user.user_editcount 
FROM 
    user
LEFT JOIN 
    user_groups ugs ON ugs.ug_user = user.user_id AND ugs.ug_group = 'bot'
WHERE 
    ugs.ug_user IS NULL
    AND user.user_name NOT IN (
        SELECT REPLACE(page_title, "_", " ") AS user_name 
        FROM categorylinks
        INNER JOIN page ON categorylinks.cl_from = page.page_id
        WHERE page_namespace = 2 
        AND cl_to = "Botên_Wîkîpediyayê"
    )
    AND user.user_name NOT LIKE '%bot%'
    AND user.user_name NOT LIKE '%Bot%'    
    AND user.user_name NOT LIKE '%CommonsDelinker%'    
ORDER BY user_editcount DESC
LIMIT 100;
"""

# Execute the first query
cursor.execute(query_1)

# Fetch the results of the first query
results_1 = cursor.fetchall()

# Second Query: Get bot users with edit count greater than 1000
query_2 = """
SELECT 
    ROW_NUMBER() OVER (ORDER BY user.user_editcount DESC) AS row_number,
    user.user_name,
    user.user_editcount 
FROM 
    user
INNER JOIN 
    user_groups ugs ON ugs.ug_user = user.user_id
WHERE 
    ug_group = 'bot' AND user_editcount > 1000
ORDER BY 
    user_editcount DESC;
"""

# Execute the second query
cursor.execute(query_2)

# Fetch the results of the second query
results_2 = cursor.fetchall()

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

# Constructing the Wikitable markup for the first query
wikitable_1 = """
{{/ser}}

<center>
{|class="wikitable sortable"
! #
! Bikarhêner 
! Hejmara guhartinan
"""
for row in results_1:
    row_number, user_name, user_editcount = row
    user_name = user_name.decode('utf-8')
    user_name = user_name.replace('_', ' ')
    user_name_temp = f"{{{{b|{user_name}}}}}"
    wikitable_1 += f"\n|-\n| {row_number}\n| {user_name_temp}\n| {user_editcount}"

# Adding table closing tags for the first query
wikitable_1 += "\n|}"

# Constructing the Wikitable markup for the second query
wikitable_2 = """
{{/ser}}

<center>
{|class="wikitable sortable"
! #
! Bikarhêner 
! Hejmara guhartinan
"""
for row in results_2:
    row_number, user_name, user_editcount = row
    user_name = user_name.decode('utf-8')
    user_name = user_name.replace('_', ' ')
    user_name_temp = f"{{{{b|{user_name}}}}}"
    wikitable_2 += f"\n|-\n| {row_number}\n| {user_name_temp}\n| {user_editcount}"

# Adding table closing tags for the second query
wikitable_2 += "\n|}"

# Connect to the Kurdish Wikipedia
site = pywikibot.Site("ku", "wikipedia")

# Get the pages for both queries
page_1 = pywikibot.Page(site, "Wîkîpediya:Lîsteya bikarhêneran li gorî hejmara guhartinan")
page_2 = pywikibot.Page(site, "Wîkîpediya:Lîsteya botan li gorî hejmara guhartinan")

# Edit the pages
page_1.text = wikitable_1
page_2.text = wikitable_2

# Save the edits for both pages
page_1.save(summary=f"[[{bot_name}|Bot]]: Lîsteya guhartinên bikarhêneran hat rojanekirin")
print("First page updated at:", datetime.datetime.now())
page_2.save(summary=f"[[{bot_name}|Bot]]: Lîsteya guhartinên botan hat rojanekirin")

print("Second page updated at:", datetime.datetime.now())