script to calculate total weekly CentOS instances

cmc.py

#!/usr/bin/python3

import datetime
import pathlib
import sqlite3
from urllib.request import urlretrieve

import click
import platformdirs


DB_URL = 'https://data-analysis.fedoraproject.org/csv-reports/countme/totals-centos-countme.db'
VERSIONS = ('9', '10')
ARCHES = ('aarch64', 'ppc64le', 's390x', 'x86_64')


@click.command()
@click.option('--version', '-v', type=click.Choice(VERSIONS))
@click.option('--arch', '-a', type=click.Choice(ARCHES))
@click.option('--weeks', '-w', default=1)
def cli(version, arch, weeks):

    # download database if the cached file is more than a day old
    db = pathlib.Path(platformdirs.user_cache_dir('totals-centos-countme.db'))
    if db.exists():
        age = datetime.datetime.now() - datetime.datetime.fromtimestamp(db.stat().st_mtime)
        if age > datetime.timedelta(days=1):
            refresh = True
        else:
            refresh = False
    else:
        refresh = True

    if refresh:
        print('downloading countme database')
        urlretrieve(DB_URL, db)
        print()

    # construct os_name condition for query
    os_name_condition = f"AND os_name = 'CentOS Stream'"

    # construct os_arch and repo_arch conditions for query
    if arch:
        os_arch_condition = f"AND os_arch = '{arch}'"
        repo_arch_condition = f"AND repo_arch = '{arch}'"
    else:
        # maybe switch to a condition for all arches, to remove possibly incorrect empty arches
        os_arch_condition = ''
        repo_arch_condition = ''

    # construct os_version and repo_tag conditions for query
    # AND (os_version = 9 OR os_version LIKE '9.%')
    repo_tag_template = "repo_tag = 'centos-baseos-{}-stream'"
    if version:
        os_version_condition = f"AND os_version = '{version}'"
        repo_tag_condition = f"AND {repo_tag_template.format(version)}"
    else:
        os_version_condition = ''
        repo_tag_condition = f"AND ({' OR '.join(map(repo_tag_template.format, VERSIONS))})"

    connection = sqlite3.connect(db)
    cursor = connection.cursor()

    # get all week numbers
    query = """
        SELECT DISTINCT weeknum
        FROM countme_totals
        ORDER BY weeknum
    """
    all_weeks = [row[0] for row in cursor.execute(query).fetchall()]

    for week in all_weeks[-weeks:]:
        query = f"""
            SELECT SUM(hits)
            FROM countme_totals
            WHERE
                weeknum = {week}
                {os_name_condition}
                {os_arch_condition}
                {repo_arch_condition}
                {os_version_condition}
                {repo_tag_condition}
        """

        # Week 0 started at timestamp 345600 (Mon 05 Jan 1970 00:00:00 - the first
        # Monday of POSIX time), and weeks are exactly 604800 (7×24×60×60) seconds
        # long.
        time = int(week) * 604800 + 345600

        total, = [row[0] for row in cursor.execute(query)]
        if not total:
            total = 0

        print(f'{datetime.date.fromtimestamp(time)}\t{total:,}')


if __name__ == '__main__':
    cli()