-
Notifications
You must be signed in to change notification settings - Fork 0
/
db_setup.py
183 lines (145 loc) · 5.01 KB
/
db_setup.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
180
181
182
183
import requests
import pprint
import re
from statistics import mean
from server import db
from server.models import Game, Company, Year, Genre, Platform, get_game_genre_table
from sqlalchemy.sql import func
genre_cache = []
# {id: 0}
company_cache = set()
header = {"token" : "Sl8Jkh1lYZKydQjStnUFa_sjrlO5bbUxWYwuaKyDk50", "offset": 0}
year_cache = []
def db_import():
global year_cache
global header
db.drop_all()
db.configure_mappers()
db.create_all()
game_cache = [g.game_id for g in Game.query.all()]
platform_cache = []
# GAME DATA PULLING
# data: id, name, release_date
url = "https://www.igdb.com/api/v1/games"
r = requests.get(url, params = header)
j = r.json()
# while len(j["games"]) > 0:
while header["offset"] < 100:
# pp = pprint.PrettyPrinter(indent = 4)
# pp.pprint(j)
games = j["games"]
# loop through games
for game in games:
game_id = game["id"]
if(game_id not in game_cache):
game_cache += [game_id]
name = game["name"]
release_year = int((re.split("-", game["release_date"]))[0])
#check year cache before adding a new year
if(release_year not in year_cache):
y = Year(release_year)
db.session.add(y)
year_cache += [release_year]
url_specific_game = "https://www.igdb.com/api/v1/games/" + str(game_id)
r = requests.get(url_specific_game, params = header)
#get specific game information
game_info = r.json()["game"]
#image
image_url = None
if("cover" in game_info and "url" in game_info["cover"]):
image_url = "https:" + game_info["cover"]["url"]
#rating
rating = 0.0
if("rating" in game_info):
rating = game_info["rating"]
g = Game(id=game_id, name=name, image_url=image_url, rating=rating, release_year=release_year)
#loop through platforms
for v in game_info["release_dates"]:
c = None
platform = v["platform_name"]
if platform not in platform_cache:
platform_cache += [platform]
c = Platform(platform)
db.session.add(c)
else:
c = Platform.query.filter_by(platform_name = platform).first()
g.associated_platforms.append(c)
if "genres" not in game_info:
continue
add_genres(game_info["genres"], g)
add_companies(game_info["companies"], g)
#add game
db.session.add(g)
db.session.commit()
r = requests.get(url, params = header)
j = r.json()
header["offset"] += 25
def update_year_entries():
years = Year.query.all()
for year_entry in years:
year_entry.num_games = Game.query.filter_by(release_year = year_entry.year_id).count()
# print("ASDSADASD")
# print(type(Game.query.filter_by(release_year=year_entry)))
# print("ASDASDADA")
games = Game.query.filter_by(release_year = year_entry.year_id).all()
ratings = [game.rating for game in games if game.rating is not None]
if(len(ratings) != 0):
# print(type(mean(ratings)))
year_entry.avg_rating = mean(ratings)
popular_genre_query = db.session.query(Genre, func.count(Genre.genre_id).label("count")).join(get_game_genre_table()).join(Game).filter(Game.release_year == year_entry.year_id).group_by(Genre).order_by("count DESC").all()
if(len(popular_genre_query) > 0):
year_entry.most_popular_genre = popular_genre_query[0][0].genre_name
db.session.commit()
def add_genres(genres, game):
global genre_cache
for genre in genres:
genre_name = genre["name"]
genre_to_add = None
if genre_name not in genre_cache:
genre_cache += [genre_name]
genre_to_add = Genre(genre_name)
db.session.add(genre_to_add)
else:
genre_to_add = Genre.query.filter_by(genre_name = genre_name).first()
game.associated_genres.append(genre_to_add)
def add_companies(companies, game):
global company_cache
global year_cache
for company in companies:
c={}
company_to_update = None
company_id = company["id"]
if company_id in company_cache:
company_to_update = Company.query.filter_by(company_id = company_id).first()
if company["developer"]:
company_to_update.num_developed += 1
if company["publisher"]:
company_to_update.num_published += 1
db.session.commit()
else:
company_cache.add(company_id)
c["company_id"] = company_id
c["name"] = company["name"]
c["num_developed"] = 1 if company["developer"] else 0
c["num_published"] = 1 if company["publisher"] else 0
url_specific_company = "https://www.igdb.com/api/v1/companies/" + str(company_id)
r = requests.get(url_specific_company, params = header)
company_info = r.json()["company"]
if "founded_year" in company_info:
year = company_info["founded_year"]
c["year_founded"] = year
if(year not in year_cache):
y = Year(year)
db.session.add(y)
year_cache+= [year]
if "average_rating" in company_info:
c["avg_rating"] = company_info["average_rating"]
else:
c["avg_rating"] = 0.0
if "company_logo" in company_info:
c["image_url"] = "https:" + company_info["company_logo"]["url"]
company_to_update = Company(**c)
db.session.add(company_to_update)
game.associated_companies.append(company_to_update)
db_import()
update_year_entries()