def searchMusic(): error = None name = str(request.args["musicName"]) if not name: error = "Music name is required." if error is not None: flash(error) else: page_index = 1 page_max_size = 20 page_size = 20 if "page" in request.args: page_index = int(request.args["page"]) if "size" in request.args: page_max_size = int(request.args["size"]) cur = get_db().cursor().execute( "select count(*) as total_items from music where music_name like \'%" + name + "%\'").fetchone() total_items = int(cur["total_items"]) total_pages = int(total_items / page_max_size) if total_items % page_max_size > 0: total_pages += 1 if page_index == total_pages: page_size = total_items % page_max_size if total_items == 0: page_index = 0 page_size = 0 cur = (get_db().cursor().execute( "select id,created,music_name,artist_id,(case when id in (select music_id from practice where player_id=" + str(session['user_id']) + ") then 1 else 0 end) as played from music where music_name like \'%" + name + "%\' limit ? offset ?", (page_size, (page_index - 1) * page_max_size))) my_query = [ dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall() ] res = { 'pageIndex': page_index, 'pageSize': page_size, 'totalItems': total_items, 'totalPages': total_pages, 'items': my_query } return json.dumps(res, cls=encoder, ensure_ascii=False)
def search_artist(): error = None name = request.args["artistName"] if not id: error = "Artist name is required." if error is not None: flash(error) else: page_index = 1 page_max_size = 20 page_size = 20 if "page" in request.args: page_index = int(request.args["page"]) if "size" in request.args: page_max_size = int(request.args["size"]) cur = get_db().cursor().execute( "select count(*) as total_items from artist where artist_name like \'%" + name + "%\'").fetchone() total_items = int(cur["total_items"]) total_pages = int(total_items / page_max_size) if total_items % page_max_size > 0: total_pages += 1 if page_index == total_pages: page_size = total_items % page_max_size if total_items == 0: page_index = 0 page_size = 0 cur = (get_db().cursor().execute( "select id, artist_name from artist where artist_name like \'%" + name + "%\' limit ? offset ?", (page_size, (page_index - 1) * page_max_size))) my_query = [ dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall() ] res = { 'pageIndex': page_index, 'pageSize': page_size, 'totalItems': total_items, 'totalPages': total_pages, 'items': my_query } return json.dumps(res, ensure_ascii=False, cls=encoder)
def add_favourite(): error = None if request.method == "POST": jsonData = request.get_json() music_id = jsonData["music_id"] user_id = session['user_id'] else: # for test music_id = 6 user_id = 1 if not music_id: error = "Music ID is required." if not user_id: error = "User ID is required." if error is not None: flash(error) else: db = get_db() db.execute( "INSERT INTO favourite (user_id, music_id) VALUES (? ,?)", (user_id, music_id), ) db.commit() return "Add success!"
def add_practice(): """Mark a music piece to have been played""" error = None if request.method == "POST": jsonData = request.get_json() music_id = jsonData["music_id"] player_id = session['user_id'] score = jsonData["score"] content = jsonData["content"] else: # for test music_id = 6 player_id = 1 score = 0 content = "miao" if not music_id: error = "Music ID is required." if error is not None: flash(error) else: if score is None: score = -1 if content is None: content = "" db = get_db() db.execute( "INSERT INTO practice (music_id, player_id, score, content) VALUES (?, ?, ? ,?)", (music_id, player_id, score, content), ) db.commit() return "Add success!"
def show(): page_index = 1 page_max_size = 20 page_size = 20 if "page" in request.args: page_index = int(request.args["page"]) if "size" in request.args: page_max_size = int(request.args["size"]) cur = get_db().cursor().execute( "select count(*) as total_items from music,practice\ where music.id=practice.music_id \ and practice.player_id=" + str(session['user_id'])).fetchone() total_items = int(cur["total_items"]) total_pages = int(total_items / page_max_size) if total_items % page_max_size > 0: total_pages += 1 if page_index == total_pages: page_size = total_items % page_max_size if total_items == 0: page_index = 0 page_size = 0 cur = (get_db().cursor().execute( "select music.id,music.created,music.music_name,music.artist_id,practice.content,practice.score\ from music,practice\ where music.id=practice.music_id \ and practice.player_id=" + str(session['user_id']) + " limit ? offset ? ", (page_size, (page_index - 1) * page_max_size))) my_query = [ dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall() ] res = { 'pageIndex': page_index, 'pageSize': page_size, 'totalItems': total_items, 'totalPages': total_pages, 'items': my_query } return json.dumps(res, cls=encoder, ensure_ascii=False)
def delete_artist(): if request.method == "POST": jsonData = request.get_json() artist_name = jsonData["artist_name"] else: # just for test artist_name = 'Mozart' db = get_db() db.execute("DELETE FROM artist WHERE artist_name = ?", (artist_name, )) db.commit() return redirect(url_for("hello")) # TODO change the url
def status(): db = get_db() error = None if session['user_id'] is None: error = "Not log in" if error is None: user = db.execute('SELECT * FROM user WHERE id = ?', (session['user_id'], )).fetchone() res = {'username': user['username'], 'nickname': user['nickname']} return json.dumps(res, ensure_ascii=False, cls=encoder) flash(error) return error # TODO change the url
def show_artist(): page_index = 1 page_max_size = 20 page_size = 20 if "page" in request.args: page_index = int(request.args["page"]) if "size" in request.args: page_max_size = int(request.args["size"]) cur = get_db().cursor().execute( "select count(*) as total_items from artist").fetchone() total_items = int(cur["total_items"]) total_pages = int(total_items / page_max_size) if total_items % page_max_size > 0: total_pages += 1 if page_index == total_pages: page_size = total_items % page_max_size if total_items == 0: page_index = 0 page_size = 0 cur = (get_db().cursor().execute( "select id, artist_name from artist limit ? offset ?", (page_size, (page_index - 1) * page_max_size))) my_query = [ dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall() ] res = { 'pageIndex': page_index, 'pageSize': page_size, 'totalItems': total_items, 'totalPages': total_pages, 'items': my_query } return json.dumps(res, ensure_ascii=False, cls=encoder)
def delete(): """Delete a music. """ if request.method == "POST": jsonData = request.get_json() music_name = jsonData["music_name"] else: # just for test music_name = 'See you again' db = get_db() db.execute("DELETE FROM music WHERE music_name = ?", (music_name, )) db.commit() return redirect(url_for("hello")) # TODO change the url
def delete_favourite(): if request.method == "POST": jsonData = request.get_json() music_id = jsonData["music_id"] user_id = session['user_id'] else: # just for test music_id = 6 user_id = 1 db = get_db() db.execute("DELETE FROM favourite WHERE music_id = ? and user_id = ? ", (music_id, user_id)) db.commit() return "Delete success!"
def delete_practice(): """Marked a piece of music as not played""" if request.method == "POST": jsonData = request.get_json() music_id = jsonData["music_id"] player_id = session['user_id'] else: # just for test music_id = 6 player_id = 1 db = get_db() db.execute("DELETE FROM practice WHERE music_id = ? and player_id=? ", (music_id, player_id)) db.commit() return "Delete success!"
def show(): error = None id = request.args.get("id") if not id: error = "Artist ID is required." if error is not None: flash(error) else: cur = (get_db().cursor().execute("select artist_name,introduction\ from artist\ where id=" + str(id))) my_query = [ dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall() ] return json.dumps(my_query, cls=encoder, ensure_ascii=False)
def getAverageScore(): error = None id = request.args.get("musicId") if not id: error = "Music ID is required." if error is not None: flash(error) else: cur = (get_db().cursor().execute("select avg(score) as avg_score\ from practice \ where music_id=" + str(id))) my_query = [ dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall() ] return json.dumps(my_query, cls=encoder, ensure_ascii=False)
def getCommentAndScore(): error = None id = request.args.get("musicId") if not id: error = "Music ID is required." if error is not None: flash(error) else: cur = (get_db().cursor().execute( "select music_id,player_id,nickname,score,content \ from practice,user \ where user.id=practice.player_id and practice.music_id=" + str(id))) my_query = [ dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall() ] return json.dumps(my_query, cls=encoder, ensure_ascii=False)
def login(): db = get_db() if request.method == 'POST': jsonData = request.get_json() username = jsonData['username'] password = jsonData['password'] error = None user = db.execute('SELECT * FROM user WHERE username = ?', (username, )).fetchone() if user is None: error = 'Incorrect username.' elif not user['password'] == password: error = 'Incorrect password.' if error is None: session.clear() session['user_id'] = user['id'] return redirect(url_for('hello')) flash(error) else: # just for test username = '******' password = '******' error = None user = db.execute('SELECT * FROM user WHERE username = ?', (username, )).fetchone() if user is None: error = 'Incorrect username.' elif not user['password'] == password: error = 'Incorrect password.' if error is None: session.clear() session['user_id'] = user['id'] return redirect(url_for('index')) # TODO change the url flash(error) return error # TODO change the url
def register(): db = get_db() if request.method == 'POST': jsonData = request.get_json() username = jsonData['username'] nickname = jsonData['nickname'] password = jsonData['password'] error = None if not username: error = 'Username is required.' elif not password: error = 'Password is required.' elif not nickname: error = 'Nickname is required.' elif db.execute('SELECT id FROM user WHERE username = ?', (username, )).fetchone() is not None: error = 'User {} is already registered.'.format(username) if error is not None: flash(error) else: db.execute( 'INSERT INTO user (username, nickname, password) VALUES (?, ?, ?)', (username, nickname, password)) db.commit() return redirect(url_for('hello')) # TODO change the url else: # just for test username = '******' nickname = 'test' password = '******' db.execute( 'INSERT INTO user (username, nickname, password) VALUES (?, ?, ?)', (username, nickname, password)) db.commit() return "Add success!"
def add(): """Create a new post for the current user.""" if request.method == "POST": jsonData = request.get_json() music_name = jsonData["music_name"] artist_id = jsonData["artist_id"] error = None if not music_name: error = "Music name is required." if error is not None: flash(error) else: cur = (get_db().cursor().execute( "SELECT * FROM music where music_name=?", (music_name, ))) if len(cur.fetchall()) != 0: return "Music named " + music_name + " already exists" else: db = get_db() db.execute( "INSERT INTO music (music_name, artist_id) VALUES (?, ?)", (music_name, artist_id), ) db.commit() return redirect(url_for("hello")) # TODO change the url else: cur = (get_db().cursor().execute( "SELECT * FROM music where music_name='See you again'")) if len(cur.fetchall()) != 0: return "Music named 'See you again' already exists" else: get_db().execute( "INSERT INTO music (music_name) VALUES ('See you again')") get_db().commit() return 'Add a music successfully'
def add_artist(): if request.method == "POST": jsonData = request.get_json() artist_name = jsonData["name"] introduction = jsonData["introduction"] error = None if not artist_name: error = "Artist name is required." if error is not None: flash(error) else: cur = (get_db().cursor().execute( "SELECT * FROM artist where artist_name=?", (artist_name, ))) if len(cur.fetchall()) != 0: return "Artist named " + artist_name + " already exists" else: db = get_db() db.execute( "INSERT INTO artist (artist_name, introduction) VALUES (?, ?)", (artist_name, introduction), ) db.commit() return redirect(url_for("hello")) # TODO change the url else: cur = (get_db().cursor().execute( "SELECT * FROM artist where artist_name='Mozart'")) if len(cur.fetchall()) != 0: return "Artist named 'Mozart' already exists" else: get_db().execute( "INSERT INTO artist (artist_name) VALUES ('Mozart')") get_db().commit() return 'Add a artist successfully' return redirect(url_for("hello")) # TODO change the url