def callback_twitter():
oauth_verifier = request.args['oauth_verifier']
auth_session = twitter.get_auth_session(
session['request_token'],
session['request_token_secret'],
method='POST',
data={'oauth_verifier': oauth_verifier})
verify = auth_session.get('account/verify_credentials.json')
if verify.status_code != 200:
response = make_response()
response.status_code = 401
return response
user_info = verify.json()
if not is_exists_record('users', 'provider_id = {0}'.format(
user_info['id'])):
exec_sql(
'insert into users(provider_id, provider_name, raw_name, name) values ({0}, \'{1}\', \'{2}\', \'{3}\')'
.format(user_info['id'], 'twitter', user_info['screen_name'],
user_info['name']), True)
cursor = db_connector.cursor(dictionary=True)
cursor.execute('''
select *
from users
where provider_id = {provider_id}'''.format(provider_id=user_info['id']))
row = cursor.fetchone()
db_connector.commit()
cursor.close()
session['user_id'] = row['id']
return redirect('/')
def get_contributor_videos_count(contributor_id):
unwatch_only = get_bool(request.args.get('unwatch_only'), False)
cursor = db_connector.cursor(dictionary = True)
if 'user_id' in session and unwatch_only:
cursor.execute('''
select count(vi.id) count
from videos vi
inner join videos_contributors vc
on vi.id = vc.video_id
left outer join users_completions ucp
on vi.id = ucp.video_id and ucp.user_id = {user_id}
where vc.contributor_id = {contributor_id} and ucp.video_id is NULL'''.format(contributor_id = contributor_id, user_id = session['user_id']))
else:
cursor.execute('''
select count(vi.id) count
from videos vi
inner join videos_contributors vc
on vi.id = vc.video_id
where vc.contributor_id = {contributor_id}'''.format(contributor_id=contributor_id))
cnt_row = cursor.fetchone()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(cnt_row, default=default)
response.status_code = 200
return response
def callback_twitter():
oauth_verifier = request.args['oauth_verifier']
auth_session = twitter.get_auth_session(session['request_token'],
session['request_token_secret'],
method = 'POST',
data = {'oauth_verifier': oauth_verifier})
verify = auth_session.get('account/verify_credentials.json')
if verify.status_code != 200:
response = make_response()
response.status_code = 401
return response
user_info = verify.json()
if not is_exists_record('users', 'provider_id = {0}'.format(user_info['id'])):
exec_sql('insert into users(provider_id, provider_name, raw_name, name) values ({0}, \'{1}\', \'{2}\', \'{3}\')'.format(
user_info['id'], 'twitter', user_info['screen_name'], user_info['name']), True)
cursor = db_connector.cursor(dictionary = True)
cursor.execute('''
select *
from users
where provider_id = {provider_id}'''.format(provider_id = user_info['id']))
row = cursor.fetchone()
db_connector.commit()
cursor.close()
session['user_id'] = row['id']
return redirect('/')
def get_my_contributor():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
page = get_page_no(request.args.get('page'))
perpage = get_perpage_no(request.args.get('perpage'))
cursor = db_connector.cursor(dictionary = True)
cursor.execute('''
select cb.*
from users_contributors uc
inner join contributors cb
on uc.contributor_id = cb.id
where uc.user_id = {user_id}
order by uc.created_datetime desc
limit {start}, {count}'''.format(user_id = session['user_id'], start = (page - 1) * perpage, count = perpage))
rows = cursor.fetchall()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(rows, default=default)
response.status_code = 200
return response
def get_my_contributor():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
page = get_page_no(request.args.get('page'))
perpage = get_perpage_no(request.args.get('perpage'))
cursor = db_connector.cursor(dictionary=True)
cursor.execute('''
select cb.*
from users_contributors uc
inner join contributors cb
on uc.contributor_id = cb.id
where uc.user_id = {user_id}
order by uc.created_datetime desc
limit {start}, {count}'''.format(user_id=session['user_id'],
start=(page - 1) * perpage,
count=perpage))
rows = cursor.fetchall()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(rows, default=default)
response.status_code = 200
return response
def get_contributor_videos_count(contributor_id):
unwatch_only = get_bool(request.args.get('unwatch_only'), False)
cursor = db_connector.cursor(dictionary=True)
if 'user_id' in session and unwatch_only:
cursor.execute('''
select count(vi.id) count
from videos vi
inner join videos_contributors vc
on vi.id = vc.video_id
left outer join users_completions ucp
on vi.id = ucp.video_id and ucp.user_id = {user_id}
where vc.contributor_id = {contributor_id} and ucp.video_id is NULL'''.
format(contributor_id=contributor_id,
user_id=session['user_id']))
else:
cursor.execute('''
select count(vi.id) count
from videos vi
inner join videos_contributors vc
on vi.id = vc.video_id
where vc.contributor_id = {contributor_id}'''.format(
contributor_id=contributor_id))
cnt_row = cursor.fetchone()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(cnt_row, default=default)
response.status_code = 200
return response
def post_completion(video_id):
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
# videoの存在チェック
if not is_exists_record('videos', 'id = {0}'.format(video_id)):
response = make_response()
response.status_code = 400
return response
# completionの存在チェック
if is_exists_record('completions', 'video_id = {0}'.format(video_id)):
# 既に視聴済みの場合は、レコードを削除し未視聴とする
if is_exists_record('users_completions', 'user_id = {0} and video_id = {1}'.format(session['user_id'], video_id)):
exec_sql('delete from users_completions where user_id = {user_id} and video_id = {video_id}'.format(
user_id = session['user_id'], video_id = video_id), True)
response = jsonify({'isWatched': 'false'})
response.status_code = 201
return response
else:
exec_sql('insert into completions (video_id) values ({0})'.format(video_id), False)
exec_sql('insert into users_completions (user_id, video_id) values ({user_id}, {video_id})'.format(user_id = session['user_id'], video_id = video_id), True)
db_connector.commit()
response = jsonify({'isWatched': 'true'})
response.status_code = 201
return response
def get_user():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
cursor = db_connector.cursor(dictionary=True)
cursor.execute(
'''select u.raw_name, u.name, ucp.comp_count, uc.contributor_count
from users u,
(select count(video_id) comp_count
from users_completions
where user_id = {user_id}) ucp,
(select count(contributor_id) contributor_count
from users_contributors
where user_id = {user_id}) uc
where id = {user_id}'''.format(user_id=session['user_id']))
row = cursor.fetchone()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(row, default=default)
response.status_code = 200
return response
def get_user():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
cursor = db_connector.cursor(dictionary = True)
cursor.execute('''select u.raw_name, u.name, ucp.comp_count, uc.contributor_count
from users u,
(select count(video_id) comp_count
from users_completions
where user_id = {user_id}) ucp,
(select count(contributor_id) contributor_count
from users_contributors
where user_id = {user_id}) uc
where id = {user_id}'''.format(user_id = session['user_id']))
row = cursor.fetchone()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(row, default=default)
response.status_code = 200
return response
def get_my_videos():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
page = get_page_no(request.args.get('page'))
perpage = get_perpage_no(request.args.get('perpage'))
unwatch_only = get_bool(request.args.get('unwatch_only'), False)
# 複数人実況などでvideoが重複して取得される場合があるのでdistinctを付与
if unwatch_only:
cursor = db_connector.cursor(dictionary = True)
cursor.execute('''
select distinct vi.*, cb.icon_url, if(my_completions.video_id <=> NULL, 'false', 'true') watched
from videos vi
inner join (
select * from users_contributors uc
where uc.user_id = {user_id}
) my_contributors
on vi.contributor_id = my_contributors.contributor_id
inner join contributors cb
on vi.contributor_id = cb.id
left outer join (
select video_id from users_completions ucp
where ucp.user_id = {user_id}
) my_completions
on vi.id = my_completions.video_id
where my_completions.video_id is NULL
order by vi.post_datetime desc, vi.serial_no desc
limit {start}, {count}'''.format(user_id = session['user_id'], start = (page - 1) * perpage, count = perpage))
else:
cursor = db_connector.cursor(dictionary = True)
cursor.execute('''
select distinct vi.*, cb.icon_url, if(my_completions.video_id <=> NULL, 'false', 'true') watched
from videos vi
inner join (
select * from users_contributors uc
where uc.user_id = {user_id}
) my_contributors
on vi.contributor_id = my_contributors.contributor_id
inner join contributors cb
on vi.contributor_id = cb.id
left outer join (
select video_id from users_completions ucp
where ucp.user_id = {user_id}
) my_completions
on vi.id = my_completions.video_id
order by vi.post_datetime desc, vi.serial_no desc
limit {start}, {count}'''.format(user_id = session['user_id'], start = (page - 1) * perpage, count = perpage))
rows = cursor.fetchall()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(rows, default=default)
response.status_code = 200
return response
def get_contributor_videos(contributor_id):
page = get_page_no(request.args.get('page'))
perpage = get_perpage_no(request.args.get('perpage'))
unwatch_only = get_bool(request.args.get('unwatch_only'), False)
if 'user_id' in session:
if unwatch_only:
cursor = db_connector.cursor(dictionary = True)
cursor.execute('''
select vi.*, cb.icon_url, if(ucp.video_id <=> NULL, 'false', 'true') watched
from videos vi
inner join videos_contributors vc
on vi.id = vc.video_id
inner join contributors cb
on vi.contributor_id = cb.id
left outer join users_completions ucp
on vi.id = ucp.video_id and ucp.user_id = {user_id}
where vc.contributor_id = {contributor_id} and ucp.video_id is NULL
order by vi.post_datetime desc, vi.serial_no desc
limit {start}, {count}'''.format(user_id = session['user_id'], contributor_id = contributor_id, start = (page - 1) * perpage, count = perpage))
else:
cursor = db_connector.cursor(dictionary = True)
cursor.execute('''
select vi.*, cb.icon_url, if(ucp.video_id <=> NULL, 'false', 'true') watched
from videos vi
inner join videos_contributors vc
on vi.id = vc.video_id
inner join contributors cb
on vi.contributor_id = cb.id
left outer join users_completions ucp
on vi.id = ucp.video_id and ucp.user_id = {user_id}
where vc.contributor_id = {contributor_id}
order by vi.post_datetime desc, vi.serial_no desc
limit {start}, {count}'''.format(user_id = session['user_id'], contributor_id = contributor_id, start = (page - 1) * perpage, count = perpage))
else:
cursor = db_connector.cursor(dictionary = True)
cursor.execute('''
select vi.*, cb.icon_url
from videos vi
inner join videos_contributors vc
on vi.id = vc.video_id
inner join contributors cb
on vi.contributor_id = cb.id
where vc.contributor_id = {contributor_id}
order by vi.post_datetime desc, vi.serial_no desc
limit {start}, {count}'''.format(contributor_id = contributor_id, start = (page - 1) * perpage, count = perpage))
rows = cursor.fetchall()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(rows, default=default)
response.status_code = 200
return response
def is_exists_record(target_tbl, where):
cnt_cursor = db_connector.cursor(dictionary = True)
cnt_cursor.execute('select count(*) as count from {table} where {where}'.format(table=target_tbl, where=where))
cnt_row = cnt_cursor.fetchone()
db_connector.commit()
cnt_cursor.close()
if cnt_row['count'] > 0:
return True
else:
return False
def get_videos_list():
page = get_page_no(request.args.get('page'))
perpage = get_perpage_no(request.args.get('perpage'))
unwatch_only = get_bool(request.args.get('unwatch_only'), False)
if 'user_id' in session:
if unwatch_only:
cursor = db_connector.cursor(dictionary=True)
cursor.execute('''
select vi.*, cb.icon_url, if(ucp.video_id <=> NULL, 'false', 'true') watched
from videos vi
inner join contributors cb
on vi.contributor_id = cb.id
left outer join users_completions ucp
on vi.id = ucp.video_id and ucp.user_id = {user_id}
where ucp.video_id is NULL
order by post_datetime desc, serial_no desc
limit {start}, {count}'''.format(user_id=session['user_id'],
start=(page - 1) * perpage,
count=perpage))
else:
cursor = db_connector.cursor(dictionary=True)
cursor.execute('''
select vi.*, cb.icon_url, if(ucp.video_id <=> NULL, 'false', 'true') watched
from videos vi
inner join contributors cb
on vi.contributor_id = cb.id
left outer join users_completions ucp
on vi.id = ucp.video_id and ucp.user_id = {user_id}
order by post_datetime desc, serial_no desc
limit {start}, {count}'''.format(user_id=session['user_id'],
start=(page - 1) * perpage,
count=perpage))
else:
cursor = db_connector.cursor(dictionary=True)
cursor.execute('''
select vi.*, cb.icon_url
from videos vi
inner join contributors cb
on vi.contributor_id = cb.id
order by post_datetime desc, serial_no desc
limit {start}, {count}'''.format(start=(page - 1) * perpage, count=perpage))
rows = cursor.fetchall()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(rows, default=default)
response.status_code = 200
return response
def post_my_contributor():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
post_data = json.loads(request.data.decode(sys.stdin.encoding))
contributor_id = post_data['id']
# contributor_idの存在チェック
if not is_exists_record('contributors', 'id = {0}'.format(contributor_id)):
res = urllib.request.urlopen('http://api.ce.nicovideo.jp/api/v1/user.info?user_id=' + str(contributor_id))
body = res.read()
contributor_xml = xmltodict.parse(body)['nicovideo_user_response']
if 'vita_option' not in contributor_xml or contributor_xml['vita_option']['user_secret'] == '1':
response = jsonify({'err_msg': '存在しないユーザーです。'})
response.status_code = 400
return response
else:
exec_sql('insert into contributors (id, name, icon_url) values ({0}, \'{1}\', \'{2}\')'.format(
contributor_id, contributor_xml['user']['nickname'], contributor_xml['user']['thumbnail_url']), False)
# 既に登録されていないかのチェック
if is_exists_record('users_contributors','user_id = {user_id} and contributor_id = {contributor_id}'.format(
user_id = session['user_id'], contributor_id = contributor_id)):
response = jsonify({'err_msg': '既に登録済みのユーザーです。'})
response.status_code = 400
return response
exec_sql('insert into users_contributors (user_id, contributor_id) values ({user_id}, {contributor_id})'.format(
user_id = session['user_id'], contributor_id = contributor_id), True)
cursor = db_connector.cursor(dictionary = True)
cursor.execute('''
select cb.*
from users_contributors uc
inner join contributors cb
on uc.contributor_id = cb.id
where uc.user_id = {user_id}
order by uc.created_datetime desc
limit 0, 20'''.format(user_id = session['user_id']))
rows = cursor.fetchall()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(rows, default=default)
response.status_code = 201
return response
def delete_my_contributor():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
post_data = json.loads(request.data.decode(sys.stdin.encoding))
contributor_id = post_data['id']
perpage = post_data['items_per_page']
page = post_data['current_page']
# contributor_idの存在チェック
if not is_exists_record('contributors', 'id = {0}'.format(contributor_id)):
response = jsonify(post_data)
response.status_code = 400
return response
# 登録されているかのチェック
if not is_exists_record(
'users_contributors',
'user_id = {user_id} and contributor_id = {contributor_id}'.format(
user_id=session['user_id'], contributor_id=contributor_id)):
response = jsonify(post_data)
response.status_code = 400
return response
exec_sql(
'delete from users_contributors where user_id = {user_id} and contributor_id = {contributor_id}'
.format(user_id=session['user_id'],
contributor_id=contributor_id), True)
cursor = db_connector.cursor(dictionary=True)
cursor.execute('''
select cb.*
from users_contributors uc
inner join contributors cb
on uc.contributor_id = cb.id
where uc.user_id = {user_id}
order by uc.created_datetime desc
limit {start}, {count}'''.format(user_id=session['user_id'],
start=(page - 1) * perpage,
count=perpage))
rows = cursor.fetchall()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(rows, default=default)
response.status_code = 201
return response
def is_exists_record(target_tbl, where):
cnt_cursor = db_connector.cursor(dictionary=True)
cnt_cursor.execute(
'select count(*) as count from {table} where {where}'.format(
table=target_tbl, where=where))
cnt_row = cnt_cursor.fetchone()
db_connector.commit()
cnt_cursor.close()
if cnt_row['count'] > 0:
return True
else:
return False
def get_my_videos_count():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
unwatch_only = get_bool(request.args.get('unwatch_only'), False)
cursor = db_connector.cursor(dictionary=True)
if unwatch_only:
cursor.execute('''
select count(vi.id) count
from videos vi
inner join (
select * from users_contributors uc
where uc.user_id = {user_id}
) my_contributors
on vi.contributor_id = my_contributors.contributor_id
left outer join (
select * from users_completions ucp
where ucp.user_id = {user_id}
) my_completions
on vi.id = my_completions.video_id
where my_completions.video_id is NULL'''.format(user_id=session['user_id']))
else:
cursor.execute('''
select count(vi.id) count
from videos vi
inner join (
select * from users_contributors uc
where uc.user_id = {user_id}
) my_contributors
on vi.contributor_id = my_contributors.contributor_id
left outer join (
select * from users_completions ucp
where ucp.user_id = {user_id}
) my_completions
on vi.id = my_completions.video_id'''.format(user_id=session['user_id']))
cnt_row = cursor.fetchone()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(cnt_row, default=default)
response.status_code = 200
return response
def get_my_videos_count():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
unwatch_only = get_bool(request.args.get('unwatch_only'), False)
cursor = db_connector.cursor(dictionary = True)
if unwatch_only:
cursor.execute('''
select count(vi.id) count
from videos vi
inner join (
select * from users_contributors uc
where uc.user_id = {user_id}
) my_contributors
on vi.contributor_id = my_contributors.contributor_id
left outer join (
select * from users_completions ucp
where ucp.user_id = {user_id}
) my_completions
on vi.id = my_completions.video_id
where my_completions.video_id is NULL'''.format(user_id = session['user_id']))
else:
cursor.execute('''
select count(vi.id) count
from videos vi
inner join (
select * from users_contributors uc
where uc.user_id = {user_id}
) my_contributors
on vi.contributor_id = my_contributors.contributor_id
left outer join (
select * from users_completions ucp
where ucp.user_id = {user_id}
) my_completions
on vi.id = my_completions.video_id'''.format(user_id = session['user_id']))
cnt_row = cursor.fetchone()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(cnt_row, default=default)
response.status_code = 200
return response
def delete_my_contributor():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
post_data = json.loads(request.data.decode(sys.stdin.encoding))
contributor_id = post_data['id']
perpage = post_data['items_per_page']
page = post_data['current_page']
# contributor_idの存在チェック
if not is_exists_record('contributors', 'id = {0}'.format(contributor_id)):
response = jsonify(post_data)
response.status_code = 400
return response
# 登録されているかのチェック
if not is_exists_record('users_contributors', 'user_id = {user_id} and contributor_id = {contributor_id}'.format(
user_id = session['user_id'], contributor_id = contributor_id)):
response = jsonify(post_data)
response.status_code = 400
return response
exec_sql('delete from users_contributors where user_id = {user_id} and contributor_id = {contributor_id}'.format(
user_id = session['user_id'], contributor_id = contributor_id), True)
cursor = db_connector.cursor(dictionary = True)
cursor.execute('''
select cb.*
from users_contributors uc
inner join contributors cb
on uc.contributor_id = cb.id
where uc.user_id = {user_id}
order by uc.created_datetime desc
limit {start}, {count}'''.format(user_id = session['user_id'], start = (page - 1) * perpage, count = perpage))
rows = cursor.fetchall()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(rows, default=default)
response.status_code = 201
return response
def get_my_contributor_count():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
cursor = db_connector.cursor(dictionary=True)
cursor.execute('''
select count(uc.contributor_id) count
from users_contributors uc
inner join contributors cb
on uc.contributor_id = cb.id
where uc.user_id = {user_id}'''.format(user_id=session['user_id']))
row = cursor.fetchone()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(row, default=default)
response.status_code = 200
return response
def get_my_contributor_count():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
cursor = db_connector.cursor(dictionary = True)
cursor.execute('''
select count(uc.contributor_id) count
from users_contributors uc
inner join contributors cb
on uc.contributor_id = cb.id
where uc.user_id = {user_id}'''.format(user_id = session['user_id']))
row = cursor.fetchone()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(row, default=default)
response.status_code = 200
return response
def post_completion(video_id):
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
# videoの存在チェック
if not is_exists_record('videos', 'id = {0}'.format(video_id)):
response = make_response()
response.status_code = 400
return response
# completionの存在チェック
if is_exists_record('completions', 'video_id = {0}'.format(video_id)):
# 既に視聴済みの場合は、レコードを削除し未視聴とする
if is_exists_record(
'users_completions', 'user_id = {0} and video_id = {1}'.format(
session['user_id'], video_id)):
exec_sql(
'delete from users_completions where user_id = {user_id} and video_id = {video_id}'
.format(user_id=session['user_id'], video_id=video_id), True)
response = jsonify({'isWatched': 'false'})
response.status_code = 201
return response
else:
exec_sql(
'insert into completions (video_id) values ({0})'.format(video_id),
False)
exec_sql(
'insert into users_completions (user_id, video_id) values ({user_id}, {video_id})'
.format(user_id=session['user_id'], video_id=video_id), True)
db_connector.commit()
response = jsonify({'isWatched': 'true'})
response.status_code = 201
return response
def exec_sql(sql, commit): ins_cursor = db_connector.cursor() ins_cursor.execute(sql) ins_cursor.close() if commit: db_connector.commit()
def post_my_contributor():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
post_data = json.loads(request.data.decode(sys.stdin.encoding))
contributor_id = post_data['id']
# contributor_idの存在チェック
if not is_exists_record('contributors', 'id = {0}'.format(contributor_id)):
res = urllib.request.urlopen(
'http://api.ce.nicovideo.jp/api/v1/user.info?user_id=' +
str(contributor_id))
body = res.read()
contributor_xml = xmltodict.parse(body)['nicovideo_user_response']
if 'vita_option' not in contributor_xml or contributor_xml[
'vita_option']['user_secret'] == '1':
response = jsonify({'err_msg': '存在しないユーザーです。'})
response.status_code = 400
return response
else:
exec_sql(
'insert into contributors (id, name, icon_url) values ({0}, \'{1}\', \'{2}\')'
.format(contributor_id, contributor_xml['user']['nickname'],
contributor_xml['user']['thumbnail_url']), False)
# 既に登録されていないかのチェック
if is_exists_record(
'users_contributors',
'user_id = {user_id} and contributor_id = {contributor_id}'.format(
user_id=session['user_id'], contributor_id=contributor_id)):
response = jsonify({'err_msg': '既に登録済みのユーザーです。'})
response.status_code = 400
return response
exec_sql(
'insert into users_contributors (user_id, contributor_id) values ({user_id}, {contributor_id})'
.format(user_id=session['user_id'],
contributor_id=contributor_id), True)
cursor = db_connector.cursor(dictionary=True)
cursor.execute('''
select cb.*
from users_contributors uc
inner join contributors cb
on uc.contributor_id = cb.id
where uc.user_id = {user_id}
order by uc.created_datetime desc
limit 0, 20'''.format(user_id=session['user_id']))
rows = cursor.fetchall()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(rows, default=default)
response.status_code = 201
return response
def get_my_videos():
if 'user_id' not in session:
response = make_response()
response.status_code = 401
return response
page = get_page_no(request.args.get('page'))
perpage = get_perpage_no(request.args.get('perpage'))
unwatch_only = get_bool(request.args.get('unwatch_only'), False)
# 複数人実況などでvideoが重複して取得される場合があるのでdistinctを付与
if unwatch_only:
cursor = db_connector.cursor(dictionary=True)
cursor.execute('''
select distinct vi.*, cb.icon_url, if(my_completions.video_id <=> NULL, 'false', 'true') watched
from videos vi
inner join (
select * from users_contributors uc
where uc.user_id = {user_id}
) my_contributors
on vi.contributor_id = my_contributors.contributor_id
inner join contributors cb
on vi.contributor_id = cb.id
left outer join (
select video_id from users_completions ucp
where ucp.user_id = {user_id}
) my_completions
on vi.id = my_completions.video_id
where my_completions.video_id is NULL
order by vi.post_datetime desc, vi.serial_no desc
limit {start}, {count}'''.format(user_id=session['user_id'],
start=(page - 1) * perpage,
count=perpage))
else:
cursor = db_connector.cursor(dictionary=True)
cursor.execute('''
select distinct vi.*, cb.icon_url, if(my_completions.video_id <=> NULL, 'false', 'true') watched
from videos vi
inner join (
select * from users_contributors uc
where uc.user_id = {user_id}
) my_contributors
on vi.contributor_id = my_contributors.contributor_id
inner join contributors cb
on vi.contributor_id = cb.id
left outer join (
select video_id from users_completions ucp
where ucp.user_id = {user_id}
) my_completions
on vi.id = my_completions.video_id
order by vi.post_datetime desc, vi.serial_no desc
limit {start}, {count}'''.format(user_id=session['user_id'],
start=(page - 1) * perpage,
count=perpage))
rows = cursor.fetchall()
db_connector.commit()
cursor.close()
response = make_response()
response.data = json.dumps(rows, default=default)
response.status_code = 200
return response
def exec_sql(sql, commit):
ins_cursor = db_connector.cursor()
ins_cursor.execute(sql)
ins_cursor.close()
if commit:
db_connector.commit()
