· 5 years ago · Sep 29, 2020, 04:42 AM
1import requests as req
2from dataclasses import dataclass
3import sqlite3
4import time
5#Connect to database
6conn = sqlite3.connect('Steam analyser.db')
7#Assign 'cur' to the db cursor
8cur = conn.cursor()
9KEY = "96862EE4E52EB78C094EA033B7B74330" #Define the key required for requesting from the steam api
10#Games incompatible with this program usually due to being removed from the steam store
11BrokenGames = ['291410', '323900', '407120', '407530', '305620', '208090', '623990']
12@dataclass
13#Define the GameInfo dataclass
14class GameInfo:
15 #The games steam AppID
16 Appid: int
17 Name: str
18 #Is it DLC, a Game or a Tool
19 Type: str
20 #The price not counting discounts
21 NormalPrice: int
22 #The price including current discounts
23 CurrentPrice: int
24
25
26#Some IDs for testing
27#76561198370607989 UAGamer
28#76561198085185592 Kubyx
29#76561198003049894 reallybad
30#76561198208874191 YangaTang
31
32#Function that obtains the Steam ID from an accounts url
33def GetSteamID(URL):
34 #Make the request for the users steam id with their vanity url
35 resp = req.get("http://api.steampowered.com/ISteamUser/ResolveVanityURL/v0001/?key=" + KEY + "&vanityurl=" + URL)
36 #Cut away excess text on the response
37 return resp.text[24:-15]
38
39#Function to obtain the Username and Steam ID from an accounts url
40def GetAccount(SteamID):
41 #Make the request for a summary of a players details with their steam id which is obtained from another function
42 resp = req.get("http://api.steampowered.com/ISteamUser/GetPlayerSummaries/v0002/?key=" + KEY + "&steamids=" + SteamID)
43 #split up the recieved text
44 PlayerSummary = list(resp.text.split(","))
45 #Extract the username from the list and get the steam id from the account url
46 AccInfo = [(PlayerSummary[3])[15:-1], int(SteamID)]
47 return AccInfo
48
49#Function to get all games on an account and their playtime
50def GetPlaytime(SteamID):
51 #Make a request for an accounts owned games
52 PlayReq = req.get("http://api.steampowered.com/IPlayerService/GetOwnedGames/v0001/?key=" + KEY + "&steamid=" + SteamID + "&format=json")
53 #an empty list for the output
54 GameInfo = []
55
56 #Split up the response into a list, each entry is a game and its details
57 OwnedList = list(PlayReq.text.split("},"))
58 #Remove the first value as it is not a game
59 OwnedList = OwnedList[1:]
60
61 #Process every game found in the request
62 for i in OwnedList:
63 #Furthur split up each games details for each specific detail
64 Det = list(i.split(","))
65 #Get the id from the list
66 ID = int(Det[0][9:])
67 #Get the playtime from the list
68 Playtime = int(Det[-4][19:])
69 #Check that the game is still on the store
70 if CheckGameExists(str(ID), 'AU') == 'Exists':
71 GameInfo.append([ID, SteamID, Playtime])
72
73 return GameInfo
74
75def WriteNewAccount(SteamID):
76 #Insert Username and steamid into database
77 if CheckDB('Account', SteamID) == False:
78 cur.execute("INSERT INTO Accounts VALUES (?,?)", GetAccount(SteamID))
79 print("Written account " + SteamID + " to database")
80 else:
81 print("Account already in DB!")
82
83def WriteOwnedGames(gamesList):
84 #Write each value to the SQL database
85 for i in gamesList:
86 cur.execute("INSERT INTO OwnedGames VALUES (?,?,?)",i)
87
88def ClearTable(TableName):
89 #Delete all entries from a specific table
90 cur.execute("DELETE FROM " + TableName)
91
92def GetGameDetails(AppID, cc):
93
94 #Make a request for a games prices
95 PriceReq = req.get("https://store.steampowered.com/api/appdetails?appids=" + AppID + "&cc=" + cc + '&filters=price_overview')
96 #Make a request for details
97 DetReq = req.get("https://store.steampowered.com/api/appdetails?appids=" + AppID + "&cc=" + cc + '&filters=basic')
98 #Check if too many requests have been made
99 #Check if request need to be resent
100 remake = TooManyRequests(DetReq.text)
101 if remake:
102 PriceReq = req.get("https://store.steampowered.com/api/appdetails?appids=" + AppID + "&cc=" + cc + '&filters=price_overview')
103 DetReq = req.get("https://store.steampowered.com/api/appdetails?appids=" + AppID + "&cc=" + cc + '&filters=basic')
104 #Split up the recieved text based on objects
105 DetObjects = list(DetReq.text.split("{"))
106 #split up the object for more specific details
107 SpecDetails = list(DetObjects[3].split(','))
108 name = SpecDetails[1][8:-1]
109 gameType = SpecDetails[0][8:-1]
110
111 #Check that the game actually has a price
112 if SpecDetails[4] == '"is_free":false':
113 #Split up the price response for each object
114 PriceObjects = list(PriceReq.text.split("{"))
115 #Check the request was successful by checking if the response is long enough
116 if len(PriceObjects) > 4:
117 #Furthur split up 'price overview' section of the response
118 SpecPrice = list(PriceObjects[4].split(","))
119 #Assign the values
120 NorPrice = SpecPrice[1][10:]
121 CurPrice = SpecPrice[2][8:]
122 else:
123 #Default price to 0 if response is unsuccesful
124 print("ERROR 404 PRICE NOT FOUND")
125 print(AppID, "Price defaulted to 0")
126 CurPrice = 0
127 NorPrice = 0
128 else:
129 #assign values for a free game
130 CurPrice = 0
131 NorPrice = 0
132
133 #Assign all values to a record
134 out = GameInfo(AppID, name, gameType, NorPrice, CurPrice)
135 return(out)
136
137
138def WriteGameDetails(AppID, cc):
139 Det = GetGameDetails(AppID, cc)
140 #Check if the game is already in db
141 if CheckDB('Game', AppID) == False:
142 #If not in db insert into db
143 cur.execute("INSERT INTO Games VALUES (?,?,?,?,?)",[Det.Appid,Det.Name,Det.Type,Det.NormalPrice,Det.CurrentPrice])
144 else:
145 #If in db update entry
146 cur.execute("UPDATE Games set GameName = ?, Type = ?, NormalPrice = ?, CurrentPrice = ? WHERE App_ID = ?", [Det.Name,Det.Type,Det.NormalPrice,Det.CurrentPrice,Det.Appid])
147
148def CheckGameExists(AppID, cc):
149 #Request the basic details of a game
150 DetReq = req.get("https://store.steampowered.com/api/appdetails?appids=" + AppID + "&cc=" + cc + '&filters=basic')
151 #Check if the request needs to be remade
152 remake = TooManyRequests(DetReq.text)
153 if remake:
154 DetReq = req.get("https://store.steampowered.com/api/appdetails?appids=" + AppID + "&cc=" + cc + '&filters=basic')
155 #Check if the request was succesful
156 if DetReq.text != '{"' + AppID + '":{"success":false}}' and AppID not in BrokenGames:
157 return('Exists')
158 else:
159 return("NonExists")
160
161#Write to db all games in an account. (this does not write into the owned games table)
162def WriteAllGames(gamesList, cc):
163 #Process each game in the list
164 for i in gamesList:
165 print("Writing " + str(i[0]))
166 WriteGameDetails(str(i[0]), cc)
167 print("Written: " + str(i[0]) + " successfully")
168
169
170def GetAccountGames(SteamID):
171 #Select the account name, id and owned games
172 cur.execute('SELECT Accounts.Username, Accounts.Account_ID, Games.Gamename FROM Accounts INNER JOIN OwnedGames ON Accounts.Account_ID = OwnedGames.Account_IDFK INNER JOIN Games ON OwnedGames.App_IDFK = Games.App_ID Where Accounts.Account_ID = ?', [SteamID])
173 #Fetch all entries
174 out = cur.fetchall()
175 return(out)
176
177def GetTotalPlaytime(SteamID):
178 #Select the sum of playtime for each game an account owns
179 cur.execute("SELECT Accounts.Username, sum(OwnedGames.TimePlayed) FROM OwnedGames, Accounts where OwnedGames.Account_IDFK = ? AND Accounts.Account_ID = ?", [SteamID, SteamID])
180 #Fetch the first entry
181 return(cur.fetchall()[0])
182
183def GetTotalCurrentValue(SteamID):
184 #Select the sum of current (includes discounts) value for each game an account owns
185 cur.execute("SELECT Accounts.Username, Accounts.Account_ID, sum(Games.CurrentPrice) FROM Accounts INNER JOIN OwnedGames ON Accounts.Account_ID = OwnedGames.Account_IDFK INNER JOIN Games ON OwnedGames.App_IDFK = Games.App_ID Where Accounts.Account_ID = ?", [SteamID])
186 #Fetch the first entry
187 return(cur.fetchall()[0])
188
189def GetTotalNormalValue(SteamID):
190 #Select the sum of the value for each game (not counting discounts) an account owns
191 cur.execute("SELECT Accounts.Username, Accounts.Account_ID, sum(Games.NormalPrice) FROM Accounts INNER JOIN OwnedGames ON Accounts.Account_ID = OwnedGames.Account_IDFK INNER JOIN Games ON OwnedGames.App_IDFK = Games.App_ID Where Accounts.Account_ID = ?", [SteamID])
192 #Fetch the first entry
193 return(cur.fetchall()[0])
194
195def DeleteGame(AppID):
196 #Delete all entries in 'Owned games' with the appid
197 cur.execute("DELETE FROM OwnedGames where App_IDFK = ?", [AppID])
198 #Delete all entries in Games' with the appid
199 cur.execute("DELETE FROM Games where App_ID = ?", [AppID])
200
201def DeleteAccount(SteamID):
202 #Delete all entries in 'Owned games' with the accountID
203 cur.execute("DELETE FROM OwnedGames where Account_IDFK = ?", [SteamID])
204 #Delete all entries in 'Accounts' with the AccountID
205 cur.execute("DELETE FROM Accounts where Account_ID = ?", [SteamID])
206
207#Check if too many requests have been made and if so ask if the user wants to wait the cooldown time
208def TooManyRequests(response):
209 if response == 'null':
210 print("Too many requests have been made to the steam api. Please wait 5 minutes.")
211 #Wait 5 minutes
212 for i in range(0,9):
213 print("Please wait")
214 #Countdown time left
215 print(str(300-i*(30)), "Seconds remaining")
216 #Wait 30 seconds
217 time.sleep(30)
218 print("Waiting done")
219 return(True)
220 else:
221 return(False)
222
223#Function for adding a new account to db
224def AddNewAccount():
225 loop = True
226 while loop == True:
227 choice1 = input("Do you have the accounts [V]anity URL or their [S]team ID? [0] to leave: ").lower()
228 if choice1 == 'v':
229 ID = input("Enter the vanity URL: ")
230 #Convert URL to ID
231 ID = GetSteamID(ID)
232 WriteNewAccount(ID)
233 #Check the user wants to commit change
234 Check()
235 loop = False
236 elif choice1 == 's':
237 ID = input("Enter the Steam ID: ")
238 WriteNewAccount(ID)
239 Check()
240 loop = False
241 if choice1 == '0':
242 loop = False
243 else:
244 print('Invalid choice')
245
246def CompareAccounts():
247 loop = True
248 while loop == True:
249 account1 = SelectAccount()
250 account2 = SelectAccount()
251 choice1 = input("What would you like to compare [P]laytime, [V]alue without discounts or [Va]lue with discounts? [0] to leave: ").lower()
252 if choice1 == 'p':
253 #Get the values of both account's playtime
254 value1 = GetTotalPlaytime(account1)
255 value2 = GetTotalPlaytime(account2)
256 diff = str(round(abs(value1[1] - value2[1])/60,1))
257 #Compare values
258 if value1[1] < value2[1]:
259 print(value2[0], "has " + diff, 'more hours')
260 elif value1[1] > value2[1]:
261 print(value1[0], "has " + diff, 'more hours')
262 else:
263 print("Both accounts have a playtime of " + str(value1))
264 input("Press enter to continue ")
265 loop = False
266 elif choice1 == 'v':
267 #Get the values of both account's playtime
268 value1 = GetTotalNormalValue(account1)
269 value2 = GetTotalNormalValue(account2)
270 diff = str(abs(value1[2] - value2[2])/100)
271 #Compare values
272 if value1[2] < value2[2]:
273 print(value2[0], "is worth $" + diff, 'more')
274 elif value1[1] > value2[2]:
275 print(value1[0], "is worth $" + diff, 'more')
276 else:
277 print("Both accounts have a value of " + str(value1))
278 input("Press enter to continue ")
279 loop = False
280 elif choice1 == 'va':
281 #Get the values of both account's playtime
282 value1 = GetTotalCurrentValue(account1)
283 value2 = GetTotalCurrentValue(account2)
284 diff = str(abs(value1[2] - value2[2])/100)
285 #Compare values
286 if value1[2] < value2[2]:
287 print(value2[0], "is worth $" + diff, 'more')
288 elif value1[1] > value2[2]:
289 print(value1[0], "is worth $" + diff, 'more')
290 else:
291 print("Both accounts have a value of " + str(value1))
292 input("Press enter to continue ")
293 loop = False
294 if choice1 == '0':
295 loop = False
296 else:
297 print('Invalid choice')
298
299def calcAccountValue():
300 #Loop until choice is made
301 loop = True
302 while loop == True:
303 #Get the user to select an account
304 account = SelectAccount()
305 choice = input("Do you want the value calculated with current [D]iscounts or [W]ithout? [0] to leave: ").lower()
306 #Process the users choice of action
307 if choice == 'd':
308 out = GetTotalCurrentValue(account)
309 print("Account", out[0], "has an account value of $" + str(out[2]/100))
310 input("Press enter to continue ")
311 loop = False
312 elif choice == 'w':
313 out = GetTotalNormalValue(account)
314 print("Account", out[0], "has an account value of $" + str(out[2]/100))
315 input("Press enter to continue ")
316 loop = False
317 if choice == '0':
318 loop = False
319 else:
320 print('Invalid choice')
321
322def UserInterface():
323 #Loop until a valid choice is made
324 while True:
325 print("What would you like to do? [0-9]")
326 print("[1] Add a new account")
327 print("[2] Add/update account's games")
328 print("[3] Calculate account value")
329 print("[4] Calculate total playtime")
330 print("[5] Compare two accounts")
331 print("[6] List stored accounts")
332 print("[7] List account's games")
333 print("[8] Delete stored game")
334 print("[9] Delete stored account")
335 print("[0] Exit")
336 #Process the choice
337 choice = input("Enter your choice: ")
338 #Add new account
339 if choice == '1':
340 AddNewAccount()
341 #Add/Update owned games
342 elif choice == '2':
343 account = SelectAccount()
344 gamesList = GetPlaytime(str(account))
345 WriteAllGames(gamesList, "AU")
346 WriteOwnedGames(gamesList)
347 Check()
348 #Calculate account value
349 elif choice == '3':
350 calcAccountValue()
351
352 #calculate playtime
353 elif choice == '4':
354 account = SelectAccount()
355 out = GetTotalPlaytime(account)
356 print("Account", out[0], "has a total playtime of", str(round(out[1]/60,1)), "hours")
357 input("Press enter to continue ")
358 #Compare two accounts
359 elif choice == '5':
360 CompareAccounts()
361
362 #Get stored accounts
363 elif choice == '6':
364 accountList = GetStoredAccounts()
365 for i in accountList:
366 print(i[0], i[1])
367 input("Press enter to continue ")
368 #List accounts games
369 elif choice == '7':
370 account = SelectAccount()
371 gamesList = GetAccountGames(account)
372 for i in gamesList:
373 print(i[2])
374 input("Press enter to continue ")
375 #Delete stored game
376 elif choice == '8':
377 game = SelectGame()
378 DeleteGame(game)
379 Check()
380 #Delete stored account
381 elif choice == '9':
382 account = SelectAccount()
383 DeleteAccount(account)
384 Check()
385 #Exit
386 elif choice =='0':
387 break
388 else:
389 print('Invalid choice')
390
391
392#Check whether a user wants to commit db changes
393def Check():
394 #Loop until a choice is made
395 while True:
396 choice = input("Is this OK [Y]es or [N]o?: ").lower()
397 if choice == 'y':
398 #Commit database changes
399 conn.commit()
400 break
401 elif choice == 'n':
402 print("Cancelling changes")
403 break
404 else:
405 print("Invalid choice")
406
407#Function to check whether a game or account is already in database
408def CheckDB(Type, ID):
409 ID = str(ID)
410 #Query the db for an account/game with the same ID
411 if Type == 'Game':
412 cur.execute("SELECT App_ID FROM Games WHERE App_ID = " + ID)
413 elif Type == 'Account':
414 cur.execute("SELECT Account_ID FROM Accounts WHERE Account_ID = " + ID)
415 #If an entry is found output true else output false
416 if len(cur.fetchall()) == 0:
417 return(False)
418 else:
419 return(True)
420
421#ask the user to choose an account from a list and return their id
422def SelectAccount():
423 #Get all accounts in db
424 accountList = GetStoredAccounts()
425 while True:
426 num = 0
427 print("Please select an account [0-" + str(len(accountList)) + "]")
428 #print list of accounts
429 for i in accountList:
430 print("[" + str(num) + "]", i[0], i[1])
431 num = num+1
432 choice = input()
433 if choice.isdigit():
434 #if the input is a number make the string into an integer
435 choice = int(choice)
436 else:
437 #if choice is not a number make the input invalid
438 choice = -1
439 #process choice
440 if 0 <= choice <= len(accountList):
441 print("Selected " + accountList[choice][0])
442 return(accountList[choice][1])
443 else:
444 print("Invalid choice")
445
446#Present the user with all games so they can make a choice
447def SelectGame():
448 #get all games from db
449 cur.execute('SELECT * FROM Games')
450 gamesList = cur.fetchall()
451 while True:
452 num = 0
453 print("Please select a game [0-" + str(len(gamesList)-1) + "]")
454 #Print all games
455 for i in gamesList:
456 print("[" + str(num) + "]", i[0], i[1])
457 num = num+1
458 choice = int(input())
459 #Process choice
460 if 0 <= choice <= len(gamesList):
461 print("Selected " + str(gamesList[choice][1]))
462 return(gamesList[choice][0])
463 else:
464 print("Invalid choice")
465
466#Get all accounts in db
467def GetStoredAccounts():
468 cur.execute('SELECT * FROM Accounts')
469 return(cur.fetchall())
470
471UserInterface()