def more_reviews(): if request.method == 'POST': views_amount = request.form.get('views_amount') print(views_amount) sql = '''SELECT reader.reader_name, reader.reader_id FROM reader WHERE reader_id IN (SELECT comment_by FROM review GROUP BY comment_by HAVING COUNT(comment_by) > '%s');''' % views_amount cursor = db.cursor() try: cursor.execute(sql) db.commit() answers = cursor.fetchall() print(answers) if answers: return render_template('show_readers_views.html', answers=answers, nav='reader', amount=views_amount) else: return render_template('more_views.html', error='no') except: db.rollback() return render_template('more_views.html', error='error') return render_template('more_views.html')
def create(account_id=None, application_name=None, application_guid=None, application_key=None, application_secret=None, application_algorithm=None, created_user_id=None, app_metadata=None, training_status=None): try: application = Applications(account_id=account_id, application_name=application_name, application_guid=application_guid, application_key=application_key, application_secret=application_secret, application_algorithm=application_algorithm, created_user_id=created_user_id, app_metadata=app_metadata, training_status=training_status) db.add(application) db.commit() return application.application_id except Exception as e: db.rollback() raise Exception(e.message)
def read_by_user(user_id=None, account_id=None): try: nerds = db.query(Nerds).filter(Nerds.users.any(user_id=user_id)). \ filter(Nerds.account_id.like(account_id)).all() return nerds except Exception as e: db.rollback() raise Exception(e.message)
def save(self): db.add(self) try: db.commit() except Exception as ex: db.rollback() db.add(self) db.commit()
def truncate_images(): """Remove all keywords""" try: db.query(Image).delete() db.query(Keyword).delete() db.commit() except sqlalchemy.exc.SQLAlchemyError as sqlexc: db.rollback() print(sqlexc)
def add_organization(self, name, manager='', status=8, domain='', extra=None, address='', phone='', website='', notes=''): """ creates an organization in the osticket database """ def get_org_id_from_name(): query_template = """ SELECT id FROM ost_organization WHERE name=%s """ cur = db.cursor() cur.execute(query_template, (name, )) org_id = cur.fetchone().get("id") cur.close() return {"organization_id": org_id} query_template = """ START TRANSACTION; BEGIN; INSERT INTO ost_organization (name, manager, status, domain, extra, created, updated) VALUES (%s, %s, %s, %s, %s, %s, %s); SET @OrgID = LAST_INSERT_ID(); INSERT INTO ost_organization__cdata (org_id, name, address, phone, website, notes) VALUES (@OrgID, %s, %s, %s, %s, %s); COMMIT; SELECT @OrgID; """ cur = db.cursor() cur.execute(query_template, (name, manager, status, domain, extra, datetime.now(), datetime.now(), name, address, phone, website, notes)) cur.close() try: db.commit() except Exception as exc: db.rollback() raise ServiceException(exc.message) return get_org_id_from_name()
def get_all_apps(): """ Get all apps :return: """ try: applications = db.query(Applications).all() return applications except Exception as e: db.rollback() raise Exception(e.message)
def set_passwd(self, old, new): try: user = db.session.query(User).get(current_user.name) if not check_password_hash(user.passwd, old): return False new_hash = generate_password_hash(new) user.passwd = new_hash db.session.commit() return True except: db.rollback() return False
def read_accounts_by_id_list(id_list=None): """ Reading the records from a table :param query: :return: """ try: accounts = db.query(Accounts) \ .filter(Accounts.account_id.in_(id_list)).all() return accounts except Exception as e: db.rollback() raise Exception(e.message)
def read(query=None): """ Reading the records from a table :param query: :return: """ try: accounts = db.query(Accounts) \ .filter_by(**query).all() return accounts except Exception as e: db.rollback() raise Exception(e.message)
def publish_details(): sql = "SELECT * FROM bookpubdetails" cursor = db.cursor() try: cursor.execute(sql) db.commit() answer = cursor.fetchall() if answer: return render_template('bookpubdetails.html', details=answer) else: return render_template('bookpubdetails.html', error='no') except: db.rollback() return render_template('bookpubdetails.html', error='error')
def insert_raw_news(raw_news_entity: RawNewsEntity): if raw_news_entity is None: logging.error(f'failed to create raw_news_entity') return logging.info(raw_news_entity) try: db.add(raw_news_entity) db.commit() except IntegrityError: db.rollback() logging.info(f'Duplicated {raw_news_entity.link}') except Exception as err: db.rollback() logging.error(f'failed to store raw_news_entity') logging.error(err)
def read(query=None): """ Reading the records from a table :param query: :return: """ try: users = db.query(Users)\ .filter_by(**query).all() assert isinstance(users, list) return users except Exception as e: db.rollback() raise Exception(e.message)
def delete(query=None): """ This methods deletes the record :param query: :return: """ try: db.query(Users).\ filter_by(**query).\ delete() db.commit() except Exception as e: db.rollback() raise Exception(e.message)
def read(query=None): """ Reading the records from a table :param query: :return: """ try: configuration = db.query(Configuration) \ .filter_by(**query).all() assert isinstance(configuration, list) return configuration except Exception as e: db.rollback() raise Exception(e.message)
def add_user(query, user): """ Adds users to an existing account :param query: :param user: :return: """ try: nerd = db.query(Nerds). \ filter_by(**query).one() nerd.users.append(user) db.commit() except Exception as e: db.rollback() raise Exception(e.message)
def add_user(query, user): """ Adds users to an existing account :param query: :param user: :return: """ try: account = db.query(Accounts). \ filter_by(**query).one() account.users.append(user) db.commit() except Exception as e: db.rollback() raise Exception(e.message)
def library_details(): sql = '''SELECT * FROM librarydetails''' cursor = db.cursor() try: cursor.execute(sql) db.commit() answers = cursor.fetchall() if answers: return render_template('library_details.html', answers=answers, nav='library-details') else: return render_template('more_views.html', error='no') except: db.rollback() return render_template('more_views.html', error='error')
def update(query=None, updated_value=None): """ This method update the account :param query: :param updated_value: :return: """ try: db.query(Accounts) \ .filter_by(**query) \ .update(updated_value) db.commit() except Exception as e: db.rollback() raise Exception(e.message)
def update(query=None, new_user=None): """ This method update the user :param query: :param new_user: :return: """ try: db.query(Users) \ .filter_by(**query) \ .update(new_user) db.commit() except Exception as e: db.rollback() raise Exception(e.message)
def add_bot(query, nerd): """ Adding applications to accounts :param query: :param bot: :return: """ try: account = db.query(Accounts). \ filter_by(**query).one() account.nerds.append(nerd) db.commit() except Exception as e: db.rollback() raise Exception(e.message)
def update(query=None, updated_value=None): """ This method update the account :param query: :param updated_value: :return: """ try: db.query(Nerds) \ .filter_by(**query) \ .update(updated_value) db.commit() except Exception as e: db.rollback() raise Exception(e.message)
def create(configuration_guid=None, bot_name=None, account_guid=None, plan_family=None, plan_metadata=None): try: configuration = Configuration(configuration_guid=None, bot_name=None, account_guid=None, plan_family=None, plan_metadata=None) db.add(configuration) db.commit() return configuration.application_id except Exception as e: db.rollback() raise Exception(e.message)
def make_apikey_inactive(self, api_key): """ marks the api key inactive so that one cannot use it """ query_template = """ UPDATE ost_api_key SET isactive=0, updated=%s WHERE apikey=%s """ cur = db.cursor() cur.execute(query_template, (datetime.now(), api_key)) cur.close() try: db.commit() except Exception as exc: db.rollback() raise ServiceException(exc.message)
def find_staffs(): if request.method == 'POST': staff_name = request.form.get('staff_name') sql = "SELECT * FROM librarydetails WHERE librarydetails.staff_name = '%s' ;" % staff_name cursor = db.cursor() try: cursor.execute(sql) db.commit() answer = cursor.fetchall() if answer: return render_template('show_staff.html', nav='library-details', details=answer) else: return render_template('find_staff.html', error='no') except: db.rollback() return render_template('find_staff.html', error='error') return render_template('find_staff.html')
def create(user_guid=None, email=None, password=None, first_name=None, last_name=None, is_system=False, company=None): try: user = Users(user_guid=user_guid, email=email, password=password, first_name=first_name, last_name=last_name, is_system=is_system, company=company) db.add(user) db.commit() return user except Exception as e: db.rollback() raise Exception(e.message)
def add_manager_to_organization(self, org_id, manager_id): """ adds a manager to the organization """ query_template = """ UPDATE ost_organization SET manager=%s, updated=%s WHERE id=%s """ cur = db.cursor() cur.execute(query_template, (manager_id, datetime.now(), org_id)) cur.close() try: db.commit() except Exception as exc: db.rollback() raise ServiceException(exc.message)
def query_books_by_author(): if request.method == 'POST': author_name = request.form.get('author_name') sql = "SELECT * FROM detailofbook WHERE detailofbook.author_name = '%s' ;" % author_name cursor = db.cursor() try: cursor.execute(sql) db.commit() answer = cursor.fetchall() if answer: return render_template('detailofbooks.html', nav='book-details', details=answer) else: return render_template('query_books_by_author.html', error='no') except: db.rollback() return render_template('query_books_by_author.html', error='error') return render_template('query_books_by_author.html')
def attach_user_to_organization(self, uid, org_id): """ attaches a user in the user directory to a particular organization """ if not org_service.organization_exists(org_id): raise ServiceException('No organization exists for given org_id') query_template = """ UPDATE ost_user SET org_id=%s, updated=%s WHERE id=%s """ cur = db.cursor() cur.execute(query_template, (org_id, datetime.now(), uid)) cur.close() try: db.commit() except Exception as exc: db.rollback() raise ServiceException(exc.message)
def create_apikey(self, ip_address, isactive=1, notes='', can_create_tickets=0, can_exec_cron=0): """ creates the api key uses the hardcoded values for can_create_tickets, can_exec_cron as 0 """ uid = uuid.uuid4() random_string = str(uid.hex[:16]) random_hex = hashlib.md5(random_string.encode("utf-8")).hexdigest() api_key = hashlib.md5( '{}.{}.{}'.format( time.time(), ip_address, random_hex ).encode('utf-8')).hexdigest().upper() query_template = """ INSERT INTO ost_api_key ( isactive, ipaddr, apikey, can_create_tickets, can_exec_cron, notes, updated, created ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) """ cur = db.cursor() cur.execute(query_template, (isactive, ip_address, api_key, can_create_tickets, can_exec_cron, notes, datetime.now(), datetime.now())) cur.close() try: db.commit() return api_key except Exception as exc: db.rollback() raise ServiceException(exc.message)
def create(user_guid=None, email=None, password=None, first_name=None, last_name=None, is_system=False, company=None ): try: user = Users(user_guid=user_guid, email=email, password=password, first_name=first_name, last_name=last_name, is_system=is_system, company=company) db.add(user) db.commit() return user except Exception as e: db.rollback() raise Exception(e.message)
def create(account_name=None, account_guid=None, account_type=None, company=None, is_active=True, is_deleted=False, owner=None): try: account = Accounts(account_name=account_name, account_guid=account_guid, account_type=account_type, company=company, is_active=is_active, is_deleted=is_deleted) if owner: account.users.append(owner) db.add(account) db.commit() return account.account_id except Exception as e: db.rollback() raise Exception(e.message)
def create(account=None, nerd_guid=None, nerd_name=None, nerd_url=None, is_deleted=False, is_active=True, user=None): try: nerd = Nerds( nerd_url=nerd_url, nerd_guid=nerd_guid, nerd_name=nerd_name, is_deleted=is_deleted, is_active=is_active, ) nerd.users.append(user) account.nerds.append(nerd) db.add(account) db.commit() except Exception as e: db.rollback() raise Exception(e.message)
def own_all(): cursor = db.cursor() sql = '''SELECT library.lib_name,lib_address FROM library WHERE NOT EXISTS ( SELECT * FROM book WHERE NOT EXISTS (SELECT * FROM collection WHERE collection.book_isbn = book.book_isbn AND collection.lib_id=library.lib_id));''' try: cursor.execute(sql) db.commit() answers = cursor.fetchall() if answers: return render_template('own_all.html', answers=answers) else: return render_template('own_all.html', error='no') except: db.rollback() return render_template('own_all.html', error='error')
def on_finish(self): db.close() db.rollback()
def rollback(): rollback(request.args.get(db_arg)) return success("reverted")
def save_new_review_form(db): occ_id = request.forms.occupancy_id if occ_id is None: raise HTTPError(400) occupancy = get_occupancy_to_review(db, occ_id) last_review = occupancy.review if 'delete' in request.forms: review = m.Review( occupancy=occupancy, published_at=datetime.now(), hidden=True ) else: sections = [] for key, value in request.forms.iteritems(): match = re.match(r'^section-(\d+)$', key) if match: heading_id = int(match.group(1)) try: heading = db.query(m.ReviewHeading).filter_by(id=heading_id).one() except NoResultFound: raise HTTPError(400) if value.strip(): sections += [ m.ReviewSection( heading=heading, content=value ) ] elif heading.is_summary: raise HTTPError(400, "{!r} section cannot be left blank".format(heading.name)) # validate the rating try: rating = int(request.forms.rating) except ValueError: raise HTTPError(404, "Rating must be an integer") if rating < 0 or rating > 10: raise HTTPError(404, "Rating must be between 0 and 10") review = m.Review( sections=sections, occupancy=occupancy, published_at=datetime.now(), rating=rating ) # if an edit occurred, record the editor if occupancy.resident != request.user: review.editor = request.user db.add(review) # check we haven't hit a double-post situation if last_review and review.contents_eq(last_review): raise HTTPError(400, "Same as last review") # TODO: fail gracefully db.rollback() flash_some_message() redirect_anyway() # look for references in the review import find_references for ref in find_references.scan_review(review): db.add(ref) redirect('/rooms/{}#review-{}'.format(occupancy.listing.room_id, review.id))