def get_all_services(filter: str):
db = init_db()
mycursor = db.cursor()
if filter == 'all':
sql = "SELECT * FROM services"
else:
sql = "SELECT * FROM services WHERE status = 'Available'"
mycursor.execute(sql)
services = mycursor.fetchall()
result: list = []
for service in services:
_dict = {
'_id': service[0],
'section': service[1],
'name': service[2],
'price': service[3],
'status': service[4]
}
result.append(_dict)
mycursor.close()
db.close()
return result
def get_password(_id):
db = init_db()
mycursor = db.cursor()
mycursor.execute(f"SELECT * FROM guests_passwords WHERE guest_id = {_id}")
guest = mycursor.fetchall()
db.close()
if len(guest) > 0:
return True
def delete_room(_id: str):
db = init_db()
mycursor = db.cursor()
sql = f"DELETE FROM rooms WHERE _id = {_id}"
mycursor.execute(sql)
db.commit()
print(mycursor.rowcount, "record(s) deleted")
mycursor.close()
db.close()
def add_service(service: dict):
db = init_db()
mycursor = db.cursor()
sql = f"INSERT INTO services (section, name, price, status) " \
f"VALUES (%s, %s, %s, %s)"
values = (service['section'], service['name'], service['price'], service['status'])
mycursor.execute(sql, values)
db.commit()
print(mycursor.rowcount, "record inserted.")
mycursor.close()
db.close()
def update_password(_id, password):
hash = generate_password_hash(password)
db = init_db()
mycursor = db.cursor(buffered=True)
sql = f"UPDATE guests_passwords SET password_hash = '{hash}' WHERE guest_id = {_id}"
mycursor.execute(sql)
db.commit()
print(password)
print(mycursor.rowcount, "record inserted.")
db.close()
def add_room(room: dict):
db = init_db()
mycursor = db.cursor()
sql = f"INSERT INTO rooms (roomNumber, capacity, price, status) " \
f"VALUES (%s, %s, %s, %s)"
values = (room['roomNumber'], room['capacity'], room['price'],
room['status'])
mycursor.execute(sql, values)
db.commit()
print(mycursor.rowcount, "record inserted.")
mycursor.close()
db.close()
def save_password(_id, password):
hash = generate_password_hash(password)
db = init_db()
mycursor = db.cursor(buffered=True)
sql = "INSERT INTO guests_passwords (guest_id, password_hash)" \
"VALUES (%s, %s)"
values = (_id, hash)
mycursor.execute(sql, values)
db.commit()
print(password)
print(mycursor.rowcount, "record inserted.")
db.close()
def update_room(_id: str, room):
db = init_db()
old_room = get_room_info(_id)
for key in room:
if room[key] != old_room[key]:
mycursor = db.cursor()
sql = f"UPDATE rooms SET {key} = '{room[key]}' WHERE _id = {_id}"
mycursor.execute(sql)
db.commit()
print(mycursor.rowcount, "record(s) affected")
mycursor.close()
db.close()
def get_room_info(_id: str):
db = init_db()
mycursor = db.cursor()
mycursor.execute(f"SELECT * FROM rooms WHERE _id = {int(_id)}")
room = mycursor.fetchall()[0]
_dict = {
'_id': room[0],
'roomNumber': room[1],
'capacity': room[2],
'price': room[3],
'status': room[4]
}
mycursor.close()
db.close()
return _dict
def update_guest(_id: str, guest: dict):
old_guest = get_guest(_id)
db = init_db()
for key in guest:
if guest[key] != old_guest[key]:
mycursor = db.cursor()
sql = f"UPDATE guests SET {key} = '{guest[key]}' WHERE _id = {_id}"
mycursor.execute(sql)
db.commit()
print(mycursor.rowcount, "record(s) affected")
mycursor.close()
db.close()
def update_service(_id: str, service: dict):
old_service = get_service_info(_id)
db = init_db()
for key in service:
if service[key] != old_service[key]:
mycursor = db.cursor()
sql = f"UPDATE services SET {key} = '{service[key]}' WHERE _id = {_id}"
mycursor.execute(sql)
db.commit()
print(mycursor.rowcount, "record(s) affected")
mycursor.close()
db.close()
def get_service_info(_id: str):
db = init_db()
mycursor = db.cursor()
mycursor.execute(f"SELECT * FROM services WHERE _id = {_id}")
service = mycursor.fetchall()[0]
_dict = {
'_id': service[0],
'section': service[1],
'name': service[2],
'price': service[3],
'status': service[4]
}
mycursor.close()
db.close()
return _dict
def get_guest(_id):
db = init_db()
mycursor = db.cursor()
mycursor.execute(f"SELECT * FROM guests WHERE _id = {_id}")
user = mycursor.fetchall()[0]
_dict = {
'_id': user[0],
'firstName': user[1],
'lastName': user[2],
'phone': user[4],
'email': user[3],
'numOfArrivals': user[5]
}
mycursor.close()
db.close()
return _dict
def get_all_rooms():
db = init_db()
mycursor = db.cursor()
mycursor.execute("SELECT * FROM rooms")
rooms = mycursor.fetchall()
result: list = []
for room in rooms:
_dict = {
'_id': room[0],
'roomNumber': room[1],
'capacity': room[2],
'price': room[3],
'status': room[4]
}
result.append(_dict)
mycursor.close()
db.close()
return result
def validate_password(login, password):
db = init_db()
user = find_guest(login)
sql_query = 'select guests.email, guests_passwords.password_hash ' \
'from guests_passwords ' \
'inner join guests ' \
'on guests._id = guests_passwords._id ' \
f'where guests.email = "{login}"'
mycursor = db.cursor(buffered=True)
mycursor.execute(sql_query)
user['pswd'] = mycursor.fetchall()[0][1]
mycursor.close()
db.close()
if not user:
raise Exception('User %s not found' % login)
elif not check_password_hash(user['pswd'], password):
raise Exception('Incorrect password')
return user
def find_guest(login: str):
db = init_db()
mycursor = db.cursor(buffered=True)
mycursor.execute(f"SELECT * FROM guests WHERE email = '{login}'")
user = mycursor.fetchall()
if not user:
return False
user = user[0]
_dict = {
'_id': user[0],
'firstName': user[1],
'lastName': user[2],
'phone': user[4],
'email': user[3],
'numOfArrivals': user[5]
}
mycursor.close()
db.close()
return _dict
def get_all_guests():
sql_query = 'select guests._id, guests.email, guests.firstName, guests.lastName, guests.phone, guests.numOfArrivals ' \
'from guests '
db = init_db()
mycursor = db.cursor(buffered=True)
mycursor.execute(sql_query)
guests = mycursor.fetchall()
result: list = []
for guest in guests:
_dict = {
'_id': guest[0],
'email': guest[1],
'firstName': guest[2],
'lastName': guest[3],
'phone': guest[4],
'numOfArrivals': guest[5]
}
result.append(_dict)
mycursor.close()
db.close()
return result
def add_guest(guest: dict):
if find_guest(guest['email']):
raise Exception('User %s already exists' % guest['email'])
if 'phone' not in guest:
guest['phone'] = None
db = init_db()
mycursor = db.cursor(buffered=True)
sql = "INSERT INTO guests (firstName, lastName, phone, email)" \
"VALUES (%s, %s, %s, %s)"
values = (guest['firstName'], guest['lastName'],
guest['phone'], guest['email'])
mycursor.execute(sql, values)
_id = mycursor.lastrowid
db.commit()
print(mycursor.rowcount, "record inserted.")
db.close()
save_password(_id, guest['password'])
def get_available_rooms(capacity: int, date_from, date_to):
db = init_db()
mycursor = db.cursor()
mycursor.execute(
f"SELECT * FROM rooms WHERE capacity = {capacity} AND status = 'Available'"
)
rooms = mycursor.fetchall()
result: list = []
booked = bookings.get_booked_rooms(date_from, date_to)
for room in rooms:
if room[0] not in booked:
_dict = {
'_id': room[0],
'roomNumber': room[1],
'capacity': room[2],
'price': room[3],
'status': room[4]
}
result.append(_dict)
mycursor.close()
db.close()
return result
import unittest
import sys
import os
if __name__ == '__main__':
PROJECT_PATH = os.path.sep.join(os.path.abspath(__file__).split(os.path.sep)[:-2])
#put the project in path
if PROJECT_PATH not in sys.path:
sys.path.append(PROJECT_PATH)
from src import db_config
cursor = db_config.connect_to_database("root", "pygame").cursor()
db_config.create_database(cursor, "test2")
uri = db_config.get_database_uri("mysql", "root", "pygame", "test2")
db_config.init_db(uri)
#find all files which ends with 'tests'
suite = unittest.TestLoader().discover(".", "*.py")
#run tests
result = unittest.TextTestRunner().run(suite)
if not result.wasSuccessful():
db_config.delete_database(cursor, "test2")
sys.exit(1)
db_config.delete_database(cursor, "test2")