def get(self):
cur_page = self.get_argument('cur_page', '1')
page_size = 5
log.info('获取文章分类列表cur_page：' + cur_page)
sql = """
select (
select count(id)
from article_class
) as total, *
from article_class
order by id desc
limit %d offset %d
""" % (page_size, (int(cur_page) - 1) * page_size)
session = DBSession()
cursor = session.execute(sql)
data = cursor.fetchall()
table_data = {
'data': [],
'page_size': page_size,
'total': data[0]['total'] if data else 0
}
for d in data:
table_data['data'].append({
'id': d.id,
'name': d.name,
'create_date': d.create_date.strftime('%Y-%m-%d %H:%M:%S'),
'write_date': d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
return self.finish(json.dumps(table_data))
def get(self):
cur_page = self.get_argument('cur_page', '1')
page_size = 5
log.info('获取文章列表cur_page：' + cur_page)
sql = """
select (
select count(id)
from article
) as total,
a.id, ac.name, a.image_url, a.title, a.author, a.create_date, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
order by id desc
limit %d offset %d
""" % (page_size, (int(cur_page) - 1) * page_size)
session = DBSession()
cursor = session.execute(sql)
data = cursor.fetchall()
table_data = {
'data': [],
'page_size': page_size,
'total': data[0]['total'] if data else 0
}
for d in data:
table_data['data'].append({
'id':
d.id,
'class_name':
d.name,
'image_url':
domain_name + d.image_url,
'title':
d.title,
'author':
d.author,
'create_date':
d.create_date.strftime('%Y-%m-%d %H:%M:%S'),
'write_date':
d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
return self.finish(json.dumps(table_data))
def get(self):
class_sql = """
select ac.id, ac.name
from article_class as ac
where ac.id in (select distinct class_id from article)
order by ac.id
"""
session = DBSession()
cursor = session.execute(class_sql)
class_data = cursor.fetchall()
session.close()
class_result = []
for d in class_data:
class_result.append({'id': d.id, 'name': d.name})
self.render("404.html", data=class_result)
def get(self, article_id):
new_sql = """
select a.id, a.class_id, ac.name, a.title, a.author, a.note, a.content, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
where a.id = %d
""" % int(article_id)
hot_sql = """
select a.id, ac.name, a.image_url, a.title, a.author, a.note, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
where ac.id = 1
order by id desc
limit 5
"""
class_sql = """
with cla_count as (
select class_id, count(id) as total
from article
group by class_id
)
select ac.id, ac.name, cc.total
from article_class as ac
inner join cla_count as cc on cc.class_id = ac.id
order by ac.id
"""
session = DBSession()
cursor = session.execute(new_sql)
new_data = cursor.fetchall()
cursor = session.execute(hot_sql)
hot_data = cursor.fetchall()
cursor = session.execute(class_sql)
class_data = cursor.fetchall()
session.close()
new_result = {}
for d in new_data:
new_result = {
'id': d.id,
'class_id': d.class_id,
'class_name': d.name,
'title': d.title,
'author': d.author,
'note': d.note,
'content': d.content,
'write_date': d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
}
hot_result = []
for d in hot_data:
image_url = d.image_url
if not image_url:
image_url = 'article/images/default.png'
hot_result.append({
'id':
d.id,
'class_name':
d.name,
'image_url':
domain_name + image_url,
'title':
d.title,
'author':
d.author,
'note':
d.note,
'write_date':
d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
class_result = []
for d in class_data:
class_result.append({'id': d.id, 'name': d.name, 'total': d.total})
data = {
'class_data': class_result,
'new_data': new_result,
'hot_data': hot_result
}
self.render("show.html", data=data)
def get(self, class_id):
cur_page = self.get_argument('page', '1')
page_size = 10
class_id = int(class_id) if class_id else -1
new_sql = """
select a.id, a.class_id, ac.name, a.image_url, a.title, a.author, a.note, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
where ac.id = %d
order by id desc
limit %d offset %d
""" % (class_id, page_size, (int(cur_page) - 1) * page_size)
hot_sql = """
select a.id, ac.name, a.image_url, a.title, a.author, a.note, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
where ac.id = 1
order by id desc
limit 5
"""
class_sql = """
with cla_count as (
select class_id, count(id) as total
from article
group by class_id
)
select ac.id, ac.name, cc.total
from article_class as ac
inner join cla_count as cc on cc.class_id = ac.id
order by ac.id
"""
session = DBSession()
cursor = session.execute(new_sql)
new_data = cursor.fetchall()
cursor = session.execute(hot_sql)
hot_data = cursor.fetchall()
cursor = session.execute(class_sql)
class_data = cursor.fetchall()
session.close()
new_result = []
for d in new_data:
image_url = d.image_url
if not image_url:
image_url = 'article/images/default.png'
new_result.append({
'id': d.id,
'class_id': d.class_id,
'class_name': d.name,
'image_url': domain_name + image_url,
'title': d.title,
'author': d.author,
'note': d.note,
'write_date': d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
hot_result = []
for d in hot_data:
image_url = d.image_url
if not image_url:
image_url = 'article/images/default.png'
hot_result.append({
'id': d.id,
'class_name': d.name,
'image_url': domain_name + image_url,
'title': d.title,
'author': d.author,
'note': d.note,
'write_date': d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
class_result = []
for d in class_data:
class_result.append({
'id': d.id,
'name': d.name,
'total': d.total
})
next_page = int(cur_page) + 1
data = {
'class_data': class_result,
'new_data': new_result,
'hot_data': hot_result,
'next_page': next_page,
'cur_class': class_id
}
self.render("list.html", data=data)
def get(self):
cur_page = self.get_argument('page', '1')
page_size = 10
new_sql = """
select a.id, a.class_id, ac.name, a.image_url, a.title, a.author, a.note, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
order by id desc
limit %d offset %d
""" % (page_size, (int(cur_page) - 1) * page_size)
hot_sql = """
select a.id, ac.name, a.image_url, a.title, a.author, a.note, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
where ac.id = 1
order by id desc
limit 5
"""
class_sql = """
select ac.id, ac.name
from article_class as ac
where ac.id in (select distinct class_id from article)
order by ac.id
"""
session = DBSession()
cursor = session.execute(new_sql)
new_data = cursor.fetchall()
cursor = session.execute(hot_sql)
hot_data = cursor.fetchall()
cursor = session.execute(class_sql)
class_data = cursor.fetchall()
session.close()
new_result = []
for d in new_data:
image_url = d.image_url
if not image_url:
image_url = 'article/images/default.png'
new_result.append({
'id':
d.id,
'class_id':
d.class_id,
'class_name':
d.name,
'image_url':
domain_name + image_url,
'title':
d.title,
'author':
d.author,
'note':
d.note,
'write_date':
d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
hot_result = []
for d in hot_data:
image_url = d.image_url
if not image_url:
image_url = 'article/images/default.png'
hot_result.append({
'id':
d.id,
'class_name':
d.name,
'image_url':
domain_name + image_url,
'title':
d.title,
'author':
d.author,
'note':
d.note,
'write_date':
d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
class_result = []
for d in class_data:
class_result.append({'id': d.id, 'name': d.name})
next_page = int(cur_page) + 1
data = {
'class_data': class_result,
'new_data': new_result,
'hot_data': hot_result,
'next_page': next_page
}
self.render("index.html", data=data)
