def editDepartment(dept_id):
try:
# retrieve dept to edit
dept_to_edit = session.query(Department).filter_by(id=dept_id).one()
departments = session.query(Department).order_by(asc(Department.name))
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404! Requested URL was not found."
# check if dept belongs to curr user
if dept_to_edit.user_id != login_session['user_id']:
flash("You do not have permissions to edit %s" % dept_to_edit.name)
return redirect(url_for('viewDepartment', dept_id=dept_to_edit.id))
if request.method == 'POST':
dept_to_edit.name = request.form['name']
dept_to_edit.address = request.form['address']
dept_to_edit.img_url = request.form['img_url']
dept_to_edit.description = request.form['description']
flash("You have successfully edited %s" % dept_to_edit.name)
session.commit()
return redirect(url_for('viewDepartment', dept_id=dept_id))
else:
return render_template("new_department.html",
departments=departments,
name=dept_to_edit.name,
address=dept_to_edit.address,
img_url=dept_to_edit.img_url,
description=dept_to_edit.description,
edit=True)
def newDepartment():
if request.method == 'POST':
name = request.form['name']
address = request.form['address']
img_url = request.form['img_url']
description = request.form['description']
# create new department and commit to DB
try:
newDepartment = Department(name=name,
address=address,
img_url=img_url,
description=description,
user_id=login_session['user_id'])
session.add(newDepartment)
session.commit()
# handle database exceptions
except (DBAPIError, SQLAlchemyError) as e:
flash("An exception occurred in the database. Please try again!")
return redirect(url_for('showDepartments'))
else:
flash("Successfully created new department %s" %
newDepartment.name)
return redirect(url_for('showDepartments'))
else:
departments = session.query(Department).order_by(asc(Department.name))
return render_template("new_department.html", departments=departments)
def deleteDepartment(dept_id):
try:
# retrieve dept to delete
dept_to_delete = session.query(Department).filter_by(id=dept_id).one()
departments = session.query(Department).order_by(asc(Department.name))
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404! Requested URL was not found."
# check if dept belongs to curr user
if dept_to_delete.user_id != login_session['user_id']:
flash("You do not have permissions to delete %s" % dept_to_delete.name)
return redirect(url_for('viewDepartment', dept_id=dept_to_delete.id))
if request.method == 'POST':
# delete courses of this dept
dept_courses = session.query(Course).filter_by(department_id=dept_id)
for course in dept_courses:
session.delete(course)
# delete dept
session.delete(dept_to_delete)
flash("You have successfully deleted '%s'" % dept_to_delete.name)
session.commit()
return redirect(url_for('showDepartments'))
else:
return render_template("delete_dept.html",
departments=departments,
name=dept_to_delete.name)
def deleteCourse(dept_id, course_id):
# retrieve course for given dept from DB
try:
departments = session.query(Department).order_by(asc(Department.name))
curr_dept = session.query(Department).filter_by(id=dept_id).one()
dept_courses = session.query(Course).filter_by(department_id=dept_id)
curr_course = session.query(Course).filter(
and_(Course.id == course_id,
Course.department_id == dept_id)).one()
# handle database exceptions
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404, requested URL was not found! Exception occured in Database."
# check if course belongs to current user
if curr_course.user_id != login_session['user_id']:
flash("You do not have permissions to delete this course.")
return redirect(
url_for('viewCourse', dept_id=dept_id, course_id=course_id))
if request.method == 'POST':
session.delete(curr_course)
flash("You have successfully deleted %s - %s" %
(curr_course.id, curr_course.name))
session.commit()
return redirect(url_for('viewDepartment', dept_id=dept_id))
else:
return render_template("delete_course.html",
departments=departments,
id=curr_course.id,
name=curr_course.name)
def createUser(login_session):
newUser = User(name=login_session['username'],
email=login_session['email'],
picture=login_session['picture'])
session.add(newUser)
session.commit()
user = session.query(User).filter_by(email=login_session['email']).one()
return user.id
def failed(self, message):
self.processing = False
self.error = message
self.tries_left = self.tries_left - 1
self.next_try_at = datetime.now() + timedelta(seconds=1)
session.add(self)
session.commit()
def init_pin_type():
session.execute("insert or replace into io_mode values(:id, :name)", {
"id": 0,
"name": "Input"
})
session.execute("insert or replace into io_mode values(:id, :name)", {
"id": 1,
"name": "Output"
})
session.commit()
def fill_function():
try:
session.execute("delete from function")
for function in functions.iterkeys():
session.execute(
"insert or replace into function values(:id, :name)", {
"id": hash(function),
"name": function
})
session.commit()
except Exception as e:
session.rollback()
print e
def init_room():
session.execute("insert or replace into room values(:id, :name)", {
"id": 100,
"name": "Kitchen"
})
session.execute("insert or replace into room values(:id, :name)", {
"id": 110,
"name": "Living"
})
session.execute("insert or replace into room values(:id, :name)", {
"id": 120,
"name": "Bathroom"
})
session.commit()
def create_new_task():
'''Create new task'''
# TODO: avoid duplicate
data = request.get_json()
url = correct_url(data['url'])
logger.info('Creating new task with data {}'.format(data))
task = CareTask(name=data.get('name'),
url=url,
interval=data['interval'],
roi=data.get('roi'))
session.add(task)
session.commit()
rand_snapshot = data.get('snapshot')
if rand_snapshot:
os.rename('../snapshot/{}'.format(rand_snapshot),
'../snapshot/{}-0.png'.format(task.id))
return 'success'
def next(klass):
query = """
UPDATE %s SET
processing = true,
tries_left = tries_left - 1,
error = NULL,
next_try_at = NULL,
updated_at = CURRENT_TIMESTAMP
WHERE id = (
SELECT id
FROM %s
WHERE tries_left > 0
AND (
next_try_at IS NULL OR
next_try_at < CURRENT_TIMESTAMP
)
AND (
processing = false OR
updated_at < CURRENT_TIMESTAMP - INTERVAL '5 SEC'
)
ORDER BY priority ASC, next_try_at ASC, id ASC
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *
""" % (klass.__tablename__, klass.__tablename__)
try:
rs = session.execute(query).fetchone()
session.commit()
if rs:
result = session.query(PaymentTask).filter_by(
id=rs['id']).one()
return result
else:
return None
except NoResultFound:
print('No task to update')
def init_pin_function():
session.execute(
"insert or replace into pin_function values(:id, :type_id, :name)", {
"id": 0,
"type_id": 0,
"name": "Button"
})
session.execute(
"insert or replace into pin_function values(:id, :type_id, :name)", {
"id": 1,
"type_id": 0,
"name": "Dimmer"
})
session.execute(
"insert or replace into pin_function values(:id, :type_id, :name)", {
"id": 20,
"type_id": 1,
"name": "Light"
})
session.execute(
"insert or replace into pin_function values(:id, :type_id, :name)", {
"id": 21,
"type_id": 1,
"name": "Light dimmered"
})
session.execute(
"insert or replace into pin_function values(:id, :type_id, :name)", {
"id": 22,
"type_id": 1,
"name": "Socket"
})
session.execute(
"insert or replace into pin_function values(:id, :type_id, :name)", {
"id": 23,
"type_id": 1,
"name": "Balcony"
})
session.commit()
def perform(self):
debit = self.payment.product.price
balance = self.user.balance
balance -= debit
if balance < 0:
message = f'''
User doesn't have enought credits.
product price: {debit},
current balance: {self.user.balance}
'''
if self.tries_left > 0:
self.failed(message)
print('payment failed or incomplete')
return
else:
self.user.balance = balance
try:
session.add(self.user)
session.commit()
return
except Exception as ex:
print(str(ex))
divs_lei = BS.find_all("div", {"class": "lei"})
for div in divs_lei:
big_rank = "D"
if ("A" in div.text):
big_rank = "A"
elif ("B" in div.text):
big_rank = "B"
elif ("C" in div.text):
big_rank = "C"
else:
continue
table = div.next_sibling.next_sibling
rows = table.find_all("tr")
for row in rows[1:]:
tds = row.find_all("td")
data = [x.text.strip() for x in tds]
new_venue = Venue()
new_venue.small_rank = int(data[0])
new_venue.abb = data[1]
new_venue.name = data[2]
new_venue.publishing_house = data[3]
new_venue.url = data[4]
new_venue.big_rank = big_rank
try:
session.add(new_venue)
# print("Add: [%s:%s]" % (new_venue.abb,str(new_venue.big_rank)+str(new_venue.small_rank)))
session.commit()
except Exception as e:
session.rollback()
print(e)
def destroy(self):
try:
session.delete(self)
session.commit()
except Exception as ex:
print(str(ex))
logging.basicConfig()
logger = logging.getLogger("db_query")
logger.setLevel(logging.DEBUG)
def query_task():
task = (
session.query(Task)
.join(Story)
.join(User)
.filter(Story.story_title == "Story 001")
.filter(User.user_name == "User 003")
).one()
logger.info("Task in Story 001 and owned by User 003 is " + task.task_title)
return task
# Query one task
task = query_task()
# Update the task and query again
task.task_title = "Changed Task Title"
session.commit()
query_task()
# Update the task back and query again
task.task_title = "Task 003"
session.commit()
query_task()
def editCourse(dept_id, course_id):
# retrieve course for given dept from DB
try:
departments = session.query(Department).order_by(asc(Department.name))
curr_dept = session.query(Department).filter_by(id=dept_id).one()
dept_courses = session.query(Course).filter_by(department_id=dept_id)
curr_course = session.query(Course).filter(
and_(Course.id == course_id,
Course.department_id == dept_id)).one()
# handle database exceptions
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404, requested URL was not found! Exception occured in Database."
# check if course belongs to current user
if curr_course.user_id != login_session['user_id']:
flash("You do not have permissions to edit this course.")
return redirect(
url_for('viewCourse', dept_id=dept_id, course_id=course_id))
if request.method == 'POST':
name = request.form['name']
id = request.form['id']
professor = request.form['professor']
img_url = request.form['img_url']
credits = request.form['credits']
max_capacity = request.form['max_capacity']
description = request.form['description']
fields = [
departments, curr_dept, name, id, professor, img_url, credits,
max_capacity, description
]
# check if course code already exists
# check only if new id is different from old id
id_exists = (id != course_id) and session.query(Course).filter_by(
id=id).count() > 0
if id_exists:
error = "This course code already exists!"
return report_error(fields, error)
# validate course code length
if len(id) > 6:
error = "Course code cannot be more than 6 characters!"
return report_error(fields, error)
# validate credits
try:
int_credits = int(credits)
if int_credits > 5:
raise ValueError
except ValueError:
error = "Please enter a valid number of credits"
return report_error(fields, error)
# validate capacity
try:
int_capacity = int(max_capacity)
except ValueError:
error = "Please enter a valid integer for capacity."
return report_error(fields, error)
curr_course.name = name
curr_course.id = id
curr_course.professor = professor
curr_course.img_url = img_url
curr_course.credits = credits
curr_course.max_capacity = max_capacity
curr_course.description = description
session.commit()
flash("You have successfully edited %s" % curr_course.name)
return redirect(
url_for('viewCourse', dept_id=dept_id, course_id=curr_course.id))
else:
return render_template("new_course.html",
departments=departments,
curr_dept=curr_dept,
name=curr_course.name,
id=curr_course.id,
professor=curr_course.professor,
img_url=curr_course.img_url,
credits=curr_course.credits,
max_capacity=curr_course.max_capacity,
description=curr_course.description,
edit=True)
