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