def get_chapter(self, screen_gid): """获取chapter4""" # import datetime # t1 = datetime.datetime.now() # print(datetime.datetime.now()) chapter_list = [] q = ( db.session.query(Chapter4).filter( Chapter4.screen_gid == screen_gid) # .options(subqueryload('displays'), # 多的时候可以提高性能,少的时候没有效果 # subqueryload('conditions'), # subqueryload('opetypes')) ) q = q.order_by(Chapter4.hk_chapter, Chapter4.hk_sub_chapter, Chapter4.hk_dev_name, func.length(Chapter4.hk_no), Chapter4.hk_no, func.length(Chapter4.hk_state_no), Chapter4.hk_state_no, Chapter4.hk_condition_branch).all() for chapter_info in q: chapter = chapter_info.to_dict() chapter["hk_condition_phrase"] = chapter_info.conditions.condition chapter["hk_condition_code"] = chapter_info.conditions.view_model chapter["hk_ope_type"] = chapter_info.opetypes.ope_type chapter["hk_event"] = chapter_info.opetypes.ope_event chapter["hk_action"] = chapter_info.displays.display chapter["hk_action_model"] = chapter_info.displays.fun_of_model chapter_list.append(chapter) # t2 = datetime.datetime.now() # print(t2) # print(t2 - t1) return chapter_list
def downgrade(): # ### commands auto generated by Alembic - please adjust! ### op.drop_column('crest_fleets', 'registration_time') op.drop_table('fleet_time_last_tracked') op.drop_table('fleet_time_by_hull') op.drop_table('fleet_time') connection = op.get_bind() t_tokens = sa.Table( 'ssotoken', sa.MetaData(), sa.Column('access_token', sa.Text()), sa.Column('access_token_expires', sa.DateTime(), default=datetime.utcnow), sa.Column('refresh_token', sa.Text()), ) connection.execute( t_tokens.delete()\ .where(func.length(t_tokens.c.refresh_token) > 128)\ ) op.alter_column('ssotoken', 'refresh_token', existing_type=sa.Text(), type_=sa.VARCHAR(length=128), existing_nullable=True) connection.execute( t_tokens.update()\ .where(func.length(t_tokens.c.access_token) > 128)\ .values(access_token=None, access_token_expires=datetime.utcnow())\ ) op.alter_column('ssotoken', 'access_token', existing_type=sa.Text(), type_=sa.VARCHAR(length=128), existing_nullable=True)
def conv_cizu2zi(): words = {} for w in Word.query.filter(func.length(Word.word) <= 3).all(): words.setdefault((w.user_id, w.word, w.book, w.chapter), w) print(f"read {len(words)} cizu") del_ids = [] for w in Word.query.filter(func.length(Word.word) > 3).all(): cizu = w.word can_delete = True for zi in cizu: k = (w.user_id, zi, w.book, w.chapter) if k in words: print(f"skip {zi}") else: print("adding ", zi, w.book, w.chapter) w2 = Word(user_id=w.user_id, word = zi, book = w.book, chapter = w.chapter, study_date = w.study_date, cur_xpoints = w.cur_xpoints, tot_xpoints = w.tot_xpoints, num_pass = w.num_pass, num_fail = w.num_fail, streak = w.streak, istep = w.istep, next_study = w.next_study) db.session.add(w2) can_delete = False words[k] = w2 db.session.commit() if can_delete: print(f"delete {cizu}, {w.id}") del_ids.append(w.id) db.session.delete(w) db.session.commit()
def analyzeShortPages(maxLength, sampleSize=None, rseed=6778): ''' Print (a sample of) pages with tokenized length less than maxLength. ''' from sqlalchemy.sql.expression import func, and_ from random import sample, seed session = Session() if isinstance(maxLength, tuple): mn, mx = maxLength else: mn = 0 mx = maxLength res = [ o for o in session.query(PageExtTok).filter( and_( func.length(PageExtTok.text) >= mn, func.length(PageExtTok.text) <= mx)) ] print len(res) if sampleSize and sampleSize < len(res): seed(rseed) res = sample(res, sampleSize) ids = set(pet.id for pet in res) origTxt = [o for o in session.query(PageExt).filter(PageExt.id.in_(ids))] oId2Txt = {o.id: o for o in origTxt} for t in res: orig = oId2Txt[t.id] print t.id, orig.name, len(orig.plainText) print u'text = [%s]' % orig.plainText.strip()
def classify(bnm, sentiment, length): short = True if length < 3000 else False get_length_q = { True: func.length(Content.content) <= 3000, False: func.length(Content.content) > 3000 } bad_count = (session.query(Content).join( Review, Review.reviewid == Content.reviewid).filter( get_length_q[short], Review.best_new_music == bnm, Content.sentiment == sentiment, Review.score <= 3).count()) mid_count = (session.query(Content).join( Review, Review.reviewid == Content.reviewid).filter( get_length_q[short], Review.best_new_music == bnm, Content.sentiment == sentiment, Review.score > 3, Review.score <= 7, ).count()) good_count = (session.query(Content).join( Review, Review.reviewid == Content.reviewid).filter( get_length_q[short], Review.best_new_music == bnm, Content.sentiment == sentiment, Review.score > 7).count()) klass = max(bad_count, mid_count, good_count) if klass == bad_count: return "Bad (0-3)" elif klass == mid_count: return "Mid (4-7)" else: return "Good (8-10)"
def allUpdatesSource(series=None): A = db.sequence_series2_updates.alias(name="A") #B = db.sequence_series2_updates.alias(name="B") maxIndex = None if series is None: maxIndex = db.connection.execute( sql.select((func.max(A.c.dummy_id),)) ).fetchone()[0] else: maxIndex = db.connection.execute( sql.select((func.max(A.c.dummy_id),)).where(A.c.source==series) ).fetchone()[0] if maxIndex is None: print("No updates found") return print("Total update records: %d" % maxIndex) #maxIndex = 4949 groupSize = 5000 if args.stop_at_id > 0: maxIndex = args.stop_at_id total = 0 for fromId in range(args.start_from_id, maxIndex, groupSize): q = None if series is None: q = sql.select(( A.c.dummy_id, A.c.sequence_id, func.length(A.c.content))).where( sql.and_( # A.c.dummy_id < B.c.dummy_id, # A.c.sequence_id == B.c.sequence_id, # A.c.source == B.c.source, sql.between(A.c.dummy_id, fromId, min(fromId+groupSize-1, maxIndex)) )).order_by(A.c.dummy_id) else: q = sql.select(( A.c.dummy_id, A.c.sequence_id, func.length(A.c.content))).where( sql.and_( # A.c.dummy_id < B.c.dummy_id, # A.c.sequence_id == B.c.sequence_id, A.c.source == series, sql.between(A.c.dummy_id, fromId, min(fromId+groupSize-1, maxIndex)) )).order_by(A.c.dummy_id) #print("Executing query for range(%d, %d)..." % (fromId, fromId+groupSize)) result = db.connection.execute( q ) pairRecords = result.fetchall() for r in pairRecords: yield r total += len(pairRecords) print("Total: %d" % total)
def __init__(self, wld_id, bra_id): TradePartner.__init__(self, wld_id, bra_id) self.max_year_query = db.session.query(func.max(Ymbw.year)).filter_by( wld_id=wld_id, month=12) if bra_id is not None: self.secex_query = Ymbw.query.join(Wld).join(Bra).filter( Ymbw.wld_id == self.wld_id, Ymbw.bra_id.like(self.bra_id + '%'), Ymbw.month == 0, Ymbw.year == self.max_year_query, func.length(Ymbw.bra_id) == 9) else: self.secex_query = Ymbw.query.join(Wld).join(Bra).filter( Ymbw.wld_id == self.wld_id, Ymbw.month == 0, Ymbw.year == self.max_year_query, func.length(Ymbw.bra_id) == 9)
def upgrade(): """ Sets all depot files for file typed revisions. Until now, files are both in database and, for the newly created ones, on disk. In order to simplify the migration, this procedure will: - delete the few files on disk, - create all files on disk from database. """ # Creates files depot used in this migration configure_depot() connection = op.get_bind() delete_files_on_disk(connection=connection) select_query = revision_helper.select() \ .where(revision_helper.c.type == 'file') \ .where(revision_helper.c.depot_file.is_(None)) \ .where(func.length(revision_helper.c.file_content) > 0) files = connection.execute(select_query).fetchall() for file in files: file_filename = '{0}{1}'.format( file.label, file.file_extension, ) depot_file_intent = FileIntent( file.file_content, file_filename, file.file_mimetype, ) depot_file_field = UploadedFile(depot_file_intent, 'tracim') update_query = revision_helper.update() \ .where(revision_helper.c.revision_id == file.revision_id) \ .values(depot_file=depot_file_field) connection.execute(update_query)
def get_open_tasks(): try: page = int(request.args.get("page",1)) except ValueError: page = 1 with db.session_scope() as sess: per_page = 10 task_count = sess.query(db.OpenTask)\ .filter(db.OpenTask.solved == False).count() open_tasks = sess.query(db.OpenTask)\ .order_by(db.OpenTask.datetime.asc(), func.substr(db.OpenTask.id, 0, func.length(db.OpenTask.id) - 2))\ .filter(db.OpenTask.solved == False)\ .offset((page-1) * per_page).limit(per_page)\ .all() pagination = Pagination(page=page, total=task_count, search=False, per_page=per_page, bs_version=3) return render_template("task_list.html", tasks = open_tasks, pagination = pagination)
def __init__(self, university_id): University.__init__(self, university_id) self.max_year_query = db.session.query(func.max(Yuc.year)) self.hedu_query = Yuc.query.filter( Yuc.university_id == self.university_id, Yuc.year == self.max_year_query, func.length(Yuc.course_hedu_id) == 6)
def get_chapter(self, screen_gid): """获取chapter1""" chapter_list = [] q = db.session.query(Chapter1).filter( Chapter1.screen_gid == screen_gid) q = q.order_by(Chapter1.display_chapter, Chapter1.display_sub_chapter, Chapter1.parts_number.ilike("S").desc(), Chapter1.parts_number.ilike("P").desc(), func.length(Chapter1.parts_number), Chapter1.parts_number, Chapter1.parts_name, Chapter1.display_condition_branch).all() for chapter_info in q: chapter = chapter_info.to_dict() chapter[ "display_condition_phrase"] = chapter_info.conditions.condition chapter[ "display_condition_code"] = chapter_info.conditions.view_model chapter[ "display_property_type"] = chapter_info.properties.property_type chapter["display_property"] = chapter_info.properties.property chapter["display_action"] = chapter_info.displays.display chapter[ "display_action_model"] = chapter_info.displays.fun_of_model chapter_list.append(chapter) return chapter_list
def get(self, kind, code): """ Returns supports for kind and code prefix. """ return paginate( page_args, Support.query.filter(Support.kind == kind, Support.code.like(code + '%'), func.length(Support.code) == len(code) + 2))
def sessionmaker(dbconfig): dbconfig = dbconfig.copy() conn_str = dbconfig.pop('url') if 'schema' in dbconfig: schema = dbconfig.pop('schema') else: schema = None engine = create_engine(conn_str, **dbconfig) mappers, tables, Session = reflect(engine, models, schema) # add mapper relationships mappers['Data'].add_properties({ 'versions': relationship(models.Version, lazy='dynamic', backref=backref('ref', lazy='joined')) }) mappers['Version'].add_properties({ 'data': deferred(tables['version'].c['data']), 'size': column_property(func.length(tables['version'].c['data'])) }) Session.class_.mappers = mappers return Session
def visualize_redirect(app_name='tree_map'): '''fetch random country''' c = Country.query.get(choice(random_countries)) latest_hs_year = [available_years['hs92'][-1]] if app_name in ["tree_map", "stacked", "network"]: year = [available_years['hs92'][0], available_years['hs92'][-1] ] if app_name == "stacked" else latest_hs_year redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \ classification="hs92", trade_flow="export", \ origin_id=c.id_3char, dest_id="all", prod_id="show", year=year) elif app_name in ["geo_map", "rings"]: '''fetch random product''' p = Hs92.query.filter(Hs92.hs92 != None).filter(func.length(Hs92.hs92) == 4) \ .order_by(func.random()).limit(1).first() origin = "show" if app_name == "rings": origin = c.id_3char redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \ classification="hs92", trade_flow="export", \ origin_id=origin, dest_id="all", prod_id=p.hs92, year=latest_hs_year) elif app_name in ["scatter"]: redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \ classification="hs92", trade_flow="gdp", \ origin_id="show", dest_id="all", prod_id="all", year=latest_hs_year) else: abort(404) return redirect(redirect_url)
def _load_preview(self, version): if version.metadata['file_type'] not in ('csv', 'text'): return None, None preview_data = g.dbsession.query(func.substring(Version.data, 1, 18000), func.length(Version.data))\ .filter(Version.id==version.id).one() preview_data, version_length = preview_data preview_data = str(preview_data) is_preview_full = ( len(preview_data) == version_length ) if not is_preview_full: try: last_return = preview_data.rindex('\n') except ValueError: # too long preview return None, None preview_data = preview_data[:last_return] encoding = chardet.detect(preview_data)['encoding'] if encoding is None: return None, None try: preview_data = preview_data.decode(encoding) except UnicodeDecodeError: return None, None preview_data = preview_data.replace(',', ', ').replace(';', '; ') preview_data = preview_data.strip().split('\n') return preview_data, is_preview_full
def visualize_redirect(app_name='tree_map'): '''fetch random country''' c = Country.query.get(choice(random_countries)) latest_hs_year = [available_years['hs92'][-1]] if app_name in ["tree_map", "stacked", "network"]: year = [available_years['hs92'][0], available_years['hs92'][-1]] if app_name == "stacked" else latest_hs_year redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \ classification="hs92", trade_flow="export", \ origin_id=c.id_3char, dest_id="all", prod_id="show", year=year) elif app_name in ["geo_map", "rings"]: '''fetch random product''' p = Hs92.query.filter(Hs92.hs92 != None).filter(func.length(Hs92.hs92) == 4) \ .order_by(func.random()).limit(1).first() origin = "show" if app_name == "rings": origin = c.id_3char redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \ classification="hs92", trade_flow="export", \ origin_id=origin, dest_id="all", prod_id=p.hs92, year=latest_hs_year) elif app_name in ["scatter"]: redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \ classification="hs92", trade_flow="gdp", \ origin_id="show", dest_id="all", prod_id="all", year=latest_hs_year) else: abort(404) return redirect(redirect_url)
def stocktype_completemanufacturer(m): result = s.execute( select([StockType.manufacturer]).\ where(StockType.manufacturer.ilike(m + '%')).\ group_by(StockType.manufacturer).\ order_by(func.length(StockType.manufacturer), StockType.manufacturer) ) return [x[0] for x in result]
def db_get_all_translations(): """ function that queries the database for all the translations, and order them by descending order of length(translated_string). :return translations: Array with the ordered translations """ translations = Translation.query.order_by( desc(func.length(Translation.translated_string))).all() return translations
def get_by_length(self, type, column, value, limit): # Open the connection self.connect() result = self.session.query(type).filter( or_(and_(func.length(KataDasar.kata) > value))).limit(limit).all() # Close the connection self.disconnect() return result
async def fix_address(startBlockNumber: int, endBlockNumber: int, batchSize: int): databaseConnectionString = Database.create_psql_connection_string( username=os.environ["DB_USERNAME"], password=os.environ["DB_PASSWORD"], host=os.environ["DB_HOST"], port=os.environ["DB_PORT"], name=os.environ["DB_NAME"]) database = Database(connectionString=databaseConnectionString) await database.connect() currentBlockNumber = startBlockNumber while currentBlockNumber < endBlockNumber: start = currentBlockNumber end = min(currentBlockNumber + batchSize, endBlockNumber) logging.info(f'Working on {start} to {end}...') async with database.transaction(): query = TokenTransfersTable.select() query = query.where(TokenTransfersTable.c.blockNumber >= start) query = query.where(TokenTransfersTable.c.blockNumber < end) query = query.where( or_( sqlalchemyfunc.length( TokenTransfersTable.c.toAddress) != 42, sqlalchemyfunc.length(TokenTransfersTable.c.toAddress) != 42, )) tokenTransfersToChange = [ token_transfer_from_row(row) async for row in database.iterate(query=query) ] logging.info( f'Updating {len(tokenTransfersToChange)} transfers...') for tokenTransfer in tokenTransfersToChange: query = TokenTransfersTable.update( TokenTransfersTable.c.tokenTransferId == tokenTransfer.tokenTransferId) values = { TokenTransfersTable.c.toAddress.key: normalize_address(tokenTransfer.toAddress), TokenTransfersTable.c.fromAddress.key: normalize_address(tokenTransfer.fromAddress), } await database.execute(query=query, values=values) currentBlockNumber = end await database.disconnect()
def getCommunesSearch(session, search, limit=50): req = session.query(distinct(VmCommunes.commune_maj), VmCommunes.insee, func.length(VmCommunes.commune_maj)).filter( VmCommunes.commune_maj.ilike('%' + search + '%')) if (current_app.config['ORDER_COMMUNES_BYLENGTH']): req = req.order_by(func.length(VmCommunes.commune_maj)) else: req = req.order_by(VmCommunes.commune_maj) req = req.limit(limit).all() communeList = list() for r in req: temp = {'label': r[0], 'value': r[1]} communeList.append(temp) return communeList
def __init__(self, wld_id, bra_id): TradePartner.__init__(self, wld_id, bra_id) self.max_year_query = db.session.query( func.max(Ymbw.year)).filter_by(wld_id=wld_id, month=12) if bra_id is not None: self.secex_query = Ymbw.query.join(Wld).join(Bra).filter( Ymbw.wld_id == self.wld_id, Ymbw.bra_id.like(self.bra_id + '%'), Ymbw.month == 0, Ymbw.year == self.max_year_query, func.length(Ymbw.bra_id) == 9) else: self.secex_query = Ymbw.query.join(Wld).join(Bra).filter( Ymbw.wld_id == self.wld_id, Ymbw.month == 0, Ymbw.year == self.max_year_query, func.length(Ymbw.bra_id) == 9)
def __init__(self, wld_id): TradePartner.__init__(self, wld_id) self.max_year_query = db.session.query(func.max(Ymbw.year)).filter_by(wld_id=wld_id) self.secex_query = Ymbw.query.join(Wld).join(Bra).filter( Ymbw.wld_id == self.wld_id, Ymbw.month == 0, Ymbw.year == self.max_year_query, func.length(Ymbw.bra_id) == 9)
def admin(self, id=None): if not h.auth.is_logged_in(): abort(401) if not h.auth.is_admin(): abort(403) c.sepdirnew = False c.alreadysepdir = False redirect = request.params.get('redirect', False) add = request.params.get('add', False) limit = request.params.get('limit', None) entity_q = Session.query(Entity) c.found = False c.custom = False c.new = False if request.params.get('q'): q = request.params['q'] o = Entity.label.like(q) entity_q = entity_q.filter(o).order_by(func.length(Entity.label)) # if only 1 result, go ahead and view that thinker if redirect and entity_q.count() == 1: print "have a q, entityq count = 1" c.thinker = h.fetch_obj(Thinker, entity_q.first().ID) c.found = True id = c.thinker.ID c.message = 'Entity edit page for thinker ' + c.thinker.name if request.params.get('entry_sep_dir'): entry_sep_dir = request.params['entry_sep_dir'] if not (c.thinker.sep_dir): c.thinker.sep_dir = request.params['entry_sep_dir'] c.sepdirnew = True else: c.alreadysepdir = True c.entry_sep_dir = request.params['entry_sep_dir'] return render('admin/thinker-edit.html') else: print "That didn't work." if id is None: print "I am here" c.message = "Please input an entity label using the search bar to the left." return render ('admin/thinker-edit.html') else: c.thinker = h.fetch_obj(Thinker, id) c.found = True c.message = 'Entity edit page for thinker ' + c.thinker.name if request.params.get('entry_sep_dir'): entry_sep_dir = request.params['entry_sep_dir'] if not (c.thinker.sep_dir): c.thinker.sep_dir = request.params['entry_sep_dir'] c.sepdirnew = True else: c.alreadysepdir = True c.entry_sep_dir = request.params['entry_sep_dir'] return render ('admin/thinker-edit.html')
def admin(self, id=None): if not h.auth.is_logged_in(): abort(401) if not h.auth.is_admin(): abort(403) c.sepdirnew = False c.alreadysepdir = False redirect = request.params.get('redirect', False) add = request.params.get('add', False) limit = request.params.get('limit', None) entity_q = Session.query(Entity) c.found = False c.custom = False c.new = False if request.params.get('q'): q = request.params['q'] o = Entity.label.like(q) entity_q = entity_q.filter(o).order_by(func.length(Entity.label)) # if only 1 result, go ahead and view that thinker if redirect and entity_q.count() == 1: print "have a q, entityq count = 1" c.thinker = h.fetch_obj(Thinker, entity_q.first().ID) c.found = True id = c.thinker.ID c.message = 'Entity edit page for thinker ' + c.thinker.name if request.params.get('entry_sep_dir'): entry_sep_dir = request.params['entry_sep_dir'] if not (c.thinker.sep_dir): c.thinker.sep_dir = request.params['entry_sep_dir'] c.sepdirnew = True else: c.alreadysepdir = True c.entry_sep_dir = request.params['entry_sep_dir'] return render('admin/thinker-edit.html') else: print "That didn't work." if id is None: print "I am here" c.message = "Please input an entity label using the search bar to the left." return render('admin/thinker-edit.html') else: c.thinker = h.fetch_obj(Thinker, id) c.found = True c.message = 'Entity edit page for thinker ' + c.thinker.name if request.params.get('entry_sep_dir'): entry_sep_dir = request.params['entry_sep_dir'] if not (c.thinker.sep_dir): c.thinker.sep_dir = request.params['entry_sep_dir'] c.sepdirnew = True else: c.alreadysepdir = True c.entry_sep_dir = request.params['entry_sep_dir'] return render('admin/thinker-edit.html')
def list(self, filetype='html'): entity_q = Session.query(self._type) #TODO: Remove the following line when Nodes are eliminated entity_q = entity_q.filter(Entity.typeID != 2) c.missing_entity = 0 # get the list of entities #c.entities = entity_q.all() c.nodes = Session.query(Node).filter(Node.parent_id == None) c.nodes = c.nodes.order_by("name").all() c.query = request.params.get('q', '') c.query = c.query.strip() c.sep = request.params.get('sep', '') c.wiki = request.params.get('wiki', '') if request.params.get('sep_filter', False): entity_q = entity_q.filter(Entity.sep_dir != '') if c.sep: entity_q = entity_q.filter(Entity.sep_dir == c.sep) if c.wiki: entity_q = entity_q.filter(Entity.wiki == c.wiki) if c.query: o = or_(Entity.label.like(c.query + '%'), Entity.label.like('% ' + c.query + '%'), Entity.label.like('%-' + c.query + '%')) entity_q = entity_q.filter(o).order_by(func.length(Entity.label)) c.total = entity_q.count() # limit must be the last thing applied to the query entity_q = entity_q.limit(request.params.get('limit', None)) c.entities = entity_q.all() if filetype == 'json': response.content_type = 'application/json' if request.params.get('redirect', False) and len(c.entities) == 1: h.redirect(h.url(controller=self._controller, action='view', filetype=filetype, id=c.entities[0].ID), code=302) else: #if there are no results, show the related SEP results if not c.entities: c.entities = self.missing_entity_search(c.query) if c.entities: c.missing_entity = 1 #raise Exception #render the page return render('{type}/{type}-list.'.format(type=self._controller) + filetype)
def get_chapter(self, screen_gid): """获取chapter5""" chapter_list = [] q = db.session.query(Chapter5).filter( Chapter5.screen_gid == screen_gid) q = q.order_by(Chapter5.init_chapter, func.length(Chapter5.init_no), Chapter5.init_no, func.length(Chapter5.init_state_no), Chapter5.init_state_no, Chapter5.init_condition_branch).all() for chapter_info in q: chapter = chapter_info.to_dict() chapter[ "init_condition_phrase"] = chapter_info.conditions.condition chapter["init_condition_code"] = chapter_info.conditions.view_model chapter["init_action"] = chapter_info.displays.display chapter["init_action_model"] = chapter_info.displays.fun_of_model chapter_list.append(chapter) return chapter_list
def list(self, filetype="html"): entity_q = Session.query(self._type) # TODO: Remove the following line when Nodes are eliminated entity_q = entity_q.filter(Entity.typeID != 2) c.missing_entity = 0 # get the list of entities # c.entities = entity_q.all() c.nodes = Session.query(Node).filter(Node.parent_id == None) c.nodes = c.nodes.order_by("name").all() c.query = request.params.get("q", "") c.query = c.query.strip() c.sep = request.params.get("sep", "") c.wiki = request.params.get("wiki", "") if request.params.get("sep_filter", False): entity_q = entity_q.filter(Entity.sep_dir != "") if c.sep: entity_q = entity_q.filter(Entity.sep_dir == c.sep) if c.wiki: entity_q = entity_q.filter(Entity.wiki == c.wiki) if c.query: o = or_( Entity.label.like(c.query + "%"), Entity.label.like("% " + c.query + "%"), Entity.label.like("%-" + c.query + "%"), ) entity_q = entity_q.filter(o).order_by(func.length(Entity.label)) c.total = entity_q.count() # limit must be the last thing applied to the query entity_q = entity_q.limit(request.params.get("limit", None)) c.entities = entity_q.all() if filetype == "json": response.content_type = "application/json" if request.params.get("redirect", False) and len(c.entities) == 1: h.redirect( h.url(controller=self._controller, action="view", filetype=filetype, id=c.entities[0].ID), code=302 ) else: # if there are no results, show the related SEP results if not c.entities: c.entities = self.missing_entity_search(c.query) if c.entities: c.missing_entity = 1 # raise Exception # render the page return render("{type}/{type}-list.".format(type=self._controller) + filetype)
def test_update_task(c): task = Task.query.filter(func.length(Task.offices) == 1).first() old_name = task.name new_name = f'{uuid4()}'.replace('-', '') c.post(f'/task/{task.id}', data={'name': new_name}, follow_redirects=True) assert Task.query.filter_by(name=old_name).first() is None assert Task.query.filter_by(name=new_name).first() is not None
def stocktype_completename(m, n): result = s.execute( select([StockType.name]).\ where(StockType.manufacturer == m).\ where(StockType.name.ilike(n + '%')).\ group_by(StockType.name).\ order_by(func.length(StockType.name), StockType.name) ) return [x[0] for x in result]
def __init__(self, bra_id): self._secex = None self._secex_sorted_by_eci = None self.bra_id = bra_id self.max_year_query = db.session.query(func.max(Ymb.year)).filter_by( bra_id=self.bra_id, month=12) self.secex_query = Ymb.query.filter(Ymb.year == self.max_year_query, Ymb.month == 0, func.length(Ymb.bra_id) == 5)
def create_node_definitions() -> dict: regexes_and_corresponding_ids = dict() # Apply longest term first: Prefer "world spirit" over "spirit". for node in get_all_nodes().order_by(desc(func.length(BlogPost.name))): regex_source = rf"\[.*?\]|({re.escape(node.name)})" regex = re.compile(regex_source, flags=re.IGNORECASE) regexes_and_corresponding_ids[node.name] = (regex, node.id) return regexes_and_corresponding_ids
def __init__(self, course_hedu_id): Major.__init__(self, course_hedu_id) self.course_hedu_id = course_hedu_id self.max_year_query = db.session.query(func.max(Ybc_hedu.year)).filter_by(course_hedu_id=course_hedu_id) self.hedu_query = Ybc_hedu.query.filter( Ybc_hedu.course_hedu_id == self.course_hedu_id, Ybc_hedu.year == self.max_year_query, func.length(Ybc_hedu.bra_id) == 9)
def __init__(self, bra_id): self._secex = None self._secex_sorted_by_eci = None self.bra_id = bra_id self.max_year_query = db.session.query( func.max(Ymb.year)).filter_by(bra_id=self.bra_id, month=12) self.secex_query = Ymb.query.filter( Ymb.year == self.max_year_query, Ymb.month == 0, func.length(Ymb.bra_id) == 5)
def obter_documentos_externos(): query = session().query( TB_DOCUMENTO.c.docu_nr_externo, TB_DOCUMENTO.c.docu_dk, TB_PROCESSO.c.prtj_dt_ultima_vista).outerjoin( TB_PROCESSO, TB_DOCUMENTO.c.docu_nr_externo == TB_PROCESSO.c.prtj_cd_numero_processo ).filter(TB_DOCUMENTO.c.docu_mate_dk == 4).filter( func.length(TB_DOCUMENTO.c.docu_nr_externo) == 20).order_by( nullsfirst(TB_PROCESSO.c.prtj_dt_ultima_vista), ) return [(doc[0], doc[1]) for doc in query]
def __init__(self, course_hedu_id, bra_id): Major.__init__(self, course_hedu_id, bra_id) self.course_hedu_id = course_hedu_id self.max_year_query = db.session.query( func.max(Ybc_hedu.year)).filter_by(course_hedu_id=course_hedu_id) if bra_id == '': self.hedu_query = Ybc_hedu.query.filter( Ybc_hedu.course_hedu_id == self.course_hedu_id, Ybc_hedu.year == self.max_year_query, not_(Ybc_hedu.bra_id.like('0xx%')), func.length(Ybc_hedu.bra_id) == 9) else: self.hedu_query = Ybc_hedu.query.filter( Ybc_hedu.course_hedu_id == self.course_hedu_id, Ybc_hedu.year == self.max_year_query, Ybc_hedu.bra_id.like(self.bra_id+'%'), not_(Ybc_hedu.bra_id.like('0xx%')), func.length(Ybc_hedu.bra_id) == 9)
def __init__(self, course_hedu_id, bra_id): Major.__init__(self, course_hedu_id, bra_id) self.course_hedu_id = course_hedu_id self.max_year_query = db.session.query(func.max( Ybc_hedu.year)).filter_by(course_hedu_id=course_hedu_id) if bra_id == '': self.hedu_query = Ybc_hedu.query.filter( Ybc_hedu.course_hedu_id == self.course_hedu_id, Ybc_hedu.year == self.max_year_query, not_(Ybc_hedu.bra_id.like('0xx%')), func.length(Ybc_hedu.bra_id) == 9) else: self.hedu_query = Ybc_hedu.query.filter( Ybc_hedu.course_hedu_id == self.course_hedu_id, Ybc_hedu.year == self.max_year_query, Ybc_hedu.bra_id.like(self.bra_id + '%'), not_(Ybc_hedu.bra_id.like('0xx%')), func.length(Ybc_hedu.bra_id) == 9)
def compute_statistics(): n_all = Question.query.count() # Partial questions n_partial_query = Question.query.filter( db.and_(Question.n_answers > 0, Question.n_answers < MAX_ANSWERS)) n_partial = n_partial_query.count() partial_questions_array = [0, 0, 0] all_partial = n_partial_query.all() for question in all_partial: partial_questions_array[question.n_answers - 1] += 1 n_full_questions = Question.query.filter( Question.n_answers == MAX_ANSWERS).count() # Partial examples n_full_examples, partial_examples = count_full_examples() partial_examples_array = [0, 0, 0, 0, 0] for example in partial_examples: partial_examples_array[5 - example[1]] += 1 # Non-empty and non-full examples: questions_not_empty = Question.query.filter(Question.n_answers > 0).all() examples_not_empty = set() for q in questions_not_empty: examples_not_empty.add(q.example) examples_not_empty = list(examples_not_empty) examples_n_answers = [] for example in examples_not_empty: sum_answers = Question.query.filter( Question.example == example).with_entities( func.sum(Question.n_answers)).scalar() # Do not add examples that have been fully answered if not sum_answers == MAX_ANSWERS * (N_MODELS * (N_MODELS - 1) / 2): examples_n_answers += [[example, sum_answers]] n_answers = Answer.query.count() n_participants = User.query.count() non_zero_participants = User.query.join(Answer).filter( func.length(User.answers) > 0).all() print "Total", n_all print "Full examples:", n_full_examples print "Partial examples:", len(partial_examples), ', distribution:', zip([ '1 question:', '2 questions:', '3 questions:', '4 questions:', '5 questions:' ], partial_examples_array) print "Non-empty examples:", len(examples_n_answers) print "Full questions: ", n_full_questions print "Partial questions:", n_partial, ', distribution:', zip( ['1 answer:', '2 answers:', '3 answers:'], partial_questions_array) print 'Total answers:', n_answers print 'Total participants:', n_participants, "(non-zero:", len( non_zero_participants), ")"
def upgrade(): session = Session(bind=op.get_bind()) session.query(RequestsLog).filter( func.length(RequestsLog.message) > MAX_LEN_MSG_REQUESTS_LOG).delete( synchronize_session=False) session.query(RequestsLog).filter_by(tag=None).update({"tag": ""}) # ### commands auto generated by Alembic - please adjust! ### op.alter_column("requests_log", "tag", existing_type=sa.TEXT(), nullable=False)
def test_incident_detail_display_read_more_button_for_descriptions_over_300_chars( mockdata, browser): # Navigate to profile page for officer with short and long incident descriptions browser.get("http://localhost:5000/officer/1") incident_long_descrip = Incident.query.filter( func.length(Incident.description) > 300).one_or_none() incident_id = str(incident_long_descrip.id) result = browser.find_element_by_id("description-overflow-row_" + incident_id) assert result.is_displayed()
def get_chapter(self, screen_gid): """获取chapter8""" chapter_list = [] q = db.session.query(Chapter8).filter( Chapter8.screen_gid == screen_gid) q = q.order_by(Chapter8.trig_chapter, Chapter8.trig_sub_chapter, func.length(Chapter8.trig_no), Chapter8.trig_no, func.length(Chapter8.trig_state_no), Chapter8.trig_state_no, Chapter8.trig_condition_branch).all() for chapter_info in q: chapter = chapter_info.to_dict() chapter[ "trig_condition_phrase"] = chapter_info.conditions.condition chapter["trig_condition_code"] = chapter_info.conditions.view_model chapter["trig_name"] = chapter_info.events.event chapter["trig_trig"] = chapter_info.events.trigger chapter["trig_action"] = chapter_info.displays.display chapter["trig_action_model"] = chapter_info.displays.fun_of_model chapter_list.append(chapter) return chapter_list
def index(): course_hedu_id = "523E04" major_service = Major(course_hedu_id=course_hedu_id) universities_service = MajorUniversities(course_hedu_id=course_hedu_id) municipalities_service = MajorMunicipalities(course_hedu_id=course_hedu_id) max_year_query = db.session.query(func.max(Yc_hedu.year)).filter_by(course_hedu_id=course_hedu_id) rank_query = Yc_hedu.query.filter( Yc_hedu.year == max_year_query, func.length(Yc_hedu.course_hedu_id) == 6 ).order_by(Yc_hedu.course_hedu_id.desc()) rank = rank_query.all() header = { "name": major_service.name(), "enrolled": major_service.enrolled(), "entrants": major_service.entrants(), "graduates": major_service.graduates(), "profile": major_service.profile(), "year": major_service.year(), "portrait_id": course_hedu_id[:2], } content = { "university_with_more_enrolled": universities_service.university_with_more_enrolled(), "highest_enrolled_number_by_university": universities_service.highest_enrolled_number(), "municipality_with_more_enrolled": municipalities_service.municipality_with_more_enrolled(), "highest_enrolled_number_by_municipality": municipalities_service.highest_enrolled_number(), "university_with_more_entrants": universities_service.university_with_more_entrants(), "highest_entrant_number_by_university": universities_service.highest_entrants_number(), "municipality_with_more_entrants": municipalities_service.municipality_with_more_entrants(), "highest_entrant_number_by_municipality": municipalities_service.highest_entrants_number(), "university_with_more_graduates": universities_service.university_with_more_graduates(), "highest_graduate_number_by_university": universities_service.highest_graduates_number(), "municipality_with_more_graduates": municipalities_service.municipality_with_more_graduates(), "highest_graduate_number_by_municipality": municipalities_service.highest_graduates_number(), } for index, maj in enumerate(rank): if rank[index].course_hedu_id == course_hedu_id: header["rank"] = index break return render_template( "major/index.html", header=header, content=content, body_class="perfil-estado", static_folder=static_folder )
def query_by_attrs(model, attrs): filter_args = [] records = model.query for attr in attrs: str_len = False op = json.loads(attrs[attr]) if attr.endswith('_len'): str_len = True attr = attr[:-4] #try: if 'gte' in op.keys(): if str_len: records = records.filter(func.length(getattr(model, attr)) >=op['gte']) else: records = records.filter(getattr(model, attr) >= op['gte']) if 'lte' in op.keys(): if str_len: records = records.filter(func.length(getattr(model, attr)) <= op['lte']) else: records = records.filter(getattr(model, attr) <= op['lte']) if 'gt' in op.keys(): if str_len: records = records.filter(func.length(getattr(model, attr)) > op['gt']) else: records = records.filter(getattr(model, attr) > op['gt']) if 'lt' in op.keys(): if str_len: records = records.filter(func.length(getattr(model, attr)) < op['lt']) else: records = records.filter(getattr(model, attr) < op['lt']) if 'eq' in op.keys(): if str_len: records = records.filter(func.length(getattr(model, attr)) == op['eq']) else: records = records.filter(getattr(model, attr) == op['eq']) if 'ne' in op.keys(): if str_len: records = records.filter(func.length(getattr(model, attr)) <> op['ne']) else: records = records.filter(getattr(model, attr) <> op['ne']) #except: # raise ValueError("gt, lt, gte, lte values must be in a json format e.g., {'lt':'5', 'gt':'3'}") return records
def list(self, filetype='html'): redirect = request.params.get('redirect', False) limit = request.params.get('limit', None) entity_q = model.meta.Session.query(model.Entity) entity_q = entity_q.filter(model.Entity.typeID != 2) c.nodes = model.meta.Session.query(model.Node).filter(model.Node.parent_id == None).order_by("name").all() c.query = '' c.sep = '' if filetype=='json': response.content_type = 'application/json' if request.params.get('sep_filter'): entity_q = entity_q.filter(model.Entity.sep_dir != '') if request.params.get('sep'): entity_q = entity_q.filter(model.Entity.sep_dir == request.params['sep']) c.sep = request.params['sep'] # if only 1 result, go ahead and view that entity if redirect and entity_q.count() == 1: h.redirect(h.url(controller='entity', action='view', filetype=filetype, id=entity_q.first().ID), code=302) # Check for query if request.params.get('q'): q = request.params['q'] c.query = q o = or_(model.Entity.label.like(q+'%'), model.Entity.label.like('% '+q+'%')) entity_q = entity_q.filter(o).order_by(func.length(model.Entity.label)) # if only 1 result, go ahead and view that idea if redirect and entity_q.count() == 1: return self.view(entity_q.first().ID, filetype) else: c.entities = entity_q.limit(limit) return render('entity/entity-list.' + filetype) c.entities = entity_q.limit(limit) return render('entity/entity-list.' + filetype)
def index(course_hedu_id, tab): bra_id = request.args.get('bra_id') menu = request.args.get('menu') url = request.args.get('url') graph = {} if menu: graph['menu'] = menu if url: graph['url'] = url max_year_query = db.session.query( func.max(Yc_hedu.year)).filter_by(course_hedu_id=course_hedu_id) if bra_id: major_service = Major(course_hedu_id, bra_id) universities_service = MajorUniversities(course_hedu_id, bra_id) municipalities_service = MajorMunicipalities(course_hedu_id, bra_id) rank_query = Ybc_hedu.query.filter( Ybc_hedu.year == max_year_query, Ybc_hedu.bra_id == bra_id, func.length(Ybc_hedu.course_hedu_id) == len(course_hedu_id))\ .order_by(Ybc_hedu.enrolled.desc()) else: major_service = Major(course_hedu_id, '') universities_service = MajorUniversities(course_hedu_id, '') municipalities_service = MajorMunicipalities(course_hedu_id, '') rank_query = Yc_hedu.query.filter( Yc_hedu.year == max_year_query, func.length(Yc_hedu.course_hedu_id) == len(course_hedu_id))\ .order_by(Yc_hedu.enrolled.desc()) rank = rank_query.all() hedu_max_year = db.session.query(func.max(Yc_hedu.year)).first()[0] if not bra_id: header = { 'name': major_service.name(), 'enrolled': major_service.enrolled(), 'entrants': major_service.entrants(), 'graduates': major_service.graduates(), 'profile': major_service.profile(), 'year': major_service.year(), 'field_id': course_hedu_id[:2] } else: header = { 'name': major_service.name(), 'enrolled': major_service.enrolled(), 'entrants': major_service.entrants(), 'graduates': major_service.graduates(), 'profile': major_service.profile(), 'year': major_service.year(), 'field_id': course_hedu_id[:2], 'id': course_hedu_id, 'bra_id': bra_id, 'location_name': major_service.location_name() } body = { 'university_with_more_enrolled': universities_service.university_with_more_enrolled(), 'highest_enrolled_number_by_university': universities_service.highest_enrolled_number(), 'municipality_with_more_enrolled': municipalities_service.municipality_with_more_enrolled(), 'municipality_with_more_enrolled_state': municipalities_service.municipality_with_more_enrolled_state(), 'highest_enrolled_number_by_municipality': municipalities_service.highest_enrolled_number(), 'university_with_more_entrants': universities_service.university_with_more_entrants(), 'highest_entrant_number_by_university': universities_service.highest_entrants_number(), 'municipality_with_more_entrants': municipalities_service.municipality_with_more_entrants(), 'municipality_with_more_entrants_state': municipalities_service.municipality_with_more_entrants_state(), 'highest_entrant_number_by_municipality': municipalities_service.highest_entrants_number(), 'university_with_more_graduates': universities_service.university_with_more_graduates(), 'highest_graduate_number_by_university': universities_service.highest_graduates_number(), 'municipality_with_more_graduates': municipalities_service.municipality_with_more_graduates(), 'municipality_with_more_graduates_state': municipalities_service.municipality_with_more_graduates_state(), 'highest_graduate_number_by_municipality': municipalities_service.highest_graduates_number() } tabs = { 'general': [], 'enrollments': [ 'enrollments-university-tree_map', 'enrollments-municipality-geo_map', 'enrollments-municipality-stacked', 'enrollments-municipality-tree_map', 'enrollments-status-line', 'enrollments-shift-stacked', ], } for index, maj in enumerate(rank): if rank[index].course_hedu_id == course_hedu_id: header['rank'] = index + 1 break location = Bra.query.filter(Bra.id == bra_id).first() major = Course_hedu.query.filter(Course_hedu.id == course_hedu_id).first() if tab not in tabs: abort(404) if menu and menu not in tabs[tab]: abort(404) if header['enrolled'] is None or hedu_max_year != header['year']: abort(404) else: return render_template('major/index.html', header=header, body=body, location=location, major=major, tab=tab, graph=graph)
def db_fix_users(session): items = session.query(User).filter(func.length(User.password) == 47) for item in items: if(item.password[0] == 'b' and item.password[1] == '\'' and item.password[len(item.password) - 1] == '\''): item.password = item.password[2: -1]
def index(course_hedu_id, tab): bra_id = request.args.get('bra_id') bra_id = bra_id if bra_id != 'all' else None menu = request.args.get('menu') url = request.args.get('url') graph = {} if menu: graph['menu'] = menu if url: url_prefix = menu.split('-')[-1] + '/' if menu and menu.startswith('new-api-') else 'embed/' graph['url'] = url_prefix + url max_year_query = db.session.query( func.max(Yc_hedu.year)).filter_by(course_hedu_id=course_hedu_id) if bra_id: major_service = Major(course_hedu_id, bra_id) universities_service = MajorUniversities(course_hedu_id, bra_id) municipalities_service = MajorMunicipalities(course_hedu_id, bra_id) rank_query = Ybc_hedu.query.filter( Ybc_hedu.year == max_year_query, Ybc_hedu.bra_id == bra_id, func.length(Ybc_hedu.course_hedu_id) == len(course_hedu_id))\ .order_by(Ybc_hedu.enrolled.desc()) else: major_service = Major(course_hedu_id, '') universities_service = MajorUniversities(course_hedu_id, '') municipalities_service = MajorMunicipalities(course_hedu_id, '') rank_query = Yc_hedu.query.filter( Yc_hedu.year == max_year_query, func.length(Yc_hedu.course_hedu_id) == len(course_hedu_id))\ .order_by(Yc_hedu.enrolled.desc()) rank = rank_query.all() hedu_max_year = db.session.query(func.max(Yc_hedu.year)).first()[0] if not bra_id: header = { 'name': major_service.name(), 'enrolled': major_service.enrolled(), 'year': major_service.year(), } else: header = { 'name': major_service.name(), 'enrolled': major_service.enrolled(), 'year': major_service.year(), 'bra_id': bra_id, 'location_name': major_service.location_name() } body = { 'university_with_more_enrolled': universities_service.university_with_more_enrolled(), 'highest_enrolled_number_by_university': universities_service.highest_enrolled_number(), } tabs = { 'general': [], 'enrollments': [ 'enrollments-university-tree_map', 'new-api-enrollments-university-tree_map', 'enrollments-municipality-geo_map', 'new-api-enrollments-municipality-geo_map', 'enrollments-municipality-stacked', 'new-api-enrollments-municipality-stacked', 'enrollments-municipality-tree_map', 'new-api-enrollments-municipality-tree_map', 'enrollments-status-line', 'new-api-enrollments-status-line', 'enrollments-shift-stacked', 'new-api-enrollments-shift-stacked', ], } id_ibge = None for index, maj in enumerate(rank): if rank[index].course_hedu_id == course_hedu_id: header['rank'] = index + 1 break location = Bra.query.filter(Bra.id == bra_id).first() if bra_id: depth = location_depth(bra_id) id_ibge = _location_service(depth, location) is_municipality = True if depth == 'municipality' else False major = Course_hedu.query.filter(Course_hedu.id == course_hedu_id).first() if tab not in tabs: abort(404) if menu and menu not in tabs[tab]: abort(404) if header['enrolled'] is None or hedu_max_year != header['year']: abort(404) else: return render_template('major/index.html', header=header, body=body, id_ibge=id_ibge, location=location, major=major, tab=tab, graph=graph)
def process(self, id=None): if not h.auth.is_logged_in(): abort(401) if not h.auth.is_admin(): abort(403) c.sepdirnew = False c.alreadysepdir = False label = request.params.get('label', None) id = request.params.get('ID', id) sep_dir = request.params.get('sep_dir', None) action = request.params.get('action', None) action2 = request.params.get('action2', None) if action2: action = action2 values = dict(request.params) if action=="Add": school_of_thought_add = model.SchoolOfThought(label) #school_of_thought_add.label = label #setup search string and search pattern school_of_thoughtname = school_of_thought_add.label school_of_thoughtname_q = model.meta.Session.query(model.Entity) o = model.Entity.label.like('( '+ school_of_thoughtname + ' )') school_of_thoughtname_q = school_of_thoughtname_q.filter(o).order_by(func.length(model.Entity.label)) if school_of_thoughtname_q.count() == 0: school_of_thought_add.searchpattern = "( " + school_of_thoughtname + " )" school_of_thought_add.searchstring = school_of_thoughtname else: school_of_thought_add.searchpattern = "( " + label + " )" school_of_thought_add.searchcstring = label #reset old school_of_thought pattern to whole name too to avoid conflict oldschool_of_thought = h.fetch_obj(model.SchoolOfThought, school_of_thoughtname_q.first().ID) oldschool_of_thought.searchpattern = "( " + oldschool_of_thought.label + " )" oldschool_of_thought.searchstring = oldschool_of_thought.label meta.Session.add(oldschool_of_thought) if sep_dir: school_of_thought_add.sep_dir = sep_dir c.school_of_thought = school_of_thought_add meta.Session.add(school_of_thought_add) meta.Session.flush() meta.Session.commit() c.found = True c.message = "SchoolOfThought " + c.school_of_thought.label + " added successfully." return render ('admin/school_of_thought-edit.html') elif action=="Modify": c.school_of_thought = h.fetch_obj(model.SchoolOfThought, id) c.found = True changed = False #set values from form if c.school_of_thought.label != label: c.school_of_thought.label = label changed = True if c.school_of_thought.sep_dir != sep_dir: c.school_of_thought.sep_dir = sep_dir changed = True #commit changes meta.Session.flush() meta.Session.commit() if changed: c.message = "SchoolOfThought " + c.school_of_thought.label + " modified successfully." else: c.message = "No change needed; SchoolOfThought " + c.school_of_thought.label + " not modified." return render ('admin/school_of_thought-edit.html') elif action == "Delete": c.school_of_thought = h.fetch_obj(model.SchoolOfThought, values['ID']) c.message = "SchoolOfThought # " + values['ID'] + " ("+ c.school_of_thought.label + ") deleted; please search for a new entity label on the left." h.delete_obj(c.school_of_thought) meta.Session.flush() meta.Session.commit() c.found = False return render('admin/school_of_thought-edit.html')
def index(course_hedu_id): bra_id = request.args.get('bra_id') max_year_query = db.session.query( func.max(Yc_hedu.year)).filter_by(course_hedu_id=course_hedu_id) if bra_id: major_service = Major(course_hedu_id, bra_id) universities_service = MajorUniversities(course_hedu_id, bra_id) municipalities_service = MajorMunicipalities(course_hedu_id, bra_id) rank_query = Ybc_hedu.query.filter( Ybc_hedu.year == max_year_query, Ybc_hedu.bra_id == bra_id, func.length(Ybc_hedu.course_hedu_id) == len(course_hedu_id))\ .order_by(Ybc_hedu.enrolled.desc()) else: major_service = Major(course_hedu_id, '') universities_service = MajorUniversities(course_hedu_id, '') municipalities_service = MajorMunicipalities(course_hedu_id, '') rank_query = Yc_hedu.query.filter( Yc_hedu.year == max_year_query, func.length(Yc_hedu.course_hedu_id) == len(course_hedu_id))\ .order_by(Yc_hedu.enrolled.desc()) rank = rank_query.all() hedu_max_year = db.session.query(func.max(Yc_hedu.year)).first()[0] if not bra_id: header = { 'name': major_service.name(), 'enrolled': major_service.enrolled(), 'entrants': major_service.entrants(), 'graduates': major_service.graduates(), 'profile': major_service.profile(), 'year': major_service.year(), 'field_id': course_hedu_id[:2] } else: header = { 'name': major_service.name(), 'enrolled': major_service.enrolled(), 'entrants': major_service.entrants(), 'graduates': major_service.graduates(), 'profile': major_service.profile(), 'year': major_service.year(), 'field_id': course_hedu_id[:2], 'id': course_hedu_id, 'bra_id': bra_id, 'location_name': major_service.location_name() } body = { 'university_with_more_enrolled': universities_service.university_with_more_enrolled(), 'highest_enrolled_number_by_university': universities_service.highest_enrolled_number(), 'municipality_with_more_enrolled': municipalities_service.municipality_with_more_enrolled(), 'municipality_with_more_enrolled_state': municipalities_service.municipality_with_more_enrolled_state(), 'highest_enrolled_number_by_municipality': municipalities_service.highest_enrolled_number(), 'university_with_more_entrants': universities_service.university_with_more_entrants(), 'highest_entrant_number_by_university': universities_service.highest_entrants_number(), 'municipality_with_more_entrants': municipalities_service.municipality_with_more_entrants(), 'municipality_with_more_entrants_state': municipalities_service.municipality_with_more_entrants_state(), 'highest_entrant_number_by_municipality': municipalities_service.highest_entrants_number(), 'university_with_more_graduates': universities_service.university_with_more_graduates(), 'highest_graduate_number_by_university': universities_service.highest_graduates_number(), 'municipality_with_more_graduates': municipalities_service.municipality_with_more_graduates(), 'municipality_with_more_graduates_state': municipalities_service.municipality_with_more_graduates_state(), 'highest_graduate_number_by_municipality': municipalities_service.highest_graduates_number() } for index, maj in enumerate(rank): if rank[index].course_hedu_id == course_hedu_id: header['rank'] = index + 1 break location = Bra.query.filter(Bra.id == bra_id).first() major = Course_hedu.query.filter(Course_hedu.id == course_hedu_id).first() if header['enrolled'] is None or hedu_max_year != header['year']: abort(404) else: return render_template('major/index.html', header=header, body=body, location=location, major=major)
def admin(self, id=None): if not h.auth.is_logged_in(): abort(401) if not h.auth.is_admin(): abort(403) redirect = request.params.get('redirect', False) add = request.params.get('add', False) limit = request.params.get('limit', None) sep_dir = request.params.get('sep_dir', "") entity_q = Session.query(model.Entity) c.found = False c.custom = False c.new = False c.sep_dir = sep_dir c.sepdirnew = False c.alreadysepdir = False if request.params.get('q'): q = request.params['q'] o = model.Entity.label.like(q) entity_q = entity_q.filter(o).order_by(func.length(model.Entity.label)) # if only 1 result, go ahead and view that entity if redirect and entity_q.count() == 1: c.entity = h.fetch_obj(model.Entity, entity_q.first().ID) #now get type and route to correct edit page #first, if it is an idea, typeID = 1 if c.entity.typeID == 1: print "have an idea, q, entityq count = 1" c.idea = h.fetch_obj(model.Idea, entity_q.first().ID) c.found = True id = c.idea.ID c.message = 'Entity edit page for idea ' + c.idea.label #set up c.search_string_list which will be used to structure intersection/union search pattern option if request.params.get('sep_dir'): sep_dir = request.params['sep_dir'] if not (c.idea.sep_dir): c.idea.sep_dir = request.params['sep_dir'] c.sepdirnew = True else: c.alreadysepdir = True c.entry_sep_dir = request.params['sep_dir'] c.search_string_list = c.idea.setup_SSL() if re.search(' and ', c.idea.label): c.search_pattern_list = ['union', 'intersection'] return render ('admin/idea-edit.html') #thinkers elif c.entity.typeID == 3: print "have a thinker, q, entityq count = 1" c.thinker = h.fetch_obj(model.Thinker, entity_q.first().ID) c.found = True id = c.thinker.ID c.message = 'Entity edit page for thinker ' + c.thinker.label if request.params.get('sep_dir'): sep_dir = request.params['sep_dir'] if not (c.thinker.sep_dir): c.thinker.sep_dir = request.params['sep_dir'] c.sepdirnew = True else: c.alreadysepdir = True c.entry_sep_dir = request.params['sep_dir'] return render ('admin/thinker-edit.html') elif c.entity.typeID == 4: print "have a journal, q, entityq count = 1" c.journal = h.fetch_obj(model.Journal, entity_q.first().ID) c.found = True id = c.journal.ID c.message = 'Entity edit page for journal ' + c.journal.label if request.params.get('sep_dir'): sep_dir = request.params['sep_dir'] if not (c.journal.sep_dir): c.journal.sep_dir = request.params['sep_dir'] c.sepdirnew = True else: c.alreadysepdir = True c.entry_sep_dir = request.params['sep_dir'] return render ('admin/journal-edit.html') elif c.entity.typeID == 5: print "have a work, q, entityq count = 1" c.work = h.fetch_obj(model.Work, entity_q.first().ID) c.found = True id = c.work.ID c.message = 'Entity edit page for work ' + c.work.label if request.params.get('sep_dir'): sep_dir = request.params['sep_dir'] if not (c.work.sep_dir): c.work.sep_dir = request.params['sep_dir'] c.sepdirnew = True else: c.alreadysepdir = True c.entry_sep_dir = request.params['sep_dir'] return render ('admin/work-edit.html') elif c.entity.typeID == 6: print "have a school_of_thought, q, entityq count = 1" c.school_of_thought = h.fetch_obj(model.SchoolOfThought, entity_q.first().ID) c.found = True id = c.school_of_thought.ID c.message = 'Entity edit page for school_of_thought ' + c.school_of_thought.label if request.params.get('sep_dir'): sep_dir = request.params['sep_dir'] if not (c.school_of_thought.sep_dir): c.school_of_thought.sep_dir = request.params['sep_dir'] c.sepdirnew = True else: c.alreadysepdir = True c.entry_sep_dir = request.params['sep_dir'] return render ('admin/school_of_thought-edit.html') elif redirect and entity_q.count() == 0: c.message = "No match found for your search; if you would like to add your idea, please enter its label and sepdir into the field below." c.new = True c.prevvalue = q return render ('admin/entity-add.html') else: return ('No exact match for your search; please click "back" and try again.') if id is None: print "I am here" c.message = "Please input an idea using the search bar to the left." return render ('admin/idea-edit.html') else: c.entity = h.fetch_obj(model.Entity, id) c.found = True c.message = 'Entity edit page for entity ' + c.entity.label #get sep_dir if present--from admin.py action addentry if request.params.get('sep_dir'): sep_dir = request.params['sep_dir'] if not (c.entity.sep_dir): c.entity.sep_dir = sep_dir else: c.message = c.message + "WARNING: entity already has a sep_dir [" + c.entity.sep_dir + "]. Not replacing with [" + sep_dir + "]. If you would like to do so, please do so manually in the form below." if request.params.get('entry_sep_dir'): entry_sep_dir = request.params['entry_sep_dir'] if not (c.entity.sep_dir): c.entity.sep_dir = entry_sep_dir else: c.message = c.message + "WARNING: entity already has a sep_dir [" + c.entity.sep_dir + "]. Not replacing with [" + sep_dir + "]. If you would like to do so, please do so manually in the form below." #set up c.search_string_list which will be used to structure intersection/union search pattern option if c.entity.typeID == 1: c.idea = h.fetch_obj(model.Idea, c.entity.ID) c.search_string_list = c.idea.setup_SSL() return render ('admin/idea-edit.html') elif c.entity.typeID == 3: c.thinker = h.fetch_obj(model.Thinker, c.entity.ID) return render('admin/thinker-edit.html') elif c.entity.typeID == 4: c.journal = h.fetch_obj(model.Journal, c.entity.ID) return render('admin/journal-edit.html') elif c.entity.typeID == 5: c.work = h.fetch_obj(model.Work, c.entity.ID) return render('admin/work-edit.html') elif c.entity.typeID == 6: c.school_of_thought = h.fetch_obj(model.SchoolOfThought, c.entity.ID) return render('admin/school_of_thought-edit.html') c.entity = h.fetch_obj(model.Entity, id, new_id=True) redirect(c.entity.url(action='admin'), code=303)
def admin(self, id=None): if not h.auth.is_logged_in(): abort(401) if not h.auth.is_admin(): abort(403) c.sepdirnew = False c.alreadysepdir = False redirect = request.params.get('redirect', False) add = request.params.get('add', False) limit = request.params.get('limit', None) entity_q = Session.query(Entity) c.found = False c.custom = False c.new = False if request.params.get('q'): q = request.params['q'] o = Entity.label.like(q) entity_q = entity_q.filter(o).order_by(func.length(Entity.label)) # if only 1 result, go ahead and view that idea if redirect and entity_q.count() == 1: print "have a q, entityq count = 1" c.idea = h.fetch_obj(Idea, entity_q.first().ID) c.found = True id = c.idea.ID c.message = 'Entity edit page for idea ' + c.idea.label if request.params.get('entry_sep_dir'): entry_sep_dir = request.params['entry_sep_dir'] if not (c.idea.sep_dir): c.idea.sep_dir = request.params['entry_sep_dir'] c.sepdirnew = True else: c.alreadysepdir = True c.entry_sep_dir = request.params['entry_sep_dir'] #set up c.search_string_list which will be used to structure intersection/union search pattern option c.search_string_list = c.idea.setup_SSL() if re.search(' and ', c.idea.label): c.search_pattern_list = ['union', 'intersection'] return render ('admin/idea-edit.html') elif redirect and entity_q.count() == 0: c.message = "No match found for your search; if you would like to add your idea, please enter its label and sepdir into the field below." c.new = True c.prevvalue = q return render ('admin/idea-edit.html') else: return ('No exact match for your search; please click "back" and try again.') if id is None: print "I am here" c.message = "Please input an idea using the search bar to the left." return render ('admin/idea-edit.html') else: c.idea = h.fetch_obj(Idea, id) c.found = True c.message = 'Entity edit page for idea ' + c.idea.label if request.params.get('entry_sep_dir'): entry_sep_dir = request.params['entry_sep_dir'] if not (c.idea.sep_dir): c.idea.sep_dir = request.params['entry_sep_dir'] c.sepdirnew = True else: c.alreadysepdir = True c.entry_sep_dir = request.params['entry_sep_dir'] #set up c.search_string_list which will be used to structure intersection/union search pattern option c.search_string_list = c.idea.setup_SSL() print "c.idea.searchpattern is " + c.idea.searchpattern return render ('admin/idea-edit.html')
def process(self, id=None): if not h.auth.is_logged_in(): abort(401) if not h.auth.is_admin(): abort(403) c.sepdirnew = False c.alreadysepdir = False label = request.params.get('label', None) id = request.params.get('ID', id) sep_dir = request.params.get('sep_dir', None) action = request.params.get('action', None) action2 = request.params.get('action2', None) if action2: action = action2 values = dict(request.params) if action=="Add": thinker_add = Thinker(label) thinker_add.label = label #setup search string and search pattern lastname = thinker_add.label.split(' ').pop() lastname_q = Session.query(Entity) o = Entity.searchstring.like(lastname) lastname_q = lastname_q.filter(o).order_by(func.length(Entity.label)) if lastname_q.count() == 0: #if there's no match currently to last name, can use last name alone as searchpattern/searchstring thinker_add.searchpatterns.append(lastname) thinker_add.searchstring = lastname else: #otherwise, we need to use the whole name for both, and reset the other pattern to full name too thinker_add.searchpatterns.append(label) thinker_add.searchstring = label #reset old thinker pattern to whole name too to avoid conflict oldthinker = h.fetch_obj(Thinker, lastname_q.first().ID) oldthinker.searchpatterns = [oldthinker.label] oldthinker.searchstring = oldthinker.label Session.add(oldthinker) if sep_dir: thinker_add.sep_dir = sep_dir c.thinker = thinker_add Session.add(thinker_add) Session.flush() Session.commit() c.found = True c.message = "Thinker " + c.thinker.label + " added successfully." return render ('admin/thinker-edit.html') elif action=="Modify": c.thinker = h.fetch_obj(Thinker, id) c.found = True changed = False searchpatterns = [] for k, v in values.items(): key = "" if k.startswith('searchpatterns'): varname, num = k.split('.') key = 'delsearchpattern.%s'%(num) keyval = request.params.get(key, False) if not keyval: searchpatterns.append(v) if values['newsearchpattern']: searchpatterns.append(values['newsearchpattern']) changed = True #do manually edited searchpatterns first, so we don't write over them with the new default ones if the searchstring has been changed if c.thinker.searchpatterns != searchpatterns: c.thinker.searchpatterns = searchpatterns changed = True #set values from form if c.thinker.name != values['name']: c.thinker.name = values['name'] changed = True if c.thinker.label != values['label']: c.thinker.name = values['label'] changed = True if c.thinker.searchstring != values['searchstring']: c.thinker.searchstring = values['searchstring'] changed = True if c.thinker.sep_dir != values['sep_dir']: c.thinker.sep_dir = values['sep_dir'] changed = True if c.thinker.wiki != values['wiki']: c.thinker.wiki = values['wiki'] changed = True if c.thinker.birth_day != values['birth_day']: c.thinker.birth_day = values['birth_day'] changed = True c.thinker.birth_day = values['birth_day'] if c.thinker.death_day != values['death_day']: c.thinker.death_day = values['death_day'] changed = True if c.thinker.birth_month != values['birth_month']: c.thinker.birth_month = values['birth_month'] changed = True if c.thinker.death_month != values['death_month']: c.thinker.death_month = values['death_month'] changed = True if not (c.thinker.birth_year == values['birth_year'] + " " + values['bornbc']): if c.thinker.birth_year != values['birth_year']: c.thinker.birth_year = values['birth_year'] changed = True if c.thinker.birth_year and values['bornbc'] and not re.search("(BC)|(AD)",c.thinker.birth_year): c.thinker.birth_year = c.thinker.birth_year + " " + values['bornbc'] changed = True if not (c.thinker.death_year == values['death_year'] + " " + values['diedbc']): if c.thinker.death_year != values['death_year']: c.thinker.death_year = values['death_year'] changed = True if c.thinker.death_year and values['diedbc']and not re.search("(BC)|(AD)",c.thinker.death_year): c.thinker.death_year = c.thinker.death_year + " " + values['diedbc'] changed = True #commit changes Session.flush() Session.commit() if changed: c.message = "Thinker " + c.thinker.label + " modified successfully." else: c.message = "No changes detected. Thinker " + c.thinker.label + " not modified." return render ('admin/thinker-edit.html') elif action == "Delete": c.thinker = h.fetch_obj(Thinker, values['ID']) c.message = "Thinker # " + values['ID'] + " ("+ c.thinker.label + ") deleted; please search for a new entity label on the left." h.delete_obj(c.thinker) Session.flush() Session.commit() c.found = False return render('admin/thinker-edit.html')
def process(self, id=None): if not h.auth.is_logged_in(): abort(401) if not h.auth.is_admin(): abort(403) c.sepdirnew = False c.alreadysepdir = False label = request.params.get('label', None) id = request.params.get('ID', id) sep_dir = request.params.get('sep_dir', None) action = request.params.get('action', None) action2 = request.params.get('action2', None) if action2: action = action2 values = dict(request.params) if action=="Add": work_add = model.Work(label) #work_add.label = label #setup search string and search pattern workname = work_add.label workname_q = model.meta.Session.query(model.Entity) o = model.Entity.label.like('( '+ workname + ' )') workname_q = workname_q.filter(o).order_by(func.length(model.Entity.label)) if workname_q.count() == 0: work_add.searchpattern = "( " + workname + " )" work_add.searchstring = workname else: work_add.searchpattern = "( " + label + " )" work_add.searchstring = label #reset old work pattern to whole name too to avoid conflict oldwork = h.fetch_obj(model.Work, workname_q.first().ID) oldwork.searchpattern = "( " + oldwork.label + " )" oldwork.searchstring = oldwork.label meta.Session.add(oldwork) if sep_dir: work_add.sep_dir = sep_dir c.work = work_add meta.Session.add(work_add) meta.Session.flush() meta.Session.commit() c.found = True c.message = "Work " + c.work.label + " added successfully." return render ('admin/work-edit.html') elif action=="Modify": c.work = h.fetch_obj(model.Work, id) c.found = True changed = False #set values from form if c.work.label != label: c.work.label = label changed = True if c.work.sep_dir != sep_dir: c.work.sep_dir = sep_dir changed = True #commit changes meta.Session.flush() meta.Session.commit() if changed: c.message = "Work " + c.work.label + " modified successfully." else: c.message = "No change required; Work " + c.work.label + " not modified." return render ('admin/work-edit.html') elif action == "Delete": c.work = h.fetch_obj(model.Work, values['ID']) c.message = "Work # " + values['ID'] + " ("+ c.work.label + ") deleted; please search for a new entity label on the left." h.delete_obj(c.work) meta.Session.flush() meta.Session.commit() c.found = False return render('admin/work-edit.html')
def get_records_len_str_greater_or_less_than(args, model, attribute): attr_pos = request.args.getlist('field_len').index(attribute) attr_op = request.args try: if 'gte' in request.args: gte = int(request.args.get('gte')) if 'lte' in request.args: lte = int(request.args.get('lte')) if 'gt' in request.args: gt = int(request.args.get('gt')) if 'lt' in request.args: lt = int(request.args.get('lt')) except: raise ValueError("gt, lt, gte, lte values must be integer") if 'gte' in locals() and 'lte' in locals(): records = model.query.filter(func.length(attribute) >= gte, func.length(attribute) <= lte) elif 'gt' in locals() and 'lte' in locals(): records = model.query.filter(func.length(attribute) > gt, func.length(attribute) <= lte) elif 'gte' in locals() and 'lt' in locals(): records = model.query.filter(func.length(attribute) >= gte, func.length(attribute) < lt) elif 'gt' in locals() and 'lt' in locals(): records = model.query.filter(func.length(attribute) > gt, func.length(attribute) < lt) elif 'gte' in locals(): records = model.query.filter(func.length(attribute) >= gte) elif 'lte' in locals(): records = model.query.filter(func.length(attribute) <= lte) elif 'gt' in locals(): records = model.query.filter(func.length(attribute) > gt) elif 'lt' in locals(): records = model.query.filter(func.length(attribute) < lt) return records