def create(self, expense: Expense) -> bool:
cur = None
category = expense.category
description = expense.description
exp_type = expense.exp_type
cost = expense.cost
date = expense.date
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur.execute(
"INSERT INTO expenses (category, description, type, cost, date) VALUES (?,?,?,?,?)",
(category, description, exp_type, cost, date))
self.sql_connection.commit()
cur.close()
self.sql_connection.close()
return True
except Exception as e:
print(e)
self.sql_connection.rollback()
cur.close()
self.sql_connection.close()
return False
def get_employee_positions(self) -> list:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur = self.sql_connection.execute(
'SELECT DISTINCT position FROM employees')
positions = [position[0] for position in cur.fetchall()]
cur.close()
self.sql_connection.close()
return positions
except Exception as e:
print(e)
cur.close()
self.sql_connection.close()
def read(self, student_id) -> Student:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur.execute(
"SELECT studentID,name,grade,age "
"FROM students "
"WHERE studentID = ?", (student_id, ))
row = cur.fetchone()
cur.close()
self.sql_connection.close()
return Student(row[1], row[2], row[3])
except Exception as e:
print(e)
self.sql_connection.close()
def read(self, expense_id: int) -> Expense:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur.execute(
"SELECT expenseID, category, description, type, cost, date "
"FROM expenses "
"WHERE expenseID = ?", (expense_id, ))
row = cur.fetchone()
cur.close()
self.sql_connection.close()
return Expense(row[1], row[3], row[4], row[5], row[2])
except Exception as e:
print(e)
self.sql_connection.close()
def get_table_fields(self) -> list:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur = self.sql_connection.execute(
'SELECT name, position, salary, joindate FROM employees')
fields = [description[0] for description in cur.description]
cur.close()
self.sql_connection.close()
return fields
except Exception as e:
print(e)
cur.close()
self.sql_connection.close()
def read(self, employee_id: int) -> list:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur.execute(
"SELECT employeeID,name,position,salary,joindate "
"FROM employees "
"WHERE employeeID = ?", (employee_id, ))
row = cur.fetchone()
cur.close()
self.sql_connection.close()
return Employee(row[1], row[2], row[3], row[4])
except Exception as e:
print(e)
self.sql_connection.close()
def get_table_fields(self) -> list:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur = self.sql_connection.execute(
'SELECT name,age,grade FROM students')
fields = [description[0] for description in cur.description]
cur.close()
self.sql_connection.close()
return fields
except Exception as e:
print(e)
cur.close()
self.sql_connection.close()
def get_table_fields(self) -> list:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur = self.sql_connection.execute(
'SELECT category, description, type, cost, date FROM expenses')
fields = [description[0] for description in cur.description]
cur.close()
self.sql_connection.close()
return fields
except Exception as e:
print(e)
cur.close()
self.sql_connection.close()
def delete(self, student_id) -> bool:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur.execute("DELETE FROM students WHERE studentID = ?",
(student_id, ))
self.sql_connection.commit()
self.sql_connection.close()
return True
except Exception as e:
print(e)
self.sql_connection.rollback()
cur.close()
self.sql_connection.close()
return False
def delete(self, expense_id: int) -> bool:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur.execute("DELETE FROM expenses WHERE expenseID = ?",
(expense_id, ))
self.sql_connection.commit()
self.sql_connection.close()
return True
except Exception as e:
print(e)
self.sql_connection.rollback()
cur.close()
self.sql_connection.close()
return False
def update(self, student, student_id) -> bool:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur.execute(
"UPDATE students "
"SET name= ?, grade = ?, age = ? "
"WHERE studentID = ?",
(student.name, student.grade, student.age, student_id))
self.sql_connection.commit()
self.sql_connection.close()
return True
except Exception as e:
print(e)
self.sql_connection.rollback()
cur.close()
self.sql_connection.close()
return False
def update(self, employee: Employee, employee_id: int) -> bool:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur.execute(
"UPDATE employees "
"SET name= ?, position = ?, salary =? , joindate = ? "
"WHERE employeeID = ?",
(employee.name, employee.position, employee.salary,
employee.join_date, employee_id))
self.sql_connection.commit()
self.sql_connection.close()
return True
except Exception as e:
print(e)
self.sql_connection.rollback()
cur.close()
self.sql_connection.close()
return False
def update(self, expense: Expense, expense_id: int) -> bool:
cur = None
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur.execute(
"UPDATE expenses "
"SET category = ?, description = ?, type = ?, cost = ?, date = ? "
"WHERE expenseID = ?",
(expense.category, expense.description, expense.exp_type,
expense.cost, expense.date, expense_id))
self.sql_connection.commit()
self.sql_connection.close()
return True
except Exception as e:
print(e)
self.sql_connection.rollback()
cur.close()
self.sql_connection.close()
return False
def __init__(self, host, port):
super(Server, self).__init__()
# Start host
self.host = SimpleHost()
self.host.startup(host, port)
self.host.db_manager = DBManager(conf.DB_NAME)
self.host.room_manager = RoomManager(self.host)
self.user_services = UserServices(self.host)
# rooms
self.rooms = []
# Generate dispatcher
self.dispatcher = Dispatcher()
self.register_dispatcher_services()
# map msg_type to message object
self.msg_dict = None
self.generate_msg_dict()
def create(self, student: Student) -> bool:
cur = None
name = student.name
grade = student.grade
age = student.age
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur.execute(
"INSERT INTO students (name, grade, age) VALUES (?,?,?)",
(name, grade, age))
self.sql_connection.commit()
cur.close()
self.sql_connection.close()
return True
except Exception as e:
print(e)
self.sql_connection.rollback()
cur.close()
self.sql_connection.close()
return False
def create(self, employee: Employee) -> bool:
cur = None
name = employee.name
position = employee.position
salary = employee.salary
join_date = employee.join_date
try:
self.sql_connection = DBManager.create_connection()
cur = self.sql_connection.cursor()
cur.execute(
"INSERT INTO employees (name, position, salary, joindate) VALUES (?,?,?,?)",
(name, position, salary, join_date))
self.sql_connection.commit()
cur.close()
self.sql_connection.close()
return True
except Exception as e:
print(e)
self.sql_connection.rollback()
cur.close()
self.sql_connection.close()
return False
def __init__(self):
self.sql_connection = DBManager.create_connection()