def climbed(self, user_id):
climbed = []
sql = """SELECT
year, month, day, comment,
summits.id, summits.ridge_id,
summits.name, height, ridges.name AS ridge
FROM ridges LEFT JOIN summits ON ridges.id=summits.ridge_id
LEFT JOIN climbs ON summits.id=climbs.summit_id
WHERE climbs.user_id=%s
ORDER BY year, month, day"""
with self.get_cursor() as cur:
cur.execute(sql, (user_id, ))
for row in cur:
s = Summit()
s.name = row['name']
s.ridge = row['ridge']
s.ridge_id = row['ridge_id']
s.id = row['id']
s.height = row['height']
climbed.append({
'summit': s,
'date': InexactDate.fromdict(row),
'comment': row['comment']
})
return climbed
def climbers(self, summit_id):
climbers = []
sql = """SELECT
year,
month,
day,
comment,
users.id,
users.name,
users.preview
FROM users LEFT JOIN climbs ON climbs.user_id=users.id
LEFT JOIN summits ON climbs.summit_id=summits.id
WHERE climbs.summit_id=%s ORDER BY year, month, day;"""
with self.get_cursor() as cur:
cur.execute(sql, (summit_id, ))
for row in cur:
u = User()
u.id = row['id']
u.name = row['name']
u.preview = row['preview']
climbers.append({
'user': u,
'date': InexactDate.fromdict(row),
'comment': row['comment']
})
return climbers
def get(self, user, summit):
sql = "SELECT year, month, day, comment" + \
" FROM climbs WHERE user_id=%s AND summit_id=%s"
with self.get_cursor() as cur:
cur.execute(sql, (user.get_id(), summit.id))
if cur.rowcount < 1:
return None
row = cur.fetchone()
climb = Climb()
climb.date = \
InexactDate.fromdict(row)
climb.comment = row['comment']
return climb
def get(self, user, summit):
sql = "SELECT year, month, day, comment" + \
" FROM climbs WHERE user_id=%s AND summit_id=%s"
with self.get_cursor() as cur:
cur.execute(sql, (user.get_id(), summit.id))
if cur.rowcount < 1:
return None
row = cur.fetchone()
climb = Climb()
climb.date = \
InexactDate.fromdict(row)
climb.comment = row['comment']
return climb
def climbed(self, user_id):
climbed = []
sql = """SELECT
year, month, day, comment,
summits.id, summits.ridge_id,
summits.name, height, ridges.name AS ridge
FROM ridges LEFT JOIN summits ON ridges.id=summits.ridge_id
LEFT JOIN climbs ON summits.id=climbs.summit_id
WHERE climbs.user_id=%s
ORDER BY year, month, day"""
with self.get_cursor() as cur:
cur.execute(sql, (user_id, ))
for row in cur:
s = Summit()
s.name = row['name']
s.ridge = row['ridge']
s.ridge_id = row['ridge_id']
s.id = row['id']
s.height = row['height']
climbed.append(
{'summit': s,
'date': InexactDate.fromdict(row),
'comment': row['comment']})
return climbed
def climbers(self, summit_id):
climbers = []
sql = """SELECT
year,
month,
day,
comment,
users.id,
users.name,
users.preview
FROM users LEFT JOIN climbs ON climbs.user_id=users.id
LEFT JOIN summits ON climbs.summit_id=summits.id
WHERE climbs.summit_id=%s ORDER BY year, month, day;"""
with self.get_cursor() as cur:
cur.execute(sql, (summit_id, ))
for row in cur:
u = User()
u.id = row['id']
u.name = row['name']
u.preview = row['preview']
climbers.append(
{'user': u, 'date': InexactDate.fromdict(row),
'comment': row['comment']})
return climbers