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
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
| # set GOOG_KEY env variable to a valid api key
# all rounds available at https://sites.google.com/view/3cb-metashape/pairings-results/past-results?authuser=0
# depends on requests and stdlib
# writes to 3cmdata.csv
# TODO upload the csv, print out the round msg
import requests, os, csv, re, io, sqlite3, titlecase, string, openpyxl, urllib
from Levenshtein import distance
con = sqlite3.connect("3cb.db")
allcards = set(open('allcards.txt','r').read().splitlines())
# cache
def fileids_in_db():
return [a[0] for a in con.cursor().execute("select fileid from round").fetchall()]
def bans_from_db():
return [a[0] for a in con.cursor().execute("select name from bans").fetchall()]
def main():
cache = fileids_in_db()
for n, file in enumerate(get_round_fileids()):
if file in cache:
continue
if file not in cache and file != "1LGHvTrQz2zhBjz1PhlW61ZYmwRWJyWkEHj-png7ZKdw": # misc file
print(f"analyzing round {n+1}")
save_round(n+1, file)
# 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)
return ids
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",
params=params,
headers=headers)
if res.status_code != 200:
print(res.text)
os.exit(1)
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:108]]
cur = con.cursor()
cur.execute("drop table if exists ban")
cur.execute("create table ban (name text)")
for card in cards:
if card:
cur.execute("insert into ban values (?)", (clean_card(card),))
con.commit()
# not really normalized well
schema = ["""
create table if not exists round (
id integer primary key,
fileid text not null
);
""",
"""
create table if not exists deck (
round integer not null,
player varchar not null,
card1 text,
card2 text,
card3 text
);""","""
create table if not exists match (
round integer,
group_name text,
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)
rows = [[cell.value for cell in row if cell.value != None] for row in sheetrows]
group = ""
i = 0
while i < len(rows):
row = rows[i]
i += 1
if len(row) < 3:
continue
group = row[0].partition(" ")[2]
if final:
group = "final"
try:
start = row.index("VS") + 1
end = row.index("Score")
if sheetrows[0][0].value == None: # missing text in upper left cell
start += 1
end += 1
except ValueError:
start = 2
end = len(row)
players = row[start:end]
for _ in range(len(players)):
row = rows[i]
i += 1
player = row[0]
if len(player) > 50 and player[0] == "B":
player = "Beefman" # messy unicode
if player == "IGNORE WAS MISTAKE":
continue
cards = row[1].split("\n")
if not final: #already done
cur.execute("insert into deck values (?,?,?,?,?);", (n, player, clean_card(cards[0]), clean_card(cards[1]), clean_card(cards[2])))
for o, s in enumerate(row[start:end]):
if s == "?" or s == "":
s = 0
cur.execute("insert into match values (?, ?, ?, ?, ?);",
(n, group, player,players[o],int(s)));
def save_round(n, fileid):
wb = file_to_wb(fileid)
try:
groups = wb["Groups"]
except KeyError:
groups = wb["Group"]
cur = con.cursor()
save_sheet(cur, groups, n, False)
if len(wb._sheets) > 1:
final = wb["Final Group"]
save_sheet(cur, final, n, True)
cur.execute("insert into round values (?, ?);", (n, fileid));
con.commit()
# fixing some data issues
def best_guess(card):
m = 6
outcard = ""
for c in allcards:
if c.startswith(card):
return c
d = distance(card, c, score_cutoff=5)
if d < m:
m = d
outcard = c
if outcard:
return outcard
return 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",
"Dreams of Oil and Steel": "Dreams of Steel and Oil",
"Lion's Eye Diamond Cheatyface": "Lion's Eye Diamond",
"Mayor of Avarbruck": "Mayor of Avabruck // Howlpack Alpha",
"Thallid Oh Yeah": "Thallid",
"Forest!!!!!!": "Forest",
"Forest (Tempest #348)": "Forest",
"Bayou - Not Legal": "Bayou",
"Bottomless Depths": "Bottomless Vault",
"Gargadon (Neither Greater Nor Lesser)": "Gargadon",
"Tabernacle at Penrall Vale": "The Tabernacle at Pendrell Value",
"Nesumi Shortfang": "Nezumi Shortfang // Stabwhisker the Odious",
"Filigree Sylex": "The Filigree Sylex",
"Gavel of the Righteous, for I Am Stubborn and Naively Hopeful": "Gavel of the Righteous",
"Annex Chan": "Chancellor of the Annex",
"Annex-Chan": "Chancellor of the Annex",
"Forge-Chan": "Chancellor of the Forge",
"Basic Plains": "Plains",
"Urborg Tomb Yawgy": "Urborg, Tomb of Yawgmoth",
"Urborg (The One That Makes All Lands Swamps)": "Urborg, Tomb of Yawgmoth",
"Chronomatonton (The 1 Cost 1/1 That Taps to Get Bigger)": "Chronomaton",
"That One Wurm That Makes the Three 5/5s When It Dies I Have Done Too Many Scryfall Searches Today Sorry": "TBD",
"Chancelor of the Tangle (Sic)": "Chancellor of the Tangle",
"Karaka (Its Listed as Unbanned but the Form Wont Let Me Submit It Idk Its My First Time Lol)": "Karakas",
"Restore Balanse (I Have to Misspell This Cuz the Regex Is Buggy Lmao)": "Restore Balance",
"Elspeth Suns Champion Wooooooo": "Elspeth, Sun's Champion",
"Plains (ONS 333)": "Plains",
"Swamp (XLN 270)": "Swamp",
"Ulamog the Infinite Gyre (Borderless) (Foil)": "Ulamog, the Infinite Gyre",
"Dark Ritual (BTD 21)": "Dark Ritual",
"Swamp (BRB 18)": "Swamp",
"Monty Python and the Holy Grail Black Knight (Oathsworn Knight)": "Oathsworn Knight",
"Red Sun's Zenith (Again)": "Red Sun's Zenith",
"Dwarven Hold (Again)": "Dwarven Hold",
"Speaker of the Heavens!?!?!?!": "Speaker of the Heavens",
"Dark Ritual (STA #89)": "Dark Ritual",
"Swamp (8ED #339)": "Swamp",
}
if card in m:
return m[card]
return card
def clean_card(card_name):
clean = titlecase.titlecase(card_name.strip())
clean = ''.join(filter(lambda x: x in string.printable, clean))
clean = clean.replace("’", "'")
clean = replacement(clean)
if clean not in allcards:
return best_guess(clean)
return clean
if __name__ == "__main__":
for table in schema:
con.execute(table)
update_bans()
main()
|
