def categoryJSON(category_name): """ JSON data for specific category """ # Create DB session db_session = Session() # Query all categories category = db_session.query(Category).filter_by(name=category_name).one() # Query items for corresponding category items = ( db_session .query(Item) .filter_by(category_id=category.id) .order_by(Item.title) .all() ) # Serialize current category and add corresponding serialized items serialized_category = category.serialize serialized_category["Items"] = [] for item in items: serialized_category["Items"].append(item.serialize) # Close DB session db_session.close() return jsonify(Category=serialized_category)
def _update_tasks(self, tasks): session = Session() while tasks: nm_list = [] bt_list = [] for task in tasks[:100]: if task.task_type in ("bt", "magnet"): bt_list.append(task.id) else: nm_list.append(task.id) for res in self.xunlei.get_task_process(nm_list, bt_list): task = self.get_task(res['task_id']) if not task: continue task.status = res['status'] task.process = res['process'] if task.status == "failed": task.invalid = True if res['cid'] and res['lixian_url']: task.cid = res['cid'] task.lixian_url = res['lixian_url'] if task.status in ("downloading", "finished"): if not self._update_file_list(task): task.status = "downloading" session.add(task) tasks = tasks[100:] session.commit() session.close()
def event(event_id): content = {'events': 'test'} success = {'status': 'event created'} updateSuccess = {'status':'account updated'} noEvent = {'error': 'User not found.'} updateError = {'error': 'User not found/input validation failed.'} error = {'error': "Error in JSON/SQL syntax"} if request.method == 'POST': data = request.json if event.createEvent(data): return success, status.HTTP_200_OK else: return error, status.HTTP_500_INTERNAL_SERVER_ERROR if request.method == 'GET': s = Session() e = s.query(Event).filter_by(id=event_id).first() if e: return Event.asdict(e), status.HTTP_200_OK else: return noEvent, status.HTTP_404_NOT_FOUND if request.method == 'POST': data = request.json if data: s = Session() u = s.query(Event).filter_by(id=event_id).update(data) if u: s.commit() s.close() return updateSuccess, status.HTTP_200_OK else: return updateError, status.HTTP_400_BAD_REQUEST
def test_16_skills(self): session = Session() race = session.query(Event).filter_by(name='Race for the Cure').first() dace = EventSkills("Teaching/Tutoring", race.id) lace = session.query(EventSkills).filter_by(event_id=race.id).first() self.assertTrue(dace.skill == lace.skill) session.close()
def create_token(user): payload = { # subject 'sub': user.id, #issued at 'iat': datetime.utcnow(), #expiry 'exp': datetime.utcnow() + timedelta(days=1) } s = Session() token = jwt.encode(payload, app.secret_key, algorithm='HS256') try: if (user.permissions == 'volunteer'): us = s.query(Volunteer).filter_by(id=user.id).first() d = volunteer.Volunteer.asdict(us) d['skills'] = VolunteerSkills.get_skills(us.id) d['neighborhoods'] = VolunteerNeighborhoods.get_neighborhoods(us.id) d['interests'] = VolunteerInterests.get_interests(us.id) if (user.permissions == 'admin'): us = s.query(Admin).filter_by(id=user.id).first() d = admin.Admin.asdict(us) if (user.permissions == 'organization'): us = s.query(Organization).filter_by(id=user.id).first() d =organization.Organization.asdict(us) except: return None finally: s.close() m = {'token': str(token), 'user': d} return m
def post_links(): session = Session() data = json.loads(request.data) for link in data['links']: post = Post.get_or_create(session, link['reddit_id']) subreddit = Subreddit.get_or_create(session, link['sub_name']) user = User.get_or_create(session, link['authorname']) post.subreddit_id = subreddit.id post.author_id = user.id post.domain = link['domain'] post.title = link['title'] post.url = link['url'] post.score = link['score'] post.downs = link['downs'] post.ups = link['ups'] post.is_self = link['is_self'] post.over_18 = link['over_18'] post.thumbnail = link['thumbnail'] post.created = float(link['created']) post.scraped_time = Util.now() session.add(post) session.commit() session.close() return jsonify({'received':True})
def category(category_name): """ Shows all items for a specific category """ # Create DB session db_session = Session() # Query all categories and find selected category categories = db_session.query(Category).all() for category in categories: if category.name == category_name: current_category = category break # Query items for corresponding category items = ( db_session .query(Item) .filter_by(category_id=current_category.id) .order_by(Item.title) .all() ) # Close DB session db_session.close() return render_template("category.html", category_name=category_name, categories=categories, items=items)
def update_account(customer_id, body): session = Session() account = session.query(AccountDAO).filter( AccountDAO.id == customer_id).first() if account: entities_updated = [] if 'customer_name' in body: account.customer_name = body['customer_name'] entities_updated.append('customer_name, ') if 'customer_address' in body: account.customer_address = body['customer_address'] entities_updated.append('customer_address, ') if 'customer_password' in body: account.customer_password = body['customer_password'] entities_updated.append('customer_password, ') if 'customer_email' in body: account.customer_email = body['customer_email'] entities_updated.append('customer_email, ') session.commit() session.close() return jsonify( {'message': f'{" ".join(entities_updated)} were updated'}), 200 else: session.close() return jsonify( {'message': f'There is no account with id {customer_id}'}), 404
def put_batch(): session = Session() data = json.loads(request.data) worker = data['id'] users = data['users'] users = filter(lambda u: u is not None, users) usernames = [user['name'] for user in users] for lock in session.query(UserLock).join(User) \ .filter(User.name.in_(usernames), UserLock.status == UserLock.STATUS_WIP, UserLock.locker == worker): for u in users: if u['name'] == lock.user.name: if 'error' in u: eId = str(uuid4()) lock.user.reddit_id = eId else: lock.user.update_from_json(u) session.add(lock.user) session.commit() session.query(UserLock).join(User) \ .filter(User.reddit_id != None, User.name.in_(usernames), UserLock.status == UserLock.STATUS_WIP, UserLock.locker == worker) \ .update({'status': UserLock.STATUS_DONE, 'locker': None}, synchronize_session='fetch') session.commit() session.close() return jsonify({'received':'true'})
def synchronize_get_data(): """ Повертає json з працівниками відсортованими по відділах в яких є невідповідності в базах 1с, АД та Mysql. Приклад об'єкту що повертаємо { "eCommerce": [ { "department": "eCommerce", "description": "1", "fixed": "0", "id": 3054, "name": "Наталія", "position": "Начальник відділу e-commerce", "sid": "S-1-5-21-3915682675-1099836910-3992233257-3989", "sname": "Сєрякова" } ] } """ result = {} session = Session() all_records = session.query(Department1c).all() converter = Department1cSchema(many=True, only=['name']) list_dep = converter.dump(all_records).data for res in list_dep: all_rcolission = session.query(Collision).filter(Collision.department == res['name']).all() converter = CollisionSchema(many=True) arg = converter.dump(all_rcolission).data result[res['name']] = arg session.close() return jsonify(result)
def filter_statistic(_id): """ Повертає в яких відділах та містах було пройдене опитування. :param _id: ід опитування Приклад відповіді: { "city": [ "Луцьк", "Львів" ], "dep": [ "eCommerce", "Інформаційні технології" ] } """ session = Session() response = {} dep = [] city = [] for arg in session.query(distinct( Statistics.department)).filter_by(fk_poll=_id).all(): dep.append(arg[0]) response.setdefault('dep', dep) for arg in session.query(distinct( Statistics.city)).filter_by(fk_poll=_id).all(): city.append(arg[0]) response.setdefault('city', city) session.close() return jsonify(response)
def create_item(request, redirect_url, user_id): """Creates a new item and add to database""" # Check if form is complete check_item_form_data(request, redirect_url) # Create DB session db_session = Session() # Add new item to database try: new_item = Item(title=request.form.get("title"), description=request.form.get("description"), category_id=request.form.get("category"), owner_id=user_id) db_session.add(new_item) db_session.commit() except exc.SQLAlchemyError: flash("There was an error. Please try again.", "danger") return redirect(redirect_url) # Close DB session db_session.close() flash("Item added!", "success")
def branch_list(): """ Заповнення філіалу в БД """ users_list = [] if load_users_from_ad(): process_ad_response(ad.response, users_list, (('userAccountControl', [], False), ('company', [], False))) session = Session() for arg in users_list: if not session.query(Branch).filter( Branch.name == arg['company']).all(): fk_city = session.query( City.id).filter(City.name == arg['cityUa']).first() branch = Branch(name=arg['company'], name_en=arg['companyEn'], zip_code=arg['postalCode'], address_ua=arg['streetAddressUa'], address_en=arg['street'], address_ru=arg['streetAddress'], fk_city=fk_city[0]) session.add(branch) session.commit() session.close()
def vacation(): """ Заповнення відпусти за поточний рік всім працівникам """ session = Session() date2 = datetime.strftime(datetime.now(), "%Y-%m-%d") date1 = datetime.date(datetime.now()) result = session.query( Worker.id, Worker.started_to_work, Worker.finished_to_work).filter(Worker.status == 'active').all() for arg in result: if date1.year != arg[1].year and not session.query(Vacation).filter( Vacation.year == date1.year).filter( Vacation.fk_worker == arg[0]).first(): # Додати період +24 за поточний рік vacation_new = Vacation(count=24, used=0, balance=24, year=date1.year, updated=date2[0:10], fk_worker=arg[0]) session.add(vacation_new) # нарахування відпустки якщо людина була прийнята на роботу цього року elif not session.query(Vacation).filter( Vacation.year == date1.year).filter( Vacation.fk_worker == arg[0]).first(): vacation_user(arg[0]) session.commit() session.close()
def spider_opened(self, spider): db_session = Session() db_spider = db_session.query(Spider).filter( Spider.name == spider.name).one() if db_spider and db_spider.use_proxies or db_spider.use_tor: self.tor_renew_ip.add(spider.name) db_session.close()
def vacation_user(_id): """ Нарахування відпустки працівнику за діючий рік якщо він прийнятий на роботу цього ж року :param _id: ід працівника """ session = Session() datenow = datetime.strftime(datetime.now(), "%Y-%m-%d") date2 = datetime.strptime(str(datenow), "%Y-%m-%d").date() result = session.query( Worker.id, Worker.started_to_work, Worker.finished_to_work).filter( Worker.status == 'active').filter(Worker.id == _id).first() date = str(result[1].year) + '-12-31' date1 = datetime.strptime(str(date), "%Y-%m-%d").date() if result[1].year == date1.year: if result[1].day < 10: s1 = 2 elif result[1].day < 21: s1 = 1 else: s1 = 0 # кількість заробленої відпустки за цей рік count = s1 + 2 * (date1.month - result[1].month) vacation_new = Vacation(count=count, used=0, balance=count, year=date2.year, updated=date2, fk_worker=result[0]) session.add(vacation_new) session.commit() session.close() return count
def transactions2csv(dest): s = Session() statement = banks.get_transaction_log_statement() result = s.execute(statement).fetchall() s.close() total = len(result) header = True with open(dest, 'w') as csvfile: spamwriter = csv.writer(csvfile, delimiter=';', quotechar='"', quoting=csv.QUOTE_NONNUMERIC) for next in result: if header: spamwriter.writerow(next.keys()) header = False row = list(next.values()) amount = row[0] while amount > unit: row[0] = unit amount -= unit spamwriter.writerow(row) row[0] = amount spamwriter.writerow(row)
def populate_db(): df = pd.read_csv('data/movies_metadata.csv') df_sub = df[[ 'adult', 'budget', 'original_language', 'overview', 'release_date', 'revenue', 'runtime', 'title' ]].copy() # 3 - create a new session session = Session() # How to connect to existing database. for index, row in df_sub.iterrows(): adult = row['adult'] budget = row['budget'] orlang = row['original_language'] overview = row['overview'] reldate = row['release_date'] revenue = row['revenue'] runtime = row['runtime'] title = row['title'] product_quantity = random.randint(1000, 5000) unit_price = random.randint(100, 500) / 100 population_objects = ProductDAO(adult=adult, budget=budget, original_language=orlang, overview=overview, release_date=reldate, revenue=revenue, runtime=runtime, title=title, product_quantity=product_quantity, unit_price=unit_price) session.add(population_objects) session.commit() session.close() print("Db Successfully populated")
def get_barh(lastUts): session = Session() query = session.query(Etape.ecart, Ligne.nom_ligne) \ .select_from(Etape).join(Trajet).join(Ligne) \ .filter(Etape.record_timestamp == lastUts) df = pd.read_sql_query(query.statement, query.session.bind) session.close() dft = df.pivot_table(index="nom_ligne", values="ecart", aggfunc=lambda x: np.mean(np.abs(x))) \ .sort_values(by="ecart") data = go.Bar(y=dft.index, x=dft['ecart'], orientation='h', marker={'color': 'rgba(210, 105, 30, 0.5)'}) layout = go.Layout( title="Ecarts absolus moyens sur les lignes du réseau Irigo", xaxis={ 'title': "écart moyen absolu (secondes)", }, yaxis={'title': "ligne"}, margin={'l': 300}) g = dcc.Graph(id='barh', figure={'data': [data], 'layout': layout}) return [g]
def test_14_neighborhoods(self): session = Session() race = session.query(Event).filter_by(name='Race for the Cure').first() dace = EventNeighborhoods("Back Bay", race.id) lace = session.query(EventNeighborhoods).filter_by(event_id=race.id).first() self.assertTrue(dace.neighborhood == lace.neighborhood) session.close()
class Admin(tornado.web.RequestHandler): def __init__(self, application, request, **kwargs): super(Admin, self).__init__(application, request, **kwargs) _log.debug('admin init') self.session = Session() self.request_handler_actor = self.application.settings[ 'request_handler_actor'] def __del__(self): self.session.close() # list existing bots def get(self): result = self.session.query(TurtlebotAgent).all() jsonResult = json.dumps(result, cls=TurtlesEncoder) self.write(jsonResult) # add a new bot with name and location def post(self): if settings.SIMULATION == True: initial_location = [settings.NEST_X, settings.NEST_Y] robots_in_database = self.session.query(TurtlebotAgent.name).all() if len(robots_in_database) == 0: maximal_index = -1 else: maximal_index = max( [int(r[0].split('_')[1]) for r in robots_in_database]) turtlebot_name = 'robot_' + repr(maximal_index + 1) if maximal_index == settings.MAXIMAL_NUMBER_OF_BOTS - 1: self.set_status(204, "maximal number of bots already reached") self.finish() else: try: initial_location = json.loads( self.get_body_argument(name='location')) except: self.set_status(400) self.finish() else: turtlebot_name = self.get_body_argument(name='name') robots_with_same_name = self.session.query( TurtlebotAgent).filter_by(name=turtlebot_name).all() if len(robots_with_same_name) > 0: self.set_status(400, "the turtlebot name must be unique") elif len(initial_location) != 2: self.set_status( 400, "the initial location must be a 2d vector") self.finish() try: new_agent = TurtlebotAgent(name=turtlebot_name, location=Point(initial_location)) self.session.add(new_agent) self.session.commit() except Exception as e: self.session.rollback() self.set_status(400, 'error writing in the database')
def saveComments(comments): session = Session() commentIds = {comment['id'] for comment in comments} commentsInDB = session.query(Comment.id).filter( Comment.id.in_(commentIds)).all() commentIdsInDB = {comment.id for comment in commentsInDB} if commentIdsInDB is None: commentIdsInDB = [] commentIdsInDB = set(commentIdsInDB) # 添加新的评论 needAddCommentIds = commentIds - commentIdsInDB needAddComments = list( filter(lambda c: c['id'] in needAddCommentIds, comments)) session.add_all([Comment(**comment) for comment in needAddComments]) session.commit() #更新旧的评论 needUpdateComments = list( filter( lambda c: c['id'] in commentIdsInDB and (c['isDelete'] is True or c['isUpDelete'] is True), comments)) for comment in needUpdateComments: session.query(Comment).filter(Comment.id == comment.get('id')).update({ 'isDelete': comment.get('isDelete'), 'isUpDelete': comment.get('isUpDelete') }) session.commit() session.close()
def getDutyShiftChain(duty_date, duty_period): session = Session() try: origin_duty = session.query(OriginDuty).options( joinedload('member')).filter( and_(OriginDuty.duty_date == duty_date, OriginDuty.duty_period == duty_period.value)).first() #print(origin_duty) if origin_duty is not None: shift_chain = DutyShiftChain(origin_duty) if origin_duty.shift_duty_id is None: return shift_chain else: shift_id = origin_duty.shift_duty_id while shift_id is not None: shift_duty = session.query(ShiftDuty).options( joinedload('member')).filter( ShiftDuty.shift_duty_id == shift_id).first() #print(shift_duty) shift_chain.addDutyShiftRecord(shift_duty) shift_id = shift_duty.next_shift_duty_id return shift_chain except Exception as e: print(e) return finally: session.close()
def shiftDuty(duty_date, duty_period, member): session = Session() try: shift_duty = ShiftDuty(duty_date, duty_period, member.member_id) duty = session.query(OriginDuty).filter( and_(OriginDuty.duty_date == duty_date, OriginDuty.duty_period == duty_period.value)).first() if duty is not None: if duty.shift_duty_id is None: shift_duty.previous_shift_duty_id = duty.duty_id duty.shift_duty_id = shift_duty.shift_duty_id session.add(shift_duty) session.commit() else: shift_id = duty.shift_duty_id while shift_id is not None: pre_shift_duty = session.query(ShiftDuty).filter( ShiftDuty.shift_duty_id == shift_id).first() shift_id = pre_shift_duty.next_shift_duty_id pre_shift_duty.next_shift_duty_id = shift_duty.shift_duty_id shift_duty.previous_shift_duty_id = pre_shift_duty.shift_duty_id session.add(shift_duty) session.commit() except Exception as e: print(e) session.rollback() finally: session.close()
def deleteShiftDutyRecord(shift_duty_date, shift_duty_period=DutyPeriod.NightDuty): session = Session() duty_shift_chain = DBOperation.getDutyShiftChain( shift_duty_date, shift_duty_period) if duty_shift_chain is None: print("无日期为{}的值班记录".format(shift_duty_date)) return shift_chain_list = duty_shift_chain.shift_chain shift_count = len(shift_chain_list) - 1 try: if shift_count == 0: print("can not delete origin duty.") return elif shift_count == 1: session.query(OriginDuty).filter( OriginDuty == shift_chain_list[-2]).update( {OriginDuty.shift_duty_id: None}) else: session.query(ShiftDuty).filter( ShiftDuty == shift_chain_list[-2]).update( {ShiftDuty.next_shift_duty_id: None}) session.delete(shift_chain_list[-1]) session.commit() except Exception as e: print(e) session.rollback() finally: session.close()
def create(self, model): session = Session() session.add(model) session.commit() session.close()
def get_account(customer_email): session = Session() account = session.query(AccountDAO).filter( AccountDAO.customer_email == customer_email).first() # if customer: # return jsonify({'test2': f'customer: {customer_email}'}), 200 # else: # return jsonify({'Error': 'No customer found'}), 404 # session = Session() # account_email = body['customer_email'] # account = session.query(AccountDAO).filter(AccountDAO.customer_email == account_email).first() if account: text_out = { "customer_id": account.id, "customer_name": account.customer_name, "customer_address": account.customer_address, "customer_email": account.customer_email, "creation_time": account.creation_time } session.close() return jsonify(text_out), 200 else: session.close() return jsonify({ 'message': f'There is no account with e-mail {customer_email}' }), 404
def create_user(login_session, redirect_url): """Creates a new user and add to database""" # Create db_session db_session = Session() # Check to see if user exists in database and add it otherwise user_id = get_user_id(login_session['profile']['username']) if not user_id: # Add user to database try: new_user = User(username=login_session['profile']['username']) db_session.add(new_user) db_session.commit() flash("User created", "success") except exc.SQLAlchemyError: flash("Failed to create user", "danger") return redirect(redirect_url) # Query created user user = db_session.query(User).filter_by( username=login_session['profile']['username']).one() # Close DB session db_session.close() return user.id
def init_intermediary_table(): s = Session() s.execute(drop_table_intermediary) s.execute(create_table_intermediary) s.execute(populate_table_intermediary) s.commit() s.close()
def init_cashflow_table(): s = Session() s.execute(drop_table_cashflow) s.execute(create_table_cashflow) s.execute(populate_table_cashflow) s.commit() s.close()
def update_user_db_ad(): """ Доповнення БД даними з АД """ session = Session() records = session.query(Worker.sid, Worker.fk_person).filter(Worker.sid != '').all() for arg in records: user = [] res = ad.search(search_base=BASE_DN, search_filter="(objectSid={sid})".format(sid=arg[0]), attributes=[ 'mail', 'sn', 'givenName', 'pager', 'thumbnailPhoto', 'mobile', 'givenName-En', 'givenNameUa', 'cityUa', 'ipPhone', 'l', 'givenName-En', 'snEn', 'givenNameUa', 'snUa', 'objectSid' ]) if res: for entry in ad.response: if 'dn' in entry: user.append(dict(entry['attributes'])) if user: if user[0]['thumbnailPhoto']: byte_string = user[0]['thumbnailPhoto'] encoded_data = base64.b64encode(byte_string) photo = Document(name='фотографія', file_type='', file=encoded_data, type_='', fk_person=arg[1]) session.add(photo) for resp in user[0]: if not user[0][resp]: user[0][resp] = '' session.query(Person).filter_by(id=arg[1]).update({ "email": user[0]['mail'], "skype": user[0]['pager'] }) session.query(Worker).filter_by(fk_person=arg[1]).update({ "name_en": user[0]['givenName-En'], "name_ru": user[0]['givenName'], "surname_en": user[0]['snEn'], "surname_ru": user[0]['sn'], "skype": user[0]['pager'], "ip_phone": user[0]['ipPhone'], "email": user[0]['mail'] }) session.commit() session.close()
def idOfPerson_family(_id): """ Повертає дані про сімю фізичної особи за id фізичної особи. В групу Family :param _id: id фізичної особи Приклад відповіді: [ { "date": "Sat, 01 Jan 2000 00:00:00 GMT", "id": 11, "name": "Іван", "position": "дизайнер", "sname": "Іванов", "type": "чоловік", "work_place": "Google" } ] """ session = Session() result = session.query(Family).filter(Family.fk_person == _id).all() converter = FamilySchema(many=True, only=[ 'id', 'name', 'surname', 'birthday', 'type_', 'workplace', 'position' ]) dumps_data = converter.dump(result).data session.close() return jsonify(dumps_data)
def main(notify): session = Session() gen = session.query(User) \ .join(UserMeta) \ .filter(User.reddit_id != None, \ UserMeta.has_public_likes == UserMeta.YES, \ UserMeta.is_active != UserMeta.NO) for user in gen: try: praw_user = r.get_redditor(user.name) user.update_from_praw(praw_user) for praw_post in praw_user.get_liked(limit=100): update(session, user, praw_post, 1) for praw_post in praw_user.get_disliked(limit=100): update(session, user, praw_post, -1) user.meta.has_public_likes = UserMeta.YES except praw.requests.exceptions.HTTPError as e: print str(e) if '403' in str(e): user.meta.has_public_likes = UserMeta.NO elif '404' in str(e): user.meta.is_active = UserMeta.NO session.add(user.meta) session.add(user) session.commit() session.close()
def update_item(item_id, request, redirect_url): """Update item in database""" # Check if form is complete check_item_form_data(request, redirect_url) # Create DB session db_session = Session() # Query item in database by id item = db_session.query(Item).filter_by(id=item_id).one() # Update corresponding item in database try: item.title = request.form.get("title") item.description = request.form.get("description") item.category_id = request.form.get("category") item.last_updated = datetime.utcnow() db_session.add(item) db_session.commit() except exc.SQLAlchemyError: flash("There was an error. Please try again.", "danger") return redirect(redirect_url, item_id=item_id) # Close DB session db_session.close()
def add(self, message, latitude, longitude, time_limit=60): post = Post(message, latitude, longitude, time_limit) session = Session() session.add(post) session.commit() session.close() return post
def test_12_interests(self): session = Session() race = session.query(Event).filter_by(name='Race for the Cure').first() dace = EventInterests("Youth", race.id) lace = session.query(EventInterests).filter_by(event_id=race.id).first() self.assertTrue(dace.interest == lace.interest) session.close()
def init_balance_table(): s = Session() s.execute(drop_table_balance) s.execute(create_table_balance) s.execute(populate_table_balance) s.commit() s.close()
def test_14_availability(self): session = Session() doey = session.query(Volunteer).filter_by(name='Joey Wood').first() moey = VolunteerAvailability("Monday", doey.id) joey = session.query(VolunteerAvailability).filter_by(volunteer_id=doey.id).first() #self.assertTrue(moey.day == joey.day) session.close()
def save_comments(self, comments): session = Session() comment_ids = {comment['id'] for comment in comments} comments_in_db = session.query(Comment.id).filter( Comment.id.in_(comment_ids)).all() comment_ids_in_db = {comment.id for comment in comments_in_db} if comment_ids_in_db is None: comment_ids_in_db = [] comment_ids_in_db = set(comment_ids_in_db) # 添加新的评论 need_add_comment_ids = comment_ids - comment_ids_in_db need_add_comments = list( filter(lambda c: c['id'] in need_add_comment_ids, comments)) session.add_all([Comment(**comment) for comment in need_add_comments]) # 更新旧的评论 need_update_comments = list( filter( lambda c: c['id'] in comment_ids_in_db and (c['isDelete'] is True or c['isUpDelete'] is True), comments)) for comment in need_update_comments: db_comment = session.query(Comment).filter( Comment.id == comment.get('id')) db_comment.isDelete = comment.get('isDelete') db_comment.isUpDelete = comment.get('isUpDelete') session.commit() session.commit() session.close()
def test_10_neighborhoods(self): session = Session() doey = session.query(Volunteer).filter_by(name='Joey Wood').first() moey = VolunteerNeighborhoods("backbay", doey.id) joey = session.query(VolunteerNeighborhoods).filter_by(volunteer_id=doey.id).first() #self.assertTrue(moey.neighborhood == joey.neighborhood) session.close()
def test_12_skills(self): session = Session() doey = session.query(Volunteer).filter_by(name='Joey Wood').first() moey = VolunteerSkills("teaching", doey.id) joey = session.query(VolunteerSkills).filter_by(volunteer_id=doey.id).first() #self.assertTrue(moey.skill == joey.skill) session.close()
def test_08_interests(self): session = Session() doey = session.query(Volunteer).filter_by(name='Joey Wood').first() moey = VolunteerInterests("youth", doey.id) joey = session.query(VolunteerInterests).filter_by(volunteer_id=doey.id).first() #self.assertTrue(moey.interest == joey.interest) session.close()
def username_exists(username): # returns false if username doesnt exist session = Session() if session.query(User).filter_by(username=username).first() == None: session.close() return False session.close() return True
def catalogJSON(): """ Full catalog API Endpoint """ # Create DB session db_session = Session() # Query all categories categories = db_session.query(Category).all() serialized_categories = [] for category in categories: # Query all items for each category items = ( db_session .query(Item) .filter_by(category_id=category.id) .order_by(Item.title) .all() ) # Serialize current category and add corresponding serialized items serialized_category = category.serialize serialized_category["Items"] = [] for item in items: serialized_category["Items"].append(item.serialize) serialized_categories.append(serialized_category) # Close DB session db_session.close() return jsonify(Categories=[ category for category in serialized_categories])
def get_skills(id): s = Session() result = [] q = s.query(VolunteerSkills).filter_by(volunteer_id=id) for sk in q: result.append(sk.skill) s.close() return result
def test_18_updating_email(self): session = Session() joey = session.query(User).filter_by(name='Joey Wood').first() q = session.query(User).filter_by(id=joey.id) q = q.update({"email":"*****@*****.**"}) joey = session.query(User).filter_by(id=joey.id).first() self.assertTrue(joey.email == '*****@*****.**') session.close()
def test_17_updating_name(self): session = Session() joey = session.query(User).filter_by(name='Joey Wood').first() q = session.query(User).filter_by(id=joey.id) q = q.update({"name":"Wood Joey"}) joey = session.query(User).filter_by(id=joey.id).first() self.assertTrue(joey.name == 'Wood Joey') session.close()
def get_interests(id): s = Session() result = [] q = s.query(VolunteerInterests).filter_by(volunteer_id=id) for n in q: result.append(n.interest) s.close() return result
def test_05_updating_email(self): session = Session() test = session.query(User).filter_by(name='Test Org').first() q = session.query(User).filter_by(id=test.id) q = q.update({"email":"*****@*****.**"}) test = session.query(User).filter_by(id=test.id).first() self.assertTrue(test.email == '*****@*****.**') session.close()
def test_06_updating_zip(self): session = Session() race = session.query(Event).filter_by(name='Race for the Cure').first() q = session.query(Event).filter_by(id=race.id) q = q.update({"zip":"02120"}) race = session.query(Event).filter_by(id=race.id).first() self.assertTrue(race.zip == '02120') session.close()
def test_07_updating_email(self): session = Session() mickey = session.query(User).filter_by(name='Mickey Mouse').first() q = session.query(User).filter_by(id=mickey.id) q = q.update({"email":"*****@*****.**"}) mickey = session.query(User).filter_by(id=mickey.id).first() self.assertTrue(mickey.email == '*****@*****.**') session.close()
def get_neighborhoods(id): s = Session() result = [] q = s.query(VolunteerNeighborhoods).filter_by(volunteer_id=id) for n in q: result.append(n.neighborhood) s.close() return result
def test_06_updating_name(self): session = Session() mickey = session.query(User).filter_by(name='Mickey Mouse').first() q = session.query(User).filter_by(id=mickey.id) q = q.update({"name":"Wood Joey"}) mickey = session.query(User).filter_by(id=mickey.id).first() self.assertTrue(mickey.name == 'Wood Joey') session.close()
def test_04_updating_name(self): session = Session() test = session.query(User).filter_by(name='Test Org').first() q = session.query(User).filter_by(id=test.id) q = q.update({"name":"Wood Joey"}) test = session.query(User).filter_by(id=test.id).first() self.assertTrue(test.name == 'Wood Joey') session.close()
def show(self, post_id): session = Session() post = session.query(Post).get(post_id) session.close() if post == None: raise web.notfound() else: return post
def getOrgMember(self, id): s = Session() content = s.query(OrgMember).filter_by(id=id).first() s.close() if content: return content else: raise ValueError("user does not exist")
def test_05_updating_name(self): session = Session() race = session.query(Event).filter_by(name='Race for the Cure').first() q = session.query(Event).filter_by(id=race.id) q = q.update({"name":"Wood Joey"}) race = session.query(Event).filter_by(id=race.id).first() self.assertTrue(race.name == 'Wood Joey') session.close()
def updateEvent(self, event_id, update_data): session = Session() try: session.query(Event).filter_by(id=event_id).update(json.loads(update_data)) except: session.rollback() raise #exception of some sort finally: session.close()
def load_user(userid): if not userid: return None session = Session() user = session.query(Author).filter(Author.id == int(userid)).first() session.close() return user
def test_15_sign_up(self): session = Session() joey = session.query(Volunteer).filter_by(name='Joey Wood').first() event = session.query(Event).filter_by(state='MA').first() addEvent(event.id, joey.id) attendee = session.query(Attendee).filter_by(userID=joey.id).first() print(attendee.userID) print(joey.id) self.assertTrue(attendee.userID == joey.id) session.close()