def load(file): conn = dbmodel.engine.connect() s = select([func.count(game.c.id)]) count = conn.execute(s).scalar() print("Database contains %s games" % count) s = select([player.c.name]) result = conn.execute(s) players = result.fetchall() print("Database contains %s players" % len(players)) s = select([game.c.id.label("Id"), pl1.c.name.label('White'), pl2.c.name.label('Black'), game.c.result.label('Result'), event.c.name.label('Event'), site.c.name.label('Site'), game.c.round.label('Round'), game.c.date_year.label('Year'), game.c.date_month.label('Month'), game.c.date_day.label('Day'), game.c.white_elo.label('WhiteElo'), game.c.black_elo.label('BlackElo'), game.c.eco.label('ECO'), game.c.fen.label('Board'), game.c.fen.label('FEN'), game.c.variant.label('Variant'), annotator.c.name.label('Annotator')], from_obj=[ game.outerjoin(pl1, game.c.white_id==pl1.c.id)\ .outerjoin(pl2, game.c.black_id==pl2.c.id)\ .outerjoin(event, game.c.event_id==event.c.id)\ .outerjoin(site, game.c.site_id==site.c.id)\ .outerjoin(annotator, game.c.annotator_id==annotator.c.id)]) result = conn.execute(s) colnames = result.keys() result.close() return Database([], colnames, s, count, players)
def __init__(self, engine): self.engine = engine self.cols = [col.label(col2label[col]) for col in col2label] self.from_obj = [ game.outerjoin(pl1, game.c.white_id == pl1.c.id).outerjoin( pl2, game.c.black_id == pl2.c.id).outerjoin( event, game.c.event_id == event.c.id).outerjoin( site, game.c.site_id == site.c.id).outerjoin( annotator, game.c.annotator_id == annotator.c.id) ] self.select = select(self.cols, from_obj=self.from_obj) self.select_offsets = select([ game.c.offset, ], from_obj=self.from_obj) self.colnames = self.engine.execute(self.select).keys() self.query = self.select self.order_cols = (game.c.offset, game.c.offset) self.is_desc = False self.where_tags = None self.where_offs = None self.where_offs8 = None
def __init__(self, path, games): PGNFile.__init__(self, path, games) self.path = path self.engine = dbmodel.get_engine(path) self.DB_MAXINT_SHIFT = get_maxint_shift(self.engine) self.cols = [ game.c.id.label("Id"), pl1.c.name.label('White'), pl2.c.name.label('Black'), game.c.result.label('Result'), event.c.name.label('Event'), site.c.name.label('Site'), game.c.round.label('Round'), game.c.date_year.label('Year'), game.c.date_month.label('Month'), game.c.date_day.label('Day'), game.c.white_elo.label('WhiteElo'), game.c.black_elo.label('BlackElo'), game.c.ply_count.label('PlyCount'), game.c.eco.label('ECO'), game.c.time_control.label('TimeControl'), game.c.fen.label('Board'), game.c.fen.label('FEN'), game.c.variant.label('Variant'), annotator.c.name.label('Annotator') ] self.from_obj = [ game.outerjoin(pl1, game.c.white_id == pl1.c.id).outerjoin( pl2, game.c.black_id == pl2.c.id).outerjoin( event, game.c.event_id == event.c.id).outerjoin( site, game.c.site_id == site.c.id).outerjoin( annotator, game.c.annotator_id == annotator.c.id) ] self.count = self.engine.execute(count_games).scalar() log.info("%s contains %s game(s)" % (self.path, self.count), extra={"task": "SQL"}) self.update_count_stats() self.select = select(self.cols, from_obj=self.from_obj) self.colnames = self.engine.execute(self.select).keys() self.query = self.select self.orderby = None self.where_tags = None self.where_bitboards = None self.ply = None
def build_query(self): self.query = self.chessfile.select if self.where is None: self.count = self.chessfile.count else: s = select([func.count(game.c.id)], from_obj=[ game.outerjoin(pl1, game.c.white_id == pl1.c.id) .outerjoin(pl2, game.c.black_id == pl2.c.id)]) self.count = self.conn.execute(s.where(self.where)).scalar() self.query = self.query.where(self.where) print("%s game(s) match to query" % self.count) if self.orderby is not None: self.query = self.query.order_by(self.orderby)
def __init__(self, engine): self.engine = engine self.cols = [ game.c.id.label("Id"), game.c.offset.label("Offset"), game.c.offset8.label("Offset8"), pl1.c.name.label('White'), pl2.c.name.label('Black'), game.c.result.label('Result'), event.c.name.label('Event'), site.c.name.label('Site'), game.c.round.label('Round'), game.c.date_year.label('Year'), game.c.date_month.label('Month'), game.c.date_day.label('Day'), game.c.white_elo.label('WhiteElo'), game.c.black_elo.label('BlackElo'), game.c.ply_count.label('PlyCount'), game.c.eco.label('ECO'), game.c.time_control.label('TimeControl'), game.c.fen.label('Board'), game.c.fen.label('FEN'), game.c.variant.label('Variant'), annotator.c.name.label('Annotator') ] self.from_obj = [ game.outerjoin(pl1, game.c.white_id == pl1.c.id).outerjoin( pl2, game.c.black_id == pl2.c.id).outerjoin( event, game.c.event_id == event.c.id).outerjoin( site, game.c.site_id == site.c.id).outerjoin( annotator, game.c.annotator_id == annotator.c.id) ] self.select = select(self.cols, from_obj=self.from_obj) self.select_offsets = select([ game.c.offset, ], from_obj=self.from_obj) self.colnames = self.engine.execute(self.select).keys() self.query = self.select self.orderby = None self.where_tags = None self.where_offs = None self.where_offs8 = None
def load(file): conn = dbmodel.engine.connect() selection = select([func.count(game.c.id)]) count = conn.execute(selection).scalar() print("Database contains %s games" % count) selection = select([player.c.name]) result = conn.execute(selection) players = result.fetchall() print("Database contains %s players" % len(players)) selection = select( [ game.c.id.label("Id"), pl1.c.name.label("White"), pl2.c.name.label("Black"), game.c.result.label("Result"), event.c.name.label("Event"), site.c.name.label("Site"), game.c.round.label("Round"), game.c.date_year.label("Year"), game.c.date_month.label("Month"), game.c.date_day.label("Day"), game.c.white_elo.label("WhiteElo"), game.c.black_elo.label("BlackElo"), game.c.eco.label("ECO"), game.c.fen.label("Board"), game.c.fen.label("FEN"), game.c.variant.label("Variant"), annotator.c.name.label("Annotator"), ], from_obj=[ game.outerjoin(pl1, game.c.white_id == pl1.c.id) .outerjoin(pl2, game.c.black_id == pl2.c.id) .outerjoin(event, game.c.event_id == event.c.id) .outerjoin(site, game.c.site_id == site.c.id) .outerjoin(annotator, game.c.annotator_id == annotator.c.id) ], ) result = conn.execute(selection) colnames = result.keys() result.close() return Database([], colnames, selection, count, players)
def __init__(self, engine): self.engine = engine self.cols = [col.label(col2label[col]) for col in col2label] self.from_obj = [ game.outerjoin(pl1, game.c.white_id == pl1.c.id) .outerjoin(pl2, game.c.black_id == pl2.c.id) .outerjoin(event, game.c.event_id == event.c.id) .outerjoin(site, game.c.site_id == site.c.id) .outerjoin(annotator, game.c.annotator_id == annotator.c.id)] self.select = select(self.cols, from_obj=self.from_obj) self.select_offsets = select([game.c.offset, ], from_obj=self.from_obj) self.colnames = self.engine.execute(self.select).keys() self.query = self.select self.order_cols = (game.c.offset, game.c.offset) self.is_desc = False self.where_tags = None self.where_offs = None self.where_offs8 = None