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 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 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))
