def update_release(release_codename, **kwargs): release = (db_session.query(Release).filter( Release.codename == release_codename).one_or_none()) if not release: return make_response( jsonify({"message": f"Release {release_codename} doesn't exist"}), 404, ) release_data = request.json release.version = release_data["version"] release.name = release_data["name"] release.development = release_data["development"] release.lts = release_data["lts"] release.release_date = release_data["release_date"] release.esm_expires = release_data["esm_expires"] release.support_expires = release_data["support_expires"] db_session.add(release) try: db_session.commit() except IntegrityError as error: return make_response( jsonify({ "message": "Failed updating release", "error": error.orig.args[0], }), 422, ) return make_response(jsonify({"message": "Release updated"}), 200)
def saveAll(cls, dataList): for d in dataList: cck = cls(**d) db_session.add(cck) db_session.commit() db_session.close()
def detail_article(article_id): blog = Blog.query.get(article_id) db_session.query(Blog).filter(Blog.id==article_id).\ update({Blog.count_scan: Blog.count_scan+1}) db_session.commit() category = get_blog_category() return render_template('articledetail.html', article=blog, categorys=category)
def insert_article(userid, title, content, type, author, img): category_id = Blog_category.query.filter( Blog_category.category_name == type).first().id blog = Blog(title, content, category_id) blog.author = author blog.img = img blog.authorid = userid db_session.add(blog) db_session.commit()
def create_notice(**kwargs): notice_data = request.json db_session.add( _update_notice_object(Notice(id=notice_data["id"]), notice_data)) db_session.commit() return make_response(jsonify({"message": "Notice created"}), 200)
def write_match_log(db_session, match_job_id, upload_id, match_start_at, match_complete_at, match_status, match_runtime): db_object = MatchLog(id=match_job_id, upload_id=upload_id, match_start_timestamp=match_start_at, match_complete_timestamp=match_complete_at, match_status=match_status, runtime=match_runtime) db_session.add(db_object) db_session.commit()
def register(): form = RegisterForm(request.form) if form.validate_on_submit(): u = User(form.name.data, form.email.data, generate_password_hash(form.password.data)) db_session.add(u) db_session.commit() return redirect(url_for('auth.signin')) return render_template("auth/register.html", form=form)
def new_user(): username = request.json.get('username') password = request.json.get('password') if username is None or password is None: return jsonify({'message': '1st'}), 400 result = Agents.query.filter_by(username=username).first() if Agents.query.filter_by(username=username).first() is not None: return jsonify({'message': 'username already being used'}), 400 agent = Agents(username=username) agent.hash_password(password) session.add(agent) session.commit() return jsonify({'username': username})
def delete_cve(cve_id): cve = db_session.query(CVE).filter(CVE.id == cve_id.upper()).one_or_none() if not cve: return make_response( jsonify({"message": f"CVE {cve_id} doesn't exist"}), 404, ) db_session.delete(cve) db_session.commit() return make_response( jsonify({"message": f"CVE with id '{cve_id}' was deleted"}), 200)
def delete_notice(notice_id): notice = (db_session.query(Notice).filter( Notice.id == notice_id).one_or_none()) if not notice: return make_response( jsonify({"message": f"Notice {notice_id} doesn't exist"}), 404, ) db_session.delete(notice) db_session.commit() return make_response(jsonify({"message": f"Notice {notice_id} deleted"}), 200)
def register(): if request.method == 'POST': username = request.json.get('username') password = request.json.get('password') hostname = request.json.get('hostname') windows_user = request.json.get('windows_user') if session.query(Agents).filter_by(username=username).first(): return jsonify({'error': 'username already taken'}), 400 new_agent = Agents(username=username, hostname=hostname, windows_user=windows_user) new_agent.hash_password(password) session.add(new_agent) session.commit() return jsonify({'username': username})
def update_notice(notice_id, **kwargs): notice = (db_session.query(Notice).filter( Notice.id == notice_id).one_or_none()) if not notice: return make_response( jsonify({"message": f"Notice '{notice_id}' doesn't exist"}), 404, ) notice = _update_notice_object(notice, request.json) db_session.add(notice) db_session.commit() return make_response(jsonify({"message": "Notice updated"}), 200)
def create_release(**kwargs): release_data = request.json release = Release( codename=release_data["codename"], version=release_data["version"], name=release_data["name"], development=release_data["development"], lts=release_data["lts"], release_date=release_data["release_date"], esm_expires=release_data["esm_expires"], support_expires=release_data["support_expires"], ) db_session.add(release) db_session.commit() return make_response(jsonify({"message": "Release created"}), 200)
def upsert_raw_table_to_master(raw_table_name, jurisdiction, event_type, upload_id, db_session): create_merged_table(jurisdiction, event_type, db_session) master_table_name = generate_master_table_name(jurisdiction, event_type) goodtables_schema = load_schema_file(event_type) base_column_list = column_list_from_goodtables_schema(goodtables_schema) # use new postgres 'on conflict' functionality to upsert update_statements = [ ' "{column}" = EXCLUDED."{column}"'.format(column=column_def[0]) for column_def in base_column_list ] start_ts = datetime.today() insert_sql = ''' insert into {master} select raw.*, '{new_ts}' inserted_ts, '{new_ts}' updated_ts, row_number() over ()::text || '{event_type}' as matched_id from "{raw}" as raw on conflict ({primary_key}) do update set {update_string}, updated_ts = '{new_ts}' '''.format(raw=raw_table_name, master=master_table_name, event_type=event_type, primary_key=', '.join([ "\"{}\"".format(col) for col in goodtables_schema['primaryKey'] ]), update_string=', '.join(update_statements), new_ts=start_ts.isoformat()) logging.info('Executing insert: %s', insert_sql) db_session.execute(insert_sql) end_ts = datetime.today() merge_log = MergeLog( upload_id=upload_id, total_unique_rows=total_unique_rows(raw_table_name, goodtables_schema['primaryKey'], db_session), new_unique_rows=new_unique_rows(master_table_name, start_ts, db_session), merge_start_timestamp=start_ts, merge_complete_timestamp=end_ts, ) db_session.add(merge_log) db_session.execute('drop table "{}"'.format(raw_table_name)) db_session.commit() return merge_log.id
def merge_file(): upload_id = request.args.get('uploadId', None) if not upload_id: return jsonify(status='invalid', reason='uploadId not present') has_access = False try: has_access = can_access_file(upload_id) if has_access: upload_log = db_session.query(Upload).get(upload_id) logger.info('Retrieved upload log, merging raw table to master') raw_table_name = 'raw_{}'.format(upload_id) logger.info('Merging raw table to master') merge_id = upsert_raw_table_to_master(raw_table_name, upload_log.jurisdiction_slug, upload_log.event_type_slug, upload_id, db_session) logger.info('Syncing merged file to s3') bootstrap_master_tables(upload_log.jurisdiction_slug, db_session) sync_merged_file_to_storage(upload_log.jurisdiction_slug, upload_log.event_type_slug, db_session.get_bind()) merge_log = db_session.query(MergeLog).get(merge_id) try: logger.info('Merge succeeded. Now querying matcher') notify_matcher(upload_log.jurisdiction_slug, upload_id) except Exception as e: logger.error('Error matching: ', e) db_session.rollback() return make_response(jsonify(status='error'), 500) db_session.commit() return jsonify(status='success', new_unique_rows=merge_log.new_unique_rows, total_unique_rows=merge_log.total_unique_rows) else: return jsonify(status='not authorized') except ValueError as e: logger.error('Error merging: ', e) db_session.rollback() return make_response(jsonify(status='error'), 500)
def write_upload_log(db_session, upload_id, jurisdiction_slug, event_type_slug, user_id, given_filename, upload_start_time, upload_complete_time, upload_status, validate_start_time, validate_complete_time, validate_status, num_rows, file_size, file_hash, s3_upload_path): db_object = Upload(id=upload_id, jurisdiction_slug=jurisdiction_slug, event_type_slug=event_type_slug, user_id=user_id, given_filename=given_filename, upload_start_time=upload_start_time, upload_complete_time=upload_complete_time, upload_status=upload_status, validate_start_time=validate_start_time, validate_complete_time=validate_complete_time, validate_status=validate_status, num_rows=num_rows, file_size=file_size, file_hash=file_hash, s3_upload_path=s3_upload_path) db_session.add(db_object) db_session.commit()
def delete_release(release_codename): release = (db_session.query(Release).filter( Release.codename == release_codename).one_or_none()) if not release: return make_response( jsonify({"message": f"Release {release_codename} doesn't exist"}), 404, ) if len(release.statuses) > 0: return ( jsonify({ "message": (f"Cannot delete '{release_codename}' release. " f"Release already in use") }), 400, ) db_session.delete(release) db_session.commit() return make_response( jsonify({"message": f"Release {release_codename} deleted"}), 200)
def test_download_file(self): with full_rig_with_s3() as (app, engine): # Create matched jail_bookings table_name = 'jail_bookings' create_and_populate_master_table(table_name, engine, MATCHED_BOOKING_FILE) # Create matched hmis_service_stays table_name = 'hmis_service_stays' create_and_populate_master_table(table_name, engine, MATCHED_HMIS_FILE) db_session.commit() response = app.get( '/api/chart/download_source?jurisdiction=boone&eventType=jail_bookings' ) assert response.status_code == 200 assert response.headers[ "Content-Disposition"] == "attachment; filename=jail_bookings.csv" assert response.headers["Content-type"] == "text/csv" data = response.get_data() reader = csv.reader(BytesIO(data)) with open(MATCHED_BOOKING_FILE, 'rb') as source_file: source_reader = csv.reader(source_file) for returned_row, expected_row in zip(reader, source_reader): assert returned_row == expected_row
def get_validated_result(job_key): job = Job.fetch(job_key, connection=get_redis_connection()) if job.is_failed: logger.error(job.exc_info) return jsonify( format_error_report( 'System error. The error has been logged, please try again later' )) if not job.is_finished: return jsonify({ 'validation': { 'jobKey': job_key, 'status': 'validating', 'message': 'Still validating data!' }, 'upload_result': { 'status': 'not yet', } }) result = job.result if 'validation' in result and 'upload_result' in result: return jsonify(result) validation_report = result['validation_report'] event_type = result['event_type'] filename_with_all_fields = result['filename_with_all_fields'] if validation_report['valid']: upload_id = job.meta['upload_id'] row_count = validation_report['tables'][0]['row-count'] - 1 sample_rows, field_names = get_sample(filename_with_all_fields) db_session.commit() return jsonify({ 'validation': { 'status': 'valid', 'jobKey': job_key, }, 'upload_result': { 'status': 'done', 'rowCount': row_count, 'exampleRows': sample_rows, 'fieldOrder': field_names, 'uploadId': upload_id } }) else: db_session.commit() return jsonify({ 'validation': { 'jobKey': job_key, 'status': 'invalid' }, 'upload_result': { 'status': 'done', 'rowCount': '', 'fieldOrder': [], 'errorReport': format_validation_report(validation_report, event_type), 'upload_id': '' } })
def validate_async(uploaded_file_name, jurisdiction, full_filename, event_type, flask_user_id, upload_id): validate_start_time = datetime.today() sync_upload_metadata_partial = partial(sync_upload_metadata, upload_id=upload_id, event_type=event_type, jurisdiction=jurisdiction, flask_user_id=flask_user_id, given_filename=uploaded_file_name, local_filename=full_filename, db_session=db_session) try: # 1. validate header validate_header(event_type, full_filename) # 2. preprocess file filename_with_all_fields = add_missing_fields(event_type, full_filename) # 3. validate body body_validation_report = two_pass_validation(event_type, filename_with_all_fields) validate_complete_time = datetime.today() if not body_validation_report['valid']: sync_upload_metadata_partial( validate_start_time=validate_start_time, validate_complete_time=validate_complete_time, validate_status=False, ) return { 'validation_report': body_validation_report, 'event_type': event_type, 'jurisdiction': jurisdiction, 'filename_with_all_fields': filename_with_all_fields, 'uploaded_file_name': uploaded_file_name, 'full_filename': full_filename } try: # 4. upload to s3 upload_start_time = datetime.today() final_upload_path = upload_path(jurisdiction, event_type, upload_id) upload_to_storage(final_upload_path, filename_with_all_fields) # 5. load into raw table copy_raw_table_to_db(final_upload_path, event_type, upload_id, db_session.get_bind()) upload_complete_time = datetime.today() # 6. sync upload metadata to db sync_upload_metadata_partial( s3_upload_path=final_upload_path, validate_start_time=validate_start_time, validate_complete_time=validate_complete_time, validate_status=True, upload_start_time=upload_start_time, upload_complete_time=upload_complete_time, upload_status=True) except ValueError as e: sync_upload_metadata_partial( validate_start_time=validate_start_time, validate_complete_time=validate_complete_time, validate_status=False, upload_start_time=upload_start_time, upload_status=False, ) body_validation_report = { 'valid': False, 'tables': [{ 'headers': [], 'errors': [{ 'column-number': None, 'row-number': None, 'message': str(e) }] }] } db_session.commit() return { 'validation_report': body_validation_report, 'event_type': event_type, 'jurisdiction': jurisdiction, 'filename_with_all_fields': filename_with_all_fields, 'uploaded_file_name': uploaded_file_name, 'full_filename': full_filename } except ValueError as e: sync_upload_metadata_partial(validate_start_time=validate_start_time, validate_status=False) db_session.commit() return format_error_report(str(e))
def saveOne(self): db_session.add(self) db_session.commit() db_session.close()
def delete(self): db_session.delete(self) db_session.commit()
def save(self): db_session.add(self) db_session.commit()
def user_register(user): db_session.add(user) db_session.commit()
def insert_comment(userid, username, content, article_id): com = Comment(userid, username, content, article_id) db_session.add(com) db_session.commit()
def bulk_upsert_cve(*args, **kwargs): cves_data = request.json if len(cves_data) > 50: return make_response( jsonify({ "message": ("Please only submit up to 50 CVEs at a time. " f"({len(cves_data)} submitted)") }), 413, ) packages = {} for package in db_session.query(Package).all(): packages[package.name] = package for data in cves_data: update_cve = False cve = db_session.query(CVE).get(data["id"].upper()) if cve is None: update_cve = True cve = CVE(id=data["id"]) if cve.status != data.get("status"): update_cve = True cve.status = data.get("status") published_date = (cve.published.strftime("%Y-%B-%d") if cve.published else None) data_published_date = (data.get("published").strftime("%Y-%B-%d") if data.get("published") else None) if published_date != data_published_date: update_cve = True cve.published = data.get("published") if cve.priority != data.get("priority"): update_cve = True cve.priority = data.get("priority") if cve.cvss3 != data.get("cvss3"): update_cve = True cve.cvss3 = data.get("cvss3") if cve.description != data.get("description"): update_cve = True cve.description = data.get("description") if cve.ubuntu_description != data.get("ubuntu_description"): update_cve = True cve.ubuntu_description = data.get("ubuntu_description") if cve.notes != data.get("notes"): update_cve = True cve.notes = data.get("notes") if cve.references != data.get("references"): update_cve = True cve.references = data.get("references") if cve.bugs != data.get("bugs"): update_cve = True cve.bugs = data.get("bugs") if cve.patches != data.get("patches"): update_cve = True cve.patches = data.get("patches") if cve.tags != data.get("tags"): update_cve = True cve.tags = data.get("tags") if cve.mitigation != data.get("mitigation"): update_cve = True cve.mitigation = data.get("mitigation") if update_cve: db_session.add(cve) _update_statuses(cve, data, packages) created = defaultdict(lambda: 0) updated = defaultdict(lambda: 0) deleted = defaultdict(lambda: 0) for item in db_session.new: created[type(item).__name__] += 1 for item in db_session.dirty: updated[type(item).__name__] += 1 for item in db_session.deleted: deleted[type(item).__name__] += 1 try: db_session.commit() except DataError as error: return make_response( jsonify({ "message": "Failed bulk upserting session", "error": error.orig.args[0], }), 400, ) return make_response( jsonify({ "created": created, "updated": updated, "deleted": deleted }), 200, )