3cb-data

commit d8d299dd12cad4a29d7e40317396e2a8197f1066
parent b0ddf3f6269a94a45bb196cfe974d1b0cbb42e17
Author: alex wennerberg <alex@alexwennerberg.com>
Date:   Sun, 29 Oct 2023 11:39:02 -0400

WIP movement to openpyxl

Diffstat:
M
README
 | 
3
---
M
getdata.py
 | 
63
++++++++++++++++++++++++++++++++-------------------------------

2 files changed, 32 insertions(+), 34 deletions(-)
diff --git a/README b/README
@@ -1,6 +1,3 @@
-3 card blind data analysis
-==========================
-
 Utilities for pulling data from 3cb google sheets
 
 Uses sqlite db
diff --git a/getdata.py b/getdata.py
@@ -3,14 +3,14 @@
 # depends on requests and stdlib
 # writes to 3cmdata.csv
 
-import requests, os, csv, re, io, sqlite3, titlecase, string
+import requests, os, csv, re, io, sqlite3, titlecase, string, openpyxl
 from Levenshtein import distance
 
 con = sqlite3.connect("3cb.db")
 allcards = set(open('allcards.txt','r').read().splitlines())
 
-def file_to_csv_reader(fileid, final):
-    params = {"mimeType": "text/csv"}
+def file_to_wb(fileid):
+    params = {"mimeType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}
     headers = {"x-goog-api-key": os.environ.get("GOOG_KEY")}
     out = []
     res = requests.get(f"https://www.googleapis.com/drive/v3/files/{fileid}/export", 
@@ -19,16 +19,14 @@ def file_to_csv_reader(fileid, final):
     if res.status_code != 200:
         print(res.text)
         os.exit(1)
-    f = io.StringIO(res.text)
-    reader = csv.reader(f, delimiter=',')
-    return reader
+    return openpyxl.load_workbook(io.BytesIO(res.content), data_only=True)
 
 # scrape site to list rounds
 def get_round_fileids():
     allrounds = requests.get("https://sites.google.com/view/3cb-metashape/pairings-results/past-results")
     sheetre = re.compile("spreadsheets/d/(.*?)/")
     curr_round = requests.get("https://sites.google.com/view/3cb-metashape/pairings-results")
-    ids = sheetre.findall(allrounds.text + curr_round.text)
+    ids = sheetre.findall(curr_round.text)
     return ids
 
 # fixing some data issues
@@ -104,35 +102,38 @@ create table if not exists match (
     score integer
 );"""]
 
-def save_group(cur, rreader, n):
+def save_sheet(cur, sheet, n):
+    rows = [[cell.value for cell in row if cell.value != None] for row in sheet.rows]
     group = ""
-    if n == 6: # One group, TODO
-        raise StopIteration
-
-    while len(group) != 1:
-        head = next(rreader)
-        group = head[0].partition(" ")[2]
-    players = head[2:-1]
-    for _ in players:
-        row = next(rreader)
-        player = row[0]
-        cards = row[1].split("\n")
-        if len(cards) != 3:
+    i = 0
+    while i < len(rows):
+        row = rows[i]
+        i += 1
+        if len(row) == 0:
             continue
-        for card in cards:
-            cur.execute("insert into card values (?,?,?);", (n, player, clean_card(card)))
-        for o, s in enumerate(row[2:-1]):
-            cur.execute("insert into match values (?, ?, ?, ?, ?);", 
-                        (n, group, player,players[o],s));
+        group = row[0].partition(" ")[2]
+        players = row[2:-1]
+        print("analysinzg group", players)
+        for _ in range(len(players)-1):
+            row = rows[i]
+            i += 1
+            print(row)
+            player = row[0]
+            cards = row[1].split("\n")
+            for card in cards:
+                cur.execute("insert into card values (?,?,?);", (n, player, clean_card(card)))
+            for o, s in enumerate(row[2:-1]):
+                cur.execute("insert into match values (?, ?, ?, ?, ?);", 
+                            (n, group, player,players[o],s));
 
 def save_round(n, fileid):
-    rreader = file_to_csv_reader(fileid, False)
+    wb = file_to_wb(fileid)
+    groups = wb["Groups"]
     cur = con.cursor()
-    while 1: # wonky
-        try:
-            save_group(cur, rreader, n)
-        except StopIteration:
-            break
+    save_sheet(cur, groups, n)
+    if len(wb) > 1:
+        final = wb["Final Group"]
+        save_sheet(cur, final, n)
     cur.execute("insert into round values (?, ?);", (n, fileid));
     con.commit()