def insert_one(self, record): try: db_session.add(record) db_session.commit() except Exception: db_session.rollback() raise
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 execute(self,args,diagnostics): print("Setting kernel test data to 0") diagnostic = Diagnostic() diagnostic.data = 0 diagnostics["counter"] = diagnostic range_arg = RangeArgument(range(0,15),0) args["range"] = range_arg db_session.add(diagnostic) db_session.add(range_arg) db_session.commit()
def insert_debug(msg): try: print(msg.topic + " " + str(msg.qos) + " " + str(msg.payload)) m_in = msg.payload.decode("UTF-8").split(";") if isinstance(m_in, list) and len(m_in) == 3: db_session.add( SensorDebug(device_id=m_in[0], code=m_in[1], message=m_in[2])) db_session.commit() else: print("Debug Message is not with the right format") except Exception as e: print(f"error trying to insert {m_in}")
def generate(email, password): user = UserRepository().get_by(email, as_dict=False) if not isinstance(user, User): return user if not user.valid_password(password): return {"message": "invalid password"}, 403 valid_until = str(datetime.now() + timedelta(days=180)) user.auth_token = {"email": email, "valid_until": valid_until} db_session.add(user) db_session.commit() return {"token": user.auth_token.decode()}
def user_register(): if request.method.__eq__('POST'): data = request.get_json() if data is None or data["email"] is None or data["password"] is None: response = { "status": Fail, "timestamp": stamp(), "reason": "data is missing!" } resp = make_response(response) resp.status_code = 403 return resp user_name = data['name'] user_email = data['email'] hashed_password = generate_password_hash(data['password'], method='sha256') optional_user = Users.query.filter_by(email=user_email).first() if optional_user is None: new_user = Users(user_name, user_email, hashed_password) new_user.token = new_user.encode_auth_token(new_user.id) db_session.add(new_user) db_session.commit() session['logged_in'] = True response = { "status": Success, "timestamp": stamp() } resp = make_response(response) resp.headers['Authorization'] = new_user.token resp.status_code = 201 return resp else: response = { "status": Fail, "timestamp": stamp(), "reason": "user already exists!" } resp = make_response(response) resp.status_code = 406 return resp response = { "status": Fail, "timestamp": stamp(), "reason": "unable to process request!" } resp = make_response(response) resp.status_code = 405 return resp
def user_login(): if request.method.__eq__('POST'): data = request.get_json() if data is None or len(data) == 0: response = { "status": Fail, "timestamp": stamp(), "reason": "data is missing!" } resp = make_response(response) resp.status_code = 403 return resp user_email = data['email'] user_password = data['password'] optional_user = Users.query.filter_by(email=user_email).first() if optional_user and check_password_hash(optional_user.password, user_password): session['logged_in'] = True auth_token = optional_user.encode_auth_token(optional_user.id) optional_user.token = auth_token assert optional_user.token == auth_token db_session.add(optional_user) db_session.commit() response = { "status": Success, "timestamp": stamp() } resp = make_response(response) resp.headers['Authorization'] = optional_user.token resp.status_code = 200 return resp else: response = { "status": Fail, "timestamp": stamp(), "reason": "unauthorized! email / password is not correct" } resp = make_response(response) resp.status_code = 401 return resp response = { "status": Fail, "timestamp": stamp(), "reason": "Unable to verify WWW-Authenticate: Basic realm 'login realm'" } resp = make_response(response) resp.status_code = 405 return resp
def insert(self, email, password): user = User.query.filter(User.email == email) if user.count(): return {"message": "email or passowrd incorrect"}, 400 try: user = User() user.password = password.encode() user.email = email db_session.add(user) db_session.commit() except ValueError as e: db_session.rollback() return {"message": f"{str(e)}"}, 400 return { "message": f"user with email {email} successfully created" }, 200
def insert_sensor_data(msg): try: print(msg.topic + " " + str(msg.qos) + " " + str(msg.payload)) m_in = json.loads(msg.payload) for key, value in m_in["channels"].items(): db_session.add( SensorData( app_key=m_in["app_key"], net_key=m_in["net_key"], device_id=m_in["device_id"], field_name=key, value=value, unit_string=m_in["unit"], )) db_session.commit() except Exception: print(f"error trying to insert {m_in}")
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_duplicates(dryrun): # batches = (db_session.query(Batch)\ # .filter(CiCommit.id == commit_id, Batch.label == label)\ # .all()) sql = "SELECT ci_commit_id, label, count(*) as qty FROM batches GROUP BY ci_commit_id, label HAVING count(*)> 1;" result = db_session.execute(sql) duplicates = list(result) # for commit_id, label, count in duplicates: # print(commit_id, label, count) print(len(duplicates), "duplicates") for commit_id, label, count in duplicates: print(f"commit_id: {commit_id}, label={label}, count={count}") # .filter(and_(CiCommit.id == commit_id, Batch.label == label))\ batches = (db_session.query(Batch)\ .join(CiCommit)\ .filter(Batch.label == label)\ .filter(CiCommit.id == commit_id)\ .all()) print(f" found {len(batches)}, expected: {count}") for b in batches: print(" ", b, b.label, b.ci_commit.id) print(" ", b.ci_commit, b.ci_commit.project) print('--') assert len(batches) == count final_batch, *other_batches = list(batches) if not final_batch.data: final_batch.data = {} if not final_batch.data.get('commands'): final_batch.data['commands'] = {} print('BEFORE', final_batch.data['commands']) for b in other_batches: if b.data and b.data.get('commands'): final_batch.data['commands'].update(b.data['commands']) for o in b.outputs: o.batch = final_batch db_session.delete(b) print('AFTER', final_batch.data['commands']) if not dryrun: db_session.add(final_batch) db_session.commit()
def add_new_todo_task(): ''' :description: - create a new task associated with user by token :param: - accepts JSON with 'taskDescription' :return: - JSON response ''' if request.method == 'POST': session_data = get_jwt_decode_data() optional_user = Users.query.filter_by(id=session_data['sub']).first() if optional_user is None: response = { "status": __STATUS_FAIL, "timestamp": stamp() } resp = make_response(response) resp.status_code = 404 return resp payload = request.get_json() task_description = payload['taskDescription'] new_todo = Todos(user_id=optional_user.id, todo_description=task_description) db_session.add(new_todo) db_session.commit() response = { 'timestamp': stamp(), 'status': __STATUS_SUCCESS, 'data': { 'taskId': new_todo.id, 'taskCreationTime': new_todo.time_created } } resp = make_response(response) resp.status_code = 201 return resp response = { 'timestamp': stamp(), 'status': __STATUS_FAIL } resp = make_response(response) resp.status_code = 403 return resp
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 insert_data(n_users=10, n_sticky=5, n_events=10): fake = Faker() users = set([fake.name() for _ in range(n_users)]) print(len(users)) for user in users: u = User(username=user, password='******') for i in range(n_sticky): sticky = ReminderSticky(reminder=fake.text()) u.reminders_sticky.append(sticky) for i in range(n_events): event = ReminderEvents(reminder=fake.text(), time_of_reminder=generate_random_datetime(), how_often=generate_how_often()) u.reminders_events.append(event) db_session.add(u) try: db_session.commit() except Exception as e: print(str(e))
def heartbeat(): data = get_jwt_decode_data() exp = data["exp"] now = datetime.datetime.utcnow() - datetime.timedelta(days=0, seconds=600) now = int(now.timestamp().__str__()[:10]) state = exp - now if state <= 0: session['log_in'] = False resp = make_response({'message': 'Invalid token'}) resp.status_code = 403 return resp user_obj = Users.query.filter_by(id=data["sub"]).first() new_token = user_obj.encode_auth_token(user_obj.id) user_obj.token = new_token db_session.add(user_obj) db_session.commit() response = { "status": Success, "timestamp": stamp() } resp = make_response(response) resp.status_code = 200 return resp
def add_new_todo_task(): if request.method.__eq__('POST'): session_data = get_jwt_decode_data() optional_user = Users.query.filter_by(id=session_data['sub']).first() if optional_user is None: response = { "status": Fail, "timestamp": stamp(), "reason": "no such user" } resp = make_response(response) resp.status_code = 404 return resp payload = request.get_json() task_description = payload['taskDescription'] new_todo = Todos(user_id=optional_user.id) new_todo.task_description = task_description db_session.add(new_todo) db_session.commit() response = { 'timestamp': stamp(), 'status': Success, 'data': { 'taskId': new_todo.id, 'taskCreationTime': new_todo.time_created } } resp = make_response(response) resp.status_code = 201 return resp response = { 'timestamp': stamp(), 'status': Fail } resp = make_response(response) resp.status_code = 403 return resp
def add_reminder_to_log(id): rl = ReminderLog(reminder=id, executed_at=datetime.now()) db_session.add(rl) db_session.commit()
# .yield_per(batch_size) ) print("Starting updates") most_recent = datetime.datetime.now() - datetime.timedelta(days=7) for idx, output in enumerate(query(most_recent)): if output.is_pending: continue if output.data.get('storage'): continue manifest_path = output.output_dir / 'manifest.outputs.json' if not manifest_path.exists(): if not output.output_dir.exists(): output.data['storage'] = 0 db_session.add(output) flag_modified(output, "data") continue else: manifest = save_outputs_manifest(output.output_dir) else: with manifest_path.open() as f: manifest = json.load(f) output.data['storage'] = total_storage(manifest) flag_modified(output, "data") db_session.add(output) if idx % batch_size == 0: print(f"{idx/output_total:.1%}", output, f"{output.data['storage']/1024/1024:.01f} MB")
def create_new_user(): ''' :description: - user sign in , creation of new user :generate: - new user object in todo-tasks db :param: - accepts JSON with 'name', 'email', 'password' :returns: - JSON response ''' recall = request if recall.method.__eq__('POST'): data = recall.get_json() if data is None or data["email"] is None or data["password"] is None: e = Exception('data is missing!') response = { "status": STATUS_FAILED, "timestamp": stamp(), "reason": e.__str__() } resp = make_response(response) resp.status_code = 403 return resp user_name = data['name'] user_email = data['email'] hashed_password = generate_password_hash(data['password'], method='sha256') optional_user = Users.query.filter_by(email=user_email).first() if optional_user is None: new_user = Users(user_name, user_email, hashed_password) new_user.token = new_user.encode_auth_token(new_user.id) db_session.add(new_user) db_session.commit() session['logged_in'] = True response = { "status": __STATUS_SUCCESS, "timestamp": stamp() } resp = make_response(response) resp.headers['Authorization'] = new_user.token.decode() resp.status_code = 201 return resp else: e = Exception("User already exists!") response = { "status": __STATUS_FAIL, "timestamp": stamp(), "reason": e.__str__() } resp = make_response(response) resp.status_code = 406 return resp e = Exception("Unable to process request!") response = { "status": __STATUS_FAIL, "timestamp": stamp(), "reason": e.__str__() } resp = make_response(response) resp.status_code = 405 return resp
def user_login(): ''' :description: - make a login with existing user :param: - accepts JSON with 'email', 'password' :return: - JSON response ''' recall = request if recall.method.__eq__('POST'): data = recall.get_json() if data is None or len(data) == 0: e = Exception('data is missing!') response = { "status": __STATUS_FAIL, "timestamp": stamp(), "reason": e.__str__() } resp = make_response(response) resp.status_code = 403 return resp user_email = data['email'] user_password = data['password'] optional_user = Users.query.filter_by(email=user_email).first() if optional_user is not None and check_password_hash(optional_user.password, user_password): session['logged_in'] = True auth_token = optional_user.encode_auth_token(optional_user.id) optional_user.token = auth_token assert optional_user.token == auth_token db_session.add(optional_user) db_session.commit() response = { "status": __STATUS_SUCCESS, "timestamp": stamp(), "message": "Login Successfully!", } resp = make_response(response) resp.headers['Authorization'] = optional_user.token.decode() resp.status_code = 200 return resp else: e = Exception("Unauthorized! email / password is not correct.") response = { "status": __STATUS_FAIL, "timestamp": stamp(), "reason": e.__str__() } resp = make_response(response) resp.status_code = 401 return resp e = Exception("Unable to verify WWW-Authenticate: Basic realm 'login realm'") response = { "status": __STATUS_FAIL, "timestamp": stamp(), "reason": e.__str__() } resp = make_response(response) resp.status_code = 405 return resp