def mute_cond(row): # type: (Dict[str, Any]) -> Selectable recipient_id = row['recipient_id'] topic_name = row['topic_name'] stream_cond = column("recipient_id") == recipient_id topic_cond = func.upper(column("subject")) == func.upper(topic_name) return and_(stream_cond, topic_cond)
def search_venues(): # TODO: implement search on artists with partial string search. Ensure it is case-insensitive. # seach for Hop should return "The Musical Hop". # search for "Music" should return "The Musical Hop" and "Park Square Live Music & Coffee" condition = case( [ (Show.start_date >= func.now(), 1) ], else_ = None ) search = request.form['search_term'] search_tag = "%{}%".format(search) query = db.session.query(Venue).filter(func.upper(Venue.name).like(func.upper(search_tag))).all() alt_response = [] for result in query: alt_response.append( { "id": result.id, "name": result.name, "num_upcoming_shows": len(db.session.query(func.count(condition)).filter(result.id == Show.venue_id).group_by(Show.venue_id).all()) } ) final_response = { "count": len(query), "data": alt_response, } return render_template('pages/search_venues.html', results=final_response, search_term=request.form.get('search_term', ''))
def rechercherAll(session, listeRecherche): listeCond = [] ELP = aliased(tables.ElementPedagogiSQLITE) for element in listeRecherche: mot = supprimerAccent(element).upper() listeCond.append(or_(func.upper(ELP.COD_ELP).like(mot), func.upper(ELP.LIB_ELP).like(mot), func.upper(ELP.COD_ELP).like(element.upper().decode("utf-8")), func.upper(ELP.LIB_ELP).like(element.upper().decode("utf-8")))) recherche = session.query(ELP.LIB_ELP, ELP.COD_ELP, ELP.TYP_ELP, ELP.ETU_ELP.label("nb_etu"), ELP.ENS_ELP.label("nb_ens")).filter(and_(*listeCond)).order_by(ELP.LIB_ELP) return convertirResultatBDD(recherche.all())
def rechercherUtilisateursByName(session, listeRecherche, typeUser): IND = aliased(tables.IndividuSQLITE) listeCond = [] for element in listeRecherche: mot = supprimerAccent(element).upper() listeCond.append(or_(func.upper(IND.LIB_NOM_PAT_IND).like(mot), func.upper(IND.LIB_PR1_IND).like(mot), func.upper(IND.SESAME_ETU).like(mot))) if typeUser == "Etudiant": listeCond.append(IND.TYPE_IND == "Etudiant") else: listeCond.append(IND.TYPE_IND <> "Etudiant") recherche = session.query(IND.LIB_PR1_IND.concat(" ").concat(IND.LIB_NOM_PAT_IND).label("name"), IND.SESAME_ETU.label("id"), IND.EMAIL_ETU.label("email")).filter(and_(*listeCond)).order_by(IND.LIB_NOM_PAT_IND) return convertirResultatBDD(recherche)
def __init__(self, word): if isinstance(word, basestring): self.word = word.upper() elif isinstance(word, CaseInsensitiveWord): self.word = word.word else: self.word = func.upper(word)
def update_keys(model, model_type, key_type, filter_content, update_years, concat_list): """ Run through the loop to update award keys for the given table. Args: model: the table model to be used model_type: whether the table being updated is FABS or FPDS key_type: the type of key (award or transaction) being populated filter_content: what we want in the base filter update_years: an array of years to work with concat_list: the concatenation to update with """ # Uppercasing here for consistency if key_type == 'award': concat_list = func.upper(concat_list) base_query = sess.query(model) update_key = model.unique_award_key if model_type == 'FPDS': base_query = base_query.filter(model.pulled_from == filter_content) else: if filter_content == 'aggregate': base_query = base_query.filter(model.record_type == '1') elif filter_content == 'non-aggregate': base_query = base_query.filter(model.record_type != '1') if key_type == 'transaction': update_key = model.afa_generated_unique logger.info('Populating unique_{}_key for {} {} records...'.format( key_type, model_type, filter_content)) if update_years: logger.info('Starting records before {}...'.format(update_years[0])) base_query.filter(func.cast_as_date(model.action_date) < '{}-01-01'.format(years[0])).\ update({update_key: concat_list}, synchronize_session=False) sess.commit() for year in update_years: logger.info('Starting records in {}...'.format(year)) base_query.filter(func.cast_as_date(model.action_date) >= '{}-01-01'.format(year), func.cast_as_date(model.action_date) <= '{}-12-31'.format(year)). \ update({update_key: concat_list}, synchronize_session=False) sess.commit() logger.info('Starting records after {}...'.format(years[-1])) base_query.filter(func.cast_as_date(model.action_date) > '{}-12-31'.format(years[-1])). \ update({update_key: concat_list}, synchronize_session=False) sess.commit() else: # FABS table may have values that cannot use cast_as_date. The table is smaller than the rest, so we just # update the whole thing at once. base_query.update({update_key: concat_list}, synchronize_session=False) sess.commit() logger.info('{} {} records populated.\n'.format(model_type, filter_content))
def news(): raterlist = db.session.execute( '''select rater.name, count(rating) as ratings from rater join rating on rater.user_id=rating.user_id group by rater.name''').fetchall() # restaurants restaurants = db.session().query( Restaurant ,Rating , Location).\ filter( func.extract('year',Rating.date) == '2015').\ filter( Location.business_id == Restaurant.business_id)\ .limit(5).all() restaurant, _, location = zip(*restaurants) # raters raters = db.session().query(Rater, func.min( Rating.mood), Location, Restaurant).filter( func.upper(Rater.name).like( 'Peter'.upper()) # since 0 is Staff in our description ).filter(Rating.business_id == Location.business_id).filter( Restaurant.business_id == Rating.business_id).group_by( Rater.user_id, Rating.mood, Location.location_id, Restaurant.business_id, Rating.date).order_by(asc(Rating.mood), func.DATE(Rating.date)).limit(20).all() # rest_spec rest_spec = raters[len(raters) - 1][3] other_critics = db.session.query( Restaurant, Rating.date, Rating.mood).filter( Restaurant.business_id == rest_spec.business_id).group_by( Restaurant.business_id, Rating.id, Rating.date).order_by(Rating.date).limit(10).all() Best_cate = db.session.execute(''' select rating,food_cate.cate_type from rating join ( select unnest(restaurant.food_type) as cate_type, restaurant.business_id as b_id from restaurant ) as food_cate on rating.business_id = food_cate.b_id where food_cate.cate_type='{}' group by ( food_cate.cate_type , rating.mood , rating.* ) having ( rating.mood >= avg(rating.mood) ) order by rating.mood desc '''.format("Active Life")).fetchall() os.system("clear") return render_template('news.html', restaurant=restaurant[0], location=location[0], johns=raters[0], others=other_critics)
def search_artists(): # TODO: implement search on artists with partial string search. Ensure it is case-insensitive. # seach for "A" should return "Guns N Petals", "Matt Quevado", and "The Wild Sax Band". # search for "band" should return "The Wild Sax Band". response={ "count": 1, "data": [{ "id": 4, "name": "Guns N Petals", "num_upcoming_shows": 0, }] } condition = case( [ (Show.start_date >= func.now(), 1) ], else_ = None ) search = request.form['search_term'] search_tag = "%{}%".format(search) query = db.session.query(Artist).filter(func.upper(Artist.name).like(func.upper(search_tag))).all() alt_response = [] for result in query: alt_response.append( { "id": result.id, "name": result.name, "num_upcoming_shows": len(db.session.query(func.count(condition)).filter(result.id == Show.artist_id).group_by(Show.artist_id).all()) } ) final_response = { "count": len(query), "data": alt_response, } return render_template('pages/search_artists.html', results=final_response, search_term=request.form.get('search_term', ''))
def rechercherUtilisateursByNameOrType(session, listeRecherche, typeUser, page=None): if not page: page = 1 IND = aliased(tables.IndividuSQLITE) listeCond = [] if listeRecherche: for element in listeRecherche: mot = supprimerAccent(element).upper() mot2 = element.decode("utf-8") listeCond.append(or_(func.upper(IND.LIB_NOM_PAT_IND).like(mot), IND.LIB_NOM_PAT_IND.like(mot2), func.upper(IND.LIB_PR1_IND).like(mot), IND.LIB_PR1_IND.like(mot2), func.upper(IND.SESAME_ETU).like(mot), IND.SESAME_ETU.like(mot2))) if typeUser: listeCond.append(IND.TYPE_IND == typeUser) if listeCond: recherche = session.query(IND.LIB_NOM_PAT_IND, IND.LIB_PR1_IND, IND.SESAME_ETU, IND.TYPE_IND, IND.COD_ETU, IND.EMAIL_ETU, IND.STATUS_IND).filter(and_(*listeCond)).order_by(IND.LIB_NOM_PAT_IND).limit(50).offset((page - 1) * 50).all() return convertirResultatBDD(recherche) return []
def update_keys(model, model_type, filter_content, update_years, concat_list): """ Run through the loop to update award keys for the given table. Args: model: the table model to be used model_type: whether the table being updated is FABS or FPDS filter_content: what we want in the base filter update_years: an array of years to work with concat_list: the concatenation to update with """ # Uppercasing here for consistency concat_list = func.upper(concat_list) base_query = sess.query(model) if model_type == 'FPDS': base_query = base_query.filter(model.pulled_from == filter_content) else: if filter_content == 'aggregate': base_query = base_query.filter(model.record_type == '1') else: base_query = base_query.filter(model.record_type != '1') logger.info('Populating unique_award_key for {} {} records...'.format(model_type, filter_content)) if update_years: logger.info('Starting records before {}...'.format(update_years[0])) base_query.filter(func.cast_as_date(model.action_date) < '{}-01-01'.format(years[0])).\ update({model.unique_award_key: concat_list}, synchronize_session=False) sess.commit() for year in update_years: logger.info('Starting records in {}...'.format(year)) base_query.filter(func.cast_as_date(model.action_date) >= '{}-01-01'.format(year), func.cast_as_date(model.action_date) <= '{}-12-31'.format(year)). \ update({model.unique_award_key: concat_list}, synchronize_session=False) sess.commit() logger.info('Starting records after {}...'.format(years[-1])) base_query.filter(func.cast_as_date(model.action_date) > '{}-12-31'.format(years[-1])). \ update({model.unique_award_key: concat_list}, synchronize_session=False) sess.commit() else: # DetachedAwardFinancialAssistance table may have values that cannot use cast_as_date. The table is smaller # than the rest, so we just update the whole thing at once. base_query.update({model.unique_award_key: concat_list}, synchronize_session=False) logger.info('{} {} records populated.\n'.format(model_type, filter_content))
def get_objects_query_list(cls, query=None, filtering=None, paging=None, ordering=None): from sqlalchemy import alias, cast, String qs = query or cls.query() if hasattr(cls, '_tab'): tab = cls._tab else: tab = alias(qs, cls.__tablename__) if filtering: for k, v in filtering.items(): if type(v) == type('') and '*' in v: # pragma: no cover v = v.upper() qs = qs.filter( func.upper(cast(getattr(tab.c, k), String)).like(v.replace('*', '%'))) else: qs = qs.filter(getattr(tab.c, k) == v) if ordering: # pragma: no cover order_by = getattr(tab.c, ordering['by']) if ordering['dir'] == 'desc': order_by = order_by.desc().nullslast() qs = qs.order_by(order_by) try: cnt = get_count(qs) #cnt = qs.count() except Exception as e: cnt = len(qs.all()) if paging: if 'from' in paging: qs = qs.offset(paging['from']) if 'till' in paging: qs = qs.limit(paging['till'] - paging.get('from', 0)) try: log.debug(_q_str(qs)) except: log.debug(str(qs)) return qs.all(), cnt
def deckQuery(decks=None, dids=[], tournaments=[], tquery=None, tids=[], players=[], min_place=None, max_place=None, archetypes=[], subarchetypes=[], deckTypes=[], exclude=[]): """Build a query for decks. All options which are specified will be required to be true (unless decks or dids is specified, each of which overrides the others), e.g. return a conjunction of the decks defined by the options. To build a disjunction, call deckQuery multiple times and use query1.union(query2). decks: Specify DBDeck objects manually. Overrides all other options. dids: Specify deck IDs manually. Overrides all other options. tournaments: A list of DBTournament objects to select decks from. tquery: A query for tournaments to select decks from. tids: IDs of tournaments to select decks from. players: Player names. min_place: Minimum place in the tournament. max_place: Maximum place in the tournament. archetypes: Only return decks with one of these archetypes. subarchetypes: Only return decks with one of these subarchetypes. deckTypes: List of (archetype, subarchetype) tuples. Only return decks with matching types. (More specific than archetypes and subarchetypes, since it requires that each pair go together.) exclude: Don't include anything with an archetype in this list. """ if decks: return deckQuery(dids=[d.id for d in decks]) query = session.query(DBDeck) if dids: return query.filter(DBDeck.id.in_(dids)) if tournaments: t_ids = [t.id for t in tournaments] query = query.filter(deckTable.c.T_ID.in_(t_ids)) if tids: query = query.filter(deckTable.c.T_ID.in_(tids)) if tquery: query = query.join(tquery.subquery()) if players: p2 = [func.upper(p) for p in players] query = query.filter(func.upper(DBDeck.player).in_(p2)) if min_place: query = query.filter(DBDeck.place >= min_place) if max_place: query = query.filter(DBDeck.place <= max_place) if archetypes: query = query.filter(DBDeck.archetype.in_(archetypes)) if subarchetypes: query = query.filter(DBDeck.subarchetype.in_(subarchetypes)) if deckTypes: clauses = [] for main, sub in deckTypes: clauses.append( and_(DBDeck.archetype == main, DBDeck.subarchetype == sub)) query = query.filter(or_(*clauses)) if exclude: query = query.filter(not_(DBDeck.archetypes.in_(exclude))) return query
def topic_match_sa(topic_name: str) -> Any: # _sa is short for Sql Alchemy, which we use mostly for # queries that search messages topic_cond = func.upper(column("subject")) == func.upper( literal(topic_name)) return topic_cond
def topic_match_sa(topic_name: str) -> "ColumnElement[bool]": # _sa is short for SQLAlchemy, which we use mostly for # queries that search messages topic_cond = func.upper(column("subject", Text)) == func.upper(literal(topic_name)) return topic_cond
c = [ ## date/time functions ## #func.timeofday(), # for postgresql func.localtime(), func.current_timestamp(), #func.date_part("month", func.now()), # for postgresql func.now(), ## mathematical functions ## func.pow(4, 2), func.sqrt(441), func.pi(), func.floor(func.pi()), func.ceil(func.pi()), ## string functions ## func.lower("ABC"), func.upper("abc"), func.length("abc"), func.trim(" ab c "), #func.chr(65), # for postgresql ] s = select(c) rs = conn.execute(s) print(rs.keys()) print(rs.fetchall())
def mute_cond(muted): # type: (List[str]) -> Selectable stream_cond = column("recipient_id") == recipient_map[muted[0].lower()] topic_cond = func.upper(column("subject")) == func.upper(muted[1]) return and_(stream_cond, topic_cond)
def topic_match_sa(topic_name: str) -> Any: # _sa is short for Sql Alchemy, which we use mostly for # queries that search messages topic_cond = func.upper(column("subject")) == func.upper(literal(topic_name)) return topic_cond
def func_upper(self, property, tree): return sqlfunctions.upper(self.get_selectable(property.property, tree))