CX-scripts/update_average.py.231024 at main · adamuk01/CX-scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
#!/usr/bin/python3

# This looks at each riders overall position and updates the average based on number of rides
# It rounds the average up

import sqlite3
import argparse
import math

parser = argparse.ArgumentParser(description="Update race average in DB file.")
parser.add_argument("input_file", help="Path to the input DB file")
args = parser.parse_args()

db_file = args.input_file

print("Updating database",db_file,"with new average values")

import sqlite3

def calculate_average(numbers):
    # Remove None, 0, and non-numeric types
    cleaned_numbers = list(filter(lambda x: isinstance(x, (int, float)) and x != 0 and x is not None, numbers))

    if cleaned_numbers:
#        result = math.ceil(sum(cleaned_numbers) / len(cleaned_numbers))  # Calculate and round up the average
        result = round(sum(cleaned_numbers) / len(cleaned_numbers)) # Round up or down based on point
        #print ("Result:", result)
    else:
        result = 0  # Handle case where all values are None, 0, or non-numeric

    return result

def calculate_average_real(numbers):
    # Remove None, 0, and non-numeric types
    cleaned_numbers = list(filter(lambda x: isinstance(x, (int, float)) and x != 0 and x is not None, numbers))

    if cleaned_numbers:
        # Calculate the average and round it to 1 decimal place
        result = round(sum(cleaned_numbers) / len(cleaned_numbers), 1)
    else:
        result = 0  # Handle case where all values are None, 0, or non-numeric

    return result


def update_average_position(db_file):
    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()

    # Iterate through each record in the database
    query = f"SELECT {', '.join(fields)} FROM riders"
    cursor.execute(query)
    for row in cursor.fetchall():
#        print ("row",row)
        # Extract specific fields with numbers (some of which can be null)
        numeric_fields = row[1:]
#        print ("Numeric fields:",numeric_fields)

        # Calculate the average of the extracted numeric fields
        average = calculate_average(numeric_fields)

        if average is not None and average != 0:
            # Update another field in the same record with the calculated average
            cursor.execute("UPDATE riders SET average_position = ? WHERE id = ?", (average, row[0]))
            # print ("UPDATE riders SET average_position = ",average," WHERE id = ",row[0])

    # Commit the changes and close the database connection
    conn.commit()
    conn.close()

def update_average_points(db_file):
    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()

    # Iterate through each record in the database
    query = f"SELECT {', '.join(fields2)} FROM riders"
    cursor.execute(query)
    for row in cursor.fetchall():
#        print ("row",row)
        # Extract specific fields with numbers (some of which can be null)
        numeric_fields = row[1:]
#        print ("Numeric fields:",numeric_fields)

        # Calculate the average of the extracted numeric fields
        average = calculate_average_real(numeric_fields)
#        print (average)

        if average is not None and average != 0:
            # Update another field in the same record with the calculated average
            cursor.execute("UPDATE riders SET average_points = ? WHERE id = ?", (average, row[0]))
#            print ("Average is:", average)
            # print ("UPDATE riders SET average_points = ",average," WHERE id = ",row[0])

    # Commit the changes and close the database connection
    conn.commit()
    conn.close()


# Example usage:
fields = ['id', 'r1_overall_position', 'r2_overall_position', 'r3_overall_position', 'r4_overall_position', 'r5_overall_position', 'r6_overall_position', 'r7_overall_position', 'r8_overall_position', 'r9_overall_position', 'r10_overall_position', 'r11_overall_position', 'r12_overall_position']


fields2 = ['id', 'r1_points', 'r2_points', 'r3_points', 'r4_points', 'r5_points', 'r6_points', 'r7_points', 'r8_points', 'r9_points', 'r10_points', 'r11_points', 'r12_points']

update_average_position(db_file)
update_average_points(db_file)