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
