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)