def seeMessages():
id = request.args.get('audio_id')
lock.acquire()
cursor.execute('Select * from comment where audio=%s',id)
lock.release()
result = cursor.fetchall()
return json.dumps(result)
def did_user_like(username: str, art_id: str):
if username is None:
username = ""
cursor.execute("SELECT * FROM likes WHERE username = ? AND artID = ?",
(username, int(art_id)))
connection.commit()
return cursor.fetchone() is not None
def dataLength():
demo_dict = {}
lock.acquire()
cursor.execute("SELECT COUNT(*) FROM wenzhao")
result = cursor.fetchone()
demo_dict["data_len"] = result['COUNT(*)']
lock.release()
return json.dumps(demo_dict)
def dataAudio():
lock.acquire()
cursor.execute("SELECT * FROM wenzhao order by `index` desc limit 10;")
result = cursor.fetchall()
lock.release()
for i in result:
if isinstance(i['upload_date'], (datetime, date)):
i['upload_date'] = i['upload_date'].strftime('%Y-%m-%d')
return json.dumps(result)
def handleCurrentChange():
offset = int(request.args.get('offset'))
cursor.execute("SELECT * FROM wenzhao order by `index` desc limit %s,10",
offset)
result = cursor.fetchall()
for i in result:
if isinstance(i['upload_date'], (datetime, date)):
i['upload_date'] = i['upload_date'].strftime('%Y-%m-%d')
return json.dumps(result)
def comment():
session.pop('login', None)
user = request.form['user']
audio_id = request.form['audio_id']
comments = request.form['comment']
time_stamp = request.form['time_stamp']
lock.acquire()
cursor.execute("Insert into comment (content,users,audio,comment_time) values(%s,%s,%s,%s)",(comments,user,audio_id,time_stamp))
db.commit()
lock.release()
return json.dumps({'status': 'success'})
def store_image(title: str, image: str, username: str) -> str:
cursor.execute(
'''
INSERT INTO arts (
title,
creator,
image
)
VALUES (?, ?, ?)''', (title, username, image))
connection.commit()
return str(cursor.lastrowid)
def searchData():
month = request.args.get("month")
cols = request.args.get("column")
lock.acquire()
sql = "Select * from %s" %cols +" where date_format(upload_date,'%%Y-%%m')=%s"
cursor.execute(sql,[month])
res = cursor.fetchall()
for item in res:
if isinstance(item['upload_date'], (datetime, date)):
item['upload_date'] = item['upload_date'].strftime('%Y-%m-%d')
lock.release()
return json.dumps(res)
def hotAudios():
first_page_data = []
lock.acquire()
cursor.execute("SELECT * FROM jfmt order by `index` desc limit 4")
lock.release()
result1 = cursor.fetchall()
first_page_data = first_page_data + result1
lock.acquire()
cursor.execute("SELECT * FROM today_history order by `index` desc limit 3")
lock.release()
result2 = cursor.fetchall()
first_page_data = first_page_data + result2
lock.acquire()
cursor.execute("SELECT * FROM trump_twitter order by `index` desc limit 2")
lock.release()
result3 = cursor.fetchall()
first_page_data = first_page_data + result3
lock.acquire()
cursor.execute("SELECT * FROM wenzhao order by `index` desc limit 4")
lock.release()
result4 = cursor.fetchall()
first_page_data = first_page_data + result4
for i in first_page_data:
if isinstance(i['upload_date'], (datetime, date)):
i['upload_date'] = i['upload_date'].strftime('%Y-%m-%d')
return json.dumps(first_page_data)
def sign_up():
userName = request.args.get('userName')
password = request.args.get('password')
lock.acquire()
cursor.execute("SELECT * FROM users where username = %s",userName)
lock.release()
result = cursor.fetchall()
md5 = hashlib.md5()
demo_md5 = password.encode(encoding='utf-8')
md5.update(demo_md5)
pass_md5 = md5.hexdigest()
if(len(result) == 0 ):
cursor.execute("Insert into users (username,password) values(%s,%s)",(userName,pass_md5))
db.commit()
sign_up = [{"sign_up": 200}]
else:
sign_up = [{"sign_up": 400}]
return json.dumps(sign_up)
def sign_in():
userName = request.form['userName']
password = request.form['password']
md5 = hashlib.md5()
demo_md5 = password.encode(encoding='utf-8')
md5.update(demo_md5)
pass_md5 = md5.hexdigest()
lock.acquire()
cursor.execute("SELECT * FROM users where username = %s and password = %s",(userName,pass_md5))
lock.release()
result = cursor.fetchall()
if(result):
sign_in = {"sign_in":200}
session['login'] = '******'
session['userName'] = userName
session['pass_md5'] = pass_md5
else:
sign_in = {"sign_in":400}
return json.dumps(sign_in)
def get_all_art(username: str) -> List:
images = []
if username is None:
username = ""
cursor.execute("SELECT id, title, creator, image from arts")
for art in cursor.fetchall():
cursor.execute("SELECT COUNT(*) from comments where artID = ?",
(art[0], ))
num_comments = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM likes where artID = ?",
(art[0], ))
likes = int(cursor.fetchone()[0])
images.append({
"image": art[3],
"likes": likes,
"creator": art[2],
"title": art[1],
"art_id": str(art[0]),
"num_comments": num_comments,
"hasLiked": did_user_like(username, art[0])
})
connection.commit()
return images
def get_image(id: str, username: str) -> Dict:
if username is None:
username = ""
cursor.execute("SELECT title, creator, image FROM arts WHERE id = ?",
(id, ))
data = cursor.fetchone()
if data is None:
return None
cursor.execute("SELECT COUNT(*) FROM likes where artID = ?", (int(id), ))
likes = int(cursor.fetchone()[0])
image = {
"title": data[0],
"creator": data[1],
"image": data[2],
"likes": likes,
"comments": [],
"hasLiked": did_user_like(username, id)
}
cursor.execute("SELECT username, content from comments WHERE artID = ?",
(id, ))
for comment in cursor.fetchall():
image["comments"].append({
"username": comment[0],
"content": comment[1]
})
connection.commit()
return image
def handleCurrentChange():
objects = request.args.get('objects')
offset = int(request.args.get('offset'))
if (objects == 'jfmt'):
cursor.execute("SELECT * FROM jfmt order by `index` desc limit %s,5",
offset)
res = cursor.fetchall()
for i in res:
if isinstance(i['upload_date'], (datetime, date)):
i['upload_date'] = i['upload_date'].strftime('%Y-%m-%d')
return json.dumps(res)
if (objects == 'today_history'):
cursor.execute(
"SELECT * FROM today_history order by `index` desc limit %s,5",
offset)
res = cursor.fetchall()
for i in res:
if isinstance(i['upload_date'], (datetime, date)):
i['upload_date'] = i['upload_date'].strftime('%Y-%m-%d')
return json.dumps(res)
if (objects == 'trump_twitter'):
cursor.execute(
"SELECT * FROM trump_twitter order by `index` desc limit %s,5",
offset)
res = cursor.fetchall()
for i in res:
if isinstance(i['upload_date'], (datetime, date)):
i['upload_date'] = i['upload_date'].strftime('%Y-%m-%d')
return json.dumps(res)
def dataLength():
demo_dict = {}
lock.acquire()
cursor.execute("SELECT COUNT(*) FROM jfmt")
lock.release()
result1 = cursor.fetchone()
demo_dict["data1_len"] = result1['COUNT(*)']
lock.acquire()
cursor.execute("SELECT COUNT(*) FROM today_history")
lock.release()
result2 = cursor.fetchone()
demo_dict["data2_len"] = result2['COUNT(*)']
lock.acquire()
cursor.execute("SELECT COUNT(*) FROM trump_twitter")
lock.release()
result3 = cursor.fetchone()
demo_dict["data3_len"] = result3['COUNT(*)']
lock.acquire()
cursor.execute("SELECT COUNT(*) FROM wenzhao")
lock.release()
result4 = cursor.fetchone()
demo_dict["data4_len"] = result4['COUNT(*)']
return json.dumps(demo_dict)
def pullAudios():
first_page_data = []
lock.acquire()
cursor.execute("Select * FROM jfmt order by `index` desc limit 5")
lock.release()
result1 = cursor.fetchall()
first_page_data.append(result1)
lock.acquire()
cursor.execute("SELECT * FROM today_history order by `index` desc limit 5")
lock.release()
result2 = cursor.fetchall()
first_page_data.append(result2)
lock.acquire()
cursor.execute(
"SELECT * FROM trump_twitter order by `index` desc limit 5;")
lock.release()
result3 = cursor.fetchall()
first_page_data.append(result3)
for i in first_page_data:
for item in i:
if isinstance(item['upload_date'], (datetime, date)):
item['upload_date'] = item['upload_date'].strftime('%Y-%m-%d')
return json.dumps(first_page_data)
def add_comment(art_id: str, content: str, username: str):
cursor.execute(
"INSERT INTO comments (username, content, artID) VALUES (?, ?, ?)",
(username, content, art_id))
connection.commit()
def unlike_artwork(art_id: str, username: str):
cursor.execute("DELETE FROM likes WHERE artID = ? AND username = ?",
(art_id, username))
connection.commit()
def does_username_exist(username: str) -> bool:
cursor.execute("SELECT username FROM users where username = ?",
(username, ))
connection.commit()
return cursor.fetchone() is not None
def create_account(username: str, password: str):
cursor.execute("INSERT INTO users VALUES (?, ?)", (username, password))
connection.commit()
def is_valid_login(username: str, password: str) -> bool:
cursor.execute(
"SELECT username FROM users WHERE username = ? AND password = ?",
(username, password))
connection.commit()
return cursor.fetchone() is not None
def like_artwork(art_id: str, username: str):
cursor.execute("INSERT INTO likes VALUES (?, ?)", (username, art_id))
connection.commit()
