def get_group_by_username(self, username):
self.cursor.execute(
"""
SELECT %(groups_table)s.name
FROM %(groups_table)s INNER JOIN %(users_table)s on %(groups_table)s.id = %(users_table)s.group_id
WHERE %(users_table)s.username = %(username)s;
""",
{"users_table": get_sql_table_name(Users), "groups_table": get_sql_table_name(Groups), "username": username}
)
group = self.cursor.fetchone()[0]
return group
def get_dog_id(self, id):
self.cursor.execute(
"""
SELECT %(participant_table)s.dog_id
FROM %(experts_table)s, %(participant_table)s
WHERE %(id)s = %(experts_table)s.ring_id
AND %(experts_table)s.participant_id = %(participant_table)s.id
""", {
"id": id,
"participant_table": get_sql_table_name(Participant),
"experts_table": get_sql_table_name(Experts)
})
rows = self.cursor.fetchall()
return {"dog_id": rows[0][0] if rows else None}
def get_report(self, id):
self.cursor.execute(
"""
SELECT %(participant_table)s.id, %(prizes_table)s.place
FROM %(prizes_table)s, %(participant_table)s
WHERE %(participant_table)s.dog_id = %(prizes_table)s.dog_id
AND %(participant_table)s.id = %(id)s;
""", {
"participant_table": get_sql_table_name(Participant),
"prizes_table": get_sql_table_name(Prizes),
"id": id,
})
result_keys = ["participant_id", "place"]
row = self.cursor.fetchone() or [id, None]
return {key: value for key, value in zip(result_keys, row)}
def get_breeds(self, id):
self.cursor.execute(
"""
SELECT %(breed_table)s.name
FROM %(participant_table)s, %(dog_table)s, %(breed_table)s
WHERE %(participant_table)s.dog_id = %(dog_table)s.id
AND %(dog_table)s.breed_id = %(breed_table)s.id
AND %(participant_table)s.club_id = %(id)s
""", {
"participant_table": get_sql_table_name(Participant),
"breed_table": get_sql_table_name(Breed),
"dog_table": get_sql_table_name(Dog),
"id": id
})
rows = self.cursor.fetchall()
return [row[0] for row in rows]
def get_ring(self, id):
self.cursor.execute(
"""
SELECT %(ring_table)s.id
FROM %(participant_table)s, %(dog_table)s, %(ring_table)s
WHERE %(participant_table)s.dog_id = %(dog_table)s.id
AND %(dog_table)s.breed_id = %(ring_table)s.breed_id
AND %(participant_table)s.id = %(participant)s
""", {
"ring_table": get_sql_table_name(Ring),
"participant_table": get_sql_table_name(Participant),
"dog_table": get_sql_table_name(Dog),
"participant": id
})
rows = self.cursor.fetchall()
return {"ring_id": rows[0][0] if rows else None}
def __init__(self, connection, obj_class):
self.obj_class = obj_class
self.connection = connection
self.cursor = connection.cursor()
self._table_name = get_sql_table_name(self.obj_class)
if isinstance(obj_class, type):
obj_class = obj_class()
self._translator = SqlTranslator(obj_class)
super().__init__()
def get_unused_rings(self):
self.cursor.execute(
"""
SELECT %(ring_table)s.id
FROM %(ring_table)s LEFT JOIN %(experts_table)s ON %(ring_table)s.id = %(experts_table)s.ring_id
WHERE %(experts_table)s.id IS NULL;
""", {
"ring_table": get_sql_table_name(Ring),
"experts_table": AsIs("experts"),
})
return [item[0] for item in self.cursor.fetchall()]
def get_prizes(self, id):
self.cursor.execute(
"""
SELECT count(%(prize_table)s) FILTER (WHERE %(prize_table)s.place = 1) as first_place,
count(%(prize_table)s) FILTER (WHERE %(prize_table)s.place = 2) as second_place,
count(%(prize_table)s) FILTER (WHERE %(prize_table)s.place = 3) as third_place
FROM %(prize_table)s, %(participant_table)s, %(dog_table)s, %(club_table)s
WHERE %(prize_table)s.dog_id = %(dog_table)s.id
AND %(participant_table)s.dog_id = %(dog_table)s.id
AND %(participant_table)s.club_id = %(club_table)s.id
AND %(club_table)s.id = %(id)s
GROUP BY %(club_table)s.name;
""", {
"prize_table": get_sql_table_name(Prizes),
"participant_table": get_sql_table_name(Participant),
"dog_table": get_sql_table_name(Dog),
"club_table": get_sql_table_name(Club),
"id": id
})
rows = self.cursor.fetchall()
prizes = rows[0] if rows else repeat(0, 3)
return {k: v for k, v in zip(self._places, prizes)}
def get_password_by_username(self, username):
self.cursor.execute(
"""
SELECT password
FROM %(users_table)s
WHERE username = %(username)s
""",
{"users_table": get_sql_table_name(Users), "username": username}
)
row = self.cursor.fetchone() or [None]
password = row[0]
if not password:
raise ValueError("There's no user")
return password
def get_report(self, id):
self.cursor.execute(
"""
SELECT %(participant_table)s.last_name, %(participant_table)s.first_name,
%(participant_table)s.middle_name, %(prizes_table)s.place, %(dog_table)s.fancy_name,
%(breed_table)s.name
FROM %(prizes_table)s, %(participant_table)s, %(dog_table)s, %(breed_table)s
WHERE %(prizes_table)s.dog_id = %(participant_table)s.dog_id
AND %(participant_table)s.club_id = %(id)s
AND %(participant_table)s.dog_id = %(dog_table)s.id
AND %(dog_table)s.breed_id = %(breed_table)s.id;
""", {
"prizes_table": get_sql_table_name(Prizes),
"participant_table": get_sql_table_name(Participant),
"dog_table": get_sql_table_name(Dog),
"breed_table": get_sql_table_name(Breed),
"id": id,
})
result_keys = [
"first_name", "last_name", "middle_name", "place", "dog", "breed"
]
rows = self.cursor.fetchall()
return [{key: value
for key, value in zip(result_keys, row)} for row in rows]