def update(schedule):
sql = """UPDATE schedules
SET (class_date, length_mins, start_time, instructor_id, class_id, room_id) =
(%s, %s, %s, %s, %s, %s)
WHERE id = %s"""
values = [schedule.class_date, schedule.length_mins, schedule.start_time, schedule.instructor.id,
schedule.gym_class.id, schedule.room.id]
run_sql(sql, values)
def update(owner):
# Create SQL query, pass in the data and run it
sql = "UPDATE owner SET (first_name, last_name, email_address, contact_number, address, post_code, city, registered) = (%s, %s, %s, %s, %s, %s, %s, %s) WHERE id = %s"
values = [
owner.first_name, owner.last_name, owner.email, owner.contact_number,
owner.address, owner.post_code, owner.city, owner.registered, owner.id
]
run_sql(sql, values)
def update(gym_class):
sql = """UPDATE classes
SET class_name = %s,
description = %s,
max_time = %s,
capacity = %s
WHERE id = %s"""
values = [
gym_class.class_name, gym_class.description, gym_class.max_time,
gym_class.capacity, gym_class.id
]
run_sql(sql, values)
def non_selected_members(id):
sql = """SELECT members.id FROM members
FULL OUTER JOIN schedules_members ON schedules_members.member_id = members.id
WHERE schedule_id = %s"""
values = [id]
rows = run_sql(sql, values)
member_ids = tuple([row[0] for row in rows])
if member_ids == ():
sql2 = "SELECT * FROM members"
members = run_sql(sql2)
else:
sql2 = """SELECT * FROM members WHERE id NOT IN %s"""
values2 = [member_ids]
members = run_sql(sql2, values2)
return members
def selected_members(id):
sql = """SELECT members.* FROM members
INNER JOIN schedules_members ON schedules_members.member_id = members.id
WHERE schedules_members.schedule_id = %s"""
values = [id]
members = run_sql(sql, values)
return members
def save(timetable):
sql = """INSERT INTO instructor_timetables ( week_start, i_details_id, i_schedules_id )
VALUES ( %s, %s, %s ) RETURNING id"""
values = [timetable.week_start_date, timetable.detail.id, timetable.schedule.id]
results = run_sql( sql, values )
timetable.id = results[0]['id']
return timetable
def save_member(member):
sql = """INSERT INTO schedules_members (member_id, schedule_id)
VALUES (%s, %s)
RETURNING id"""
values = [member.member.id, member.schedule.id]
results = run_sql(sql, values)
id = results[0]['id']
member.id = id
def save(instructor):
sql = """INSERT INTO instructor_details
(first_name, last_name, date_of_birth)
VALUES ( %s, %s, %s )
RETURNING id"""
values = [instructor.first_name, instructor.last_name, instructor.date_of_birth]
results = run_sql( sql, values )
instructor.id = results[0]['id']
return instructor
def select_all():
instructors = []
sql = "SELECT * FROM instructor_details"
results = run_sql(sql)
for row in results:
instructor = InstructorDetails(row['first_name'], row['last_name'],
row['date_of_birth'], row['id'])
instructors.append(instructor)
return instructors
def select(id):
instructor = None
sql = "SELECT * FROM instructor_details WHERE id = %s"
values = [id]
result = run_sql(sql, values)[0]
if result is not None:
instructor = InstructorDetails(result['first_name'], result['last_name'],
result['date_of_birth'], result['id'])
return instructor
def save(room):
sql = """INSERT INTO rooms
(room_name, capacity, description)
VALUES (%s, %s, %s)
RETURNING id"""
values = [room.room_name, room.capacity, room.description]
results = run_sql(sql, values)
room.id = results[0]['id']
return room
def select(id):
room = None
sql = "SELECT * FROM rooms WHERE id = %s"
values = [id]
result = run_sql(sql, values)[0]
if result is not None:
room = Room(result['room_name'], result['capacity'],
result['description'], result['id'])
return room
def select_all():
rooms = []
sql = "SELECT * FROM rooms ORDER BY id"
results = run_sql(sql)
for row in results:
room = Room(row['room_name'], row['capacity'], row['description'],
row['id'])
rooms.append(room)
return rooms
def update(member):
sql = """UPDATE members
SET first_name = %s,
last_name = %s,
email = %s,
phone = %s,
date_of_birth = %s,
membership = %s,
premium = %s,
member_since = %s,
member_until = %s
WHERE id = %s"""
values = [
member.first_name, member.last_name, member.email, member.phone,
member.date_of_birth, member.membership, member.premium,
member.member_since, member.member_until, member.id
]
run_sql(sql, values)
def save(note):
# Create the SQL query && input data before running it
sql = "INSERT INTO note (date, note_text, pet_id, vet_id) VALUES (%s, %s, %s, %s) RETURNING id"
values = [note.date, note.note_text, note.pet.id, note.vet.id]
result = run_sql(sql, values)
# Set the note objects ID to the ID generated by the database
note.id = result[0]['id']
return note
def save(schedule):
sql = """INSERT INTO schedules
(class_date, start_time, length_mins, instructor_id, class_id, room_id)
VALUES ( %s, %s, %s, %s, %s, %s ) RETURNING id"""
values = [schedule.class_date, schedule.start_time, schedule.length_mins, schedule.instructor.id,
schedule.gym_class.id, schedule.room.id]
results = run_sql(sql, values)
id = results[0]['id']
schedule.id = id
def save(pet_type):
# Create the SQL query && input data before running it
sql = "INSERT INTO pet_type (type, breed) VALUES (%s, %s) RETURNING id"
values = [pet_type.type, pet_type.breed]
result = run_sql(sql, values)
# Set the pet objects ID to the ID generated by the database
pet_type.id = result[0]['id']
return pet_type
def select_all():
gym_classes = []
sql = "SELECT * FROM classes ORDER BY id"
results = run_sql(sql)
for row in results:
gym_class = GymClass(row['class_name'], row['description'],
row['max_time'], row['capacity'], row['id'])
gym_classes.append(gym_class)
return gym_classes
def save(perscribed_treatment):
# Create SQL query, pass in data && run
sql = "INSERT INTO perscribed_treatment (pet_id, treatment_id) VALUES (%s, %s) RETURNING *"
values = [perscribed_treatment.pet.id, perscribed_treatment.treatment.id]
result = run_sql(sql, values)
# Set the objects id to the id generated by db
id = result[0]['id']
perscribed_treatment.id = id
return perscribed_treatment
def select(id):
gym_class = None
sql = "SELECT * FROM classes WHERE id = %s"
values = [id]
result = run_sql(sql, values)[0]
if result is not None:
gym_class = GymClass(result['class_name'], result['description'],
result['max_time'], result['capacity'],
result['id'])
return gym_class
def select_classes(id):
classes = []
sql = "SELECT * FROM schedules_members WHERE member_id = %s"
values = [id]
results = run_sql(sql, values)
for row in results:
schedule = schedule_repository.select(row['schedule_id'])
if schedule.class_date >= date.today():
classes.append(schedule)
return classes
def select_all():
timetables = []
sql = "SELECT * FROM instructor_timetables"
results = run_sql(sql)
for row in results:
detail = details_repository.select(row['i_details_id'])
schedule = schedule_repository.select(row['i_schedules_id'])
timetable = InstructorTimetable(row['week_start'], detail, schedule, row['id'])
timetables.append(timetable)
return timetables
def select_all():
members = []
sql = "SELECT * FROM members"
results = run_sql(sql)
for row in results:
member = Member(row['first_name'], row['last_name'], row['email'],
row['phone'], row['date_of_birth'], row['membership'],
row['premium'], row['member_since'],
row['member_until'], row['id'])
members.append(member)
return members
def select(id):
# Create SQL query, input data && run
sql = "SELECT * FROM treatment WHERE id = %s"
values = [id]
result = run_sql(sql, values)
# If the result exists create an object to be returned
if len(result) > 0:
treatment = Treatment(result[0]['name'], result[0]['cost'],
result[0]['length'], result[0]['medicine'],
result[0]['type'], result[0]['id'])
return treatment
def save(vet):
# Create the SQL query, pass in data and run it
sql = "INSERT INTO vet (first_name, last_name) VALUES (%s, %s) RETURNING id"
values = [vet.first_name, vet.last_name]
result = run_sql(sql, values)
# Set the object ID to the one generated by the database
if len(result) > 0:
id = result[0]['id']
vet.id = id
return vet
def select(vet_id):
# Create the SQL query, pass in the data and run it
sql = "SELECT * FROM vet WHERE id = %s"
values = [vet_id]
result = run_sql(sql, values)
# Create object if data is found in the database
if len(result) > 0:
new_vet = Vet(result[0]['first_name'], result[0]['last_name'],
result[0]['id'])
return new_vet
def save(gym_class):
sql = """INSERT INTO classes
(class_name, description, max_time, capacity)
VALUES ( %s, %s, %s, %s )
RETURNING id"""
values = [
gym_class.class_name, gym_class.description, gym_class.max_time,
gym_class.capacity
]
results = run_sql(sql, values)
gym_class.id = results[0]['id']
return gym_class
def select(id):
schedule = None
sql = "SELECT * FROM schedules WHERE id = %s"
values = [id]
result = run_sql(sql, values)[0]
if result is not None:
instructor = instructor_repository.select(result['instructor_id'])
gym_class = gym_class_repository.select(result['class_id'])
room = room_repository.select(result['room_id'])
schedule = Schedule(result['class_date'], result['start_time'], result['length_mins'], instructor,
gym_class, room, result['id'])
return schedule
def select(id):
member = None
sql = "SELECT * FROM members WHERE id = %s"
values = [id]
result = run_sql(sql, values)[0]
if result is not None:
member = Member(result['first_name'], result['last_name'],
result['email'], result['phone'],
result['date_of_birth'], result['membership'],
result['premium'], result['member_since'],
result['member_until'], result['id'])
return member
def select_all():
schedules = []
sql = "SELECT * FROM schedules ORDER BY class_date"
results = run_sql(sql)
for row in results:
instructor = instructor_repository.select(row['instructor_id'])
gym_class = gym_class_repository.select(row['class_id'])
room = room_repository.select(row['room_id'])
schedule = Schedule(row['class_date'], row['start_time'], row['length_mins'], instructor,
gym_class, room, row['id'])
schedules.append(schedule)
return schedules
