def get_comments():
session = Session()
comments_objects = session.query(Comment).all()
schema = CommentSchema(many=True)
comments = schema.dump(comments_objects)
session.close()
return comments
def get_book(title):
session = Session()
book_objects = session.query(Book).filter_by(title=title)
schema = BookSchema(many=True)
books = schema.dump(book_objects)
session.close()
return books
def get_users():
session = Session()
users_objects = session.query(User).all()
schema = UserSchema(many=True)
users = schema.dump(users_objects)
session.close()
return users
def get_books():
session = Session()
books_objects = session.query(Book).all()
schema = BookSchema(many=True)
books = schema.dump(books_objects)
session.close()
return books
def get_user(username):
session = Session()
user_objects = session.query(User).filter_by(username=username).limit(1)
schema = UserSchema(many=True)
user = schema.dump(user_objects)
session.close()
return user
def create_comment_platform(data):
posted_comment = CommentSchema(only=('text', 'id_book', 'id_user')) \
.load(data)
comment = Comment(**posted_comment)
session = Session()
session.add(comment)
session.commit()
new_comment = CommentSchema().dump(comment)
session.close()
return {'info': 'Save comment in DB'}
def create_book_platform(data):
data['publication_date'] = str(
datetime.strptime(data['publication_date'], "%Y-%m-%d"))
posted_book = BookSchema(only=('title', 'publication_date')) \
.load(data)
book = Book(**posted_book)
session = Session()
session.add(book)
session.commit()
new_book = BookSchema().dump(book)
session.close()
return {'info': 'Save book in DB'}
def authenticate(username, password):
if not username:
return None
# fetching from the database
session = Session()
user_objects = session.query(User).filter_by(username=username).limit(1)
schema = UserSchema(many=True)
user = schema.dump(user_objects)
session.close()
if not user:
return None
user_internal = User(**user[0])
if user_internal.check_password(password):
flask_login.login_user(user_internal)
return user[0]
def create_user_platform(data):
posted_user = UserSchema(only=('username', 'password', 'first_name', 'last_name'))\
.load(data)
user = User(**posted_user)
user.set_password(data['password'])
session = Session()
user_objects = session.query(User).filter_by(
username=data['username']).limit(1)
schema = UserSchema(many=True)
user_old = schema.dump(user_objects)
if user_old:
return
session.add(user)
session.commit()
new_user = UserSchema().dump(user)
session.close()
return {'info': 'Save user in DB'}
class DatabaseActions:
def __init__(self):
Base.metadata.create_all(engine)
self.session = Session()
def process_device_dict(self, devices_dict):
current_time = datetime.now()
pb_notification = PBNotification()
for k, v in devices_dict.items():
# checks to see if any new devices have connected, if so add them
device_exists = self.session.query(exists().where(Device.mac_address == k)).scalar()
if not device_exists:
mac_address = k
ip_address = v['IP Address']
hostname = v['Hostname']
connected = v['Connected']
device = Device(mac_address=mac_address, ip_address=ip_address,
hostname=hostname, connected=connected)
self.session.add(device)
# update connection states for devices
db_device = self.session.query(Device).filter(Device.mac_address == k).scalar()
prev_con_status = db_device.connected
cur_con_status = v['Connected']
if prev_con_status != cur_con_status:
db_device.connected = cur_con_status
# update the device_connection_log for the device
if db_device.connected:
con_log_entry_exists = self.session.query(
exists().where(and_(DeviceConnectionLog.mac_address == k,
DeviceConnectionLog.connected.isnot(None),
DeviceConnectionLog.disconnected.is_(None)))).scalar()
if con_log_entry_exists:
continue
con_log_entry = DeviceConnectionLog(mac_address=k,
connected=current_time)
self.session.add(con_log_entry)
if db_device.watched:
device_name = db_device.hostname if db_device.nickname is None else db_device.nickname
pb_notification.add_connected_device(device_name)
else:
con_log_entry = self.session.query(DeviceConnectionLog).filter(
DeviceConnectionLog.mac_address == k, DeviceConnectionLog.disconnected.is_(None)).scalar()
if con_log_entry is None:
continue
con_log_entry.disconnected = current_time
if db_device.watched:
device_name = db_device.hostname if db_device.nickname is None else db_device.nickname
pb_notification.add_disconnected_device(device_name)
pb_notification.send_notification()
self.session.commit()
def populate_device_db(self, devices_dict):
for k, v in devices_dict.items():
mac_address = k
ip_address = v['IP Address']
hostname = v['Hostname']
device = Device(mac_address=mac_address, ip_address=ip_address,
hostname=hostname, connected=False)
self.session.add(device)
self.session.commit()
self.session.close()
def query_connected_devices(self, ignore_device_list=[]):
if ignore_device_list:
return self.session.query(Device).order_by(Device.nickname).filter(
Device.connected.is_(True), not_(Device.mac_address.in_(ignore_device_list))
).all()
return self.session.query(Device).order_by(Device.nickname).filter(Device.connected.is_(True)).all()
def query_watched_devices(self):
return self.session.query(Device).order_by(Device.nickname).filter(Device.watched.is_(True)).all()