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")