def box_type_monthly(start, end): #票房分类按月统计
db = get_db()
unique_date = db.execute(
'SELECT DISTINCT substr(date, 1, 7)'
'FROM movies '
'WHERE ? <= substr(date, 1, 7) AND substr(date, 1, 7) <= ? '
'ORDER BY date', (start, end)).fetchall()
mtype = db.execute(
'SELECT DISTINCT t.name '
'FROM movie_type mt '
'JOIN movies m ON m.id = mt.movie_id '
'JOIN types t ON t.id = mt.type_id '
'WHERE ? <= substr(date, 1, 7) AND substr(date, 1, 7) <= ? '
'LIMIT 5', (start, end)).fetchall()
unique_date = [x[0] for x in unique_date]
result = {}
for tp, *other in mtype:
result.setdefault(tp, list())
for month in unique_date:
value = db.execute(
'SELECT sum(box_office) value '
'FROM movie_type mt '
'JOIN movies m ON m.id = mt.movie_id '
'JOIN types t ON t.id = mt.type_id '
'WHERE substr(date, 1, 7) = ? AND t.name = ? ',
(month, tp)).fetchone()['value']
if value is None:
value = 0
result[tp].append(value)
return unique_date, result
def count_type_monthly(start, end): #按类型分类按月计数，弃用
result = {}
unique_date = set()
db = get_db()
for each_movie in db.execute(
'SELECT type, substr(date, 1, 7) FROM movies WHERE'
'? <= substr(date, 1, 7) AND substr(date, 1, 7) <= ?',
(start, end)).fetchall():
for each_type in json.loads(each_movie['type']):
date = each_movie['substr(date, 1, 7)']
unique_date.add(date)
result.setdefault(each_type, {})
result[each_type].setdefault(date, 0)
result[each_type][date] += 1
try:
result.pop('None')
except:
pass
unique_date = list(unique_date)
unique_date.sort()
for tp, count in result.items():
result[tp] = []
for date in unique_date:
result[tp].append(count.get(date, 0))
top_five = sorted(result.items(), key=lambda d: sum(d[1]), reverse=True)
top_five = dict(top_five[:5])
return unique_date, top_five
def box_yearly(start, end, mtype=None): #票房年同比
db = get_db()
unique_year = db.execute(
'SELECT DISTINCT substr(date, 1, 4)'
'FROM movies '
'WHERE ? <= substr(date, 1, 4) AND substr(date, 1, 4) <= ? '
'ORDER BY date', (start, end)).fetchall()
unique_year = [x[0] for x in unique_year]
result = {}
for year in unique_year:
result[year] = list()
for i in range(1, 13):
month = '{}-{:02d}'.format(year, i)
if mtype is not None:
result[year].append(
db.execute(
'SELECT sum(box_office) value '
'FROM movie_type mt'
'JOIN movies m ON m.id = mt.movie_id '
'JOIN types t ON t.id = mt.type_id '
'WHERE substr(date, 1, 7) = ? AND t.name = ? ',
(month, mtype)).fetchone()['value'])
else:
result[year].append(
db.execute(
'SELECT sum(box_office) value '
'FROM movies '
'WHERE substr(date, 1, 7) = ?',
(month, )).fetchone()['value'])
return [i for i in range(1, 13)], result
def load_logged_in_user(): #app交互前登录记录
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute('SELECT * FROM user WHERE id = ?',
(user_id, )).fetchone()
def rank_score(start, end): #评分排序
db = get_db()
result = []
for each in db.execute(
'SELECT name, score FROM movies WHERE'
'? <= substr(date, 1, 7) AND substr(date, 1, 7) <= ? '
'ORDER BY score DESC '
'LIMIT 10', (start, end)).fetchall():
result.append(tuple(each))
return result
def box_type(start, end): #票房分类统计
result = []
db = get_db()
for each in db.execute(
'SELECT t.name, sum(box_office) value '
'FROM movie_type mt '
'JOIN movies m ON m.id = mt.movie_id '
'JOIN types t ON t.id = mt.type_id '
'WHERE ? <= substr(date, 1, 7) AND substr(date, 1, 7) <= ? '
'GROUP BY t.name '
'ORDER BY value DESC '
'LIMIT 25', (start, end)).fetchall():
result.append(tuple(each))
return result
def search_db(stype, keyword): #搜索有关stype（电影名、导演等）的关键字相关电影
db = get_db()
keyword = '%{}%'.format(keyword)
if stype == 'name':
movies = db.execute(
'SELECT DISTINCT id, name, date, score FROM movies '
'WHERE name LIKE ? '
'ORDER BY score DESC '
'LIMIT 100', (keyword, )).fetchall()
elif stype == 'director':
movies = db.execute(
'SELECT DISTINCT id, name, date, score FROM movies '
'WHERE director LIKE ? '
'ORDER BY score DESC '
'LIMIT 100', (keyword, )).fetchall()
elif stype == 'actor':
movies = db.execute(
'SELECT DISTINCT m.id id, m.name name, date, score '
'FROM movie_actor ma '
'JOIN movies m ON m.id = ma.movie_id '
'JOIN actors a ON ma.actor_id = a.id '
'WHERE a.name LIKE ? '
'ORDER BY score DESC '
'LIMIT 100', (keyword, )).fetchall()
result = []
for each in movies:
actors = db.execute(
'SELECT a.name '
'FROM movie_actor ma '
'JOIN actors a ON ma.actor_id = a.id '
'WHERE movie_id = ?', (each['id'], )).fetchall()
gen = (a[0] for a in actors)
actors = ', '.join(gen)
types = db.execute(
'SELECT t.name '
'FROM movie_type mt '
'JOIN types t ON mt.type_id = t.id '
'WHERE movie_id = ?', (each['id'], )).fetchall()
gen = (a[0] for a in types)
types = ', '.join(gen)
result.append({
'name': each['name'],
'date': each['date'],
'score': each['score'],
'actors': actors,
'types': types
})
return result
def get_model(start, end): #获取劳模演员
db = get_db()
result = []
for each in db.execute(
'SELECT a.name, count(*) times '
'FROM movie_actor ma '
'JOIN movies m ON m.id = ma.movie_id '
'JOIN actors a ON a.id = ma.actor_id '
'WHERE ? <= substr(date, 1, 7) AND substr(date, 1, 7) <= ? '
'GROUP BY actor_id '
'ORDER BY times DESC '
'LIMIT 15', (start, end)).fetchall():
if each['times'] < 2:
break
result.append(tuple(each))
return result
def count_type(start, end): #按类型计数，弃用
result = {}
db = get_db()
for each_movie in db.execute(
'SELECT type FROM movies WHERE'
'? <= substr(date, 1, 7) AND substr(date, 1, 7) <= ?',
(start, end)).fetchall():
for each_type in json.loads(each_movie['type']):
result.setdefault(each_type, 0)
result[each_type] += 1
try:
result.pop('None')
except:
pass
for each in tuple(result.keys()):
if result[each] <= 3:
result.pop(each)
return result
def get_listing(start, end, mtype=None): #获取榜单，如果给定mtype则说明指定类型，否则为所有类型
db = get_db()
result = []
if mtype is None:
movies = db.execute(
'SELECT id, name, date, score FROM movies '
'WHERE ? <= substr(date, 1, 7) AND substr(date, 1, 7) <= ? '
'ORDER BY score DESC '
'LIMIT 50', (start, end)).fetchall()
else:
movies = db.execute(
'SELECT m.id id, m.name name, date, score '
'FROM movie_type mt '
'JOIN movies m ON m.id = mt.movie_id '
'JOIN types t ON t.id = mt.type_id '
'WHERE ? <= substr(date, 1, 7) AND substr(date, 1, 7) <= ? AND t.name = ? '
'ORDER BY score DESC '
'LIMIT 50', (start, end, mtype)).fetchall()
for each in movies:
actors = db.execute(
'SELECT a.name '
'FROM movie_actor ma '
'JOIN actors a ON ma.actor_id = a.id '
'WHERE movie_id = ?', (each['id'], )).fetchall()
gen = (a[0] for a in actors)
actors = ', '.join(gen)
types = db.execute(
'SELECT t.name '
'FROM movie_type mt '
'JOIN types t ON mt.type_id = t.id '
'WHERE movie_id = ?', (each['id'], )).fetchall()
gen = (a[0] for a in types)
types = ', '.join(gen)
result.append({
'name': each['name'],
'date': each['date'],
'score': each['score'],
'actors': actors,
'types': types
})
return result
def password():
if request.method == 'POST':
password = request.form['password1']
password2 = request.form['password2']
db = get_db()
error = None
if password is None:
error = '请输入密码'
elif password != password2:
error = '两次输入密码不一致'
elif check_valid_password(password) is False:
error = '密码应为8-16位字母、数字'
if error is None:
db.execute('UPDATE user '
'SET password = ? '
'WHERE id = ?',
(generate_password_hash(password), g.user['id']))
db.commit()
return redirect(url_for('movienest.home'))
flash(error)
return render_template('auth/password.html')
def login():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
user = db.execute('SELECT * FROM user WHERE username = ?',
(username, )).fetchone()
error = None
if user is None:
error = '用户不存在'
elif password is None:
error = '请输入密码'
elif not check_password_hash(user['password'], password):
error = '密码错误'
if error is None:
session.clear()
session['user_id'] = user['id']
return redirect(url_for('movienest.home'))
flash(error)
return render_template('auth/login.html')
def register():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
if username is None:
error = '请输入用户名'
elif password is None:
error = '请输入密码'
elif db.execute('SELECT id FROM user where username = ?',
(username, )).fetchone() is not None:
error = '用户已存在'
elif check_valid_password(password) is False:
error = '密码应为8-16位字母、数字'
if error is None:
db.execute('INSERT INTO USER (username, password) VALUES (?, ?)',
(username, generate_password_hash(password)))
db.commit()
return redirect(url_for('auth.login'))
flash(error)
return render_template('auth/register.html')