def __create_table_query(self, columns): self.logger.info( "Dropping existing table - {schema}.{table_name}".format( schema=self.schema, table_name=self.table)) q = "DROP TABLE IF EXISTS {schema}.{table_name};".format( schema=self.schema, table_name=self.table) run_query(self.conn, q) q = "CREATE SCHEMA IF NOT EXISTS {schema};CREATE TABLE {schema}.{table_name} (".format( schema=self.schema, table_name=self.table) for column in columns: q += "{column_name} VARCHAR(8000),".format(column_name=column) q = q[:-1] + ");" return q
def get_operating_hours_by_month(start_date, end_date, trainId): query = ( " SELECT HISTOGRAM(loggedAt, INTERVAL 1 hour) as h, vobcid from dlr_train_move" " where loggedAt >= '{}' and loggedAt < '{}' and vobcid ={} group by vobcid, h" ).format(start_date, end_date, trainId) L = util.run_query(query) return len(L.index)
def get_switch_linechart_data(switchId, start_date, end_date): query = ( "SELECT switchCommand, switchCommandDesc,statusDesc,positionDesc, loggedAt, switchId from dlr_switch_move" " where switchId = {} and loggedAt >= '{}' and loggedAt < '{}'" " order by loggedAt ").format(switchId, start_date, end_date) L = util.run_query(query) return L
def get_faultcount_by_vobcid_loc_date(start_date, end_date, vobcid, fault_code, location, velocity_dropdown = None, apstatus = None): faults = '' vobcs = '' loc = '' veld = '' apstat = '' if (fault_code != -1 ): faults = " and faultCode = {}".format(fault_code) if (vobcid != -1 ): vobcs = " and vobcid = {}".format(vobcid) if (location is not None ): loc = " and locationName = '{}'".format(location) if velocity_dropdown is 0: veld = " and velocity = 0" if velocity_dropdown is 1: veld = " and NOT(velocity = 0)" if apstatus is 1: apstat = " and activePassiveStatus = true" if apstatus is 0: apstat = " and activePassiveStatus = false" start_date,end_date = util.date2str2(start_date,end_date) query = ("SELECT HISTOGRAM(loggedAt, INTERVAL 1 DAY) as date, count(loggedAt) as FaultCount" " from dlr_vobc_fault" " where loggedAt >= '{}' and loggedAt < '{}' and faultCodeSet = true {} {} {} {} {}" " GROUP BY date").format(start_date, end_date, vobcs, faults, loc, veld, apstat) df = util.run_query(query) return df
def get_fault_name(faultCode): query = ("SELECT faultName FROM dlr_vobc_fault " " where faultCode = {} group by faultName").format(faultCode) L = util.run_query(query) if L.empty == False: return L["faultName"].tolist() return []
def get_commLoss_by_vobcid_loc(start_date, end_date, velocity_dropdown=None, apstatus=None, commtype=None): start_date, end_date = util.date2str2(start_date, end_date) veld = '' apstat = '' commT = '' if velocity_dropdown is 0: veld = " and velocity = 0" if velocity_dropdown is 1: veld = " and NOT(velocity = 0)" if apstatus is 1: apstat = " and activePassiveStatus = true" if apstatus is 0: apstat = " and activePassiveStatus = false" if commtype is not None and commtype != -1: commT = " and commType = {}".format(commtype) query = ( "SELECT vobcid, locationName, count(commType) as commLossCount from dlr_vobc_comloss" " where commType > 0 and commType < 6 and loggedAt >= '{}' and loggedAt < '{}' {} {} {}" " group by vobcid, locationName" " order by commLossCount desc" " LIMIT 300").format(start_date, end_date, veld, apstat, commT) df = util.run_query(query) if df is None: df = pd.DataFrame() return df
def get_train_mileage_by_loop(vobcid, start_date, end_date): query = ( "SELECT HISTOGRAM(loggedAt, INTERVAL 30 MINUTES) as time, loopName, SUM(distance) as Distance FROM dlr_train_move " " where vobcid = {} and loggedAt >= '{}' and loggedAt < '{}' group by time, loopName" ).format(vobcid, start_date, end_date) L = util.run_query(query) return L
def get_commLoss_list(start_date, end_date, vobc_id=None, location=None, velocity=None, apstatus=None, commtype=None): start_date, end_date = util.date2str2(start_date, end_date) query = "SELECT vobcid, parentTrainId, loggedAt, velocity, activePassiveStatus, locationName, commType from dlr_vobc_comloss where commType > 0 and commType < 6 and loggedAt >= '{}' and loggedAt < '{}'".format( start_date, end_date) if vobc_id is not None and vobc_id != -1: query += " and vobcid = {}".format(vobc_id) if (location is not None): query += " and locationName = '{}'".format(location) if velocity is 0: query += " and velocity = 0" if velocity is 1: query += " and NOT(velocity = 0)" if apstatus is 1: query += " and activePassiveStatus = true" if apstatus is 0: query += " and activePassiveStatus = false" if commtype is not None and commtype != -1: query += " and commType = {}".format(commtype) df = util.run_query(query) if df is None: df = pd.DataFrame() return df
def get_faultcount_by_vobcid_loc(start_date, end_date,fault_code, velocity_dropdown = None, apstatus = None ): start_date,end_date = util.date2str2(start_date,end_date) faults = '' veld = '' apstat = '' if (fault_code != -1 ): faults = " and faultCode = {}".format(fault_code) if velocity_dropdown is 0: veld = " and velocity = 0" if velocity_dropdown is 1: veld = " and NOT(velocity = 0)" if apstatus is 1: apstat = " and activePassiveStatus = true" if apstatus is 0: apstat = " and activePassiveStatus = false" query = ("SELECT vobcid, locationName, count(vobcid) as FaultCount from dlr_vobc_fault" " where loggedAt >= '{}' and loggedAt < '{}' and faultCodeSet = true {} {} {}" " group by vobcid, locationName" " order by FaultCount desc" " LIMIT 300").format(start_date, end_date, faults, veld, apstat) df = util.run_query(query) return df
def get_mileage_by_train(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) query = ( "SELECT vobcid, SUM(distance)/1000 as Distance FROM dlr_train_move " " where loggedAt >= '{}' and loggedAt < '{}' group by vobcid").format( start_date, end_date) L = util.run_query(query) return L
def get_fleet_daily_mileage(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) query = ( "SELECT loggedDate, SUM(distance)/1000 as daily_distance FROM dlr_train_move " " where loggedAt >= '{}' and loggedAt < '{}' group by loggedDate" ).format(start_date, end_date) L = util.run_query(query) return L
def __import_file(self): content = pd.read_csv(self.file_path) columns = list(content.columns) columns = [c.replace(' ', '_') for c in columns] create_table_query = self.__create_table_query(columns) self.logger.info("Creating table {schema}.{table_name}".format( schema=self.schema, table_name=self.table)) run_query(self.conn, create_table_query) self.logger.info("Importing file...") run_query( self.conn, " COPY {schema}.{table} FROM '{file_path}' DELIMITER ',' CSV HEADER;" .format(schema=self.schema, table=self.table, file_path=os.path.abspath(self.file_path))) self.logger.info("Import Completed!")
def get_trainId(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) query = ( "SELECT vobcid from dlr_train_move where loggedAt >= '{}' and loggedAt < '{}' " " group by vobcid").format(start_date, end_date) L = util.run_query(query) L = L["vobcid"].tolist() return L
def get_posts_ids(limit, offset): key = create_posts_keys(limit, offset) latest = memcache.get(key) if latest is None: keys = util.run_query(create_posts_query(), limit, offset=offset, keys_only=True) latest = ','.join([str(k.id()) for k in keys]) memcache.add(key, latest, 86400) return [long(k) for k in latest.split(',')]
def get_vobc_fault_list(start_date, end_date,faultCode): start_date, end_date = util.date2str2(start_date, end_date) query = ("SELECT loggedAt, faultCodeSet, faultCode,parentTrainId, vehicleName," " vobcid, activePassiveStatus as VobcStatus, locationName, loopName, velocity, " " faultDescription FROM dlr_vobc_fault where loggedAt >= '{}' and loggedAt < '{}' and faultCode = {}" " and faultCodeSet = true").format(start_date, end_date, faultCode) L = util.run_query(query) return L
def get_fault_names(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) query = ("SELECT faultName FROM dlr_vobc_fault " " where faultCode > 0 and loggedAt >= '{}' and loggedAt < '{}' group by faultName").format(start_date, end_date) L = util.run_query(query) if L.empty == False: return L["faultName"].tolist() return []
def get_switchId(start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) query = ( "SELECT switchId from dlr_switch_move where loggedAt >= '{}' and loggedAt < '{}' " " group by switchId").format(start_date, end_date) L = util.run_query(query) L = L["switchId"].tolist() return L
def get_unlock_count(start_date, end_date): query = ( "SELECT switchId, COUNT(*) as count from dlr_switch_move" " where interval > 0 and loggedAt >= '{}' and loggedAt < '{}'" " and intervalDesc in ('Moving Time to Left' , 'Moving Time to Right' ) " " and duration >= 10 group by switchId").format(start_date, end_date) L = util.run_query(query) return L
def get_fault_types_per_day(start_date, end_date, vobcid = None): start_date, end_date = util.date2str2(start_date, end_date) if vobcid is not None: x = ("and vobcid = {}").format(vobcid) else: x = "" query = ("SELECT HISTOGRAM(loggedAt, INTERVAL 1 Day) as loggedDay, faultName, count(*) as faultcount FROM dlr_vobc_fault " " where faultCode > 0 and loggedAt >= '{}' and loggedAt < '{}' {} group by faultName, loggedDay").format(start_date, end_date, x) L = util.run_query(query) return L
def index(page): tag = util.param('tag') y = util.param('year') m = util.param('month') if tag or y or m: return abort(404) posts = create_posts_query() title = '' if page > 1: title = u" {0}-я страница".format(page) all_posts = util.run_query(create_posts_query()) archieve = {} for year_and_month, group in itertools.groupby(all_posts, key=lambda post: (post.created.year, post.created.month)): if year_and_month[0] not in archieve: archieve[year_and_month[0]] = [] # iterate each month in the year and month group for m, months in itertools.groupby(group, key=lambda p: p.created.month): posts_count = len(filter(None, months)) item = (m, posts_count) archieve[year_and_month[0]].append(item) # all posts for each year counting for y in archieve: total = 0 for year_and_month in archieve[y]: total += year_and_month[1] item = (y, total) # all posts for a year fake item to output year's statistic archieve[y].insert(0, item) posts = get_paginator(util.run_query(posts), page) return render_template( 'news/index.html', title=title, posts=posts, archieve=archieve, tags=create_tag_rank(all_posts), )
def get_switch_data(switchId, start_date, end_date): query = ( "SELECT switchCommand, switchCommandDesc,statusDesc,positionDesc, loggedAt, switchId from dlr_switch_move" " where switchId = {} and loggedAt >= '{}' and loggedAt < '{}'" " order by loggedAt ").format(switchId, start_date, end_date) df = util.run_query(query) if df.empty == True: return df = df.set_index("loggedAt") df = df.between_time("6:00", "00:00") df = df.reset_index() return df
def get_unique_vobcid_list(start_date, end_date, trainId): start_date, end_date = util.date2str2(start_date, end_date) if trainId == None or end_date == None or start_date == None: return [] query = ("SELECT vobcid from dlr_train_move" " where trainId = '{}' and loggedAt >= '{}' and loggedAt < '{}'" " group by vobcid").format(trainId, start_date, end_date) df = util.run_query(query) if df is None or df.empty: df = pd.DataFrame() return df df = df['vobcid'].unique() return df
def get_first_fault_time(op_date, fault_code, vobc_id): op_date = util.str2date1(op_date) query = ("SELECT min(loggedAt) as fcStart" " from dlr_vobc_fault" " where faultCode = {} and vobcid ={} and loggedDate = '{}'").format(fault_code, vobc_id, op_date.date()) df = util.run_query(query) if (df is None): return op_date dt_str = numpy.datetime_as_string(df['fcStart'][0].to_datetime64(), unit='s') return dateparser.parse(dt_str)
def posts_json(): year = util.param('year', int) month = util.param('month', int) q = create_posts_query() if year: if year and month: current_month = datetime.datetime(year, month, 1) next_month = util.add_months(datetime.datetime(year, month, 1), 1) else: current_month = datetime.datetime(year, 1, 1) next_month = datetime.datetime(year+1, 1, 1) q = q.filter(Post.created >= current_month, Post.created < next_month) q = util.run_query(q) else: tag = util.param('tag') if tag: q = q.filter(Post.tags.IN([tag])) q = util.run_query(q) else: offset = util.param('offset', int) or 0 limit = util.param('limit', int) or config.ATOM_FEED_LIMIT q = util.run_query(q, limit, offset=offset) return util.jsonify_model_dbs(q)
def get_count_location(fault_code, start_date, end_date, vobcid): fault_condition = '' vobc_condition = '' if (fault_code != -1 ): fault_condition = " and faultCode = {}".format(fault_code) if (vobcid != -1 ): vobc_condition = " and vobcid = {}".format(vobcid) start_date,end_date = util.date2str2(start_date,end_date) query = ("SELECT faultName, faultCode, locationName as LocationName, count(*) as FaultCount" " from dlr_vobc_fault" " where vobcid <=300 and loggedAt >= '{}' and loggedAt < '{}' {} {}" " group by faultName, faultCode, locationName LIMIT 5000").format(start_date, end_date, fault_condition, vobc_condition) df = util.run_query(query) return df
def get_commLoss(start_date, end_date, vobcid): if vobcid == None or end_date == None or start_date == None: return pd.DataFrame() start_date, end_date = util.date2str2(start_date, end_date) query = ("SELECT commLossCount, loggedAt, velocity from dlr_train_move" " where vobcid = '{}' and loggedAt >= '{}' and loggedAt < '{}'" ).format(vobcid, start_date, end_date) df = util.run_query(query) if df is None: df = pd.DataFrame() return df
def recent_feed(): feed = AtomFeed('{0} feed'.format(config.CONFIG_DB.brand_name), feed_url=flask.request.url, url=flask.request.url_root) limit = config.ATOM_FEED_LIMIT if util.param('limit'): limit = util.param('limit', int) articles = util.run_query(create_posts_query(), limit) make_external = lambda url: urljoin(flask.request.url_root, url) for article in articles: feed.add(article.title, unicode(article.short_text), content_type='html', author="Alexander Egorov", url=make_external(flask.url_for('news.post', key_id=article.key.id())), updated=article.modified, published=article.created) return feed.get_response()
def get_trainmove(parent_id, start_date, end_date): start_date, end_date = util.date2str2(start_date, end_date) if (parent_id == None or parent_id == -1): return None query = ( "SELECT activePassiveStatus, loggedAt, loggedDate, loopName, velocity, vobcid, trainId, maximumVelocity, doorCmd, doorStatus" " from dlr_train_move " " where trainId = {} and loggedAt >= '{}' and loggedAt < '{}'" " order by loggedAt LIMIT 10000 ").format(parent_id, start_date, end_date) df = util.run_query(query) if df is not None and not df.empty: df['doorStatus'] = df['doorStatus'].apply(lambda x: x * 10 - 35) df['doorCmd'] = df['doorCmd'].apply(lambda x: x * 10 - 35) df['loggedAt'] = pd.to_datetime(df['loggedAt']) return df
def page(self, number): "Returns a Page object for the given 1-based page number." number = self.validate_number(number) bottom = (number - 1) * self.per_page # Get one more entity than requested to see if # we have one more page if type(self.object_list) == list: top = bottom + self.per_page page_items = self.object_list[bottom:top+1] else: page_items = run_query(self.object_list, self.per_page, offset=bottom) if not page_items: if number == 1 and self.allow_empty_first_page: pass else: raise EmptyPage('That page contains no results') # Check if there is a next page has_next = number < self.num_pages return Page(page_items[:self.per_page], number, self, has_next)
def get_commLoss_by_vobcid_loc_date(start_date, end_date, vobcid, location, velocity_dropdown=None, apstatus=None, commtype=None): vobcs = '' loc = '' veld = '' apstat = '' commT = '' if (vobcid != -1): vobcs = " and vobcid = {}".format(vobcid) if (location is not None): loc = " and locationName = '{}'".format(location) if velocity_dropdown is 0: veld = " and velocity = 0" if velocity_dropdown is 1: veld = " and NOT(velocity = 0)" if apstatus is 1: apstat = " and activePassiveStatus = true" if apstatus is 0: apstat = " and activePassiveStatus = false" if commtype is not None and commtype != -1: commT = " and commType = {}".format(commtype) start_date, end_date = util.date2str2(start_date, end_date) query = ( "SELECT HISTOGRAM(loggedAt, INTERVAL 1 DAY) as date, count(*) as commLossCount" " from dlr_vobc_comloss" " where commType > 0 and commType < 6 and loggedAt >= '{}' and loggedAt < '{}' {} {} {} {} {}" " GROUP BY date").format(start_date, end_date, vobcs, loc, veld, apstat, commT) df = util.run_query(query) return df
def get_fault_list(start_date,end_date, vobc_id = None, faultCode = None, location = None, velocity= None, apstatus = None): start_date,end_date = util.date2str2(start_date,end_date) query = "SELECT vobcid, parentTrainId, faultName, faultCode, loggedAt, velocity, faultCodeSet, activePassiveStatus, locationName from dlr_vobc_fault where loggedAt >= '{}' and loggedAt < '{}'".format(start_date,end_date) if vobc_id is not None and vobc_id != -1: query += " and vobcid = {}".format(vobc_id) if faultCode is not None and faultCode != 0 and faultCode != -1: query += " and faultCode = {}".format(faultCode) if (location is not None ): query += " and locationName = '{}'".format(location) if velocity is 0: query += " and velocity = 0" if velocity is 1: query += " and NOT(velocity = 0)" if apstatus is 1: query += " and activePassiveStatus = true" if apstatus is 0: query += " and activePassiveStatus = false" df = util.run_query(query) return df
def get_count_trend(fault_code, start_date, end_date, vobcid, velocity = None, apstatus = None): fault_condition = '' vobc_condition = '' veld = '' apstat = '' if (fault_code != -1 ): fault_condition = " and faultCode = {}".format(fault_code) if (vobcid != -1 ): vobc_condition = " and vobcid = {}".format(vobcid) if velocity is 0: veld = " and velocity = 0" if velocity is 1: veld = " and NOT(velocity = 0)" if apstatus is 1: apstat = " and activePassiveStatus = true" if apstatus is 0: apstat = " and activePassiveStatus = false" query = ("SELECT faultName, faultCode, loggedDate as LoggedDate, count(*) as FaultCount" " from dlr_vobc_fault" " where vobcid <=300 and faultCodeSet = True and loggedAt >= '{}' and loggedAt < '{}' {} {} {} {}" " group by faultName, faultCode, loggedDate LIMIT 5000").format(start_date, end_date, fault_condition, vobc_condition, veld, apstat) df = util.run_query(query) return df
def get_count_by(fault_code, start_date, end_date, velocity = None, apstatus = None): fault_condition = '' apstat ='' vel = '' if (fault_code != -1): fault_condition = " and faultCode = {}".format(fault_code) if velocity is 0: vel += " and velocity = 0" if velocity is 1: vel += " and NOT(velocity = 0)" if apstatus is 1: apstat += " and activePassiveStatus = true" if apstatus is 0: apstat += " and activePassiveStatus = false" start_date,end_date = util.date2str2(start_date,end_date) query = ("SELECT faultName, faultCode, vobcid as VOBCID, count(*) as FaultCount" " from dlr_vobc_fault " " where vobcid <= 300 and faultCodeSet = True and loggedAt >= '{}' and loggedAt < '{}' {} {} {} " " group by faultName, faultCode, vobcid " " LIMIT 10000 ").format( start_date, end_date, fault_condition, vel, apstat) df = util.run_query(query) return df
def test_runquery(): df = util.run_query( "SELECT faultName, loggedAt, velocity from dlr_vobc_fault where loggedAt >= '2014-01-01T00:00:00' and loggedAt < '2015-04-25T00:13:26.017995' LIMIT 2000 " ) assert df['loggedAt'].count() > 100
def get_all_fault(): query = "SELECT faultName, faultCode, count(*) as FaultCount from dlr_vobc_fault group by faultName, faultCode LIMIT 50" df = util.run_query(query) return df