3cb-data

commit 938ce7623f93c739336c54e36a320432e2cc88e4
parent 443e32850ac2a51a0e8f22558970df011a739eba
Author: alex wennerberg <alex@alexwennerberg.com>
Date:   Sat, 29 Jun 2024 11:18:55 -0400

add card view

Diffstat:
M
getdata.py
 | 
11
+++++++++--
M
sql/export.sql
 | 
7
+------

2 files changed, 10 insertions(+), 8 deletions(-)
diff --git a/getdata.py b/getdata.py
@@ -47,8 +47,9 @@ def file_to_wb(fileid):
     return openpyxl.load_workbook(io.BytesIO(res.content), data_only=True)
 
 def update_bans():
+    curr_round = requests.get("https://sites.google.com/view/3cb-metashape/pairings-results")
     sheet = file_to_wb("1NZuROOCctbq4p4-CAHE-jOC0675QQjuqVnUqdXFCVD8")["banlist"]
-    cards = [row[1].value for row in [*sheet.rows][3:92]]
+    cards = [row[1].value for row in [*sheet.rows][3:108]]
     cur = con.cursor()
     cur.execute("drop table if exists ban")
     cur.execute("create table ban (name text)")
@@ -78,7 +79,12 @@ create table if not exists match (
     player text, 
     opp_player text,
     score integer
-);"""]
+);""","""
+create view if not exists card as 
+    select round,player,card1 as card from deck
+    union select round,player,card2 as card from deck
+    union select round,player,card3 as card from deck;
+"""]
 
 def save_sheet(cur, sheet, n, final):
     sheetrows = list(sheet.rows)
@@ -152,6 +158,7 @@ def best_guess(card):
 # data cleaning
 def replacement(card):
     m = {
+            "Boseiju, Who Destroys Target Artifact, Enchantment, or Nonbasic Land (Who Endures)": "Boseiju, Who Endures",
             "Mox Pearl (The One Without Metalcraft)": "Mox Pearl",
             "Ink Mothy Nexy": "Inkmoth Nexus",
             "Phelia, Tail-Wagging Shepherd": "Phelia, Exuberant Shepherd",
diff --git a/sql/export.sql b/sql/export.sql
@@ -3,12 +3,7 @@
 /* .mode list */
 /* .separator "\t" */
 
-with card as (
-select round,player,card1 as card from deck
-union select round,player,card2 as card from deck
-union select round,player,card3 as card from deck),
-
-firsts as (
+with firsts as (
   select card,min(round) as first_round from card
   group by 1
 ),