def __init__(self, chessfile, append_pgn=False): self.chessfile = chessfile self.append_pgn = append_pgn self.db_handle = chessfile.handle self.engine = chessfile.engine self.conn = self.engine.connect() self.CHUNK = 1000 self.cancel = False self.count_source = select([func.count()]).select_from(source) self.ins_event = event.insert() self.ins_site = site.insert() self.ins_player = player.insert() self.ins_annotator = annotator.insert() self.ins_source = source.insert() self.ins_game = game.insert() self.ins_tag_game = tag_game.insert() self.event_dict = {} self.site_dict = {} self.player_dict = {} self.annotator_dict = {} self.source_dict = {} self.next_id = [0, 0, 0, 0, 0, 0] self.next_id[GAME] = self.ini_names(game, GAME) self.next_id[EVENT] = self.ini_names(event, EVENT) self.next_id[SITE] = self.ini_names(site, SITE) self.next_id[PLAYER] = self.ini_names(player, PLAYER) self.next_id[ANNOTATOR] = self.ini_names(annotator, ANNOTATOR) self.next_id[SOURCE] = self.ini_names(source, SOURCE)
def initialize(self): self.db_handle = self.chessfile.handle self.engine = self.chessfile.engine self.conn = self.engine.connect() self.CHUNK = 1000 self.count_source = select([func.count()]).select_from(source) self.ins_event = event.insert() self.ins_site = site.insert() self.ins_player = player.insert() self.ins_annotator = annotator.insert() self.ins_source = source.insert() self.ins_game = game.insert() self.ins_tag_game = tag_game.insert() self.event_dict = {} self.site_dict = {} self.player_dict = {} self.annotator_dict = {} self.source_dict = {} self.next_id = [0, 0, 0, 0, 0, 0] self.next_id[GAME] = self.ini_names(game, GAME) self.next_id[EVENT] = self.ini_names(event, EVENT) self.next_id[SITE] = self.ini_names(site, SITE) self.next_id[PLAYER] = self.ini_names(player, PLAYER) self.next_id[ANNOTATOR] = self.ini_names(annotator, ANNOTATOR) self.next_id[SOURCE] = self.ini_names(source, SOURCE)
def __init__(self, engine): self.engine = engine self.conn = self.engine.connect() self.CHUNK = 1000 self.cancel = False self.ins_event = event.insert() self.ins_site = site.insert() self.ins_player = player.insert() self.ins_annotator = annotator.insert() self.ins_source = source.insert() self.ins_game = game.insert() self.ins_bitboard = bitboard.insert() self.ins_tag_game = tag_game.insert() self.event_dict = {} self.site_dict = {} self.player_dict = {} self.annotator_dict = {} self.source_dict = {} self.next_id = [0, 0, 0, 0, 0, 0] self.next_id[GAME] = self.ini_names(game, GAME) self.next_id[EVENT] = self.ini_names(event, EVENT) self.next_id[SITE] = self.ini_names(site, SITE) self.next_id[PLAYER] = self.ini_names(player, PLAYER) self.next_id[ANNOTATOR] = self.ini_names(annotator, ANNOTATOR) self.next_id[SOURCE] = self.ini_names(source, SOURCE) s = select([player.c.fideid, player.c.id]) self.fideid_dict = dict([(p[0], p[1]) for p in self.conn.execute(s)]) self.perfix_stmt = select([player.c.id]).where( player.c.name.startswith(bindparam('name')))
def import_FIDE_players(self): #print 'drop index' #idx = Index('ix_player_name', player.c.name) #self.conn.execute(DropIndex(idx)) #print 'import FIDE players' #import_players() #print 'create index' #idx = Index('ix_player_name', player.c.name) #idx.create(engine) ins_player = player.insert() player_data = [] with open("players_list.txt") as f: # use transaction to avoid autocommit slowness trans = self.conn.begin() try: for i, line in enumerate(f): if i==0: continue elo = line[53:58].rstrip() elo = int(elo) if elo else None born = line[64:68].rstrip() born = int(born) if born else None title = line[44:46].rstrip() title = title if title else None player_data.append({ "fideid": int(line[:8]), "name": line[10:42].rstrip(), "title": title, "fed": line[48:51], "elo": elo, "born": born, }) if len(player_data) >= CHUNK: self.conn.execute(ins_player, player_data) player_data = [] print(i) if player_data: self.conn.execute(ins_player, player_data) print(i+1) trans.commit() except: trans.rollback() raise
def import_FIDE_players(self): #print 'drop index' #idx = Index('ix_player_name', player.c.name) #self.conn.execute(DropIndex(idx)) #print 'import FIDE players' #import_players() #print 'create index' #idx = Index('ix_player_name', player.c.name) #idx.create(engine) ins_player = player.insert() player_data = [] with open("players_list.txt") as f: # use transaction to avoid autocommit slowness trans = self.conn.begin() try: for i, line in enumerate(f): if i == 0: continue elo = line[53:58].rstrip() elo = int(elo) if elo else None born = line[64:68].rstrip() born = int(born) if born else None title = line[44:46].rstrip() title = title if title else None player_data.append({ "fideid": int(line[:8]), "name": line[10:42].rstrip(), "title": title, "fed": line[48:51], "elo": elo, "born": born, }) if len(player_data) >= CHUNK: self.conn.execute(ins_player, player_data) player_data = [] print(i) if player_data: self.conn.execute(ins_player, player_data) print(i + 1) trans.commit() except: trans.rollback() raise
def __init__(self): self.conn = engine.connect() self.ins_collection = collection.insert() self.ins_event = event.insert() self.ins_site = site.insert() self.ins_player = player.insert() self.ins_annotator = annotator.insert() self.ins_game = game.insert() self.collection_dict = {} self.event_dict = {} self.site_dict = {} self.player_dict = {} self.annotator_dict = {} self.next_id = [0, 0, 0, 0, 0] self.next_id[COLLECTION] = self.ini_names(collection, COLLECTION) self.next_id[EVENT] = self.ini_names(event, EVENT) self.next_id[SITE] = self.ini_names(site, SITE) self.next_id[PLAYER] = self.ini_names(player, PLAYER) self.next_id[ANNOTATOR] = self.ini_names(annotator, ANNOTATOR)
def __init__(self): self.conn = engine.connect() self.ins_collection = collection.insert() self.ins_event = event.insert() self.ins_site = site.insert() self.ins_player = player.insert() self.ins_annotator = annotator.insert() self.ins_game = game.insert() self.collection_dict = {} self.event_dict = {} self.site_dict = {} self.player_dict = {} self.annotator_dict = {} self.next_id = [0, 0, 0, 0, 0] self.next_id[COLLECTION] = self.ini_names(collection, COLLECTION) self.next_id[EVENT] = self.ini_names(event, EVENT) self.next_id[SITE] = self.ini_names(site, SITE) self.next_id[PLAYER] = self.ini_names(player, PLAYER) self.next_id[ANNOTATOR] = self.ini_names(annotator, ANNOTATOR)
def import_players(self, progressbar=None): self.progressbar = progressbar filename = "http://ratings.fide.com/download/players_list.zip" filename = download_file(filename, progressbar=progressbar) # filename = "/tmp/players_list.zip" if filename is None: return ins_player = insert_or_ignore(self.engine, player.insert()) player_data = [] zf = zipfile.ZipFile(filename, "r") basename = "players_list_foa.txt" size = zf.getinfo(basename).file_size with io.TextIOWrapper(zf.open(basename)) as f: if progressbar is not None: GLib.idle_add(progressbar.set_text, "Pocessing %s ..." % basename) else: print("Processing %s ..." % basename) # use transaction to avoid autocommit slowness trans = self.conn.begin() i = 0 try: for line in f: if self.cancel: trans.rollback() return if line.startswith("ID"): all_players = size / len(line) - 1 continue i += 1 title = line[84:88].rstrip() title = title if title else None elo = line[113:117].rstrip() elo = int(elo) if elo else None born = line[152:156].rstrip() born = int(born) if born else None player_data.append({ "fideid": int(line[:14]), "name": line[15:75].rstrip(), "fed": line[76:79], "sex": line[80:81], "title": title, "elo": elo, "born": born, }) if len(player_data) >= self.CHUNK: self.conn.execute(ins_player, player_data) player_data = [] if progressbar is not None: GLib.idle_add(progressbar.set_fraction, i / float(all_players)) GLib.idle_add( progressbar.set_text, "%s / %s from %s imported" % (i, all_players, basename)) else: print(basename, i) if player_data: self.conn.execute(ins_player, player_data) trans.commit() except: trans.rollback() raise
def import_players(self, progressbar=None): self.progressbar = progressbar if progressbar is not None: self.pulse = True self.timeout_id = GObject.timeout_add(50, self.on_timeout, None) filename = "http://ratings.fide.com/download/players_list.zip" filename = download_file(filename, progressbar=progressbar) # filename = "/tmp/players_list.zip" if filename is None: return # TODO: this is Sqlite specific !!! # can't use "OR UPDATE" because it delete+insert records # and breaks referential integrity ins_player = player.insert().prefix_with("OR IGNORE") player_data = [] zf = zipfile.ZipFile(filename, "r") basename = "players_list_foa.txt" size = zf.getinfo(basename).file_size with zf.open(basename) as f: if progressbar is not None: self.pulse = False GLib.idle_add(progressbar.set_text, "Pocessing %s ..." % basename) else: print("Processing %s ..." % basename) # use transaction to avoid autocommit slowness trans = self.conn.begin() i = 0 try: for line in f: if self.cancel: trans.rollback() return if line.startswith("ID"): all_players = size / len(line) - 1 continue i += 1 title = line[84:88].rstrip() title = title if title else None elo = line[113:117].rstrip() elo = int(elo) if elo else None born = line[152:156].rstrip() born = int(born) if born else None player_data.append({ "fideid": int(line[:14]), "name": line[15:75].rstrip(), "fed": line[76:79], "sex": line[80:81], "title": title, "elo": elo, "born": born, }) if len(player_data) >= self.CHUNK: self.conn.execute(ins_player, player_data) player_data = [] if progressbar is not None: GLib.idle_add(progressbar.set_fraction, i / float(all_players)) GLib.idle_add( progressbar.set_text, "%s / %s from %s imported" % (i, all_players, basename)) else: print(basename, i) if player_data: self.conn.execute(ins_player, player_data) trans.commit() except: trans.rollback() raise