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)