def add_user(self, username, email=None, fname=None, lname=None, password=None): """ Add a user to the DB""" s = Session() try: u = User(username=username, email=email, fname=fname, lname=lname, password=password, strava_id=self.athlete.id) s.add(u) s.commit() return int(u.id) except SQLAlchemyError as err: s.rollback() print('Error: \n', err) raise s.close() return
def setup_data(): delete_db(engine) session = Session() AppModelBase.metadata.create_all(engine) acs = [ ArtifactCoordinatesModel(lat=40.09389490340147, lon=-85.62538134792705), ArtifactCoordinatesModel(lat=40.09585039419487, lon=-85.62004021718168), ArtifactCoordinatesModel(lat=40.09451269825916, lon=-85.62251577299321) ] aims = [ ArtifactImageModel('/static/whiteoak.jpg', 'The white oak tree'), ArtifactImageModel('/static/entrance.jpg', 'The visitor center'), ArtifactImageModel('/static/greatmound.jpg', 'The great mound!') ] artifacts = [ ArtifactModel(name="White Oak", description="The white oak tree"), ArtifactModel(name="Visitor Center", description="The visitor center."), ArtifactModel(name="Great Mound", description="The great mound.") ] for loc in acs: session.add(loc) for a in aims: session.add(a) for idx, artifact in enumerate(artifacts): artifact.addCoordinates(acs[idx]) artifact.addImage(aims[idx]) session.commit() session.close()
class TestCard(unittest.TestCase): def setUp(self): self.session = Session() def tearDown(self): self.session.close() def testSaveCard(self): delete_db(engine) AppModelBase.metadata.create_all(engine) forbidden = "{'word1', 'word2', 'word3', 'word4'}" card = CardModel(buzzword="unit test", forbidden_words=forbidden, source="Book", source_page="345", skipped_count=0, won_count=0, lost_count=0, is_phrase=IS_PHRASE) self.session.add(card) self.session.commit() card = CardModel.findCardById(self.session, 1) self.assertEqual(card.buzzword, "unit test") def testCountCard(self): self.assertEqual(CardModel.numberOfRows(self.session), 1) def testRemoveForbiddenWords(self): card = CardModel.findCardById(self.session, 1) card.removeForbiddenWords() self.assertEqual(card.forbiddenWords, "")
def insert_actor(iActorUUID, iShortName, iCommonName, iCountry, iVictimology, iTOI, iSS, iSince, iMOO, iCaps, iLastUpdate, iDescription): session = Session() try: dbInsert = md.ActorMeta() dbInsert.uuid = iActorUUID dbInsert.shortname = iShortName dbInsert.commonname = iCommonName dbInsert.country = iCountry dbInsert.victimology = iVictimology dbInsert.cfrtypeofincident = iTOI dbInsert.cfrstatesponsor = iSS dbInsert.since = iSince dbInsert.modeofoperation = iMOO dbInsert.capabilities = iCaps dbInsert.last_update = iLastUpdate dbInsert.description = iDescription session.merge(dbInsert) session.commit() except Exception as error: session.rollback() print("f(x) insert_actor: DATABASE ERROR: {}".format(error)) sys.exit(error) finally: session.close() #DEBUG SEQ if gv._DEBUG: print("f(x) insert_actor: {} DATA ADDED TO ACTORS TABLE".format( iShortName))
def add_streams(self, user_id, s_id): """ Add Strava data streams for a given user_id and activity_id """ # get the strava streams for that activity stream = self.get_streams(s_id) # convert the streams to a DF if stream is not None: s = Session() df = self.stream_to_DF(stream) # add `user_id` to the DF df['user_id'] = user_id # add `activity_id` to the DF df['activity_id'] = s.query( Activity.id).filter_by(strava_id=s_id.astype(str)).one()[0] try: df.to_sql(self.streams_TBL, engine, if_exists='append', index=False) s.commit() except: s.rollback() print('Error: `add_streams` cannot write event to DB. \n') raise s.close() else: print('Error: Stream is empty for User {0}, Activity {1}'.format( user_id, s_id)) return
def get_actor_meta(iUUID="", iCommonName=""): ret_list = [] session = Session() try: if iUUID: # query = "SELECT * FROM mp_actor_meta WHERE mpactor_uuid = ? or mp_commonname = ? " # value = (iUUID, iCommonName) # ret_list = cursor.fetchall() ret_list = session.query(md.ActorMeta). \ filter(md.ActorMeta.uuid == iUUID). \ first() return md.ActorMetaSchema(many=False).dump(ret_list) elif iCommonName: ret_list = session.query(md.ActorMeta). \ filter(md.ActorMeta.commonname == iCommonName). \ first() return md.ActorMetaSchema(many=False).dump(ret_list) else: return {} except Exception as error: print("f(x) get_actor_meta: DATABASE ERROR: {}".format(error)) sys.exit(error) finally: session.close()
def get_family_meta(iUUID="", iName=""): ret_list = [] session = Session() try: if iUUID: # query = "SELECT * FROM mp_malware_meta WHERE mpmalware_uuid = ? or mp_name = ?" # value = (iUUID, iName) ret_list = session.query(md.MalwareMeta). \ filter(md.MalwareMeta.uuid == iUUID). \ first() return md.MalwareMetaSchema(many=False).dump(ret_list) elif iName: # query = "SELECT * FROM mp_malware_meta WHERE mpmalware_uuid = ? or mp_name = ?" # value = (iUUID, iName) ret_list = session.query(md.MalwareMeta). \ filter(md.MalwareMeta.name == iName). \ first() return md.MalwareMetaSchema(many=False).dump(ret_list) else: return {} except Exception as error: print("f(x) get_family_meta: DATABASE ERROR: {}".format(error)) sys.exit(error) finally: session.close()
def get_galaxy_specific_tags(iSearchTerm, iGalaxy="mitre-attack-pattern"): ret_list = [] session = Session() try: # STANDARD SEARCH IN DESCRIPTIONS AND tAG if iGalaxy == "": iSearchTerm = "%" + iSearchTerm + "%" # query = "select galaxy, tag from misp_galaxy_clusters where tag like ?" ret_list = session.query(md.MispGalaxyCluster). \ filter(md.MispGalaxyCluster.tag.ilike(iSearchTerm)). \ all() elif iGalaxy == "malware": iSearchTerm = "%- " + iSearchTerm + "%" # query = "select galaxy, tag from misp_galaxy_clusters where (galaxy = 'mitre-tool' or galaxy = 'mitre-malware') AND tag like ?" ret_list = session.query(md.MispGalaxyCluster). \ filter(md.MispGalaxyCluster.tag.ilike(iSearchTerm)). \ filter(sa.or_(md.MispGalaxyCluster.galaxy == 'mitre-tool', md.MispGalaxyCluster.galaxy == 'mitre-tool')). \ all() else: iSearchTerm = "%- " + iSearchTerm + "%" # query = "select galaxy, tag from misp_galaxy_clusters where galaxy=? and tag like ?" ret_list = session.query(md.MispGalaxyCluster). \ filter(sa.and_(md.MispGalaxyCluster.galaxy.ilike(iGalaxy), md.MispGalaxyCluster.tag.ilike(iSearchTerm))). \ all() return md.MispGalaxyClusterSchema(many=True).dump(ret_list) except Exception as error: print( "f(x) get_galaxy_specific_tags: DATABASE ERROR: {}".format(error)) sys.exit(error) finally: session.close()
def get_images(): session = Session() image_type = request.args.get('type') images = session.query(DisplayImage).filter_by(type=image_type).all() res = json.dumps(images, cls=AlchemyEncoder, encoding='utf-8') session.close() return res
def return_existing_kode(kode): session = Session() check_if_present = session.query(Company_List).\ filter(Company_List.company_code == kode).\ scalar() session.close() return check_if_present
def create_insert_object_from_model(model, artifactObject): session = Session() session.add(artifactObject) session.commit() artifactID = artifactObject.id artifact = session.query(model).get(artifactObject.id) arttifactID1 = artifact.id session.close() assert arttifactID1 == artifactID
def session(): Base.metadata.bind = engine Base.metadata.create_all() connection = engine.connect() transaction = connection.begin() session = Session(bind=connection) yield session session.close() Base.metadata.drop_all(bind=engine) connection.close()
class TestShop(unittest.TestCase): def setUp(self): self.session = Session().session def tearDown(self): self.session.close() def test_select(self): shop = self.session.query(Shop.name).filter(Shop.id == 1).first() self.assertEqual(shop.name, "ラビットハウス")
def search_artifact_name(data): session=Session() searchString=data['searchString'] if(searchString==""): emit('retrieved_search_results',[]) else: searchResults=ArtifactModel.getPartials(session,searchString) searchResults = [ item.getDict() for item in searchResults ] emit('retrieved_search_results',searchResults) session.close()
def post_images(): session = Session() if 'type' in request.form and 'url' in request.form: image_type = request.form['type'] image_url = request.form['url'] session.add(DisplayImage(url=image_url, type=image_type)) session.commit() session.close() return handle_response({'success': True}) else: return handle_response({'success': False, 'msg': 'invalid post data'})
def tearDown(self): session = Session() user = session.query(User).filter( User.email == self.new_user_email).first() # TODO make it cascade to the users's transfers if user: session.delete(user) session.commit() session.close() else: session.rollback()
def get_parent_child_data(iValue="", iUUID=""): ret_list = "" session = Session() try: if gv._DEBUG: print("f(x) get_parent_child_data: PULLING UUID:VALUE: [{}]:[{}]". format(iUUID, iValue)) if iValue == "all": # query = "select uuid from mp_parent_child_by_uuid" ret_list = session.query(md.ParentChildByUuid). \ with_entities(md.ParentChildByUuid.uuid). \ all() return md.ParentChildByUuidSchema(many=True).dump(ret_list) elif iValue == "actor" or iValue == "family" or iValue == "path" or iValue == "malware": # query = "select uuid from mp_parent_child_by_uuid where mytype = ? order by path asc" # value = (iValue.upper(),) ret_list = session.query(md.ParentChildByUuid). \ with_entities(md.ParentChildByUuid.uuid). \ filter(md.ParentChildByUuid.mytype == iValue.upper()). \ order_by(md.ParentChildByUuid.path). \ all() return md.ParentChildByUuidSchema(many=True).dump(ret_list) elif iValue == "" and iUUID != "": # query = "select * from mp_parent_child_by_uuid where uuid = ?" # value = (iUUID,) # ret_val = cursor.fetchone() ret_list = ret_list = session.query(md.ParentChildByUuid). \ filter(md.ParentChildByUuid.uuid == iUUID). \ first() return md.ParentChildByUuidSchema(many=False).dump(ret_list) elif iValue != "" and iUUID == "": # query = "select * from mp_parent_child_by_uuid where name = ?" # ret_val = cursor.fetchone() ret_list = session.query(md.ParentChildByUuid). \ filter(md.ParentChildByUuid.name == iValue). \ first() return md.ParentChildByUuidSchema(many=False).dump(ret_list) elif iValue != "" and iUUID != "": # query = "select * from mp_parent_child_by_uuid where name = ? or uuid = ?" # value = (iValue, iUUID) # ret_val = cursor.fetchone() ret_list = session.query(md.ParentChildByUuid). \ filter(sa.or_(md.ParentChildByUuid.name == iValue, md.ParentChildByUuid.uuid == iUUID)). \ first() return md.ParentChildByUuidSchema(many=False).dump(ret_list) else: return {} except Exception as error: print("f(x) get_parent_child_data: DATABASE ERROR: {}".format(error)) sys.exit(error) finally: session.close()
def get_children(iUUID): ret_list = [] session = Session() try: # query = "SELECT uuid FROM mp_parent_child_by_uuid WHERE parentuuid = ?" ret_list = session.query(md.ParentChildByUuid). \ with_entities(md.ParentChildByUuid.uuid). \ filter(md.ParentChildByUuid.parentuuid == iUUID). \ all() return md.ParentChildByUuidSchema(many=True).dump(ret_list) except Exception as error: print("f(x) get_children: DATABASE ERROR: {}".format(error)) sys.exit(error) finally: session.close()
def add_user(self, username, email, fname=None, lname=None, password=None): s = Session() try: u = User(username=username, email=email, fname=fname, lname=lname, password=password) s.add(u) s.commit() return int(u.id) except SQLAlchemyError as err: s.rollback() print('Error: \n', err) raise s.close()
def timed_job(): session = Session() users = session.query(User) for u in users: if datetime.datetime.utcnow() >= u.time_reminder: try: viber.send_messages(u.viber_id, [ TextMessage(text="Время повторить слова", keyboard=WAIT_KEYBOARD, tracking_data='tracking_data') ]) except: print("Пользователь отписался") print(u.full_name) session.close()
def test6GameRollWheel(self): wheel = {"gameID": 1, "duration": 4} self.game_namespace.on("my_roll_result", self.passWheelInfo) self.game_namespace.emit("roll_wheel", wheel) self.socketIO.wait(seconds=5) session = Session() turnID = 1 turn = TurnModel.getTurnById(turnID, session) print( "\n\n**********Test wheel result game changer write to DB*********Pass" ) self.assertEqual(self.currentWheel["rollID"], turn.gameChangerNumber) session.commit() session.close()
def book_list(): session = Session() query = session.query(Book) show_author = bool(int(request.args.get("show_author", 0))) show_id = bool(int(request.args.get("show_id", 0))) if "title" in request.args: query = query.filter(Book.title == request.args.get("title")) if "isbn" in request.args: query = query.filter(Book.isbn == request.args.get("isbn")) if "book_read" in request.args: query = query.filter(Book.book_read == request.args.get("book_read")) rsp = [book.as_dict(show_id, show_author) for book in query] session.close() return json.dumps(rsp), 200
def get_synonyms(iUUID): ret_list = [] session = Session() try: # query = "select * from synonyms where uuid = ?" ret_list = session.query(md.Synonym). \ filter(md.Synonym.uuid == iUUID). \ all() return md.SynonymSchema(many=True).dump(ret_list) except Exception as error: print("f(x) get_synonyms: DATABASE ERROR: {}".format(error)) sys.exit(error) finally: session.close()
def add_user(): """ Add a user from the GUI interface""" s = Session() try: u = User(username='******', email='*****@*****.**', fname='me', lname='me', password='******') s.add(u) s.commit() s.close() flash ("User %s added" %u.username) return int(u.id) except: s.rollback() s.close() raise
def get_manual_tags(iSearchTag, iSearchGalaxy): ret_list = [] session = Session() try: # query = "select * from manual_tag_maps where tagvalue = ? and galaxy like ?" ret_list = session.query(md.ManualTagMap). \ filter(sa.and_(md.ManualTagMap.tagvalue == iSearchTag, md.ManualTagMap.galaxy.ilike(iSearchGalaxy))). \ all() return md.ManualTagMapSchema(many=True).dump(ret_list) except Exception as error: session.rollback() print("f(x) insert_galaxy: DATABASE ERROR: {}".format(error)) sys.exit(error) finally: session.close()
def add_activity(self, user_id, before=None, after=None, limit=None, add_streams=True): """ Get & add a list of activities from strava """ # get the list of activities from strava activities = self.get_activities(before=before, after=after, limit=limit) activities = activities # transform activities to a DF ready for Postgres df = self.munge_activity(activities) df['user_id'] = user_id s = Session() try: df.to_sql(self.activity_TBL, engine, if_exists='append', index=False) s.commit() print('Added {0} activities from Strava.\n'.format( len(df.strava_id))) except: s.rollback() print('Error: `add_activity` cannot write event to DB. \n') raise s.close() # if needed, add the streams as well if add_streams is True: size = len(df.strava_id) for (i, strava_id) in enumerate(df.strava_id): print('Fetching data streams for {0}: {1} of {2}'.format( strava_id, i, size), end='\r') time.sleep( self.API_CALL_PAUSE_SECONDS) # limit API call to 40 / min self.add_streams(user_id, strava_id) print('Added `Streams` for {0} activities from Strava.'.format( len(df.strava_id))) return
def get_mitre_software_code(iValue): ret_list = [] session = Session() try: namevariations = iValue.replace("-", "").replace(" ", "").replace( "(", "").replace(")", "").replace("/", "").replace("\\", "") # query = "select * from mitre_software_xref where mitreName like ? or mitreName like ?" ret_list = session.query(md.MitreSoftwareXref). \ filter(sa.or_(md.MitreSoftwareXref.mitrename.ilike(iValue), md.MitreSoftwareXref.mitrename.ilike(namevariations))). \ all() return md.MitreSoftwareXrefSchema(many=True).dump(ret_list) except Exception as error: print("f(x) get_mitre_software_code: DATABASE ERROR: {}".format(error)) sys.exit(error) finally: session.close()
def setUp(self): self.app = app.test_client() self.app.testing = True try: session = Session() session.add(User(email=self.test_email)) session.commit() except Exception: session.rollback() finally: session.close() session = Session() self.user_id = session.query( User.id).filter(User.email == self.test_email).scalar() session.close()
def book_list_by_id(book_id): session = Session() query = session.query(Book).filter(Book.id == book_id) show_author = False show_id = False if not exists_in_db("id", book_id, "Book"): return json.dumps(return_code[4]), 404 if request.args.get("show_author") == "1": show_author = True if request.args.get("show_id") == "1": show_id = True rsp = [book.as_dict(show_id, show_author) for book in query] session.close() return json.dumps(rsp), 200
def book_add(): session = Session() authorid = None title = None isbn = None read = 0 if len(request.json) == 0: return json.dumps(return_code[1]), 400 if "title" in request.json: title = request.json["title"] else: return json.dumps(return_code[2]), 400 if "author_id" in request.json: authorid = request.json["author_id"] if not exists_in_db("id", authorid, "Author"): return json.dumps(return_code[5]), 400 else: return json.dumps(return_code[2]), 400 if "isbn" in request.json: isbn = request.json["isbn"] if "read" in request.json: read = request.json["read"] new_book = Book(authorid, title, isbn=isbn, book_read=read) if exists_in_db("title", title, "Book"): return json.dumps(return_code[3]), 409 if exists_in_db("isbn", isbn, "Book"): return json.dumps(return_code[3]), 409 session.add(new_book) session.commit() rsp_dict = dict(return_code[0]) rsp_dict["added"] = new_book.as_dict(show_id=True) rsp_dict = json.dumps(rsp_dict) rsp = Response(rsp_dict) rsp.headers.add("Location", "/api/books/{}".format(new_book.id)) session.close() return rsp, 201