def _substring_index(self): # 使用 func.substring_index string = 'keyword' with self.session_scope() as session: query_t = session.query(Test).filter( func.substring_index(Test.s, '/', -1).like('%' + string + '%')) row_t = query_t.first() for row_t in query_t.all(): cnt = query_t.count() print((row_t.s, row_t.i, row_t.i2), '\n', cnt) return True
def ajax_html_dirlist(self, args): p = args.get('p', '') p = urllib.unquote_plus(p) # Sanitize path path = re.sub(r"/+", "/", p.rstrip('/')).rstrip('#') # Sanitize physical dir pdir = os.path.realpath(re.sub (r"/+", "/", self.config['rootdir'] + '/' + path)) + '/' # The result has to start with the configured rootdir if pdir[0:len(self.config['rootdir'])] != self.config['rootdir']: pdir = os.path.realpath(self.config['rootdir']) + '/' sql = select([func.substring_index(func.replace(self.db_songtable.c.path, pdir, '' ), '/', 1).label('npath')]).\ where(func.substring_index(func.replace(self.db_songtable.c.path, pdir, '' ), '/', 1) != '').distinct().order_by('npath') rows = self.dbc.execute(sql).fetchall() plroute = 'dir_playlist_%s' % self.config['plformat'] if len(rows) == 0: return render_template('playdir.html', path=path, plroute=plroute) else: numpercol = ceil (len(rows) / 2.0) return render_template('dirlist.html', rows=rows, numpercol=numpercol, path=path)
def metadata_trend(num_days): results = db.session.query( RepoMean.repo_id, func.substring_index( func.group_concat( RepoMean.value.op('ORDER BY')(expression.desc(RepoMean.created_at)) ), ',', 2) )\ .filter(RepoMean.created_at >= datetime.now() + timedelta(days=num_days * -1))\ .group_by(RepoMean.repo_id)\ .all() for result in filter(lambda x: ',' in x[1], results): curr, prev = map(lambda v: float(v), result[1].split(',')) if is_worth_decreased(curr, prev): log.info( 'Mean value of {0} is {1}, previous was {2}. The "worth" has been decreased by 1' .format(result[0], curr, prev)) db.session.query(Repo)\ .filter(Repo.id == result[0])\ .update({Repo.worth: Repo.worth - 1}) db.session.commit()
def metadata_trend(num_days): results = db.session.query( RepoMean.repo_id, func.substring_index( func.group_concat( RepoMean.value.op('ORDER BY')(expression.desc(RepoMean.created_at)) ), ',', 2) )\ .filter(RepoMean.created_at >= datetime.now() + timedelta(days=num_days * -1))\ .group_by(RepoMean.repo_id)\ .all() for result in filter(lambda x: ',' in x[1], results): curr, prev = result[1].split(',') if curr < prev: app.logger.info( 'Mean value of {0} is {1}, previous was {2}. The "worth" has been decreased by 1' .format(result[0], curr, prev) ) db.session.query(Repo)\ .filter(Repo.id == result[0])\ .update({Repo.worth: Repo.worth - 1}) db.session.commit()