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)
