-
Notifications
You must be signed in to change notification settings - Fork 0
/
create_database.py
179 lines (145 loc) · 7.38 KB
/
create_database.py
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
from operator import itemgetter
# from service import json_data
from sqlalchemy.sql import select, and_
from sqlalchemy import MetaData, Table, Column, Text, Float, Integer, create_engine, VARCHAR, ForeignKey
from configparser import ConfigParser
def lookup_card_id(conn, card):
query = select([card_table.c.id]).where(and_(card_table.c.artist == card.get("artist"),
card_table.c.type == card.get("type"),
card_table.c.name == card.get("name"),
card_table.c.imageName == card.get("imageName"),
card_table.c.rarity == card.get("rarity"),
card_table.c.layout == card.get("layout"),
card_table.c.set_code == card.get("set_code")))
card_rec_id, *_ = conn.execute(query).fetchone()
return card_rec_id
def populate_set_table(eng):
conn = eng.connect()
from service import json_data
getter = itemgetter(*"code name border releaseDate type".split())
for key, val in json_data.items():
code, name, border, releaseDate, type = getter(val)
conn.execute(card_set_table.insert().values(code=code, name=name, border=border,
releaseDate=releaseDate, type=type))
def populate_card_table(eng, all_cards):
conn = eng.connect()
getter = itemgetter(*"artist type name imageName rarity layout set_code".split())
for card in all_cards:
artist, type, name, imageName, rarity, layout, set_code = getter(card)
query = card_table.insert().values(artist=artist, type=type, name=name,
imageName=imageName, rarity=rarity,
layout=layout, set_code=set_code)
conn.execute(query)
def populate_strength_table(eng):
conn = eng.connect()
for card in cards_by_attr("power"):
card_rec_id = lookup_card_id(conn, card)
power, toughness = itemgetter(*"power toughness".split())(card)
conn.execute(strength_table.insert().values(id=card_rec_id, power=power, toughness=toughness))
def populate_text_table(eng):
conn = eng.connect()
for card in cards_by_attr("text"):
card_id = lookup_card_id(conn, card)
conn.execute(text_table.insert().values(id=card_id, text=card.get("text")))
def populate_loyalty_table(eng):
conn = eng.connect()
for card in cards_by_attr("loyalty"):
card_id = lookup_card_id(conn, card)
conn.execute(loyalty_table.insert().values(id=card_id, loyalty=card.get("loyalty")))
def populate_mana_table(eng):
conn = eng.connect()
for card in cards_by_attr("cmc"):
manaCost, cmc = card.get("manaCost"), card.get("cmc")
card_id = lookup_card_id(conn, card)
conn.execute(mana_table.insert().values(id=card_id, manaCost=manaCost, cmc=cmc))
def populate_color_table(eng):
conn = eng.connect()
for card in cards_by_attr("colors"):
card_id = lookup_card_id(conn, card)
for color in card.get("colors"):
conn.execute(color_table.insert().values(color_name=color, card_id=card_id))
if __name__ == '__main__':
parser = ConfigParser()
parser.read("creds.ini")
user_name = parser.get("mysql", "user")
password = parser.get("mysql", "pw")
db_name = parser.get("mysql", "db")
url_template = "mysql+pymysql://{}:{}@localhost/{}?charset=utf8"
url = url_template.format(user_name, password, db_name)
meta = MetaData()
card_set_table = Table("card_set", meta,
Column("code", VARCHAR(10), primary_key=True),
Column("name", Text),
Column("border", Text),
Column("releaseDate", Text),
Column("type", Text))
card_table = Table("card", meta,
Column("id", Integer, autoincrement=True, primary_key=True),
Column("artist", Text),
Column("type", Text),
Column("name", Text),
Column("imageName", Text),
Column("rarity", Text),
Column("layout", Text),
Column("set_code", VARCHAR(10), ForeignKey("card_set.code")))
strength_table = Table("strength", meta,
Column("id", Integer, ForeignKey("card.id"), primary_key=True),
Column("power", Text),
Column("toughness", Text))
mana_table = Table("mana", meta,
Column("id", Integer, ForeignKey("card.id"), primary_key=True),
Column("manaCost", Text),
Column("cmc", Float))
color_table = Table("color", meta,
Column("id", Integer, autoincrement=True, primary_key=True),
Column("color_name", Text),
Column("card_id", Integer, ForeignKey("card.id")))
text_table = Table("text", meta,
Column("id", Integer, ForeignKey("card.id"), primary_key=True),
Column("text", Text))
loyalty_table = Table("loyalty", meta,
Column("id", Integer, ForeignKey("card.id"), primary_key=True),
Column("loyalty", Integer))
def generate_abridged_set():
from service import json_data
getter = itemgetter(*"artist type name imageName rarity layout".split())
for set_code, v in json_data.items():
for card in v.get("cards"):
artist, type, name, imageName, rarity, layout = getter(card)
new_card = {"artist": artist, "type": type, "name": name,
"imageName": imageName, "rarity": rarity, "layout": layout,
"set_code": set_code}
yield new_card
def generate_abridged_index_set(eng):
new_card_set = []
db_recs = eng.connect().execute(select([card_table])).fetchall()
for id, artist, type, name, imageName, rarity, layout, set_code in db_recs:
new_card = {"id": id, "artist": artist, "type": type,
"name": name, "imageName": imageName, "rarity": rarity,
"layout": layout, "set_code": set_code}
new_card_set.append(new_card)
return new_card_set
def cards_by_attr(attr_name):
from service import json_data
for set_code, card_set in json_data.items():
for card in card_set.get("cards"):
if attr_name in card:
card["set_code"] = set_code
yield card
eng = create_engine(url)
meta.create_all(eng)
conn = eng.connect()
populate_set_table(eng)
print("finished populating set table")
populate_card_table(eng, generate_abridged_set())
print("finished populating card table")
populate_strength_table(eng)
print("finished populating strength table")
populate_color_table(eng)
print("finished populating color table")
populate_mana_table(eng)
print("finished populating mana table")
populate_loyalty_table(eng)
print("finished populating loyalty table")
populate_text_table(eng)
print("finished populating text table")