def upsert_team(self, team_id: int, team: Team, countries: list) -> None: with session_scope(self.get_session) as session: if team_id: team.id = team_id team.update(session) else: team.add(session) for country_id in countries: statement = countries_teams.insert().values( country_id=country_id, team_id=team.id) session.execute(statement)
def on_ok(self): team = Team(name=self.wgName.value, team_description=self.wgDescription.value) country_id = None if isinstance(self.wgAvaliableCountries.value[0], int): country_id = self.wgAvaliableCountries.values[ self.wgAvaliableCountries.value[0]][1] else: country_id = self.country_id if self.team_id: team.id = self.team_id self.parentApp.database.update_team(team) else: team.id = self.parentApp.database.add_team(team) self.parentApp.database.add_country_to_team(team.id, country_id) self.parentApp.switchFormPrevious()
def get_team(self, team_id: int) -> Team: with self.get_cursor() as cur: cur.execute( 'SELECT * FROM team WHERE team_id = {0}'.format(team_id)) team = cur.fetchone() return Team(id=team['team_id'], name=team['team_name'], team_description=team['team_description'], team_country_id=team['team_country_id'])
def text_search_by_word_not_belong(self, word): with session_scope(self.get_session) as session: results = session.query(Team).filter( sqlalchemy.not_( Team.tsv.match(word, postgresql_regconfig='english'))).all() return [ Team(id=t.id, name=t.name, description=t.description) for t in results ]
def get_teams(self) -> list: with self.get_cursor() as cur: cur.execute('SELECT * FROM team') teams = cur.fetchall() return [ Team(id=t['team_id'], name=t['team_name'], team_description=t['team_description'], team_country_id=t['team_country_id']) for t in teams ]
def on_ok(self): team = Team(name=self.wgName.value, description=self.wgDescription.value) country_indexes = filter(lambda el: isinstance(el, int), self.wgAvaliableCountries.value) country_ids = [] if country_indexes: country_ids = [ self.wgAvaliableCountries.values[i][1] for i in country_indexes ] self.parentApp.database.upsert_team(self.team_id, team, country_ids) self.parentApp.switchFormPrevious()
def text_search_by_word_not_belong(self, word): script = """SELECT team_id, team_name, team_description, team_country_id FROM team WHERE to_tsvector(team_description) @@ !!to_tsquery(%s);""" with self.get_cursor() as cur: cur.execute(script, [word]) teams = cur.fetchall() return [ Team(id=t['team_id'], name=t['team_name'], team_description=t['team_description'], team_country_id=t['team_country_id']) for t in teams ]
def text_search_by_word(self, word) -> list: with session_scope(self.get_session) as session: results = session.query(Team).filter( Team.tsv.match(word, postgresql_regconfig='english')).all() return [ Team(id=t.id, name=t.name, description=session.query( func.ts_headline( 'english', t.description, func.to_tsquery( word, postgresql_regconfig='english'))).first()) for t in results ]
def delete_team(self, team_id: int) -> None: with session_scope(self.get_session) as session: Team.delete(session, team_id)
def get_teams(self) -> list: return Team.getAll(self.get_session())
def get_team(self, team_id: int) -> Team: return Team.get(self.get_session(), team_id)
def generate_random_teams(self, team_count: int) -> None: fake = Faker() with session_scope(self.get_session) as session: for i in range(team_count): Team(name=fake.first_name_male(), description=fake.word()).add(session)