def fileDelete():
files_to_delete = request.form.getlist("files")
deeb_data = query_db(
"select fname, fhash from audiofiles where fname in ({})".format(
",".join(['?'] * len(files_to_delete))), tuple(files_to_delete))
# Delete from DB
deeb = get_db()
cur = deeb.cursor()
cur.execute(
"DELETE FROM audiofiles WHERE fname in ({})".format(",".join(
['?'] * len(files_to_delete))), tuple(files_to_delete))
deeb.commit()
close_db()
# Delete hash folder that holds all files
for dat in deeb_data:
shutil.rmtree(os.path.join(UPLOAD_FOLDER, 'test', dat['fhash']), True)
retnames = []
deeb_data = query_db("select fname from audiofiles")
for dat in deeb_data:
retnames.append(dat["fname"])
return jsonify(names=retnames)
def suche():
form = SearchForm(request.form)
if request.method == 'POST':
results = []
if request.form['name']:
name = "%" + request.form['name'] + "%"
results += query_db(
"SELECT DISTINCT A.Name, A.Datum, A.Uhrzeit, A.Dirigent, P.Künstlername \
FROM Aufführung_von A INNER JOIN singen s ON A.Datum=s.Datum AND A.Uhrzeit=s.Uhrzeit \
INNER JOIN Sänger P ON s.SozNr = P.Soznr \
WHERE A.Name LIKE ?",
(name, ))
elif request.form['dirigent']:
dirigent = "%" + request.form['dirigent'] + "%"
results += query_db(
"SELECT * FROM Aufführung_von WHERE Dirigent LIKE ?",
(dirigent, ))
elif request.form['datum']:
datum = request.form['datum']
results += query_db(
"SELECT * FROM Aufführung_von WHERE Datum LIKE ?", (datum, ))
elif request.form['saenger']:
saenger = "%" + request.form['saenger'] + "%"
results += query_db(
"SELECT DISTINCT A.Name, A.Datum, A.Uhrzeit, A.Dirigent, P.Künstlername \
FROM Aufführung_von A INNER JOIN singen s ON A.Datum=s.Datum AND A.Uhrzeit=s.Uhrzeit \
INNER JOIN Sänger P ON s.SozNr = P.Soznr \
WHERE P.Künstlername LIKE ?",
(saenger, ))
return render_template("ergebnisse.html", results=results)
return render_template("suche.html", form=form)
def catalog_import():
query_db("DELETE FROM ps_pack", [], "new", commit=True)
for t in [
'pack', 'image', 'image_lang', 'image_shop', 'image_type',
'manufacturer', 'manufacturer_lang', 'manufacturer_shop',
'attribute', 'attribute_group', 'attribute_group_lang',
'attribute_group_shop', 'attribute_impact', 'attribute_lang',
'attribute_shop', 'feature', 'feature_shop', 'feature_lang',
'feature_value', 'feature_value_lang', 'product',
'product_attachment', 'product_attribute',
'product_attribute_combination', 'product_attribute_image',
'product_attribute_shop', 'product_carrier', 'product_lang',
'product_sale', 'product_shop', 'product_supplier', 'product_tag',
'product_country_tax', 'supplier', 'supplier_lang',
'supplier_shop', 'supply_order', 'supply_order_detail', 'category',
'category_group', 'category_lang', 'category_product',
'category_shop', 'stock_available', "stock_mvt_reason",
"stock_mvt_reason_lang", 'product', 'product_sale',
'product_comment', 'product_comment_criterion',
'product_comment_criterion_category',
'product_comment_criterion_lang',
'product_comment_criterion_product', 'product_comment_grade',
'product_comment_report', 'product_comment_usefulness'
]:
copy1t1("psNew2_" + t, "ps_" + t, [], clear_new_table=True)
def download(channelid):
f = StringIO()
writer = csv.writer(f,
quotechar='"',
quoting=csv.QUOTE_ALL,
lineterminator="\n")
if channelid == 'channels' and current_user.is_authenticated:
writer.writerow([
'Channel Id', 'Channel name', 'Published at', 'subscriberCount',
'subscriberChange', 'viewCount', 'viewChange', 'videoCount',
'videoChange', 'commentCount', 'commentChange'
])
downloadFileName = 'channels' + '-' + datetime.datetime.now().strftime(
'%y%m%d') + '.csv'
for channel in query_db(
'select user_channel.channelid,channel.title,channel.publish_at,channel.subscriberCount,channel.viewCount,channel.videoCount,channel.commentCount,channel.subscriberChange,channel.viewChange,channel.videoChange,channel.commentChange from user_channel left outer join channel on user_channel.channelid = channel.channelid where user_channel.userid = ?',
[current_user.id]):
writer.writerow([
channel['channelid'], channel['title'], channel['publish_at'],
channel['subscriberCount'], channel['subscriberChange'],
channel['viewCount'], channel['viewChange'],
channel['videoCount'], channel['videoChange'],
channel['commentCount'], channel['commentChange']
])
else:
newdata = query_db('select * from channel where channelid = ?',
(channelid, ), True)
if newdata:
downloadFileName = newdata['title'] + '-' + datetime.datetime.now(
).strftime('%y%m%d') + '.csv'
else:
downloadFileName = channelid + '-' + datetime.datetime.now(
).strftime('%y%m%d') + '.csv'
if youtubechecker.check_channelid(channelid):
writer.writerow([
'Date', 'subscriberCount', 'subscriberChange', 'viewCount',
'viewChange', 'videoCount', 'videoChange', 'commentCount',
'commentChange'
])
for channel in query_db(
'select * from channel_history where channelid = ?',
[channelid]):
writer.writerow([
channel['date'], channel['subscriberCount'],
channel['subscriberChange'], channel['viewCount'],
channel['viewChange'], channel['videoCount'],
channel['videoChange'], channel['commentCount'],
channel['commentChange']
])
else:
return 'err: channel ID'
res = make_response()
res.data = f.getvalue()
res.headers['Content-Type'] = 'text/csv'
res.headers['Content-Disposition'] = functions.rfc5987_content_disposition(
downloadFileName) #'attachment; filename='+ downloadFileName
return res
def cashDep(username,password):
account_number, balance, __ = db.query_db(db.display_customer_data_query,username,password)[0]
amt = int(input('Enter amount to deposit: '))
final_amt = int(balance) + amt
db.query_db(db.transfer_to_query,amt,account_number)
db.logger.debug('Deposit successful for : {}'.format(account_number))
print('Deposit of',amt,'Successful! Available balance is:',final_amt)
return True
def create_reply(post_id, text, img, user, ip):
sql_string = """insert into reply(reply_post_id, user, date, reply, img_filename, img_uid, ip)
values (?, ?, strftime('%Y-%m-%d %H:%M', 'now', 'localtime'), ?, ?, ?, ?);"""
filename, uid = get_filename_uid_from_img(img)
text = make_none(text)
params = [post_id, user, text, filename, uid, ip]
query_db(sql_string, params)
def main():
cur_page = request.args.get("page") if request.args.get("page") else '0'
order = request.args.get("order") if request.args.get("order") else '0'
num = 30 # number of list
num_range = 2 # number of around current pager item
if not (re.match('^[0-9]+$', cur_page) and re.match('^[0-9]+$', order)):
return 'page or order error'
order_query = {}
numdata = query_db('select count(*) from channel', order_query, True)
num_all = int(numdata[0]) # all amount of data
orderstr = settings.channel_order[int(order)]['val']
order_query.update({'start': int(cur_page) * num, 'num': num})
newdata = query_db(
'select * from channel order by {} limit :start, :num'.format(
orderstr), order_query)
page_max = int(num_all / num) # max page number ( 0 start)
pager_list = [0]
#pager_start = 0 if int(cur_page) - 2 - (0 if int(cur_page) + 2 <= page_max else page_max - int(cur_page) + 2) < 0 else int(cur_page) - 2 - (0 if int(cur_page) + 2 <= page_max else page_max - int(cur_page) + 2)
pager_start = max(
num_range - 1,
int(cur_page) - num_range -
max(int(cur_page) + num_range - page_max, 0))
#pager_end = page_max if int(cur_page) + 2 + abs(0 if int(cur_page) - 2 > 0 else int(cur_page) - 2) > page_max else int(cur_page) + 2 + abs(0 if int(cur_page) - 2 > 0 else int(cur_page) - 2)
pager_end = min(
page_max - 1,
int(cur_page) + num_range + abs(min(0,
int(cur_page) - num_range)))
pager_list.extend(list(range(pager_start, pager_end + 1)))
pager_list.append(page_max)
pager_list = list(dict.fromkeys(pager_list))
#for item in newdata:
pager = {
'page':
int(cur_page),
'num':
num,
'page_min':
0,
'page_max':
page_max,
'page_min_ommit':
True if len(pager_list) > 1 and pager_list[1] != 1 else False,
'page_max_ommit':
True if len(pager_list) > 1
and pager_list[len(pager_list) - 2] != page_max - 1 else False,
'num_all':
num_all,
'list':
pager_list
}
order = {'order': int(order), 'list': settings.channel_order}
return render_template('main.html',
title='Channels',
data=newdata,
pager=pager,
order=order)
def send_notify_from_user(user_id, notify_token, category):
if 'channel' in category:
result = list()
for channel in query_db(
'select user_channel.channelid,channel.title,channel.publish_at,channel.subscriberCount,channel.viewCount,channel.videoCount,channel.commentCount,channel.subscriberChange,channel.viewChange,channel.videoChange,channel.commentChange from user_channel left outer join channel on user_channel.channelid = channel.channelid where user_channel.userid = ?',
[user_id]):
result.append(channel['title'] + ' 🔔: ' + functions.human_format(
int(0 if channel['subscriberCount'] is None else
channel['subscriberCount'])) + ' (' +
str(change_format(channel['subscriberChange'])) +
')' + ' 👀: ' +
functions.human_format(int(channel['viewCount'])) +
' (' + str(change_format(channel['viewChange'])) +
')' + ' 🎞: ' +
functions.human_format(int(channel['videoCount'])) +
' (' + str(change_format(channel['videoChange'])) +
')')
n = 10
for split_result in [
result[idx:idx + n] for idx in range(0, len(result), n)
]: # リストを10ずつ分割（長すぎると切れるため）
send_line_notify(
'\n' + '\n'.join(split_result),
notify_token) # 結果が入ったリストを改行で結合させて，LINE Notifyで結果をLINEに送信する
if 'video' in category:
video_result = list()
for video in query_db(
'select user_video.videoid,video.title,video.publish_at,video.duration,video.viewCount,video.likeCount,video.dislikeCount,video.commentCount,video.viewChange,video.likeChange,video.dislikeChange,video.commentChange from user_video left outer join video on user_video.videoid = video.videoid where user_video.userid = ?',
[user_id]):
video_result.append(video['title'][:15] + ' 👀: ' +
functions.human_format(
int(0 if video['viewCount'] is None else
video['viewCount'])) + ' (' +
str(change_format(video['viewChange'])) + ')' +
' 👍: ' + functions.human_format(
int(0 if video['likeCount'] is None else
video['likeCount'])) + ' (' +
str(change_format(video['likeChange'])) + ')' +
' 👎: ' + functions.human_format(
int(0 if video['dislikeCount'] is None else
video['dislikeCount'])) + ' (' +
str(change_format(video['dislikeChange'])) +
')' + ' 💬: ' + functions.human_format(
int(0 if video['commentCount'] is None else
video['commentCount'])) + ' (' +
str(change_format(video['commentChange'])) +
')')
n = 10
for split_result in [
video_result[idx:idx + n]
for idx in range(0, len(video_result), n)
]: # リストを10ずつ分割（長すぎると切れるため）
send_line_notify(
'\n' + '\n'.join(split_result),
notify_token) # 結果が入ったリストを改行で結合させて，LINE Notifyで結果をLINEに送信する
def test_remove_duplicate_movies(self):
with flask_app.test_request_context():
sql = 'select count(_id) as fcount from movies'
expected_count = 1
for i in range(2):
execute_sql('insert into movies(name, imdb_id) values(?, ?)', ['duplicate_movie_name_' + str(i), 'tt9999999'])
count = query_db(sql, one=True)['fcount']
MovieManager().remove_duplicate_movies()
actual_count = count - query_db(sql, one=True)['fcount']
assert actual_count == expected_count, 'actual: {0}, expected: {1}'.format(actual_count, expected_count)
print('OK - test_remove_duplicate_movies')
def archives(keyword):
"""
展示Archives部分的内容
:param keyword:
:return:
"""
select_sql = "SELECT * FROM posts WHERE ceated_at > '"+keyword+"-00-00 00:00:00 ' ORDER BY id DESC"
rows = db.query_db(select_sql)
select_sql = "select DISTINCT tag_name from posts_tags"
tag = db.query_db(select_sql)
return render_template('tags.html', keyword=keyword,tag=tag, rows=rows)
def cashWdr(username, password):
account_number, balance, __ = db.query_db(db.display_customer_data_query,
username, password)[0]
amt = int(input('Enter amount to withdraw: '))
if amt <= int(balance):
final_amt = int(balance) - amt
db.query_db(db.transfer_from_query, amt, account_number)
print('Withdrawal successful! available balance is:', final_amt)
return True
else:
print('Insufficient account balance.')
return False
def push_event(ip, event=None):
if event == None:
event = get_event(ip)
if event:
assert ip == event.ip
sql_string = """update event
set last_event_date = ?
where ip = ?;"""
query_db(sql_string, [time(), ip])
else:
sql_string = """insert into event(ip) values (?);"""
query_db(sql_string, [ip], one=True)
def post(post_path):
"""
展示文章
:param post_path:
:return:
"""
select_sql = "select * from posts where url ='"+post_path+"'"
content = db.query_db(select_sql, one=True)
select_sql = "select DISTINCT tag_name from posts_tags"
tag = db.query_db(select_sql)
return render_template('post.html', post_path=content[4], tag=tag)
def channelvideos(channelid):
newdata = query_db('select * from channel where channelid = ?',
(channelid, ), True)
result = query_db(
'SELECT json_extract(video, "$") as video FROM channel_video WHERE channelid = ? ',
[channelid], True)
videos = list()
if result:
result = None if result['video'] is None else json.loads(
result['video'])
for video in result:
detail = video.get('detail')
snippet = detail.get('snippet') if detail != None else None
contentDetails = detail.get(
'contentDetails') if detail != None else None
statistics = detail.get('statistics') if detail != None else None
title = snippet.get('title') if snippet != None else ''
duration = functions.duration_format(
contentDetails.get('duration').replace('PT', '').replace(
'H', ':').replace('M', ':').replace(
'S', '')) if contentDetails != None else ''
viewCount = statistics.get(
'viewCount') if statistics != None else ''
likeCount = statistics.get(
'likeCount') if statistics != None else ''
dislikeCount = statistics.get(
'dislikeCount') if statistics != None else ''
commentCount = statistics.get(
'commentCount') if statistics != None else ''
publishedAt = snippet.get('publishedAt').replace('Z', '').replace(
'T', ' ') if snippet != None else ''
videos.append({
'videoid': video.get('videoId'),
'title': title,
'duration': duration,
'viewCount': viewCount,
'likeCount': likeCount,
'dislikeCount': dislikeCount,
'commentCount': commentCount,
'publishedAt': publishedAt
})
return render_template('channelvideos.html',
title='Videos for ' + newdata['title'],
data=newdata,
videos=videos)
def get_posts(since):
sql_string = """
select * from post where date >= date(?) order by post_id desc;
"""
rows = query_db(sql_string, args=[since])
posts = [make_post_lite(r) for r in rows] if rows else None
return posts
def get_replies(since):
sql_string = """
select * from reply where date >= date(?) order by reply_id desc;
"""
rows = query_db(sql_string, args=[since])
replies = make_replies(rows) if rows else None
return replies
def get_feedbacks(since):
sql_string = """
select * from feedback where date >= date(?) order by feedback_id desc;
"""
rows = query_db(sql_string, args=[since])
feedbacks = [make_feedback(row) for row in rows] if rows else None
return feedbacks
def post(self):
data = parser_beers.parse_args()
styles = ','.join(
map(lambda str: f"'{str}'", json.loads(data['style'])))
query = f'select * from data_beers2 where data_beers2.country=? and abv between ? and ? and style in ({styles})order by average desc limit 10'
req = query_db(query, args=(data['country'], data['min'], data['max']))
return req
def post(self):
data = parser_beers_volume.parse_args()
styles = ','.join(
map(lambda str: f"'{str}'", json.loads(data['style'])))
query = f'''select country,sum(sumScore)/sum(nbBeer) as average from data_beers2 where abv between ? and ? and style in ({styles}) and country!="undefined" group by country order by average desc'''
req = query_db(query, (data['min'], data['max']))
return req
def get_reports(since):
sql_string = """
select * from report where date >= date(?) order by report_id desc;
"""
rows = query_db(sql_string, args=[since])
reports = [make_report(row) for row in rows] if rows else None
return reports
def create_reply_to_reply(post_id, parent_reply_id, text, img, user, ip):
"""
Replies and replies to replies go in the same SQLite table.
reply_id parent_reply_id text
1 null drawing is fun!
2 1 what did you draw?
3 2 a pink elephant.
4 1 drawing takes too much patience.
"""
sql_string = """insert into reply(reply_post_id, parent_reply_id, user, date, reply, img_filename, img_uid, ip)
values (?, ?, ?, strftime('%Y-%m-%d %H:%M', 'now', 'localtime'), ?, ?, ?, ?);"""
filename, uid = get_filename_uid_from_img(img)
text = make_none(text)
params = [post_id, parent_reply_id, user, text, filename, uid, ip]
query_db(sql_string, params)
def pushtime():
hour = "09"
min = "15"
if request.method == "POST":
hour = request.form["hour"]
min = request.form["min"]
if re.match('^[0-9]{4}$', hour + min):
db = get_db()
db.execute(
"UPDATE user set push_time = ? WHERE id = ?",
(hour + min, current_user.id),
)
db.commit()
flash('Your notify time was updated!', 'alert-success')
if session.get("newregister") == 1:
return redirect(url_for("gonotify"))
#else:
# return redirect(url_for("account"))
else:
return 'err: invalid value'
userdata = query_db('select push_time from user where id = ?',
(current_user.id, ), True)
if userdata:
hour = userdata['push_time'][:2]
min = userdata['push_time'][2:]
newdata = {'title': 'Notify time', 'hour': hour, 'min': min}
return render_template('pushtime.html',
title=newdata['title'],
data=newdata)
def get_board_names():
sql_string = """
select board_name
from board;
"""
rows = query_db(sql_string)
return set(row['board_name'] for row in rows) if rows else None
def login():
username = request.form['username']
password = request.form['password']
action = request.form['action']
if action == 'Login':
user = check_password(username, password)
if user is not None:
session['session_id'] = user['id']
session['user_id'] = user['id']
session['logged_in'] = True
session['username'] = user['username']
session['is_admin'] = (user.get('group_id') == app.config['ADMIN_GROUP_ID'])
flash('Welcome ' + user['username'], 'flash')
else:
flash('Login failed.', 'error')
elif action =='Register':
username = request.form['username']
password = request.form['password']
check = query_db('SELECT * FROM users WHERE username = ?', [username], one=True)
if check is None:
create_user(username, password)
flash('You successfully registered as ' + username, 'flash')
else:
flash('Your username, ' + username + ', is already taken.', 'error')
return redirect(url_for('home'))
def get_all():
result = query_db('SELECT * from Posts ORDER BY timestamp DESC')
posts = []
for r in result:
posts.append(Post(r['author_id'], r['content'], r[
'attachment_name'], r['timestamp']))
return posts
def login():
if request.method == 'POST':
# Get Form Fields
username = request.form['username']
password_candidate = request.form['password']
result = query_db("SELECT * FROM users WHERE username = ?",
(username, ),
one=True)
if result:
# Get stored hash
password = result['password']
soznr = result['SozNr']
# Compare Passwords
if pbkdf2_sha256.verify(password_candidate, password):
# Passed
session['logged_in'] = True
session['username'] = username
session['soznr'] = soznr
flash('You are now logged in', 'success')
return redirect(url_for('index'))
else:
error = 'Invalid login'
return render_template('login.html', error=error)
else:
error = 'Username not found'
return render_template('login.html', error=error)
return render_template('login.html')
def getFileNames():
retnames = []
deeb_data = query_db("select fname from audiofiles")
for dat in deeb_data:
retnames.append(dat["fname"])
return jsonify(names=retnames)
def get_posts_by_user_id(user_id):
result = query_db('SELECT * from Posts WHERE author_id = ?', [user_id])
posts = []
for r in result:
posts.append(Post(r['author_id'], r['content'], r[
'attachment_name'], r['timestamp']))
return posts
def get_itinerary(start, destination):
# We check if the starting and ending point begins by B1 or B3
if not start.startswith('B3') and not start.startswith('B1'):
raise ValueError(
f'Start name should start with either B1 or B3. Got {start}')
if not destination.startswith('B3') and not destination.startswith('B1'):
raise ValueError(
f'Start name should start with either B1 or B3. Got {destination}')
# We get all the rooms saved in the database
rooms = query_db("SELECT name, linked_to FROM points")
graph = Graph()
for room in rooms:
# We convert each room into a dictionary and we get the current room name
room = dict(room)
room_name = room.get("name", "")
if room_name:
# For each room connected to our current room
for room_connected_and_distance in room.get("linked_to",
"").split("|"):
# On the connected room we get the name of the room and the distance
room_connected, distance = room_connected_and_distance.split(
";")
# We add the connection in our graph
graph.add_edge(room_name, room_connected, int(distance))
# We generate our dijkstra and we search ou path
dijkstra = DijkstraSPF(graph, start)
itinerary_list = dijkstra.get_path(destination)
return itinerary_list
def new_post():
if request.method == "POST":
mar = request.form.get("markdown") # 编辑元吗
html = request.form.get("html") # HTML 源码
title = request.form.get("title") # 标题
tag = request.form.get("tag") # 标签
tag_list = tag.split('，')
user_id = session['user']
# user_id = int(user)
# 存储 标题信息
sql = "INSERT INTO posts(`url`, `title`, `content_text`, `content_html`,user_id) VALUES \
('%s','%s','%s','%s','%d')" % (title, title, mar, html, user_id)
db.update_db(sql)
# 存储标签内容
title_selectsql = "SELECT * FROM posts WHERE title='" + title + "'"
rows = db.query_db(title_selectsql, one=True)
for i in tag_list:
post_tag_sql = "INSERT INTO posts_tags(post_id,tag_name) value('%d','%s')" % (
rows[0], i)
db.update_db(post_tag_sql)
return mar + "---" + html + "---" + title + "---" + str(
tag_list) + 'l:' + str(rows[0])
# return "错误了"
return render_template('new.html')
def get_latest_posts(amount):
result = query_db('SELECT * from Posts LIMIT ?', (amount,))
posts = []
for r in result:
posts.append(Post(r['author_id'], r['content'], r[
'attachment_name'], r['timestamp']))
return posts
def has_more_results(self, keywords, page_size=DEFAULT_PAGE_SIZE, page_number=0):
sql = u"select _id from movies where is_active = 'true' limit 1 offset {0}".format((page_number + 1)*page_size)
args = []
if keywords:
sql = u"select _id from movies where is_active = 'true' and name like ? limit 1 offset {0}".format((page_number + 1)*page_size)
args=['%' + keywords + '%']
result = query_db(query=sql, args=args, one=True)
return True if result else False
def get_all_movies(self, page_size=DEFAULT_PAGE_SIZE, page_number=0, include_not_active=False):
if page_size <= 0:
page_size = DEFAULT_PAGE_SIZE
if page_number < 0:
page_number = 0
sql = u"select * from movies" + (" where is_active = 'true' " if not include_not_active else '') + " order by ratings desc limit {0} offset {1}".format(page_size, page_number*page_size)
result = query_db(sql)
return self.wrap_results(result, page_number, page_size, '')
def get_linked_points_of_point(point):
if not point.startswith('B3') and not point.startswith('B1'):
raise ValueError(
f'Point name should start with either B1 or B3. Got {point}')
return [
list(row)[0] for row in query_db(
f"SELECT name FROM points WHERE linked_to LIKE '%{point}%'")
]
def user_timeline(username):
"""Display's a users tweets."""
profile_user = query_db('select * from user where username = ?',
[username], one=True)
if profile_user is None:
abort(404)
followed = False
if g.user:
followed = query_db('''select 1 from follower where
follower.who_id = ? and follower.whom_id = ?''',
[session['user_id'], profile_user['user_id']],
one=True) is not None
return render_template('timeline.html', messages=query_db('''
select message.*, user.* from message, user where
user.user_id = message.author_id and user.user_id = ?
order by message.pub_date desc limit ?''',
[profile_user['user_id'], PER_PAGE]), followed=followed,
profile_user=profile_user)
def search(self, keywords, page_size=DEFAULT_PAGE_SIZE, page_number=0):
if page_size <= 0:
page_size = DEFAULT_PAGE_SIZE
if page_number < 0:
page_number = 0
result = []
sql = u"select * from movies where is_active = 'true' and name like ? or also_known_as like ? or plot_keywords like ? or tags like ? order by ratings desc limit {0} offset {1}".format(page_size, page_number*page_size)
if keywords:
result = query_db(sql, ['%' + keywords + '%', '%' + keywords + '%', '%' + keywords + '%', '%' + keywords + '%'])
return self.wrap_results(result, page_number, page_size, keywords)
def explore():
curUser = ""
if current_user.is_authenticated():
curUser = db.curUsername(current_user.get_id())
nodes = db.query_db("select * from nodes",[],one=False)
#cur = g.db.execute('select * from nodes order by id')
#nodes = [dict(name=row[0]) for row in cur.fetchall()]
return render_template('explore.html', nodes=json.dumps(nodes), curUser= curUser)
def score_answer(question_id, answer, test_id, score_table='scores', print_only=False):
# get in memory map of all questions and points for displaying results
questions = db.query_db('select * from questions')
questions_map = dict()
for question in questions:
questions_map[question['id']] = (question['question'], question['points'])
# get all keyword sets
rows = db.query_db('select * from keywords where question_id = ?', [question_id])
answered_words = preprocess_words(answer)
# evaluate answered words against each set of answer keywords
points = 0
criteria_id = ''
for row in rows:
keywords = preprocess_words(row['ordered_keywords'])
tmp_criteria_id = choose_criteria(keywords, answered_words)
tmp_points = choose_score(question_id, tmp_criteria_id)
# keep only the highest point value and corresponding criteria id
if tmp_points > points:
points = tmp_points
criteria_id = tmp_criteria_id
# save score to database
fields = ('id', 'test_id', 'question_id', 'answered', 'score', 'criteria_id')
values = (str(uuid.uuid4())[:5], test_id, question_id, answer, points, criteria_id)
if not print_only:
db.insert(score_table, fields=fields, values=values)
else:
print(points)
# create score dict with added fields for display purposes and append to test scores list
score_dict = dict()
for idx, key in enumerate(fields):
score_dict[key] = values[idx]
if key == 'question_id':
score_dict['question'], score_dict['points_possible'] = questions_map[question_id]
return score_dict
def search_execute():
q = query_db('SELECT thread FROM posts WHERE message LIKE ? ORDER BY time', ['%' + request.form['query'] + '%'])
unique_threads = list()
for p in q:
if p['thread'] in unique_threads:
continue
else:
unique_threads.append(p['thread'])
query = 'SELECT threads.id AS id, users.username AS author, threads.author AS user_id, \
threads.title AS title FROM threads, users WHERE threads.id = ? AND users.id == threads.author'
thread_query = list()
if len(unique_threads) > 0:
for x in unique_threads:
thread_query.append(query_db(query, [x], one=True))
else:
return jsonify(good=False)
boilerplate = "<tr class=search_result id=thread_%d><td>%s</td><td>%s</td></tr>"
threads = ""
for t in thread_query:
threads += (boilerplate % (t['id'], t['title'], t['author']))
return jsonify(good=True,threads=threads)
def timeline():
"""Shows a users timeline or if no user is logged in it will
redirect to the public timeline. This timeline shows the user's
messages as well as all the messages of followed users.
"""
if not g.user:
return redirect(url_for('public_timeline'))
return render_template('timeline.html', messages=query_db('''
select message.*, user.* from message, user
where message.author_id = user.user_id and (
user.user_id = ? or
user.user_id in (select whom_id from follower
where who_id = ?))
order by message.pub_date desc limit ?''',
[session['user_id'], session['user_id'], PER_PAGE]))
def login():
"""Logs the user in."""
if g.user:
return redirect(url_for('timeline'))
error = None
if request.method == 'POST':
user = query_db('''select * from user where
username = ?''', [request.form['username']], one=True)
if user is None:
error = 'Invalid username'
elif not check_password_hash(user['pw_hash'],
request.form['password']):
error = 'Invalid password'
else:
flash('You were logged in')
session['user_id'] = user['user_id']
return redirect(url_for('timeline'))
return render_template('login.html', error=error)
def choose_score(question_id, criteria_id):
score = 0
row = db.query_db('select * from questions where id = ?',
[question_id],
one=True)
possible = row['points']
level_max = 3
level = 0
if criteria_id == 'criteria-1':
level = 3
elif criteria_id == 'criteria-2':
level = 2
elif criteria_id == 'criteria-3':
level = 1
elif criteria_id == 'criteria-4':
level = 0
scale = level/level_max
score = scale * possible
return int(math.ceil(score))
def querylastdata(jianqu):
sql = 'select * from jianqu where createtime = (select max(createtime) from jianqu where jianqu = \'%s\' )' % jianqu
jianqu = db.query_db(sql)
return jianqu
def view_thread(thread_id):
query = query_db('SELECT users.username AS author, users.signature AS signature, posts.author AS user_id, posts.first_post AS first_post, posts.id AS id, posts.message AS message, datetime(posts.time, "unixepoch") AS time FROM posts, users WHERE thread = ? AND users.id = posts.author ORDER BY time', [thread_id])
topic = query_db('SELECT title,forum FROM threads WHERE id = ?', [thread_id], one=True)
forum = query_db('SELECT name FROM forums WHERE id = ?', [topic['forum']], one=True)
return render_template('view_thread.html', thread_id=thread_id, posts=query, thread_topic=topic['title'], forum_id=topic['forum'], forum_name=forum['name'])
def queryall():
sql = 'select * from kanshou'
sql2 = 'select * from jianqu'
kanshou = db.query_db(sql)
jianqu = db.query_db(sql2)
return kanshou,jianqu
def querylastks():
sql = 'select * from kanshou where createtime = (select max(createtime) from kanshou)'
kanshou = db.query_db(sql)
return kanshou
def delete_all():
sql = 'truncate table jianqu'
if db.query_db(sql):
return True
else:
return False
def public_timeline():
"""Displays the latest messages of all users."""
return render_template('timeline.html', messages=query_db('''
select message.*, user.* from message, user
where message.author_id = user.user_id
order by message.pub_date desc limit ?''', [PER_PAGE]))
def new_post(thread_id):
if request.method == 'POST':
forum_id = query_db('SELECT forum FROM threads WHERE id = ?', [thread_id], one=True)['forum']
insert_post(forum_id, thread_id, request.form['message'], session['user_id'])
return redirect(url_for('view_thread', thread_id=thread_id))
return render_template('new_post.html', thread_id=thread_id)
def delete_thread(thread_id):
q = query_db('SELECT author FROM threads WHERE id = ?', [thread_id], one=True)
pred = (q['author'] == session['user_id'])
if pred:
drop_thread(thread_id)
return jsonify(delete=pred)
def update_profile():
error = ''
flash_msg = ''
cur_pass = request.args.get('cur_pass')
if cur_pass is None:
pass
elif cur_pass == '':
error = 'You must always provide your current password.'
else:
q = check_password(session['username'], cur_pass)
if q is None:
error = 'Current password was incorrect. No operation was executed.'
else:
set_query = ''
query_params = list()
new_pass1 = request.args.get('new_pass1')
new_pass2 = request.args.get('new_pass2')
old_signature = q['signature']
new_signature = request.args.get('signature')
coins_buy = request.args.get('buycoins')
coins_sell = request.args.get('sellcoins')
coins_trade_amt = request.args.get('tradecoins_amt')
coins_trade_dest= request.args.get('tradecoins_dest')
coins = start_coins = q['coins']
if new_pass1 != '':
if new_pass1 != new_pass2:
error += 'New passwords do not match. Password was not changed.<br>'
else:
set_query += 'password = ?, '
query_params.append(new_pass1)
flash_msg += 'Password changed.<br>'
if old_signature != new_signature:
set_query += 'signature = ?, '
query_params.append(new_signature)
flash_msg += 'Signature changed.<br>'
if coins_buy:
if coins_buy.isdigit():
coins += int(coins_buy)
flash_msg += 'Purchased %s coins.<br>' % coins_buy
else:
error += 'Could not buy coins. The value given was invalid.<br>'
if coins_sell:
if coins_sell.isdigit():
coins -= int(coins_sell)
flash_msg += 'Sold %s coins.<br>' % coins_sell
else:
error += 'Could not sell coins. The value given was invalid.<br>'
if coins_trade_dest:
trade_dest = query_db('SELECT id FROM users WHERE username = ?', [coins_trade_dest], one=True)
if trade_dest is not None:
if coins_trade_amt.isdigit():
coins -= int(coins_trade_amt)
query_db('UPDATE users SET coins = ? WHERE id = ?', [coins_trade_amt, trade_dest['id']])
flash_msg += 'Transferred %s coins to %s.<br>' % [coins_trade_amt, coins_trade_dest]
else:
error += 'Could not trade coins. The value given was invalid.<br>'
else:
error += 'Could not trade coins. The recipient does not exist.<br>'
set_query += 'coins = ? '
if coins < 0:
query_params.append(start_coins)
error += 'Unable to process transaction. You do not have enough coins.<br>'
else:
query_params.append(coins)
query_params.append(session['user_id'])
if set_query != '':
query_db('UPDATE users SET ' + set_query + 'WHERE id = ?', query_params)
if error != '':
flash(Markup(error), 'error')
if flash_msg != '':
flash(Markup(flash_msg), 'flash')
coins = query_db('SELECT coins FROM users WHERE id = ?', [session['user_id']], one=True)['coins']
return render_template('update_profile.html', coins=coins)
def delete_post(post_id):
q = query_db('SELECT author, first_post FROM posts WHERE id = ?', [post_id], one=True)
pred = (q['author'] == session['user_id']) and not q['first_post']
if pred:
drop_post(post_id)
return jsonify(delete=pred)
def home():
forums = None
if session.get('logged_in'):
forums = query_db('SELECT * FROM forums')
return render_template('home.html', forums=forums)
def load_user(id):
g.db = db.connect_db() # Since this isn't a request we need to connect first.
user = db.query_db('select * from users where id = ?', [id], one=True)
g.db.close()
return User(user['id'], user['username'], user['password'], user['name'], user['city'])
def view_forum(forum_id):
threads = query_db('SELECT threads.id AS id, threads.title AS name, threads.post_count AS post_count, users.username AS author, threads.author AS user_id FROM threads, users WHERE threads.author = users.id AND forum = ' + str(forum_id))
forum_name = query_db('SELECT name FROM forums WHERE id = ' + str(forum_id), one=True)
return render_template('view_forum.html', threads=threads, forum_id=forum_id, forum_name=forum_name['name'])
def get_total_count(self):
sql = u"select count(_id) as mcount from movies where is_active = 'true'"
result = query_db(query=sql, one=True)
return int(result['mcount']) if result else 0