import json import sqlite3 import datetime conn = sqlite3.connect('db.sqlite3') def byteify(input): if isinstance(input, dict): return {byteify(key):byteify(value) for key,value in input.iteritems()} elif isinstance(input, list): return [byteify(element) for element in input] elif isinstance(input, unicode): return input.encode('utf-8') else: return input def import_fortunes(conn): fortune_file = 'fortunes.json' json_data=open(fortune_file).read() data = json.loads(json_data) c = conn.cursor() for fortune in data: try: c.execute("INSERT INTO fortunes (fortune, date_added) VALUES (?,'2016/01/18 00:00:00')", (fortune,)) except Exception as e: print(e) pass conn.commit() def import_jokes(conn): joke_file = 'jokes.json' json_data=open(joke_file).read() data = json.loads(json_data) c = conn.cursor() for joke in data: try: c.execute("INSERT INTO jokes (joke, date_added) VALUES (?,'2016/01/18 00:00:00')", (joke,)) except Exception as e: print(e) pass conn.commit() def import_messages(conn): deliveries_file = 'deliveries.json' json_data=open(deliveries_file).read() data = json.loads(json_data) c = conn.cursor() for username in data: print("Username: %s" % username) for author in data[username]: try: c.execute("INSERT INTO messages (message, delivery_time, channel, message_from, message_to, user_id) VALUES (?, ?, ?, ?, ?, ?)", (data[username][author]['message'], data[username][author]['delivery_time'],data[username][author]['channel'], author, username, data[username][author]['user_id'])) except Exception as e: print(e) pass conn.commit() def get_game_names(game_id_list): games_file = 'games.json' json_data=open(games_file).read() data = json.loads(json_data) result = [] for game_id in game_id_list: if isinstance(game_id, str) and not game_id.isdigit(): result.append(game_id) continue name_set = False for game in data: if game['id'] == game_id: result.append(game['name']) name_set = True return result def dict_factory(cursor, row): if row == None: return None d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d def db_get_member(discord_id=None, username=None): # Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name c = conn.cursor() result = None if discord_id: result = c.execute("SELECT member_id, member_name, discord_id, discord_mention, is_afk, afk_at, status, prev_status, status_change_at, current_game FROM members WHERE discord_id = ?;", (discord_id,)).fetchone() if username: result = c.execute("SELECT member_id, member_name, discord_id, discord_mention, is_afk, afk_at, status, prev_status, status_change_at, current_game FROM members WHERE member_name = ?;", (username,)).fetchone() return dict_factory(c, result) def log(message): print("{} - {}".format(datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'), message)) def db_add_game(member_id, game_name): # Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name c = conn.cursor() games = c.execute("SELECT game_id FROM games WHERE game_name = ?;", (game_name,)).fetchone() db_game_id = 0 if not games: log("Adding Game: {}".format(game_name,)) c.execute("INSERT INTO games(game_name) VALUES(?);", (game_name,)) conn.commit() db_game_id = c.execute("select last_insert_rowid();").fetchone()[0] else: db_game_id = games[0] #log("DB Game ID: {}".format(db_game_id,)) member_games = c.execute("SELECT launch_count FROM xmember_games WHERE game_id = ? AND member_id = ?;", (db_game_id, member_id)).fetchone() if not member_games: #log("Inserting Member Games: {}, {}".format(db_game_id, member_id)) c.execute("INSERT INTO xmember_games(game_id, member_id, launch_count) VALUES(?, ?, 1);", (db_game_id, member_id)) conn.commit() else: #log("Updating Member Games: {}, {}".format(db_game_id, member_id)) c.execute("UPDATE xmember_games SET launch_count = launch_count + 1 WHERE game_id = ? AND member_id = ?;", (db_game_id, member_id)) conn.commit() def db_create_member(member): # Do a lookup by ID, if it's found but the name doesn't match then add a row to aliases with the previous name and change the member name c = conn.cursor() c.execute("""INSERT INTO members (member_name, discord_id, discord_mention, is_afk, afk_at, status, prev_status, status_change_at, current_game) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);""", (member.name.lower(), member.id, member.mention, 0, datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'), 'online', 'offline', datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S'), member.game_id)) conn.commit() if member.game_id != None: db_add_game(db_get_member(member.id)['member_id'], member.game_id) class Member: pass def import_games(conn): games_file = 'games.json' members_file = 'members.json' json_data=open(members_file).read() member_data = json.loads(json_data) json_data=open(games_file).read() game_data = json.loads(json_data) c = conn.cursor() user_games = {} for username in member_data: if 'games_played' in member_data[username]: for game_id in member_data[username]['games_played']: if (game_id != 'None' and game_id != 0): if username not in user_games: user_games[username] = [] if game_id: if isinstance( game_id, int) or game_id.isdigit(): game_names = get_game_names([game_id]) #print(game_names) if len(game_names) > 0: user_games[username].append(game_names[0]) else: user_games[username].append(game_id) #print(user_games) for username, game_list in user_games.iteritems(): member = db_get_member(username=username) if member: for game in game_list: db_add_game(member['member_id'], game) print("Added Game: {} - {}".format(member['member_id'], game)) else: if 'id' not in member_data[username]: continue member_to_create = Member() member_to_create.name = username member_to_create.id = member_data[username]['id'] member_to_create.mention = member_data[username]['mention'] member_to_create.game_id = None db_create_member(member_to_create) print("missing {}".format(byteify(username))) print(member_to_create) # 'id': user_id, # 'mention': mention, # 'is_afk': is_afk, # 'afk_at': afk_at, # 'status': status, # 'prev_status': prev_status, # 'status_change_at': status_change_at, # 'game_id': game_id, # 'games_played': games_played, # 'aliases': aliases # for username in data: # print("Username: %s" % username) # for author in data[username]: # try: # c.execute("INSERT INTO messages (message, delivery_time, channel, message_from, message_to, user_id) VALUES (?, ?, ?, ?, ?, ?)", (data[username][author]['message'], data[username][author]['delivery_time'],data[username][author]['channel'], author, username, data[username][author]['user_id'])) # except Exception as e: # print(e) # pass # conn.commit() #import_jokes(conn) #import_fortunes(conn) #import_messages(conn) #import_games(conn)