def upsert_tournament(self, tournament_id: int, tournament: Tournament, clubs: list):
with session_scope(self.get_session) as session:
if tournament_id:
tournament.id = tournament_id
tournament.update(session)
else:
tournament.add(session)
for club_id in clubs:
statement = clubs_tournaments.insert().values(club_id=club_id, tournament_id=tournament.id)
session.execute(statement)
def on_ok(self):
tournament = Tournament(name=self.wgName.value, description=self.wgDescription.value)
clubs_indexes = filter(lambda el: isinstance(el, int), self.wgAvaliableClubs.value)
clubs_ids = []
if clubs_indexes:
clubs_ids = [self.wgAvaliableClubs.values[i][1] for i in clubs_indexes]
if self.tournament_id:
tournament.id = self.tournament_id
self.parentApp.database.update_tournament(tournament)
else:
tournament.id = self.parentApp.database.add_tournament(tournament)
self.parentApp.database.add_clubs_to_tournament(tournament.id, clubs_ids)
self.parentApp.switchFormPrevious()
def get_tournaments(self) -> list:
with self.get_cursor() as cur:
cur.execute('SELECT tournament_id as id, name FROM tournaments')
db_tournaments = cur.fetchall()
res = [Tournament(id=t['id'], name=t['name']) for t in db_tournaments]
res.reverse()
return res
def get_tournament(self, tournament_id: int) -> Tournament:
with self.get_cursor() as cur:
cur.execute(
'SELECT * FROM tournaments WHERE tournament_id = {0}'.format(
tournament_id))
t = cur.fetchone()
return Tournament(id=t['tournament_id'],
name=t['name'],
description=t['description'])
def text_search_by_phrase(self, phrase: str) -> list:
with session_scope(self.get_session) as session:
query_func = func.phraseto_tsquery(phrase, postgresql_regconfig='english')
results = session.query(Tournament) \
.filter(Tournament.tsv.op('@@')(query_func)).all()
return [Tournament(id=t.id,
name=t.name,
description=session.query(func.ts_headline('english', t.description, query_func))
.first())
for t in results]
def text_search_by_words(self, words: list) -> list:
search_words = ' & '.join(words)
with session_scope(self.get_session) as session:
results = session.query(Tournament) \
.filter(Tournament.tsv.match(search_words, postgresql_regconfig='english')).all()
return [Tournament(id=t.id,
name=t.name,
description=session.query(
func.ts_headline('english', t.description,
func.to_tsquery(search_words, postgresql_regconfig='english')))
.first())
for t in results]
def get_tournaments_by_club(self, club_id: int):
script = """
SELECT t.tournament_id as id, t.name as name FROM tournaments t
JOIN clubs_tournaments ct
ON t.tournament_id = ct.tournament_id
WHERE ct.club_id = %s"""
with self.get_cursor() as cur:
cur.execute(script, [club_id])
tournaments = cur.fetchall()
res = [Tournament(id=t['id'], name=t['name']) for t in tournaments]
res.reverse()
return res
def text_search_by_phrase(self, phrase: str) -> list:
script = """SELECT id, ts_headline('english', description, q) description, name
FROM (SELECT tournament_id id, description, name, q
FROM tournaments, phraseto_tsquery('english', %s) q
WHERE tsv @@ q) AS t;"""
with self.get_cursor() as cur:
cur.execute(script, [phrase])
tournaments = cur.fetchall()
return [
Tournament(id=t['id'],
name=t['name'],
description=t['description']) for t in tournaments
]
def generate_random_tournaments(self, tournaments_count: int) -> None:
fake = Faker()
with session_scope(self.get_session) as session:
for i in range(tournaments_count):
Tournament(name=fake.word(), description=fake.text()).add(session)
def delete_tournament(self, tournament_id: int) -> None:
with session_scope(self.get_session) as session:
Tournament.delete(session, tournament_id)
def get_tournaments(self) -> list:
return Tournament.getAll(self.get_session())
def get_tournament(self, tournament_id: int) -> Tournament:
return Tournament.get(self.get_session(), tournament_id)