def history(self):
start = request.params.get('start',None)
end = request.params.get('end',None)
limit = int(request.params.get('days','0'))
opts = request.params.get('opts','').split(',')
whereclause = []
vars = {}
groupby = []
if limit and not start and not end:
whereclause.append(History.published >= UTCDateTime.now() - timedelta(days=limit))
if start:
whereclause.append(History.published >= UTCDateTime.from_string(start))
if end:
whereclause.append(History.published < UTCDateTime.from_string(end))
if 'perday' in opts:
if 'domain' in opts:
s = select([func.date_format(History.published, "%Y-%m-%d"), History.domain, func.count(History.domain)],)
groupby.append(func.to_days(History.published))
groupby.append(History.domain)
else:
s = select([func.date_format(History.published, "%Y-%m-%d"), func.count(History.id)])
groupby.append(func.to_days(History.published))
else:
s = select([func.count(History.domain), History.domain])
groupby.append(History.domain)
if whereclause:
s = s.where(and_(*whereclause))
if groupby:
s = s.group_by(*groupby)
return [list(a) for a in Session.execute(s).fetchall()]
def web_article(seo):
params = list()
params.append(Article.isvisible == 1)
params.append(Article.ispublish == 1)
params.append(Article.valid == 1)
params.append(Article.deleted_at == None)
params.append(
Article.date_expire > datetime.datetime.now().strftime("%Y-%m-%d"))
params.append(Article.article_seo == seo)
article = db.session.query(Article).filter(*params).first()
# category
category = db.session.query(Category.category_name,
Category.category_desc).filter(
Category.deleted_at == None,
Category.valid == '1').all()
# tags
tags = db.session.query(Article.article_tag).filter(
Article.deleted_at == None, Article.ispublish == 1,
Article.isvisible == 1, Article.valid == 1).all()
tag_set = set()
for tag in tags:
tag_set.update([g for g in tag[0].split("@") if g])
# 友情链接
link = db.session.query(Link.link_name, Link.link_href,
Link.link_desc).filter(Link.deleted_at == None,
Link.valid == 1).all()
# archive by date
archives = db.session.query(
func.date_format(Article.created_at, "%Y-%m"),
func.count('*').label('t')).filter(*params).group_by(
func.date_format(Article.created_at, "%Y-%m")).all()
if article:
return render_template('{}/article.html'.format(session.get('skin')),
setting=session.get('setting'),
article=article,
category=category,
link=link,
archives=archives,
tag_set=tag_set)
else:
abort(404)
def published_chart(self):
query = db.session.query(
func.date_format(Document.published_at, '%Y/%m/%d').label('t'),
func.count(Document.id),
).group_by('t')
return {'values': dict(self.filter(query).all())}
def mention_frequencies(self, ids):
"""
Return dict from person ID to a list of how frequently each
person was mentioned per day, over the period.
"""
rows = db.session.query(
Entity.person_id,
func.date_format(Document.published_at, '%Y-%m-%d').label('date'),
func.count(distinct(DocumentEntity.doc_id)).label('count')
) \
.join(DocumentEntity, Entity.id == DocumentEntity.entity_id) \
.join(Document, DocumentEntity.doc_id == Document.id) \
.filter(Entity.person_id.in_(ids))\
.filter(DocumentEntity.doc_id.in_(self.doc_ids))\
.group_by(Entity.person_id, 'date')\
.order_by(Entity.person_id, Document.published_at)\
.all()
freqs = {}
for person_id, group in groupby(rows, lambda r: r[0]):
freqs[person_id] = [0] * (self.days+1)
# set day buckets based on date
for row in group:
d, n = parse(row[1]).date(), row[2]
day = (d - self.start_date).days
freqs[person_id][day] = n
return freqs
def category_lists():
if request.method == 'POST':
try:
pageSize = int( request.form.get('pageSize') )
pageNumber = int( request.form.get('pageNumber') )
except Exception:
pageSize = app.config.get('PAGE_SIZE')
pageNumber = 1
params = []
if not 'root' in session.get('role'):
params.append(Category.category_creator==session.get('username'))
pagination = db.session.query(
Category.id,
Category.category_name,
Category.category_desc,
Category.valid,
func.date_format(Category.created_at, "%Y-%m-%d %H:%m:%S"),
).filter(*params) \
.order_by(Category.created_at.desc()) \
.paginate(pageNumber, per_page=pageSize, error_out=True)
total = pagination.total
items = pagination.items
title = ['id','category_name', 'category_desc', 'valid', 'created_at']
return make_response(jsonify({
"code": app.config.get('RESPONSE_SUCCESS_CODE'),
"message" : app.config.get('RESPONSE_SUCCESS_MESSAGE'),
"rows": zip_dict(title, list(items)),
"total": total
}))
return render_template('category.html')
def source_frequencies(self, ids):
"""
Return dict from person ID to a list of how frequently each
source was used per day, over the period.
"""
rows = db.session.query(
DocumentSource.person_id,
func.date_format(Document.published_at, '%Y-%m-%d').label('date'),
func.count(1).label('count')
)\
.join(Document, DocumentSource.doc_id == Document.id)\
.filter(DocumentSource.person_id.in_(ids))\
.filter(DocumentSource.doc_id.in_(self.doc_ids))\
.group_by(DocumentSource.person_id, 'date')\
.order_by(DocumentSource.person_id, Document.published_at)\
.all()
freqs = {}
for person_id, group in groupby(rows, lambda r: r[0]):
freqs[person_id] = [0] * (self.days+1)
# set day buckets based on date
for row in group:
d, n = parse(row[1]).date(), row[2]
day = (d - self.start_date).days
freqs[person_id][day] = n
return freqs
def mention_frequencies(self, ids):
"""
Return dict from person ID to a list of how frequently each
person was mentioned per day, over the period.
"""
rows = (
db.session.query(
Entity.person_id,
func.date_format(Document.published_at, "%Y-%m-%d").label("date"),
func.count(distinct(DocumentEntity.doc_id)).label("count"),
)
.join(DocumentEntity, Entity.id == DocumentEntity.entity_id)
.join(Document, DocumentEntity.doc_id == Document.id)
.filter(Entity.person_id.in_(ids))
.filter(DocumentEntity.doc_id.in_(self.doc_ids))
.group_by(Entity.person_id, "date")
.order_by(Entity.person_id, Document.published_at)
.all()
)
freqs = {}
for person_id, group in groupby(rows, lambda r: r[0]):
freqs[person_id] = [0] * (self.days + 1)
# set day buckets based on date
for row in group:
d, n = parse(row[1]).date(), row[2]
day = (d - self.start_date).days
freqs[person_id][day] = n
return freqs
def published_chart(self):
query = db.session.query(
func.date_format(Document.published_at, '%Y/%m/%d').label('t'),
func.count(Document.id),
).group_by('t')
return {
'values': dict(self.filter(query).all())
}
def article_lists():
if request.method == 'POST':
try:
pageSize = int(request.form.get('pageSize'))
pageNumber = int(request.form.get('pageNumber'))
except Exception:
pageSize = app.config.get('PAGE_SIZE')
pageNumber = 1
params = []
params.append(Article.deleted_at == None)
params.append(Article.valid == '1')
article_title = request.form.get('article_title')
if article_title:
params.append(Article.article_title == article_title)
'''
if not 'root' in session.get('role'):
params.append(Article.article_author==session.get('username'))
'''
pagination = db.session.query(
Article.id,
Article.article_title,
Article.article_category,
func.date_format( Article.created_at, "%Y-%m-%d %H:%m:%S"),
Article.article_author,
Article.article_click,
Article.top,
Article.iscomment,
Article.ispublish,
Article.isvisible,
Article.article_password
).filter(*params)\
.order_by(Article.created_at.desc())\
.paginate(pageNumber, per_page=pageSize, error_out=True)
total = pagination.total
items = pagination.items
title = [
'id', 'title', 'category', 'created', 'author', 'click', 'top',
'iscomment', 'ispublish', 'isvisible', 'ispassword'
]
return make_response(
jsonify({
"code": 0,
"rows": zip_dict(title, list(items)),
"total": total
}))
return render_template('article.html')
def getPunchInfoByMonth(group_puid, group_name, nick_name, user_puid):
mySession = session()
punchInfos = mySession.query(PunchInfo, PunchInfo.addtime).filter(
or_(PunchInfo.group_name == group_name,
PunchInfo.group_puid == group_puid),
or_(PunchInfo.nick_name == nick_name,
PunchInfo.user_puid == user_puid),
func.date_format(
PunchInfo.addtime,
"%Y-%m") == datetime.datetime.now().strftime('%Y-%m')).order_by(
desc(PunchInfo.addtime)).all()
mySession.close()
return punchInfos
def share_lists():
if request.method == 'POST':
try:
pageSize = int(request.form.get('pageSize'))
pageNumber = int(request.form.get('pageNumber'))
except Exception:
pageSize = app.config.get('PAGE_SIZE')
pageNumber = 1
params = []
params.append(Share.deleted_at == None)
pagination = db.session.query(
Share.id,
Share.share_name,
Share.share_click,
Share.share_download,
Share.share_creator,
Share.share_link,
Share.share_location,
Share.share_password,
Share.share_price,
Share.share_type,
Share.valid,
func.date_format(Share.created_at, "%Y-%m-%d %H:%m:%S")
).filter(*params) \
.order_by(Share.share_click.desc(), Share.share_download.desc()) \
.paginate(pageNumber, per_page=pageSize, error_out=True)
total = pagination.total
items = pagination.items
title = [
'id', 'share_name', 'share_click', 'share_download',
'share_creator', 'share_link', 'share_location', 'share_password',
'share_price', 'share_type', 'valid', 'created_at'
]
return make_response(
jsonify({
"code": app.config.get('RESPONSE_SUCCESS_CODE'),
"message": app.config.get('RESPONSE_SUCCESS_MESSAGE'),
"rows": zip_dict(title, list(items)),
"total": total
}))
get_page_name()
return render_template('share.html')
def navigate_lists():
if request.method == 'POST':
try:
pageSize = int(request.form.get('pageSize'))
pageNumber = int(request.form.get('pageNumber'))
except Exception:
pageSize = app.config.get('PAGE_SIZE')
pageNumber = 1
params = []
params.append(Navigate.deleted_at == None)
if session.get('username') != 'root':
params.append(Navigate.navigate_creator == session.get('username'))
pagination = db.session.query(
Navigate.id,
Navigate.navigate_name,
Navigate.navigate_url,
Navigate.navigate_tag,
Navigate.navigate_icon,
Navigate.sq,
Navigate.valid,
func.date_format(Navigate.created_at, "%Y-%m-%d %H:%m:%S")
).filter(*params) \
.order_by(Navigate.created_at.desc()) \
.paginate(pageNumber, per_page=pageSize, error_out=True)
total = pagination.total
items = pagination.items
title = [
'id', 'navigate_name', 'navigate_url', 'navigate_tag',
'navigate_icon', 'sq', 'valid', 'created_at'
]
return make_response(
jsonify({
"code": app.config.get('RESPONSE_SUCCESS_CODE'),
"message": app.config.get('RESPONSE_SUCCESS_MESSAGE'),
"rows": zip_dict(title, list(items)),
"total": total
}))
return render_template('navigate.html')
def link_lists():
if request.method == 'POST':
try:
pageSize = int(request.form.get('pageSize'))
pageNumber = int(request.form.get('pageNumber'))
except Exception:
pageSize = app.config.get('PAGE_SIZE')
pageNumber = 1
params = []
params.append(Link.deleted_at == None)
pagination = db.session.query(
Link.id,
Link.link_name,
Link.link_href,
Link.link_desc,
Link.link_creator,
Link.link_field,
Link.valid,
func.date_format(Link.created_at, "%Y-%m-%d %H:%m:%S")
).filter(*params) \
.order_by(Link.created_at.desc()) \
.paginate(pageNumber, per_page=pageSize, error_out=True)
total = pagination.total
items = pagination.items
title = [
'id', 'link_name', 'link_href', 'link_desc', 'link_creator',
'link_field', 'valid', 'created_at'
]
return make_response(
jsonify({
"code": app.config.get('RESPONSE_SUCCESS_CODE'),
"message": app.config.get('RESPONSE_SUCCESS_MESSAGE'),
"rows": zip_dict(title, list(items)),
"total": total
}))
return render_template('link.html')
def dialect_format_date(field, format_string):
if db.session.bind.dialect.name == 'sqlite':
return func.strftime(format_string, field)
elif db.session.bind.dialect.name == 'mysql':
return func.date_format(field, format_string)
pprint(results)
pl(28) # _______________________________________________________________
# Accessing Built- in Functions:
c = [
# date/time functions #
func.curtime(),
func.localtime(),
func.current_timestamp(),
func.date_format(func.now(), '%M'),
func.now(),
# matematical 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(" a bc "),
func.char(65)
