def add_image_db(albumid, filename): db = connect_to_database() cur = db.cursor() # current info cur.execute('SELECT sequencenum, albumid, picid, caption FROM Contain') results = cur.fetchall() current_seqnum = results[-1]['sequencenum'] print('num of photos: %s, latest sequencenum: %s' % (len(results), current_seqnum)) # update Photo Instance m = hashlib.md5((str(albumid) + filename).encode('utf-8')) picid = m.hexdigest() picformat = filename.rsplit('.', 1)[1].lower() picdate = datetime.datetime.fromtimestamp( time.time()).strftime('%Y-%m-%d %H:%M:%S') #picdate="TIMESTAMP '%s'" % st q = 'INSERT INTO Photo (picid, format, date) VALUES ("%s","%s",TIMESTAMP "%s")' % ( picid, picformat, picdate) print('query:%s' % q) cur.execute(q) # update Contain Instance q = 'INSERT INTO Contain (sequencenum, albumid, picid, caption) VALUES (%s,%s,"%s","")' % ( current_seqnum + 1, int(albumid), picid) print('query:%s' % q) cur.execute(q) return ''
def get_picid_lst(albumid): image_names = [ filename for filename in os.listdir(IMG_DIR) if filename.endswith(tuple(ALLOWED_EXTENSIONS)) ] db = connect_to_database() cur = db.cursor() picid_lst = [] q = 'SELECT sequencenum ,picid FROM Contain WHERE albumid="%s"' % albumid cur.execute(q) results = cur.fetchall() for result in results: pic_id_type = [ x for x in image_names if x.split('.')[0] == result['picid'] ] # debug if not pic_id_type[0]: print('ERROR! Cant find image file') picid_lst.append(pic_id_type[0]) #print([result['sequencenum'],pic_id_type[0]]) #print('albumid: %s' % albumid) #print('# of images: %s' % len(results)) return picid_lst
def pic_route(): db = connect_to_database() cur = db.cursor() cur.execute('SELECT * FROM Contain ;') results = cur.fetchall() image_list = [ result['picid'] for result in results] # access Album id cur.execute('SELECT albumid FROM Album ;') results_db = cur.fetchall() alblum_list=[]*len(results_db) albid=[ int(x["albumid"]) for x in results_db] alblum_list=['0']*(max(albid)+1) for albumid in albid: alblum_list[albumid]=[ x['picid'] for x in results if x['albumid']==albumid] #image_list = [ filename for filename in os.listdir('static/image_hash')] pic_name=request.args.get("picid") #pic_name='001025dd643b0eb0661e359de86e3ea9' #albumid=request.args.get("albumid") #albumid=1 album_num=results[image_list.index(pic_name)]['albumid'] #print (album_num) this_al=alblum_list[album_num] pic_num=this_al.index(pic_name) #print (alblum_list) #pic_num=1 return render_template("pic.html",album_num=album_num,image_list=this_al,i=pic_num,len=len(this_al))
def get_people_list(projectid): db = connect_to_database() cur = db.cursor() command = "SELECT People.Peoplename AS Peoplename FROM People JOIN PeopleContain WHERE PeopleContain.Peopleid = People.Peopleid AND PeopleContain.Projectid = '" + projectid + "'" cur.execute(command) initial_list = cur.fetchall() return initial_list
def project_edit_route(): db = extensions.connect_to_database() cur = db.cursor() errList = [] if 'username' not in session: return render_template('error2.html'), 403 if request.method == 'POST': if not check_project_name(cur, request.form['project_name'].replace("'", "\'")): errList.append('Project name exists') else: file = request.files['file'] if not file: query = 'INSERT INTO Projects VALUES(NULL, %s, %s, %s, %s, NULL, %s)' cur.execute(query, [request.form['project_name'], request.form['project_subtitle'], request.form['project_contents'], request.form['project_website'], request.form['status']]) return redirect(url_for('main.main_route')) if file and allowed_file(file.filename): file_format = os.path.splitext(str(file.filename))[1] filename = getHash(file.filename) file.save(os.path.join(imagePath, filename + file_format)) query = 'INSERT INTO Projects VALUES(NULL,%s,%s,%s,%s,%s,%s)' cur.execute(query,[request.form['project_name'], request.form['project_subtitle'], request.form['project_contents'], request.form['project_website'], filename + file_format ,request.form['status']]) return redirect(url_for('main.main_route')) else: errList.append("Wrong file type! type can only be one of 'jpg', 'gif', 'png', 'bmp'") return render_template('project_edit.html', errList = errList)
def add_publication(pubname, pubtime, information, projectid, people): db = connect_to_database() cur = db.cursor() command = "SELECT * FROM Publication" cur.execute(command) pub_list = cur.fetchall() print(len(pub_list)) #pdb.set_trace() newid = str(len(pub_list) + 2) print(newid) command1 = "INSERT INTO Publication(Publicationid,Pubname,Pubtime,Information,People) VALUES(" + newid + ",'" + pubname + "','" + pubtime + "','" + information + "','" + people + "')" cur.execute(command1) #Publicationid = get_pubid(pubname) insert_Pubcontain(projectid, newid) #command2 = "SELECT Publicationid FROM Publication WHERE Pubname = '"+pubname+"'" #Publicationid = cur.execute(command2) #print(Publicationid) #pdb.set_trace() #command3 = "INSERT INTO PublicationContain(Publicationid, Projectid) VALUES("+Publicationid+","+projectid+")" #print('===line 75 command3=======') #print(command3) #status = cur.execute(command3) #print('proj_help status line 78' + str(status)) return True
def photos_route(): if 'username' not in session: return jsonify(errors="User not logged in") username = session['username'] db = connect_to_database() cur = db.cursor() cur.execute( 'SELECT * FROM PhotoZip WHERE username=\'{}\''.format(username)) results = cur.fetchall() # Only one result should be returned since users can only have one zipfile # of photos hosted on our site at a time #if(len(results) > 1): # return jsonify(errors="Too many files") # Return the URL for the zipfile if one exists and a notification # that it does not exist otherwise metadata if (len(results) >= 1): # Remove this zipfile's metadata from database cur.execute( 'DELETE FROM PhotoZip WHERE username=\'{}\''.format(username)) photos_url = results[0]['url'] return jsonify(status="ZIPFILE_FOUND", url=photos_url) else: return jsonify(status="NO_ZIPFILE_FOUND")
def get_fullname(username): db = connect_to_database() cur = db.cursor() command = "SELECT firstname,lastname FROM User WHERE username='******'" cur.execute(command) fullname = cur.fetchall() #return a list of dictionary return fullname[0]['firstname'], fullname[0]['lastname']
def contact_route(): logged_in_data = '' if 'username' in session: db = connect_to_database() cur = db.cursor() cur.execute( 'SELECT firstname, lastname FROM User WHERE username = \"' + session['username'] + '\"') result = cur.fetchall() cur.close() result = result[0] firstname = result['firstname'] lastname = result['lastname'] logged_in_data += '<li class="nav-item"><a class="nav-link" href=' + url_for( 'user.user_route') + '>%s %s</a></li>' % (firstname, lastname) logged_in_data += '<li class="nav-item"><form method=\"POST\" action=\"%s\" id=nav_logout>' % ( url_for('user.logout_route')) logged_in_data += '<button type=\"submit\">Logout</button><br/>' logged_in_data += '</form></li>' else: logged_in_data += '<li class="nav-item"><a class="nav-link" href=' + url_for( 'user.login_route') + ' id=home_login>Login</a></li>' logged_in_data += '<li class="nav-item"><a class="nav-link" href=' + url_for( 'user.user_create_route') + ' id=home_user_create>Sign Up</a></li>' return render_template("contact.html", logged_in_data=logged_in_data)
def get_past_project_list(): db = connect_to_database() cur = db.cursor() command = "SELECT * FROM Project WHERE status='Past'" cur.execute(command) project_list = cur.fetchall() return project_list
def login_route(): if request.method == "GET": return render_template("login.html") db = extensions.connect_to_database() cur = db.cursor() fields = ['username', 'password'] errList = [] username = request.form['username'].lower() extensions.CheckField(errList, fields, request.form) query = "SELECT * FROM User WHERE username = %s" cur.execute(query, [request.form['username']]) result = cur.fetchone() password = None if result is None: errList.append('Username does not exist') else: password = result['password'] if not extensions.PasswordCheck(request.form['password'], password): errList.append('Password is inocorrect for the specified username') if errList == []: session['username'] = username session['firstname'] = result['firstname'] session['lastname'] = result['lastname'] return redirect(url_for('main.main_route')) return render_template('login.html', errList=errList)
def username_check(username): # check if username exist db = connect_to_database() cur = db.cursor() command = "SELECT* FROM User WHERE username='******'" status = cur.execute(command) if status <= 0: return False return True
def picid_check(picid): db = connect_to_database() cur = db.cursor() command = "SELECT* FROM Contain WHERE picid='"+picid+"'" status = cur.execute(command) if status <= 0: return False return True
def albumid_check(albumid): db = connect_to_database() cur = db.cursor() command = "SELECT* FROM Album WHERE albumid='"+albumid+"'" status = cur.execute(command) if status <= 0: return False return True
def main_route(): db = connect_to_database() cur = db.cursor() cur.execute('SELECT username FROM User') results = cur.fetchall() name_list = [r['username'] for r in results] return render_template("index.html", name_list=name_list)
def check_login(uname, psw): db = connect_to_database() cur = db.cursor() cur.execute('SELECT * FROM Users WHERE Username=%s;', (uname)) results = cur.fetchall() print(results) return len(results) == 1
def get_project_pubcs(projectid): db = connect_to_database() cur = db.cursor() projectid = int(projectid) command1 = "SELECT Publication.Publicationid, Publication.Pubname, Publication.Pubtime, Publication.Information,Publication.People FROM Publication JOIN PublicationContain ON PublicationContain.Publicationid = Publication.Publicationid WHERE PublicationContain.Projectid = " + projectid + "" cur.execute(command1) pubs_list = cur.fetchall() #list of dict return pubs_list
def delete_publication(publicationid, projectid): db = connect_to_database() cur = db.cursor() command1 = "DELETE FROM Publication WHERE Publicationid =" + publicationid + "" cur.execute( command1 ) #don't need to delete from the publicationcontain table because it is on delete cascade return True
def insert_Pubcontain(projectid, Publicationid): db = connect_to_database() cur = db.cursor() command3 = "INSERT INTO PublicationContain(Publicationid, Projectid) VALUES(" + Publicationid + ",'" + projectid + "')" print('===line 75 command3=======') print(command3) status = cur.execute(command3) print('proj_help status line 78' + str(status))
def get_project_basic_info(projectid): db = connect_to_database() cur = db.cursor() #projectid = int(projectid) command = "SELECT Topic, Abstract, Website FROM Project WHERE Projectid =" + projectid + "" cur.execute(command) project_info_dict = cur.fetchall()[0] #pdb.set_trace() return project_info_dict
def add_project_image(filename,projectid): db = connect_to_database() cur = db.cursor() pic_format = filename.rsplit('.')[0].lower() name = filename.rsplit('.')[1] command1 = "INSERT INTO Picture VALUES('"+name+"','"+pic_format+"')" cur.execute(command1) command2 = "INSERT INTO PictureContain VALUES('"+projectid+"','"+name+"')" cur.execute(command2) return True
def main_hello(): db = connect_to_database() cur = db.cursor() cur.execute('SELECT username FROM User') results = cur.fetchall() print(results) lst = [] for result in results: lst.append(result['username']) return render_template("index.html", lst=lst)
def main_hello(): db = connect_to_database() cur = db.cursor() cur.execute('SELECT id, name FROM test_tbl') results = cur.fetchall() print(results) print_str = "<table>" for result in results: print_str += "<tr><td>%s</td><td>%s</td><tr>" % (result['id'], result['name']) print_str += "</table>" return print_str
def main_hello(): db = connect_to_database() cur = db.cursor() cur.execute('SELECT * FROM User') results = cur.fetchall() print(results) print_str = "<table>" for result in results: print("<p>" + result + "</p>") print_str += "</table>" return print_str
def sqlpage_route(): db = connect_to_database() cur = db.cursor() result = '' search = '' error = '' cols = [] if request.method == 'POST': search = request.form.get('command') lower = search.split(";")[0].lower() birds = lower.split("from")[0] if 'update' in lower or 'delete' in lower or 'insert' in lower: error = 'Command not allowed, please only use the SELECT command.' elif 'create' in lower or 'alter' in lower or 'drop' in lower: error = 'Command not allowed, please only use the SELECT command.' elif 'select' not in lower or 'from sampleinfo' not in lower: error = 'Command not allowed, please only use the SELECT command.' if '*' in birds or 'sampleid' in birds: cols.append('sampleid') if '*' in birds or 'deviceid' in birds: cols.append('deviceid') if '*' in birds or 'added' in birds: cols.append('added') if '*' in birds or 'type1' in birds: cols.append('type1') if '*' in birds or 'type2' in birds: cols.append('type2') if '*' in birds or 'type3' in birds: cols.append('type3') if '*' in birds or 'per1' in birds: cols.append('per1') if '*' in birds or 'per2' in birds: cols.append('per2') if '*' in birds or 'per3' in birds: cols.append('per3') if '*' in birds or 'humidity' in birds: cols.append('humidity') if '*' in birds or 'temp' in birds: cols.append('temp') if '*' in birds or 'light' in birds: cols.append('light') if '*' in birds or 'latitude' in birds: cols.append('latitude') if '*' in birds or 'longitude' in birds: cols.append('longitude') if '*' in birds or 'user' in birds: cols.append('user') if not error: try: cur.execute(search) result = cur.fetchall() except: error = 'The SQL command returned an error. Query is: "' + search + '".' if not result and not error: error = 'Search did not return any results.' options = { "result": result, "search": search, "error": error, "cols": cols } return render_template("sqlpage.html", **options)
def get_project_content(projectid): db = connect_to_database() cur = db.cursor() projectid = int(projectid) command = "SELECT Content.Contentid AS Contentid, Content.Paragraph As Paragraph FROM Content JOIN ContentContain ON Content.Contentid = ContentContain.Contentid WHERE ContentContain.Projectid = " + projectid + "" cur.execute(command) content = cur.fetchall() if len(content) > 0: real_content = content[0]['Paragraph'] #string else: real_content = "" return real_content
def check_password(username, ori_password): # pdb.set_trace() db = connect_to_database() cur = db.cursor() command = "SELECT password FROM User WHERE username='******'" cur.execute(command) correct_password = cur.fetchall()[0]['password'] if correct_password != ori_password: return False else: return True
def api_login_route(): errors = [] if ('username' not in request.json) or ('password' not in request.json): e = {} e['message'] = 'You did not provide the necessary fields' errors.append(e) return jsonify(errors = errors),422 empty_username = 0 no_username = 0 empty_password = 0 wrong_pw = 0 fault_in = 0 db = connect_to_database() if request.method =='POST': username = request.json['username'] password = request.json['password'] #if not username: # e = {} # e['message'] = 'You did not provide the necessary fields' # errors.append(e) # return jsonify(errors = errors),422 cur = db.cursor() cur.execute('SELECT password FROM User WHERE username = %s', username) result = cur.fetchone() if (not result) and (username!=''): e = {} e['message'] = 'Username does not exist' errors.append(e) return jsonify(errors = errors),404 print(errors) #get the real password real_password = result['password'] salt = real_password[7:len(real_password)] num = salt.find('$') salt = salt[:num] m = hashlib.new(algorithm) m.update(str(salt+password).encode('utf-8')) password_hash = m.hexdigest() new_word = "$".join([algorithm, salt, password_hash]) if new_word != real_password: e = {} e['message'] = 'Password is incorrect for the specified username' errors.append(e) if not errors: session['username'] = username cur = db.cursor() cur.execute('SELECT firstname,lastname FROM User WHERE username = %s', username) results = cur.fetchone(); session['firstname'] = results['firstname'] session['lastname'] = results['lastname'] return jsonify(username = username) return jsonify(errors = errors),422
def albums_edit_route(): db = extensions.connect_to_database() cur = db.cursor() logged_in = False if 'username' in session: logged_in = True if logged_in: user = session['username'] else: return redirect(url_for('main.login_route')) cur.execute('use maindb') cur.execute('SELECT * FROM User WHERE username = "******"' % (user)) user_exist = cur.fetchall() if not user_exist: abort(404) op = request.form.get('op') if not op: cur.execute('SELECT * FROM Album WHERE username = "******"' % (user)) albums = cur.fetchall() cur.execute('SELECT * FROM AlbumAccess WHERE username = "******"' % (user)) access_albums = cur.fetchall() cur.execute('SELECT * FROM Album') all_albums = cur.fetchall() options = { "access_albums": access_albums, "edit": True, "username": user, "albums": albums, "all_albums": all_albums } return render_template("albums.html", **options) elif op == "delete": album_id = int(request.form.get('albumid')) cur.execute('SELECT * FROM Contain WHERE albumid = %d' % (album_id)) picids = cur.fetchall() cur.execute('DELETE FROM Contain WHERE albumid = %d' % (album_id)) for pic in picids: cur.execute('SELECT format FROM Photo WHERE picid = "%s"' % pic['picid']) format = cur.fetchall()[0]['format'] cur.execute('DELETE FROM Photo WHERE picid = "%s"' % pic['picid']) os.remove('/static/images/' + pic['picid'] + "." + str(format)) cur.execute('DELETE FROM Album WHERE albumid = %d' % (album_id)) cur.execute('DELETE FROM AlbumAccess WHERE albumid = %d' % (album_id)) else: title = request.form.get('title') cur.execute( 'INSERT INTO Album VALUES(NULL, "%s", CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(), "%s", "%s")' % (title, user, "private")) return redirect(url_for('albums.albums_edit_route', username=user))
def apiPath_route(postID, option): #if second URL variable option == "file", return the sound file if option == "file": path = '/home/bybsongbird/app/bybsongbird/static/songs/users/' + postID + '.WAV' attachment = postID + '.WAV' return send_file(path, attachment_filename=attachment) #if option == "info", return file if option == "info": db = extensions.connect_to_database() cur = db.cursor() cur.execute("SELECT * FROM sampleInfo WHERE sampleid = %s", (postID, )) result = cur.fetchone() match = {} match['sample_id'] = result['sampleid'] match['first_match'] = json.dumps([{ "name": result['type1'][0:result['type1'].find('_')].title(), "value": float(result["per1"]) }, { "name": "Other", "value": 1 - float(result["per1"]) }]) match['second_match'] = json.dumps([{ "name": result['type2'][0:result['type2'].find('_')].title(), "value": float(result["per2"]) }, { "name": "Other", "value": 1 - float(result["per2"]) }]) match['third_match'] = json.dumps([{ "name": result['type3'][0:result['type3'].find('_')].title(), "value": float(result["per3"]) }, { "name": "Other", "value": 1 - float(result["per3"]) }]) match['added'] = result['added'].strftime("%b %d %Y %X") match['latitude'] = result['latitude'] match['longitude'] = result['longitude'] match['humidity'] = int(round(result['humidity'])) match['temperature'] = int(round(result['temp'])) match['light'] = int(round(result['light'])) options = {"match": match} return jsonify(options)