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()