def __init__(self, cnae_id, bra_id):
Industry.__init__(self, cnae_id)
self.max_year = db.session.query(func.max(Ybi.year)).filter_by(cnae_id=cnae_id)
self.rais_query = Ybi.query.join(Bra).filter(
Bra.id == Ybi.bra_id,
Ybi.cnae_id == self.cnae_id,
Ybi.bra_id_len == 9,
Ybi.year == self.max_year,
)
self.state_query = Ybi.query.join(Bra).filter(
Ybi.cnae_id == self.cnae_id,
Ybi.year == self.max_year,
Ybi.bra_id_len == 3
).order_by(desc(Ybi.num_jobs)).first()
if bra_id:
self.bra_id = bra_id
self.max_year = db.session.query(func.max(Ybi.year)).filter_by(cnae_id=cnae_id)
self.rais_query = Ybi.query.join(Bra).filter(
Bra.id == Ybi.bra_id,
Ybi.cnae_id == self.cnae_id,
Ybi.bra_id_len == 9,
Ybi.bra_id.like(self.bra_id+'%'),
Ybi.year == self.max_year
)
def get_current_data():
"""Returns JSON describing the last thing in the system log."""
global ol0
global wd0
#perform query
opLog = db.session.query(ol0).filter(ol0.id==db.session.query(ol0).with_entities(func.max(ol0.id)).one()[0])[0]
wData = db.session.query(wd0).filter(wd0.id==db.session.query(wd0).with_entities(func.max(wd0.id)).one()[0])[0]
mTime = unix_time(opLog.time)
inTemp = opLog.indoorTemp
setPtTemp = opLog.setpointTemp
state = unicode(thermoStateStr[opLog.state])
extTemp = wData.extTemp
extTempTime = unix_time(wData.time)
return jsonify({
u'inTemp' : inTemp,
u'inTempTime' : mTime,
u'outTemp' : extTemp,
u'outTempTime' : extTempTime,
u'setPtTemp' : setPtTemp,
u'opMode' : state
})
def newer_version(lowersuite_name, highersuite_name, session, include_equal=False):
'''
Finds newer versions in lowersuite_name than in highersuite_name. Returns a
list of tuples (source, higherversion, lowerversion) where higherversion is
the newest version from highersuite_name and lowerversion is the newest
version from lowersuite_name.
'''
lowersuite = get_suite(lowersuite_name, session)
highersuite = get_suite(highersuite_name, session)
query = session.query(DBSource.source, func.max(DBSource.version)). \
with_parent(highersuite).group_by(DBSource.source)
list = []
for (source, higherversion) in query:
q = session.query(func.max(DBSource.version)). \
filter_by(source=source)
if include_equal:
q = q.filter(DBSource.version >= higherversion)
else:
q = q.filter(DBSource.version > higherversion)
lowerversion = q.with_parent(lowersuite).group_by(DBSource.source).scalar()
if lowerversion is not None:
list.append((source, higherversion, lowerversion))
list.sort()
return list
def _get_cache_key(self, session): updated_cron = session.query( func.max(paasmaker.model.ApplicationInstanceTypeCron.updated) ).scalar() deleted_cron = session.query( func.max(paasmaker.model.ApplicationInstanceTypeCron.deleted) ).scalar() updated_version = session.query( func.max(paasmaker.model.ApplicationVersion.updated) ).scalar() deleted_version = session.query( func.max(paasmaker.model.ApplicationVersion.deleted) ).scalar() max_date = self._max_dates( updated_cron, deleted_cron, updated_version, deleted_version ) summer = hashlib.md5() summer.update(max_date) key = summer.hexdigest() return key
def _analyze(self, session):
newest_timestamp = session.execute(func.max(NodeSample.sampled)).scalar()
self.nodes = session.query(NodeSample).filter(NodeSample.sampled == newest_timestamp).all()
newest_timestamp = session.execute(func.max(LinkSample.sampled)).scalar()
self.links = session.query(LinkSample).filter(LinkSample.sampled == newest_timestamp).all()
self.samples.add(newest_timestamp)
def book(bookID):
form = BidForm()
bookS = db.session.query(Book).filter(Book.book_id == bookID).scalar()
print(str(db.session.query(Book).filter(Book.book_id == bookID).as_scalar()))
aucID = db.session.query(Auction.auc_id).filter(Auction.book_id == bookID).scalar()
print(str(db.session.query(Auction.auc_id).filter(Auction.book_id == bookID).as_scalar()))
curPrice = db.session.query(func.max(Bid.bid_price)).filter(Bid.auc_id == aucID).scalar()
print(str(db.session.query(func.max(Bid.bid_price)).filter(Bid.auc_id == aucID).as_scalar()))
if request.method == 'POST':
if not form.validate():
flash('Bid Unsuccessful')
return render_template('auction/book.html', form=form, book=bookS, curPrice=curPrice)
else:
if 'email' not in session:
return redirect(url_for('auth.signin'))
else:
usrID = db.session.query(User.user_id).filter(User.email == session['email']).scalar()
highBid = db.session.query(Bid.bid_price).filter(Bid.auc_id == aucID).\
filter(Bid.bid_price >= form.bid_price.data).first()
if highBid:
flash('Your bid needs to be higher then the current price')
return render_template('auction/book.html', form=form, book=bookS, curPrice=curPrice)
else:
newBid = Bid(auc_id=aucID, user_id=usrID, bid_price=form.bid_price.data)
db.session.add(newBid)
db.session.commit()
flash('Bid Successful')
return render_template('auction/book.html', form=form, book=bookS, curPrice=form.bid_price.data)
elif request.method == 'GET':
return render_template('auction/book.html', form=form, book=bookS, curPrice=curPrice)
def compress_smallest_box():
last_box = session.query(func.max(sqlalchemy.cast(InvCard.box, sqlalchemy.Integer))).first()[0]
box_capacity = list(metadata.bind.execute("select box,60 - count(*) as c from inv_cards where box not null group by box having c>0 order by c desc;"))
remove_box = box_capacity[0][0]
box_capacity = box_capacity[1:]
cards_in_remove_box = InvCard.query.filter_by(box=str(remove_box)).order_by(InvCard.box_index.desc()).all()
move_orders = fit_boxes(box_capacity, len(cards_in_remove_box))
i=0
print "********** move %d cards from box %s **********" % (60-box_capacity[0][1], remove_box)
print "\tall boxes: %s" % sorted([int(x) for x in [remove_box] + [b for b,o in move_orders]])
for box, count in move_orders:
max_index = session.query(func.max(InvCard.box_index)).filter_by(box=box).one()[0]
print "======= moving %d cards to box %s ======" % (count, box)
for card in cards_in_remove_box[i:count+i]:
print u"move %s to %s/%d" % (card, box, max_index)
max_index += 1
card.box = box
card.box_index = max_index
i+=count
if remove_box != last_box:
cards_in_last_box = InvCard.query.filter_by(box=str(last_box)).order_by(InvCard.box_index).all()
print "********** finally, move all %d cards from %s to %s **********" % (len(cards_in_last_box),last_box, remove_box)
for card in cards_in_last_box:
card.box = remove_box
raw_input()
session.commit()
def api_outlimit_stations(request, propid, startdate, enddate, limit):
session = Session()
#response = HttpResponse(mimetype='text/csv')
#response['Content-Disposition'] = 'attachment; filename="obs_' + stid + '_' + propid + '_' + date + '.csv"'
startdate = datetime(int(startdate.split('-')[0]), int(startdate.split('-')[1]), int(startdate.split('-')[2]))
enddate = datetime(int(enddate.split('-')[0]), int(enddate.split('-')[1]), int(enddate.split('-')[2]))
#max = aliased(func.max(Observation.value))
#currentdate = aliased(func.date(Observation.date))
observations = session.query(func.max(Observation.value), Station.municipality, Station.lat, Station.lng, func.date(Observation.date)).\
join(Station).join(Property).\
filter(Observation.date.between(startdate, enddate), Property.name == propid).\
group_by(func.date(Observation.date), Station.code).having(func.max(Observation.value) >= float(limit)).all()
#Observation.value >= limit
#writer = csv.writer(response)
#for obs in observations:
# writer.writerow([obs.station.lat, obs.station.lng, obs.date, obs.value])
resp = []
for obs in observations:
o = {}
o['lat'] = obs[2]
o['lng'] = obs[3]
o['date'] = obs[4].isoformat()
o['municipality'] = obs[1]
o['value'] = obs[0]
resp.append(o)
session.close()
#return response
return resp
def _compute_ranks():
tc_session.clear()
min_r = select([func.min(BwHistory.rank)],
BwHistory.table.c.router_idhex
== RouterStats.table.c.router_idhex).as_scalar()
avg_r = select([func.avg(BwHistory.rank)],
BwHistory.table.c.router_idhex
== RouterStats.table.c.router_idhex).as_scalar()
max_r = select([func.max(BwHistory.rank)],
BwHistory.table.c.router_idhex
== RouterStats.table.c.router_idhex).as_scalar()
avg_bw = select([func.avg(BwHistory.bw)],
BwHistory.table.c.router_idhex
== RouterStats.table.c.router_idhex).as_scalar()
avg_desc_bw = select([func.avg(BwHistory.desc_bw)],
BwHistory.table.c.router_idhex
== RouterStats.table.c.router_idhex).as_scalar()
RouterStats.table.update(values=
{RouterStats.table.c.min_rank:min_r,
RouterStats.table.c.avg_rank:avg_r,
RouterStats.table.c.max_rank:max_r,
RouterStats.table.c.avg_bw:avg_bw,
RouterStats.table.c.avg_desc_bw:avg_desc_bw}).execute()
#min_avg_rank = select([func.min(RouterStats.avg_rank)]).as_scalar()
max_avg_rank = select([func.max(RouterStats.avg_rank)]).as_scalar()
RouterStats.table.update(values=
{RouterStats.table.c.percentile:
(100.0*RouterStats.table.c.avg_rank)/max_avg_rank}).execute()
tc_session.commit()
def get_stats():
result = db.session.query(
func.min(Stop.stop_lat), func.min(Stop.stop_lon), func.max(Stop.stop_lat), func.max(Stop.stop_lon), func.count()
).first()
data = {"minLat": result[0], "minLon": result[1], "maxLat": result[2], "maxLon": result[3], "numbers": result[4]}
return jsonify({"stops": data})
def get_extent():
return DBSession.query(
func.min(func.ST_XMin(AdminZone.geometry)),
func.min(func.ST_YMin(AdminZone.geometry)),
func.max(func.ST_XMax(AdminZone.geometry)),
func.max(func.ST_YMax(AdminZone.geometry)),
).first()
def get_todays_electricity():
return Electricity.query.with_entities(
(func.max(Electricity.meter_280) - func.min(Electricity.meter_280)).label(
'todays_export'), (func.max(Electricity.meter_180) - func.min(Electricity.meter_180)).label(
'todays_import')).filter(
func.strftime('%Y-%m-%d', Electricity.created_at) == datetime.now().strftime('%Y-%m-%d')).group_by(
func.strftime('%Y-%m-%d', Electricity.created_at)).first()
def last_trial(cls, script=None, parent_required=False,
session=None):
"""Return last trial according to start time
Keyword arguments:
script -- specify the desired script (default=None)
parent_required -- valid only if script exists (default=False)
"""
model = cls.m
session = session or relational.session
trial = (
session.query(model)
.filter(model.start.in_(
select([func.max(model.start)])
.where(model.script == script)
))
).first()
if trial or parent_required:
return trial
return (
session.query(model)
.filter(model.start.in_(
select([func.max(model.start)])
))
).first()
def main():
logging.config.fileConfig(path_to_cfg)
start_utc = datetime.utcnow()
start_time = time()
global r
try:
r = reddit.Reddit(user_agent=cfg_file.get('reddit', 'user_agent'))
logging.info('Logging in as %s', cfg_file.get('reddit', 'username'))
r.login(cfg_file.get('reddit', 'username'),
cfg_file.get('reddit', 'password'))
subreddits = Subreddit.query.filter(Subreddit.enabled == True).all()
sr_dict = dict()
for subreddit in subreddits:
sr_dict[subreddit.name.lower()] = subreddit
mod_subreddit = r.get_subreddit('mod')
except Exception as e:
logging.error(' ERROR: %s', e)
# check reports
items = mod_subreddit.get_reports(limit=1000)
stop_time = datetime.utcnow() - REPORT_BACKLOG_LIMIT
check_items('report', items, sr_dict, stop_time)
# check spam
items = mod_subreddit.get_spam(limit=1000)
stop_time = (db.session.query(func.max(Subreddit.last_spam))
.filter(Subreddit.enabled == True).one()[0])
check_items('spam', items, sr_dict, stop_time)
# check new submissions
items = mod_subreddit.get_new_by_date(limit=1000)
stop_time = (db.session.query(func.max(Subreddit.last_submission))
.filter(Subreddit.enabled == True).one()[0])
check_items('submission', items, sr_dict, stop_time)
# check new comments
comment_multi = '+'.join([s.name for s in subreddits
if not s.reported_comments_only])
if comment_multi:
comment_multi_sr = r.get_subreddit(comment_multi)
items = comment_multi_sr.get_comments(limit=1000)
stop_time = (db.session.query(func.max(Subreddit.last_comment))
.filter(Subreddit.enabled == True).one()[0])
check_items('comment', items, sr_dict, stop_time)
# respond to modmail
try:
respond_to_modmail(r.user.get_modmail(), start_utc)
except Exception as e:
logging.error(' ERROR: %s', e)
# check reports html
try:
check_reports_html(sr_dict)
except Exception as e:
logging.error(' ERROR: %s', e)
logging.info('Completed full run in %s', elapsed_since(start_time))
def index_old(self):
from gviz_data_table import Table
from rockpack.mainsite.services.user.models import User, UserActivity
user_count = readonly_session.query(func.count(User.id)).\
filter(User.refresh_token != '').scalar()
header = ('user count', 'max lifetime', 'avg lifetime', 'stddev lifetime',
'max active days', 'avg active days', 'stddev active days')
lifetime = func.date_part('days', func.max(UserActivity.date_actioned) -
func.min(UserActivity.date_actioned)).label('lifetime')
active_days = func.count(func.distinct(func.date(
UserActivity.date_actioned))).label('active_days')
activity = readonly_session.query(UserActivity.user, lifetime, active_days).\
group_by(UserActivity.user)
ctx = {}
for key, having_expr in ('all', None), ('1day', lifetime > 1), ('7day', lifetime > 7):
data = activity.having(having_expr).from_self(
func.count('*'),
func.max(lifetime),
func.avg(lifetime),
func.stddev_samp(lifetime),
func.max(active_days),
func.avg(active_days),
func.stddev_samp(active_days)
).one()
table = Table([
dict(id='metric', type=str),
dict(id='value', type=float),
dict(id='%', type=str),
])
pdata = ('%d%%' % (data[0] * 100 / user_count),) + ('',) * 6
table.extend(zip(*(header, map(float, data), pdata)))
ctx['ret_%s_data' % key] = table.encode()
return self.render('admin/retention_stats_old.html', **ctx)
def new_lease(self, add_ctrl, add_node, options):
"""Get a new lease
return add_ctrl, add_node, options
:param add_ctrl: the controller part of the address
:type add_ctrl: Integer
:param add_node: the node part of the address. 0 for controller
:type add_node: Integer
:returns: A dict with all informations
:rtype: dict()
"""
#Check for malformated request
self._new_lease_lock.acquire()
try:
if add_ctrl == -1:
#A new controller wants an hadd.
#Find and return max(add_ctrl), 0
max_ctrl = self.dbsession.query(func.max(jntmodel.Lease.add_ctrl)).scalar()
if max_ctrl < 10:
add_ctrl = 10
else:
add_ctrl = max_ctrl + 1
add_node = 0
else:
#A new node wants an hadd
#check if add_ctrl,0 exists
#Find and return add_ctrl, max(add_node)
max_node = self.dbsession.query(func.max(jntmodel.Lease.add_node)).filter(jntmodel.Lease.add_ctrl==add_ctrl).scalar()
if max_node is None:
return None
add_node = max_node + 1
return self.repair_lease(add_ctrl, add_node, options)
finally:
self._new_lease_lock.release()
def createGraph(self):
# Get the highest node id
query = self.session.query(func.max(self.edge_data.start_node), func.max(self.edge_data.end_node))
for q in query:
counter = max(q) + 1
self.consoleAppend("Start creating the graph")
self.virtual_edges = dict()
for edge in self.query:
key1 = edge.start_node, edge.end_node
key2 = edge.end_node, edge.start_node
# Edge has not been added to the graph yet has not been added
if self.G.get_edge_data(*key1) is None or self.G.get_edge_data(*key2) is None:
if self.weighted:
self.G.add_edge(edge.start_node, edge.end_node, weight=edge.length)
else:
self.G.add_edge(edge.start_node, edge.end_node)
else:
# Add a vitual node
if self.weighted:
self.G.add_edge(edge.start_node, counter, weight=edge.length / 2)
self.G.add_edge(counter, edge.end_node, weight=edge.length / 2)
else:
self.G.add_edge(edge.start_node, counter)
self.G.add_edge(counter, edge.end_node)
vedge = edge.start_node, counter
# vedge1 bc value is equal to vegde2 bc value by definition -> only one edge vedge
self.virtual_edges[key1] = vedge
self.virtual_edges[key2] = vedge
counter = counter + 1
self.consoleAppend("Graph created")
def populate_months(session):
if session.query(Month).count() > 0:
raise Exception("Months table is already populated.")
demimonth = datetime.timedelta(days=14)
first_chat = session.query(func.min(Chat.date)).scalar()
first_bugevent = session.query(func.min(BugEvent.date)).scalar()
start_date = max(first_chat, first_bugevent)
print "First chat is " + str(first_chat)
print "First bug event is " + str(first_bugevent)
print "Starting months on " + str(start_date)
last_chat = session.query(func.max(Chat.date)).scalar()
last_bugevent = session.query(func.max(BugEvent.date)).scalar()
end_date = min(last_chat, last_bugevent)
print "Last chat is " + str(last_chat)
print "Last bug event is " + str(last_bugevent)
print "End months on or around " + str(end_date)
start = start_date
end = start_date + datetime.timedelta(days=27) # start + 27 days = 28 day span
while end < end_date:
month = Month(first=start, last=end)
session.add(month)
start += demimonth
end += demimonth
session.commit()
def api_organisation_activities(organisation_code, test_id, hierarchy_id=None):
if (("offset" in request.args) and (int(request.args['offset'])>=0)):
offset = int(request.args['offset'])
else:
offset = 0
organisation = Organisation.query.filter(Organisation.organisation_code==organisation_code
).first()
if (hierarchy_id):
if (hierarchy_id=="None"): hierarchy_id=None
"""test_count = db.session.query(func.count(Result.result_identifier)
).filter(Organisation.organisation_code == organisation_code,
Result.test_id==test_id,
Result.result_hierarchy==hierarchy_id
).join(Package
).join(OrganisationPackage
).join(Organisation
).all()"""
test_results = db.session.query(Result.result_identifier,
Result.result_data,
func.max(Result.runtime_id)
).filter(Organisation.organisation_code == organisation_code,
Result.test_id==test_id,
Result.result_hierarchy==hierarchy_id
).group_by(Result.result_identifier
).group_by(Result.result_data
).join(Package
).join(OrganisationPackage
).join(Organisation
).limit(50
).offset(offset
).all()
else:
"""test_count = db.session.query(func.count(Result.result_identifier)
).filter(Organisation.organisation_code == organisation_code,
Result.test_id==test_id
).join(Package
).join(OrganisationPackage
).join(Organisation
).all()"""
test_results = db.session.query(Result.result_identifier,
Result.result_data,
func.max(Result.runtime_id)
).filter(Organisation.organisation_code == organisation_code,
Result.test_id==test_id
).group_by(Result.result_identifier
).join(Package
).join(OrganisationPackage
).join(Organisation
).limit(50
).offset(offset
).all()
test_results = dict(map(lambda x: (x[0],x[1]), test_results))
if ((organisation_code == None) or (test_results==None)):
abort(404)
else:
return jsonify({"results": test_results})
def index(self):
max = DBSession.query(func.max(Sensor.lat)).one()[0]
min = DBSession.query(func.min(Sensor.lat)).one()[0]
lat = (max + min) / 2
max = DBSession.query(func.max(Sensor.lng)).one()[0]
min = DBSession.query(func.min(Sensor.lng)).one()[0]
lng = (max + min) / 2
return dict(page='map', lat=lat, lng=lng)
def index(page = 1):
form = DataForm()
user_data = Data.query.filter_by(user_id = g.user.id)
#ms = user_data.order_by(Data.systolic_pressure.desc()).first()
four_weeks_ago = datetime.datetime.now() - datetime.timedelta(weeks=4)
maxs = db.session.query(func.max(Data.systolic_pressure).label('max_systolic')).filter_by(user_id = g.user.id).one()
max_systolic = maxs.max_systolic
mins = db.session.query(func.min(Data.systolic_pressure).label('min_systolic')).filter_by(user_id = g.user.id).one()
min_systolic = mins.min_systolic
avgs = db.session.query(func.avg(Data.systolic_pressure).label('avg_systolic')).filter_by(user_id = g.user.id).\
filter(Data.timestamp > four_weeks_ago).one()
avg_systolic = avgs.avg_systolic
maxd = db.session.query(func.max(Data.diastolic_pressure).label('max_diastolic')).filter_by(user_id = g.user.id).one()
max_diastolic = maxd.max_diastolic
mind = db.session.query(func.min(Data.diastolic_pressure).label('min_diastolic')).filter_by(user_id = g.user.id).one()
min_diastolic = mind.min_diastolic
avgd = db.session.query(func.avg(Data.diastolic_pressure).label('avg_diastolic')).filter_by(user_id = g.user.id).\
filter(Data.timestamp > four_weeks_ago).one()
avg_diastolic = avgd.avg_diastolic
maxc = db.session.query(func.max(Data.cardiac_rate).label('max_rate')).filter_by(user_id = g.user.id).one()
max_rate = maxc.max_rate
minc = db.session.query(func.min(Data.cardiac_rate).label('min_rate')).filter_by(user_id = g.user.id).one()
min_rate = minc.min_rate
avgc = db.session.query(func.avg(Data.cardiac_rate).label('avg_rate')).filter_by(user_id = g.user.id).\
filter(Data.timestamp > four_weeks_ago).one()
avg_rate = avgc.avg_rate
if form.validate_on_submit():
data = Data(systolic_pressure = form.systolic_pressure.data,
diastolic_pressure = form.diastolic_pressure.data,
cardiac_rate = form.cardiac_rate.data,
timestamp = datetime.datetime.now(),
body = form.note.data,
user = g.user)
db.session.add(data)
db.session.commit()
db.session.close()
flash('Added successfully')
return redirect(url_for('index'))
datas = user_data.order_by(Data.timestamp.desc()).paginate(page, DATAS_PER_PAGE, False)
return render_template('index.html',
title = 'Home',
form = form,
max_systolic = max_systolic,
min_systolic = min_systolic,
avg_systolic = avg_systolic,
max_diastolic = max_diastolic,
min_diastolic = min_diastolic,
avg_diastolic = avg_diastolic,
max_rate = max_rate,
min_rate = min_rate,
avg_rate = avg_rate,
datas = datas)
def get_cmp(table, col_name, value):
"""
Build a SQL expression for a given column from a representational
dictionary.
"""
col = getattr(table.c, col_name)
original_value = value
neg = False
cmp_in = False
regex = False
max_ilvl = False
is_string = isinstance(value, basestring)
if is_string and value.startswith("!"):
value = value[1:]
neg = True
if is_string and value.endswith("^"):
value = value[:-1]
assert str(table) == "item_template"
# TODO: support other tables where a use case exists for restricting
# results to the MAX/MIN of a specific column
max_ilvl = True
if is_string and value.startswith("/") and value.endswith("/"):
value = value[1:-1]
regex = True
elif is_string and value.startswith("in "):
value = value[3:]
cmp_in = True
flags = get_flags(col_name, value)
if flags is not None:
value = col.in_(flags) if cmp_in else (col == flags)
elif regex:
value = col.op("rlike")(value)
elif is_string and "%" in value:
value = col.like(value)
if neg:
value = not_(value)
elif neg:
value = not_(value)
elif flags is None:
value = col == value
# TODO: actually handle this monkeypatch in the Model object, and stop using
# the AWFUL subquery hack below.
value._max = None
if max_ilvl:
value._max = func.max(table.c.ItemLevel)
subquery = select(["*"]).select_from(select([func.max(table.c.ItemLevel)]).where(value).alias("tmp"))
value = and_(value, table.c.ItemLevel == subquery)
return (value, original_value)
def history__github():
grain = _get_grain()
# Filtered list of github IDs
repo = request.args.get('repo', None)
repoFilter = None
if repo is not None:
repo = repo.split(',')
repoFilter = SnapshotOfGithub.repo_name.in_(repo)
# Date filter
date_group = func.date_trunc(grain, SnapshotOfGithub.timestamp)
# Query: Range of dates
q1 = Session.query()\
.add_column( func.distinct(date_group).label('d') )\
.order_by(date_group.desc())
response = _prepare(q1.count())
q1 = q1.offset( response['offset'] )\
.limit( response['per_page'] )
if q1.count():
date_column = q1.subquery().columns.d
(min_date,max_date) = Session.query(func.min(date_column), func.max(date_column)).first()
else:
# Impossible date range
(min_date,max_date) = datetime.now()+timedelta(days=1),datetime.now()
# Grouped query
S = SnapshotOfGithub
q = Session.query()\
.add_column( func.sum(S.watchers) )\
.add_column( func.max(S.forks) )\
.add_column( func.max(S.open_issues) )\
.add_column( func.max(S.size) )\
.add_column( date_group )\
.add_column( S.repo_name )\
.group_by(date_group)\
.group_by(S.repo_name)\
.order_by(date_group.desc())\
.filter( date_group>=min_date )\
.filter( date_group<=max_date )\
.filter( repoFilter )
results = {}
_dictize = lambda x: {
'watchers':x[0],
'forks':x[1],
'issues':x[2],
'size':x[3],
'timestamp':x[4].date().isoformat(),
}
for x in q:
repo_name = x[5]
results[repo_name] = results.get(repo_name, { 'repo':repo_name, 'data':[] })
results[repo_name]['data'].append( _dictize(x) )
# Inner function transforms SELECT tuple into recognizable format
response['grain'] = grain
response['data'] = results
response['repos'] = repo
response['min_date'] = min_date.date().isoformat()
response['max_date'] = max_date.date().isoformat()
return response
def max_id(orm, Entity, Entity_v, key):
m = None
value = orm.query(func.max(getattr(Entity, key))).first()
if value:
m = max(m or 0, value[0])
value = orm.query(func.max(getattr(Entity_v, key))).first()
if value:
m = max(m or 0, value[0])
return m
def display_summary(options):
"""
Display series summary.
:param options: argparse options from the CLI
"""
formatting = ' %-30s %-10s %-10s %-20s'
console(formatting % ('Name', 'Latest', 'Age', 'Downloaded'))
console('-' * 79)
session = Session()
try:
query = (session.query(Series).outerjoin(Series.episodes).outerjoin(Episode.releases).
outerjoin(Series.in_tasks).group_by(Series.id))
if options.configured == 'configured':
query = query.having(func.count(SeriesTask.id) >= 1)
elif options.configured == 'unconfigured':
query = query.having(func.count(SeriesTask.id) < 1)
if options.premieres:
query = (query.having(func.max(Episode.season) <= 1).having(func.max(Episode.number) <= 2).
having(func.count(SeriesTask.id) < 1)).filter(Release.downloaded == True)
if options.new:
query = query.having(func.max(Episode.first_seen) > datetime.now() - timedelta(days=options.new))
if options.stale:
query = query.having(func.max(Episode.first_seen) < datetime.now() - timedelta(days=options.stale))
for series in query.order_by(Series.name).yield_per(10):
series_name = series.name
if len(series_name) > 30:
series_name = series_name[:27] + '...'
new_ep = ' '
behind = 0
status = 'N/A'
age = 'N/A'
episode_id = 'N/A'
latest = get_latest_release(series)
if latest:
if latest.first_seen > datetime.now() - timedelta(days=2):
new_ep = '>'
behind = new_eps_after(latest)
status = get_latest_status(latest)
age = latest.age
episode_id = latest.identifier
if behind:
episode_id += ' +%s' % behind
console(new_ep + formatting[1:] % (series_name, episode_id, age, status))
if behind >= 3:
console(' ! Latest download is %d episodes behind, this may require '
'manual intervention' % behind)
console('-' * 79)
console(' > = new episode ')
console(' Use `flexget series show NAME` to get detailed information')
finally:
session.close()
def _init_position(self):
s = select(
[(func.max(position_column)+1).label('value')],
qualifier_method(self)
).union(
select([literal(1).label('value')])
)
a = s.alias()
#XXX: two func.max?
setattr(self, position_column_name, select([func.max(a.c.value)]))
def mesicni_vypis_alluser(mesic):
# form=Card.find_by_number(current_user.card_number)
# form = db.session.query(Card.time).filter_by(card_number=current_user.card_number)
form = db.session.query(func.strftime('%Y-%m-%d', Card.time).label("date"),
func.max(func.strftime('%H:%M', Card.time)).label("Max"), \
func.min(func.strftime('%H:%M', Card.time)).label("Min"),
(func.max(Card.time) - func.min(Card.time)).label("Rozdil")) \
.filter(func.strftime('%Y-%-m', Card.time) == mesic).group_by(func.strftime('%Y-%m-%d', Card.time))
# .group_by([func.day(Card.time)])
return render_template("auth/mesicni_vypisy.tmpl", form=form, user=current_user)
def history__mailman():
grain = _get_grain()
# Filtered list of mailman IDs
lists = request.args.get('list')
listFilter = None
if lists is not None:
lists = lists.split(',')
listFilter = SnapshotOfMailman.list_name.in_(lists)
# Date filter
date_group = func.date_trunc(grain, SnapshotOfMailman.timestamp)
# Query: Range of dates
q1 = Session.query()\
.add_column( func.distinct(date_group).label('d') )\
.order_by(date_group.desc())
response = _prepare(q1.count())
q1 = q1.offset( response['offset'] )\
.limit( response['per_page'] )
if q1.count():
subquery = q1.subquery()
(min_date,max_date) = Session.query(func.min(subquery.columns.d), func.max(subquery.columns.d)).first()
else:
# Impossible date range
(min_date,max_date) = datetime.now()+timedelta(days=1),datetime.now()
# Grouped query
S = SnapshotOfMailman
q = Session.query()\
.add_column( func.sum(S.posts_today) )\
.add_column( func.max(S.subscribers) )\
.add_column( date_group )\
.add_column( S.list_name )\
.group_by(date_group)\
.group_by(S.list_name)\
.order_by(date_group.desc())\
.filter( date_group>=min_date )\
.filter( date_group<=max_date )\
.filter( listFilter )
results = {}
# Inner function transforms SELECT tuple into recognizable format
_dictize = lambda x: {
'posts':x[0],
'subscribers':x[1],
'timestamp':x[2].isoformat(),
}
# Build output datastructure from rows
for x in q:
list_name = x[3]
results[list_name] = results.get(list_name, { 'list_name':list_name, 'data':[] })
results[list_name]['data'].append( _dictize(x) )
# Write response
response['grain'] = grain
response['data'] = results
response['list'] = lists
response['min_date'] = min_date.isoformat()
response['max_date'] = max_date.isoformat()
return response
def get_next_modulid(package, session):
if package == "ringo":
id = session.query(
func.max(ModulItem.id)).filter(ModulItem.id < 1000).one()[0]
else:
id = session.query(
func.max(ModulItem.id)).filter(ModulItem.id > 999).one()[0]
if id:
return id + 1
else:
return 1000
def _make_stats_query(self, event_filter):
query = self.session.query(
func.min(Meter.timestamp).label('tsmin'),
func.max(Meter.timestamp).label('tsmax'),
func.avg(Meter.counter_volume).label('avg'),
func.sum(Meter.counter_volume).label('sum'),
func.min(Meter.counter_volume).label('min'),
func.max(Meter.counter_volume).label('max'),
func.count(Meter.counter_volume).label('count'))
return make_query_from_filter(query, event_filter)
def calculate_summary(self, product_name: str,
time: Range) -> TimePeriodOverview:
"""
Create a summary of the given product/time range.
"""
log = self.log.bind(product_name=product_name, time=time)
log.debug("summary.query")
begin_time, end_time, where_clause = self._where(product_name, time)
select_by_srid = (select((
func.ST_SRID(DATASET_SPATIAL.c.footprint).label("srid"),
func.count().label("dataset_count"),
func.ST_Transform(
func.ST_Union(DATASET_SPATIAL.c.footprint),
self._target_srid(),
type_=Geometry(),
).label("footprint_geometry"),
func.sum(DATASET_SPATIAL.c.size_bytes).label("size_bytes"),
func.max(DATASET_SPATIAL.c.creation_time).label(
"newest_dataset_creation_time"),
)).where(where_clause).group_by("srid").alias("srid_summaries"))
# Union all srid groups into one summary.
result = self._engine.execute(
select((
func.sum(
select_by_srid.c.dataset_count).label("dataset_count"),
func.array_agg(select_by_srid.c.srid).label("srids"),
func.sum(select_by_srid.c.size_bytes).label("size_bytes"),
func.ST_Union(
select_by_srid.c.footprint_geometry,
type_=Geometry(srid=self._target_srid()),
).label("footprint_geometry"),
func.max(select_by_srid.c.newest_dataset_creation_time).label(
"newest_dataset_creation_time"),
func.now().label("summary_gen_time"),
)))
rows = result.fetchall()
log.debug("summary.query.done", srid_rows=len(rows))
assert len(rows) == 1
row = dict(rows[0])
row["dataset_count"] = int(
row["dataset_count"]) if row["dataset_count"] else 0
if row["footprint_geometry"] is not None:
row["footprint_crs"] = self._get_srid_name(
row["footprint_geometry"].srid)
row["footprint_geometry"] = geo_shape.to_shape(
row["footprint_geometry"])
else:
row["footprint_crs"] = None
row["crses"] = None
if row["srids"] is not None:
row["crses"] = {self._get_srid_name(s) for s in row["srids"]}
del row["srids"]
# Convert from Python Decimal
if row["size_bytes"] is not None:
row["size_bytes"] = int(row["size_bytes"])
has_data = row["dataset_count"] > 0
log.debug("counter.calc")
# Initialise all requested days as zero
day_counts = Counter({
d.date(): 0
for d in pd.date_range(begin_time, end_time, closed="left")
})
region_counts = Counter()
if has_data:
day_counts.update(
Counter({
day.date(): count
for day, count in self._engine.execute(
select([
func.date_trunc(
"day",
DATASET_SPATIAL.c.center_time.op(
"AT TIME ZONE")(self.grouping_time_zone),
).label("day"),
func.count(),
]).where(where_clause).group_by("day"))
}))
region_counts = Counter({
item: count
for item, count in self._engine.execute(
select([
DATASET_SPATIAL.c.region_code.label("region_code"),
func.count(),
]).where(where_clause).group_by("region_code"))
})
summary = TimePeriodOverview(
**row,
timeline_period="day",
time_range=Range(begin_time, end_time),
timeline_dataset_counts=day_counts,
region_dataset_counts=region_counts,
# TODO: filter invalid from the counts?
footprint_count=row["dataset_count"] or 0,
)
log.debug(
"summary.calc.done",
dataset_count=summary.dataset_count,
footprints_missing=summary.dataset_count - summary.footprint_count,
)
return summary
def start_date(start):
"""Return a JSON list of the minimum temperature, the average temp, and the max temp for a given start or start-end range"""
"""When given the start only, calculate TMIN, TAVG, and TMAX for all dates greater than and equal to the start date"""
"""TMIN, TAVG, and TMAX for a list of dates.
Args:
start_date (string): A date string in the format %Y-%m-%d
end_date (string): A date string in the format %Y-%m-%d
Returns:
TMIN, TAVE, and TMAX
"""
starting_date = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= start).filter(Measurement.date <= dt.date(2017, 8, 23)).all()
return jsonify(starting_date)
def get_last_script_execution_id(database_session):
max = database_session.query(func.max(ScriptExecutionModel.id).label('last_id')).one()
return max.last_id
def read_latest_date() -> List[IntradayEntity]:
return db.session.query(func.max(IntradayEntity.date),
IntradayEntity.ticker).group_by(
IntradayEntity.ticker).all()
def get_states(hass,
utc_point_in_time,
entity_ids=None,
run=None,
filters=None):
"""Return the states at a specific point in time."""
from homeassistant.components.recorder.models import States
if run is None:
run = recorder.run_information(hass, utc_point_in_time)
# History did not run before utc_point_in_time
if run is None:
return []
from sqlalchemy import and_, func
with session_scope(hass=hass) as session:
if entity_ids and len(entity_ids) == 1:
# Use an entirely different (and extremely fast) query if we only
# have a single entity id
most_recent_state_ids = session.query(
States.state_id.label('max_state_id')).filter(
(States.last_updated < utc_point_in_time)
& (States.entity_id.in_(entity_ids))).order_by(
States.last_updated.desc())
most_recent_state_ids = most_recent_state_ids.limit(1)
else:
# We have more than one entity to look at (most commonly we want
# all entities,) so we need to do a search on all states since the
# last recorder run started.
most_recent_states_by_date = session.query(
States.entity_id.label('max_entity_id'),
func.max(
States.last_updated).label('max_last_updated')).filter(
(States.last_updated >= run.start)
& (States.last_updated < utc_point_in_time))
if entity_ids:
most_recent_states_by_date.filter(
States.entity_id.in_(entity_ids))
most_recent_states_by_date = most_recent_states_by_date.group_by(
States.entity_id)
most_recent_states_by_date = most_recent_states_by_date.subquery()
most_recent_state_ids = session.query(
func.max(States.state_id).label('max_state_id')).join(
most_recent_states_by_date,
and_(
States.entity_id ==
most_recent_states_by_date.c.max_entity_id,
States.last_updated ==
most_recent_states_by_date.c.max_last_updated))
most_recent_state_ids = most_recent_state_ids.group_by(
States.entity_id)
most_recent_state_ids = most_recent_state_ids.subquery()
query = session.query(States).join(
most_recent_state_ids,
States.state_id == most_recent_state_ids.c.max_state_id).filter(
(~States.domain.in_(IGNORE_DOMAINS)))
if filters:
query = filters.apply(query, entity_ids)
return [
state for state in execute(query)
if not state.attributes.get(ATTR_HIDDEN, False)
]
def get_resources(self, user=None, project=None, source=None,
start_timestamp=None, start_timestamp_op=None,
end_timestamp=None, end_timestamp_op=None,
metaquery=None, resource=None, limit=None):
"""Return an iterable of api_models.Resource instances
:param user: Optional ID for user that owns the resource.
:param project: Optional ID for project that owns the resource.
:param source: Optional source filter.
:param start_timestamp: Optional modified timestamp start range.
:param start_timestamp_op: Optional start time operator, like gt, ge.
:param end_timestamp: Optional modified timestamp end range.
:param end_timestamp_op: Optional end time operator, like lt, le.
:param metaquery: Optional dict with metadata to match on.
:param resource: Optional resource filter.
:param limit: Maximum number of results to return.
"""
if limit == 0:
return
s_filter = storage.SampleFilter(user=user,
project=project,
source=source,
start_timestamp=start_timestamp,
start_timestamp_op=start_timestamp_op,
end_timestamp=end_timestamp,
end_timestamp_op=end_timestamp_op,
metaquery=metaquery,
resource=resource)
session = self._engine_facade.get_session()
# get list of resource_ids
has_timestamp = start_timestamp or end_timestamp
# NOTE: When sql_expire_samples_only is enabled, there will be some
# resources without any sample, in such case we should use inner
# join on sample table to avoid wrong result.
if cfg.CONF.sql_expire_samples_only or has_timestamp:
res_q = session.query(distinct(models.Resource.resource_id)).join(
models.Sample,
models.Sample.resource_id == models.Resource.internal_id)
else:
res_q = session.query(distinct(models.Resource.resource_id))
res_q = make_query_from_filter(session, res_q, s_filter,
require_meter=False)
res_q = res_q.limit(limit) if limit else res_q
for res_id in res_q.all():
# get max and min sample timestamp value
min_max_q = (session.query(func.max(models.Sample.timestamp)
.label('max_timestamp'),
func.min(models.Sample.timestamp)
.label('min_timestamp'))
.join(models.Resource,
models.Resource.internal_id ==
models.Sample.resource_id)
.filter(models.Resource.resource_id ==
res_id[0]))
min_max_q = make_query_from_filter(session, min_max_q, s_filter,
require_meter=False)
min_max = min_max_q.first()
# get resource details for latest sample
res_q = (session.query(models.Resource.resource_id,
models.Resource.user_id,
models.Resource.project_id,
models.Resource.source_id,
models.Resource.resource_metadata)
.join(models.Sample,
models.Sample.resource_id ==
models.Resource.internal_id)
.filter(models.Sample.timestamp ==
min_max.max_timestamp)
.filter(models.Resource.resource_id ==
res_id[0])
.order_by(models.Sample.id.desc()).limit(1))
res = res_q.first()
yield api_models.Resource(
resource_id=res.resource_id,
project_id=res.project_id,
first_sample_timestamp=min_max.min_timestamp,
last_sample_timestamp=min_max.max_timestamp,
source=res.source_id,
user_id=res.user_id,
metadata=res.resource_metadata
)
def populate_user_metadata(session, user_ids, users, current_user_id):
# build dict of user id --> track count
track_counts = (session.query(
Track.owner_id, func.count(Track.owner_id)).filter(
Track.is_current == True, Track.is_delete == False,
Track.owner_id.in_(user_ids)).group_by(Track.owner_id).all())
track_count_dict = {
user_id: track_count
for (user_id, track_count) in track_counts
}
# build dict of user id --> playlist count
playlist_counts = (session.query(
Playlist.playlist_owner_id,
func.count(Playlist.playlist_owner_id)).filter(
Playlist.is_current == True, Playlist.is_album == False,
Playlist.is_private == False, Playlist.is_delete == False,
Playlist.playlist_owner_id.in_(user_ids)).group_by(
Playlist.playlist_owner_id).all())
playlist_count_dict = {
user_id: playlist_count
for (user_id, playlist_count) in playlist_counts
}
# build dict of user id --> album count
album_counts = (session.query(
Playlist.playlist_owner_id,
func.count(Playlist.playlist_owner_id)).filter(
Playlist.is_current == True, Playlist.is_album == True,
Playlist.is_private == False, Playlist.is_delete == False,
Playlist.playlist_owner_id.in_(user_ids)).group_by(
Playlist.playlist_owner_id).all())
album_count_dict = {
user_id: album_count
for (user_id, album_count) in album_counts
}
# build dict of user id --> follower count
follower_counts = (session.query(
Follow.followee_user_id, func.count(Follow.followee_user_id)).filter(
Follow.is_current == True, Follow.is_delete == False,
Follow.followee_user_id.in_(user_ids)).group_by(
Follow.followee_user_id).all())
follower_count_dict = {
user_id: follower_count
for (user_id, follower_count) in follower_counts
}
# build dict of user id --> followee count
followee_counts = (session.query(
Follow.follower_user_id, func.count(Follow.follower_user_id)).filter(
Follow.is_current == True, Follow.is_delete == False,
Follow.follower_user_id.in_(user_ids)).group_by(
Follow.follower_user_id).all())
followee_count_dict = {
user_id: followee_count
for (user_id, followee_count) in followee_counts
}
# build dict of user id --> repost count
repost_counts = (session.query(
Repost.user_id, func.count(Repost.user_id)).filter(
Repost.is_current == True, Repost.is_delete == False,
Repost.user_id.in_(user_ids)).group_by(Repost.user_id).all())
repost_count_dict = {
user_id: repost_count
for (user_id, repost_count) in repost_counts
}
# build dict of user id --> track blocknumber
track_blocknumbers = (session.query(
Track.owner_id, func.max(Track.blocknumber)).filter(
Track.is_current == True, Track.is_delete == False,
Track.owner_id.in_(user_ids)).group_by(Track.owner_id).all())
track_blocknumber_dict = {
user_id: track_blocknumber
for (user_id, track_blocknumber) in track_blocknumbers
}
current_user_followed_user_ids = {}
current_user_followee_follow_count_dict = {}
if current_user_id:
# does current user follow any of requested user ids
current_user_followed_user_ids = (session.query(
Follow.followee_user_id).filter(
Follow.is_current == True, Follow.is_delete == False,
Follow.followee_user_id.in_(user_ids),
Follow.follower_user_id == current_user_id).all())
current_user_followed_user_ids = {
r[0]: True
for r in current_user_followed_user_ids
}
# build dict of user id --> followee follow count
current_user_followees = (session.query(
Follow.followee_user_id).filter(
Follow.is_current == True, Follow.is_delete == False,
Follow.follower_user_id == current_user_id))
current_user_followees = {r[0]: True for r in current_user_followees}
current_user_followee_follow_counts = (session.query(
Follow.followee_user_id,
func.count(Follow.followee_user_id)).filter(
Follow.is_current == True, Follow.is_delete == False,
Follow.follower_user_id.in_(current_user_followees),
Follow.followee_user_id.in_(user_ids)).group_by(
Follow.followee_user_id).all())
current_user_followee_follow_count_dict = {
user_id: followee_follow_count
for (user_id,
followee_follow_count) in current_user_followee_follow_counts
}
for user in users:
user_id = user["user_id"]
user[response_name_constants.track_count] = track_count_dict.get(
user_id, 0)
user[response_name_constants.playlist_count] = playlist_count_dict.get(
user_id, 0)
user[response_name_constants.album_count] = album_count_dict.get(
user_id, 0)
user[response_name_constants.follower_count] = follower_count_dict.get(
user_id, 0)
user[response_name_constants.followee_count] = followee_count_dict.get(
user_id, 0)
user[response_name_constants.repost_count] = repost_count_dict.get(
user_id, 0)
user[response_name_constants.
track_blocknumber] = track_blocknumber_dict.get(user_id, -1)
# current user specific
user[response_name_constants.
does_current_user_follow] = current_user_followed_user_ids.get(
user_id, False)
user[
response_name_constants.
current_user_followee_follow_count] = current_user_followee_follow_count_dict.get(
user_id, 0)
return users
Base.prepare(engine, reflect=True)
# We can view all of the classes that automap found
Base.classes.keys()
# Save references to each table
measurement = Base.classes.measurement
station = Base.classes.station
# Design a query to retrieve the last 12 months of precipitation data and plot the results
# Calculate the date 1 year ago from the last data point in the database
max_date = session.query(func.max(measurement.date)).all()[0]
for row in max_date:
pprint(row)
one_year = dt.timedelta(days=365)
min_date = dt.date(2017, 8, 23) - one_year
min_date
# Perform a query to retrieve the data and precipitation scores
lasy_year_prcp = session.query(
measurement.date,
measurement.prcp).filter(measurement.date >= min_date).all()
def tmin_tmax_tavg1():
# calculate the TMIN, TAVG, and TMAX for dates between the start and end date inclusive.
session = Session(engine)
between_date_temp = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date.between('2016-8-23','2017-8-23')).all()
between_list = {'min':between_date_temp[0][0],
'avg': between_date_temp[0][1],
'max':between_date_temp[0][2] }
return jsonify(between_list)
def tmin_tmax_tavg():
#Return a JSON list of the minimum temperature, the average temperature, and the max temperature for a given start or start-end range.
# When given the start only, calculate TMIN, TAVG, and TMAX for all dates greater than and equal to the start date.
session = Session(engine)
start_date_temp = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= datetime.datetime(2017,8,16)).all()
start_temp_list = {'min':start_date_temp[0][0],
'avg': start_date_temp[0][1],
'max':start_date_temp[0][2] }
return jsonify(start_temp_list)
def start_end_day(start, end):
start_end_day = session.query(Measurement.date, func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= start).\
filter(Measurement.date <= end).\
group_by(Measurement.date).all()
# Convert List of Tuples Into Normal List
start_end_day_list = list(start_end_day)
# Return JSON List of Min Temp, Avg Temp and Max Temp for a Given Start-End Range
return jsonify(start_end_day_list)
def start_end(start, end):
results1 = session.query(func.min(measurement.tobs), func.avg(measurement.tobs),func.max(measurement.tobs)).\
filter(measurement.date >= start, measurement.date <= end).order_by(measurement.date.desc()).all()
return jsonify(results1)
def temp_start(start):
results = session.query(func.min(measurement.tobs), func.avg(measurement.tobs), func.max(measurement.tobs)).\
filter(measurement.date >= start).order_by(measurement.date.desc()).all()
return jsonify(results)
def get_meters(self, user=None, project=None, resource=None, source=None,
metaquery=None, limit=None, unique=False):
"""Return an iterable of api_models.Meter instances
:param user: Optional ID for user that owns the resource.
:param project: Optional ID for project that owns the resource.
:param resource: Optional ID of the resource.
:param source: Optional source filter.
:param metaquery: Optional dict with metadata to match on.
:param limit: Maximum number of results to return.
:param unique: If set to true, return only unique meter information.
"""
if limit == 0:
return
s_filter = storage.SampleFilter(user=user,
project=project,
source=source,
metaquery=metaquery,
resource=resource)
# NOTE(gordc): get latest sample of each meter/resource. we do not
# filter here as we want to filter only on latest record.
session = self._engine_facade.get_session()
subq = session.query(func.max(models.Sample.id).label('id')).join(
models.Resource,
models.Resource.internal_id == models.Sample.resource_id)
if unique:
subq = subq.group_by(models.Sample.meter_id)
else:
subq = subq.group_by(models.Sample.meter_id,
models.Resource.resource_id)
if resource:
subq = subq.filter(models.Resource.resource_id == resource)
subq = subq.subquery()
# get meter details for samples.
query_sample = (session.query(models.Sample.meter_id,
models.Meter.name, models.Meter.type,
models.Meter.unit,
models.Resource.resource_id,
models.Resource.project_id,
models.Resource.source_id,
models.Resource.user_id).join(
subq, subq.c.id == models.Sample.id)
.join(models.Meter, models.Meter.id == models.Sample.meter_id)
.join(models.Resource,
models.Resource.internal_id == models.Sample.resource_id))
query_sample = make_query_from_filter(session, query_sample, s_filter,
require_meter=False)
query_sample = query_sample.limit(limit) if limit else query_sample
if unique:
for row in query_sample.all():
yield api_models.Meter(
name=row.name,
type=row.type,
unit=row.unit,
resource_id=None,
project_id=None,
source=None,
user_id=None)
else:
for row in query_sample.all():
yield api_models.Meter(
name=row.name,
type=row.type,
unit=row.unit,
resource_id=row.resource_id,
project_id=row.project_id,
source=row.source_id,
user_id=row.user_id)
def determine_temps_for_date_range(start, end):
"""Return min temperature, the average temp, and the max temp for a given range."""
#Start and End.
if end != None:
result = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= start).filter(
Measurement.date <= end).all()
#Start only.
else:
result = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= start).all()
# Convert to list.
temp_list = []
no_data = False
for min_temp, avg_temp, max_temp in result:
if min_temp == None or avg_temp == None or max_temp == None:
no_data = True
temp_list.append(min_temp)
temp_list.append(avg_temp)
temp_list.append(max_temp)
# Return JSON.
if no_data == True:
return f"No Data in Date Range"
else:
return jsonify(temp_list)
def get_distribution(
summary_table,
run_table,
machine_table,
commit_table,
commit,
summary,
machine_hash,
limit,
):
commits_up = (
get_commits_up(commit_table, commit, limit).subquery().alias("commits_up")
)
return (
select(
func.text(summary.case_id).label("case_id"),
func.text(summary.context_id).label("context_id"),
func.text(commit.id).label("commit_id"),
func.concat(
machine_table.c.name,
"-",
machine_table.c.gpu_count,
"-",
machine_table.c.cpu_core_count,
"-",
machine_table.c.cpu_thread_count,
"-",
machine_table.c.memory_bytes,
).label("hash"),
func.max(summary_table.c.unit).label("unit"),
func.avg(summary_table.c.mean).label("mean_mean"),
func.stddev(summary_table.c.mean).label("mean_sd"),
func.avg(summary_table.c.min).label("min_mean"),
func.stddev(summary_table.c.min).label("min_sd"),
func.avg(summary_table.c.max).label("max_mean"),
func.stddev(summary_table.c.max).label("max_sd"),
func.avg(summary_table.c.median).label("median_mean"),
func.stddev(summary_table.c.median).label("median_sd"),
func.min(commits_up.c.timestamp).label("first_timestamp"),
func.max(commits_up.c.timestamp).label("last_timestamp"),
func.count(summary_table.c.mean).label("observations"),
)
.group_by(
summary_table.c.case_id,
summary_table.c.context_id,
machine_table.c.name,
machine_table.c.gpu_count,
machine_table.c.cpu_core_count,
machine_table.c.cpu_thread_count,
machine_table.c.memory_bytes,
)
.join(run_table, run_table.c.id == summary_table.c.run_id)
.join(machine_table, machine_table.c.id == run_table.c.machine_id)
.join(commits_up, commits_up.c.id == run_table.c.commit_id)
.filter(
run_table.c.name.like("commit: %"),
summary_table.c.case_id == summary.case_id,
summary_table.c.context_id == summary.context_id,
func.concat(
machine_table.c.name,
"-",
machine_table.c.gpu_count,
"-",
machine_table.c.cpu_core_count,
"-",
machine_table.c.cpu_thread_count,
"-",
machine_table.c.memory_bytes,
)
== machine_hash,
)
)
def weather(start_date):
session = Session(engine)
results = session.query(Measurement.date, func.min(Measurement.tobs), func.max(Measurement.tobs), func.avg(Measurement.tobs)).\
filter(Measurement.date >= start_date).group_by(Measurement.date).all()
session.close()
return (jsonify(results))
def calc_temps_start_end(start_date, end_date):
tobs_stats = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= start_date).filter(Measurement.date <= end_date).all()
tobs_stats_all = []
for min, avg, max in tobs_stats:
dict = {}
dict['min'] = min
dict['avg'] = avg
dict['max'] = max
tobs_stats_all.append(dict)
return jsonify(tobs_stats_all)
stations_total = list(np.ravel(station_results))
return jsonify(stations_total)
@app.route("/api/v1.0/tobs")
def tobs():
tobs_results = session.query(Measurement.tobs).filter(Measurement.date > '2016-08-22').all()
tobs_total = list(np.ravel(tobs_results))
return jsonify(tobs_total)
@app.route("/api/v1.0/<start>")
def calc_temp1(start_date)
start_date = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= start_date).all()
return jsonify(calc_temp1)
@app.route("/api/v1.0/<start>/<end>")
def calc_temp2(start_date, end_date)
start_date = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= start_date).filter(Measurement.date <= end_date).all()
return jsonify(calc_temp.)
if __name__ == "__main__":
app.run(debug=True)
def start_date(start=None):
date_result=session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs),func.max(Measurement.tobs)).filter(Measurement.date>=start).all()
d_result=list(np.ravel(date_result))
session.close()
return jsonify(d_result)
from ceilometer.i18n import _, _LI
from ceilometer import storage
from ceilometer.storage import base
from ceilometer.storage import models as api_models
from ceilometer.storage.sqlalchemy import models
from ceilometer.storage.sqlalchemy import utils as sql_utils
from ceilometer import utils
LOG = log.getLogger(__name__)
STANDARD_AGGREGATES = dict(
avg=func.avg(models.Sample.volume).label('avg'),
sum=func.sum(models.Sample.volume).label('sum'),
min=func.min(models.Sample.volume).label('min'),
max=func.max(models.Sample.volume).label('max'),
count=func.count(models.Sample.volume).label('count')
)
UNPARAMETERIZED_AGGREGATES = dict(
stddev=func.stddev_pop(models.Sample.volume).label('stddev')
)
PARAMETERIZED_AGGREGATES = dict(
validate=dict(
cardinality=lambda p: p in ['resource_id', 'user_id', 'project_id']
),
compute=dict(
cardinality=lambda p: func.count(
distinct(getattr(models.Resource, p))
).label('cardinality/%s' % p)
def temps():
lowest_temp = session.query(Measurements.station, func.min(Measurements.tobs), func.max(Measurements.tobs), func.avg(Measurements.tobs)).\
filter(Measurements.station == "USC00519281").all()
session.close()
return jsonify(lowest_temp)
def run_task(self):
date_insertion = datetime.now()
logger.info("extracting %s ", self.input_filename)
# this pattern matches the first date
# e.g. '20200803ExtractApp'
# will match 20200803
date_string = self.input_filename.split('/')[-1][0:8]
try:
self.last_historical_data_date_in_file = datetime.strptime(date_string, "%Y%m%d")
except ValueError:
raise Exception("couldn't find a date pattern in filename. filename should be \
like 20200803ExtractApp.csv")
count = 0
statements = []
something_new = False
query = """
INSERT into %s(
siret,
hiring_date,
contract_type,
departement,
contract_duration,
iiann,
tranche_age,
handicap_label,
duree_pec,
date_insertion
)
values(%%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s)
""" % settings.HIRING_TABLE
imported_alternance_contracts = 0
imported_alternance_contracts_distribution = {}
not_imported_alternance_contracts = 0
last_historical_data_date_in_db = db_session.query(func.max(Hiring.hiring_date))\
.filter(Hiring.contract_type == self.contract_type).first()[0]
logger.info("will now extract all alternance contracts with hiring_date between %s and %s",
last_historical_data_date_in_db, self.last_historical_data_date_in_file)
with import_util.get_reader(self.input_filename) as myfile:
con, cur = import_util.create_cursor()
header_line = myfile.readline().strip() # FIXME detect column positions from header
if b"SIRET" not in header_line:
logger.debug(header_line)
raise Exception("wrong header line")
for line in myfile:
line = line.decode()
count += 1
if not count % 10000:
logger.debug("reading line %i", count)
try:
try:
cur.executemany(query, statements)
except OperationalError: # retry once in case of deadlock error
time.sleep(10)
cur.executemany(query, statements)
statements = []
con.commit()
something_new = True
except:
logger.error("error in executing statement into hirings table: %s", sys.exc_info()[1])
statements = []
raise
try:
siret, hiring_date, departement = parse_alternance_line(line)
except InvalidRowException:
logger.info("invalid_row met at row: %i", count)
self.invalid_row_errors += 1
continue
except InvalidSiretException:
error_message = traceback.format_exc()
logger.info("invalid siret met at row: %i", count)
logger.info(error_message)
self.invalid_siret_errors += 1
continue
except InvalidZipCodeException:
logger.info("invalid zip code met at row: %i", count)
self.invalid_zipcode_errors += 1
continue
# This part of code is useless :
# The data used has a lot of late contracts inputs
# So we have to insert ALL the contracts from different dates
# alternance_contract_should_be_imported = (
# hiring_date > last_historical_data_date_in_db
# and hiring_date <= self.last_historical_data_date_in_file
#)
if hiring_date <= self.last_historical_data_date_in_file:
statement = (
siret,
hiring_date,
self.contract_type,
departement,
None, #contract_duration
None, #iiann
None, #tranche_age
None, #handicap_label
None, #duree_pec
date_insertion
)
statements.append(statement)
imported_alternance_contracts += 1
if hiring_date.year not in imported_alternance_contracts_distribution:
imported_alternance_contracts_distribution[hiring_date.year] = {}
if hiring_date.month not in imported_alternance_contracts_distribution[hiring_date.year]:
imported_alternance_contracts_distribution[hiring_date.year][hiring_date.month] = {}
if hiring_date.day not in imported_alternance_contracts_distribution[hiring_date.year][hiring_date.month]:
imported_alternance_contracts_distribution[hiring_date.year][hiring_date.month][hiring_date.day] = 0
imported_alternance_contracts_distribution[hiring_date.year][hiring_date.month][hiring_date.day] += 1
# run remaining statements
try:
cur.executemany(query, statements)
something_new = True
except:
logger.error("error in executing statement into hirings table: %s", sys.exc_info()[1])
raise
logger.info(f"Types de contrats à importer : {self.contract_name}")
logger.info(f"processed {count} lba_contracts...")
logger.info(f"imported lba_contracts: {imported_alternance_contracts}")
logger.info(f"not imported lba_contracts: {not_imported_alternance_contracts}")
logger.info(f"zipcode errors: {self.invalid_zipcode_errors}")
logger.info(f"invalid_row errors: {self.invalid_row_errors}")
logger.info(f"invalid siret errors: {self.invalid_siret_errors}")
# if self.zipcode_errors > settings.MAXIMUM_ZIPCODE_ERRORS:
# raise IOError('too many zipcode errors')
# if self.invalid_row_errors > settings.MAXIMUM_INVALID_ROWS:
# raise IOError('too many invalid_row errors')
con.commit()
cur.close()
con.close()
try:
statistics = DpaeStatistics(
last_import=datetime.now(),
most_recent_data_date=self.last_historical_data_date_in_file,
file_type=self.file_type
)
db_session.add(statistics)
db_session.commit()
logger.info("First way to insert DPAE statistics in DB : OK")
except OperationalError:
# For an obscure reason, the DpaeStatistics way to insert does not work on the bonaparte server
# So we insert it directly via an SQL query
# This job has been broken for more than a year, only way to fix it :
db_session.rollback()
last_import_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
most_recent_date = self.last_historical_data_date_in_file.strftime('%Y-%m-%d %H:%M:%S')
query = f"insert into dpae_statistics (last_import, most_recent_data_date, file_type) values ('{last_import_date}','{most_recent_date}','{self.file_type}')"
con, cur = import_util.create_cursor()
cur.execute(query)
con.commit()
cur.close()
con.close()
logger.info("Second way to insert DPAE statistics in DB : OK")
logger.info("finished importing dpae...")
return something_new
def calc_temps(start_date, end_date):
return session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= start_date).filter(Measurement.date <= end_date).all()
def changelog_last_serial(request):
return request.db.query(func.max(JournalEntry.id)).scalar()
def start_end(start,end):
session = Session(engine)
temperatures = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).filter(Measurement.date >= start).filter(Measurement.date <= end).all()
session.close()
temperature_stats = ['Minimum Temp','Average Temp','Maximum Temp']
temps = []
for minimum, average, maximum in temperatures:
temps.append(minimum)
temps.append(average)
temps.append(maximum)
temp_dict = dict(zip(temperature_stats,temps))
return jsonify(temp_dict)
def find_conversation_addressees(cls, login):
query_base = cls.query.filter(or_(cls.receiver==login, cls.sender==login)).with_entities(case([(cls.receiver == login, cls.sender)], else_ = cls.receiver).label("receiver"), func.max(cls.sentAt)).group_by(case([(cls.receiver == login, cls.sender)], else_ = cls.receiver)).order_by(func.max(cls.sentAt).desc()).all()
return query_base
def temperature(start=None,end=None):
Min,Avg,Max = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= start).filter(Measurement.date <= end).all()[0]
results = {
"Max":Max,
"Avg":Avg,
"Min":Min}
return jsonify(results)
def get_last_output_files_for_instance(
asset_instance_id,
temporal_entity_id,
task_type_id=None,
output_type_id=None,
name=None,
representation=None,
file_status_id=None,
):
"""
Get last output files for given entity grouped by output type and name.
"""
# Query maximum revision for each possible arguments
# Only group and filter by file status if specified. Otherwise this could
# lead to different groups of files sharing the same history but with
# different status.
# This could be very misleading when the user would want to get the last.
if file_status_id:
query = OutputFile.query.with_entities(
OutputFile.temporal_entity_id,
OutputFile.task_type_id,
OutputFile.output_type_id,
OutputFile.name,
OutputFile.representation,
OutputFile.file_status_id,
func.max(OutputFile.revision).label("MAX"),
).group_by(
OutputFile.temporal_entity_id,
OutputFile.task_type_id,
OutputFile.output_type_id,
OutputFile.name,
OutputFile.representation,
OutputFile.file_status_id,
)
else:
query = OutputFile.query.with_entities(
OutputFile.temporal_entity_id,
OutputFile.task_type_id,
OutputFile.output_type_id,
OutputFile.name,
OutputFile.representation,
func.max(OutputFile.revision).label("MAX"),
).group_by(
OutputFile.temporal_entity_id,
OutputFile.task_type_id,
OutputFile.output_type_id,
OutputFile.name,
OutputFile.representation,
)
query = query.filter(OutputFile.asset_instance_id == asset_instance_id)
query = query.filter(OutputFile.temporal_entity_id == temporal_entity_id)
if file_status_id:
query = query.filter(OutputFile.file_status_id == file_status_id)
statement = query.subquery()
# Create a join query to retrieve maximum revision
query = OutputFile.query.join(
statement,
and_(
OutputFile.temporal_entity_id == statement.c.temporal_entity_id,
OutputFile.task_type_id == statement.c.task_type_id,
OutputFile.output_type_id == statement.c.output_type_id,
OutputFile.name == statement.c.name,
OutputFile.representation == statement.c.representation,
OutputFile.revision == statement.c.MAX,
),
)
# Filter by specified arguments
query = query.filter(OutputFile.asset_instance_id == asset_instance_id)
query = query.filter(OutputFile.temporal_entity_id == temporal_entity_id)
if task_type_id:
query = query.filter(OutputFile.task_type_id == task_type_id)
if output_type_id:
query = query.filter(OutputFile.output_type_id == output_type_id)
if name:
query = query.filter(OutputFile.name == name)
if representation:
query = query.filter(OutputFile.representation == representation)
if representation:
query = query.filter(OutputFile.file_status_id == file_status_id)
output_files = query.all()
return fields.serialize_models(output_files)
def calc_temps(start, end):
"""TMIN, TAVG, and TMAX for a list of dates.
Args:
start_date (string): A date string in the format %Y-%m-%d
end_date (string): A date string in the format %Y-%m-%d
Returns:
TMIN, TAVE, and TMAX
"""
start_end = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(Measurement.date >= start).filter(Measurement.date <= end).all()
return jsonify(start_end)
