def update_db(self):
cur = mysql.get_db().cursor()
query = """Update application set realName = %s,falseName = %s,categoryId = %s,picture1 = %s,picture2 = %s where id = %s"""
input=(self.realName,self.falseName,self.categoryId,self.picture1,self.picture2,self.id)
cur.execute(query,input)
mysql.get_db().commit()
cur.close()
def update_db(self):
cur = mysql.get_db().cursor()
query = """Update userDetailsTempate set attr = %s,name = %s,type = %s where id = %s"""
input = (self.attr, self.name, self.type, self.id)
cur.execute(query, input)
mysql.get_db().commit()
cur.close()
def update_db(self):
cur = mysql.get_db().cursor()
query = """Update report set experimentDetaileID = %s,questionID = %s,answer = %s,part = %s,securityId = %s where id = %s"""
input = (self.experimentDetaileID, self.questionID, self.answer,
self.part, self.securityId, self.id)
cur.execute(query, input)
mysql.get_db().commit()
cur.close()
def update_db(self):
cur = mysql.get_db().cursor()
query = """Update experiment set type = %s,numberParti = %s,leftParti = %s,checkboxLen = %s,run = %s where id = %s"""
input = (self.type, self.numberParti, self.leftParti, self.checkboxLen,
self.run, self.id)
cur.execute(query, input)
mysql.get_db().commit()
cur.close()
def update_db(self):
cur = mysql.get_db().cursor()
query = """Update experimentDetaile set ExperimentId = %s,applicationId = %s,questionDisplay = %s,userID = %s,done = %s,groupExp = %s where id = %s"""
input = (self.ExperimentId, self.applicationId, self.questionDisplay,
self.userID, self.done, self.groupExp, self.id)
cur.execute(query, input)
mysql.get_db().commit()
cur.close()
def update_db(self):
cur = mysql.get_db().cursor()
query = """Update question set question = %s,section = %s,type = %s,answer1 = %s,answer2 = %s,answer3 = %s,answer4 = %s where id = %s"""
input = (self.question, self.section, self.type, self.answer1,
self.answer2, self.answer3, self.answer4, self.id)
cur.execute(query, input)
mysql.get_db().commit()
cur.close()
def delete_by_id(cls, _id):
cur = mysql.get_db().cursor()
query = """Delete from report where id = %s"""
cur.execute(query, _id)
if (cur.rowcount > 0):
cur.close()
mysql.get_db().commit()
return True
cur.close()
return False
def delete_by_securityApplictionID(cls, _securityApplictionID):
cur = mysql.get_db().cursor()
query = """Delete from securityApplictionDescription where securityApplictionId = %s"""
cur.execute(query, _securityApplictionID)
if (cur.rowcount > 0):
cur.close()
mysql.get_db().commit()
return True
cur.close()
return False
def save_to_db(self):
cur = mysql.get_db().cursor()
if (UserDetailsTemplateModel.find_by_id(self.id) != False):
cur.close()
return False
input = (self.name, self.attr, self.type)
query = """INSERT INTO userDetailsTempate (attr,name,type) VALUES (%s,%s,%s)"""
cur.execute(query, input)
mysql.get_db().commit()
query = """SELECT * FROM userDetailsTempate ORDER BY id DESC LIMIT 1"""
cur.execute(query)
if (cur.rowcount > 0):
self.id = cur.fetchone()[0]
cur.close()
def save_to_db(self):
cur = mysql.get_db().cursor()
if( ApplicationModel.find_by_id(self.id) != False ):
cur.close()
return False
query= """INSERT INTO application (realName,falseName,categoryId,picture1,picture2) VALUES (%s, %s,%s,%s,%s)"""
input=(self.realName,self.falseName,self.categoryId,self.picture1,self.picture2)
cur.execute(query,input)
mysql.get_db().commit()
query = """SELECT * FROM application ORDER BY id DESC LIMIT 1"""
cur.execute(query)
if(cur.rowcount > 0):
self.id=cur.fetchone()[0]
cur.close()
def save_to_db(self):
cur = mysql.get_db().cursor()
if (SecurityApplictionDescriptionModel.find_by_id(self.id) != False):
cur.close()
return False
input = (self.securityApplictionId, self.privacyId, self.privacyRankId)
query = """INSERT INTO securityApplictionDescription (securityApplictionId,privacyId,privacyRankId) VALUES (%s, %s, %s)"""
cur.execute(query, input)
mysql.get_db().commit()
query = """SELECT * FROM securityApplictionDescription ORDER BY id DESC LIMIT 1"""
cur.execute(query)
if (cur.rowcount > 0):
self.id = cur.fetchone()[0]
cur.close()
def save_to_db(self):
cur = mysql.get_db().cursor()
if (PrivacyModel.find_by_id(self.id) != False):
cur.close()
return False
input = (self.name)
query = """INSERT INTO privacy (name) VALUES ( %s)"""
cur.execute(query, input)
mysql.get_db().commit()
query = """SELECT * FROM privacy ORDER BY id DESC LIMIT 1"""
cur.execute(query)
if (cur.rowcount > 0):
self.id = cur.fetchone()[0]
cur.close()
def save_to_db(self):
cur = mysql.get_db().cursor()
if (UserDetailsModel.find_by_id(self.id) != False):
cur.close()
return False
input = (self.value, self.attrParent, self.userId)
query = """INSERT INTO userDetails (value,attrParent,userId) VALUES (%s,%s,%s)"""
cur.execute(query, input)
mysql.get_db().commit()
query = """SELECT * FROM userDetails ORDER BY id DESC LIMIT 1"""
cur.execute(query)
if (cur.rowcount > 0):
self.id = cur.fetchone()[0]
cur.close()
def save_to_db(self):
cur = mysql.get_db().cursor()
if (ExperimentDetaileModel.find_by_id(self.id) != False):
cur.close()
return False
input = (self.ExperimentId, self.applicationId, self.questionDisplay,
self.userID, self.done, self.groupExp)
query = """INSERT INTO experimentDetaile (ExperimentId,applicationId,questionDisplay,userID,done,groupExp) VALUES (%s,%s,%s,%s, %s, %s)"""
cur.execute(query, input)
mysql.get_db().commit()
query = """SELECT * FROM experimentDetaile ORDER BY id DESC LIMIT 1"""
cur.execute(query)
if (cur.rowcount > 0):
self.id = cur.fetchone()[0]
cur.close()
def save_to_db(self):
cur = mysql.get_db().cursor()
if (ExperimentModel.find_by_id(self.id) != False):
cur.close()
return False
input = (self.type, self.numberParti, self.leftParti, self.checkboxLen,
self.run)
query = """INSERT INTO experiment (type,numberParti,leftParti,checkboxLen,run) VALUES (%s,%s,%s, %s, %s)"""
cur.execute(query, input)
mysql.get_db().commit()
query = """SELECT * FROM experiment ORDER BY id DESC LIMIT 1"""
cur.execute(query)
if (cur.rowcount > 0):
self.id = cur.fetchone()[0]
cur.close()
def save_to_db(self):
cur = mysql.get_db().cursor()
if (QuestionModel.find_by_id(self.id) != False):
cur.close()
return False
query = """INSERT INTO question (question,section,type,answer1,answer2,answer3,answer4) VALUES (%s,%s, %s,%s, %s,%s, %s)"""
input = (self.question, self.section, self.type, self.answer1,
self.answer2, self.answer3, self.answer4)
cur.execute(query, input)
mysql.get_db().commit()
query = """SELECT * FROM question ORDER BY id DESC LIMIT 1"""
cur.execute(query)
if (cur.rowcount > 0):
self.id = cur.fetchone()[0]
cur.close()
def save_to_db(self):
cur = mysql.get_db().cursor()
if (ReportModel.find_by_id(self.id) != False):
cur.close()
return False
query = """INSERT INTO report (experimentDetaileID,questionID,answer,part,securityId) VALUES (%s,%s,%s,%s,%s)"""
input = (self.experimentDetaileID, self.questionID, self.answer,
self.part, self.securityId)
cur.execute(query, input)
mysql.get_db().commit()
query = """SELECT * FROM report ORDER BY id DESC LIMIT 1"""
cur.execute(query)
if (cur.rowcount > 0):
self.id = cur.fetchone()[0]
cur.close()
def signup_route():
if request.method == 'GET':
print 'SignUp GET'
return render_template('signup.html')
elif request.method == 'POST':
print 'SignUp POST'
f = request.form
# Create a new User
if checkIfEmailExists(f['email']):
salt = encrypt.genSalt()
newPassword = encrypt.encryptPassword(salt, f['password'])
query_AddUser = '******'
data_AddUser = [f['firstname'], f['lastname'], newPassword, f['email'], f['phone']]
conn = mysql.get_db()
cursor = conn.cursor()
cursor.execute(query_AddUser, data_AddUser)
conn.commit()
# on Success, create a session, include username as a part of url
session['email'] = f['email']
session['name'] = f['firstname'] + ' ' + f['lastname']
user = f['email'].split('@')[0]
session['user'] = user
return redirect(url_for('main_user.main_user_route', user=session['user']))
else:
error = 'Email already exists'
return render_template('signup.html', error = error)
def drop_db(self):
cur = mysql.get_db().cursor()
stmt = "SHOW TABLES LIKE 'experimentDetaile'"
cur.execute(stmt)
result = cur.fetchone()
if result:
sql = "DROP TABLE experimentDetaile"
cur.execute(sql)
cur.close()
def drop_db(self):
cur = mysql.get_db().cursor()
stmt = "SHOW TABLES LIKE 'securityApplictionDescription'"
cur.execute(stmt)
result = cur.fetchone()
if result:
sql = "DROP TABLE securityApplictionDescription"
cur.execute(sql)
cur.close()
def drop_db(self):
cur = mysql.get_db().cursor()
stmt = "SHOW TABLES LIKE 'privacy'"
cur.execute(stmt)
result = cur.fetchone()
if result:
sql = "DROP TABLE privacy"
cur.execute(sql)
cur.close()
def find_by_id(cls, _id):
cur = mysql.get_db().cursor()
query = """select * from application where id = %s"""
cur.execute(query,_id)
if(cur.rowcount > 0):
row = cur.fetchone()
cur.close()
application=ApplicationModel(row[1],row[2],row[3],row[4],row[5])
application.id=row[0]
return application
cur.close()
return False
def find_by_id(cls, _id):
cur = mysql.get_db().cursor()
query = """select * from report where id = %s"""
cur.execute(query, _id)
if (cur.rowcount > 0):
row = cur.fetchone()
cur.close()
report = ReportModel(row[1], row[2], row[3], row[4], row[5])
report.id = row[0]
return report
cur.close()
return False
def find_by_attrParent(cls, _id):
cur = mysql.get_db().cursor()
query = """select * from userDetails where attrParent = %s"""
cur.execute(query, _id)
if (cur.rowcount > 0):
row = cur.fetchone()
cur.close()
UserDetails = UserDetailsModel(row[1], row[2], row[5], row[4])
UserDetails.id = row[0]
cur.close()
return UserDetails
cur.close()
return False
def find_by_id(cls, _id):
cur = mysql.get_db().cursor()
query = """select * from securty where id = %s"""
cur.execute(query,_id)
if(cur.rowcount > 0):
row = cur.fetchone()
cur.close()
securtyFeature=SecurtyModel(row[1])
SecurtyFeature.id=row[0]
cur.close()
return securtyFeature
cur.close()
return False
def find_Experiment_run_type(cls, _type):
cur = mysql.get_db().cursor()
query = """select * from experiment where run = %s AND type = %s"""
cur.execute(query, 1, _type)
if (cur.rowcount > 0):
rows = cur.fetchone()
cur.close()
Experiment = ExperimentModel(row[1], row[2], row[4], row[5])
Experiment.leftParti = row[3]
Experiment.id = row[0]
return Experiment
cur.close()
return False
def checkIfEmailExists(email): print 'checkIfEmailExists' query_GetEmail = 'SELECT * FROM User WHERE email = %s' data_GetEmail = [email] conn = mysql.get_db() cursor = conn.cursor() cursor.execute(query_GetEmail, data_GetEmail) result = cursor.fetchall() print len(result) if len(result) == 0: return True return False
def find_by_id(cls, _id):
cur = mysql.get_db().cursor()
query = """select * from privacy where id = %s"""
cur.execute(query, _id)
if (cur.rowcount > 0):
row = cur.fetchone()
cur.close()
privacyFact = PrivacyModel(row[1])
PrivacyModel.id = row[0]
cur.close()
return privacyFact
cur.close()
return False
def find_by_id(cls, _id):
cur = mysql.get_db().cursor()
query = """select * from securityApplictionDescription where id = %s"""
cur.execute(query, _id)
if (cur.rowcount > 0):
row = cur.fetchone()
cur.close()
SecurityAppliction = SecurityApplictionDescriptionModel(
row[1], row[2], row[3])
SecurityAppliction.id = row[0]
cur.close()
return SecurityAppliction
cur.close()
return False
def find_by_id(cls, _id):
cur = mysql.get_db().cursor()
query = """select * from experimentDetaile where id = %s"""
cur.execute(query, _id)
if (cur.rowcount > 0):
row = cur.fetchone()
cur.close()
Experiment = ExperimentDetaileModel(row[1], row[2], row[3], row[4],
row[5], row[6])
Experiment.id = row[0]
cur.close()
return Experiment
cur.close()
return False
def find_all_UserDetails(cls):
cur = mysql.get_db().cursor()
query = """select * from userDetails"""
cur.execute(query)
if (cur.rowcount > 0):
rows = cur.fetchall()
cur.close()
UserDetailsArry = []
for row in rows:
UserDetails = UserDetailsModel(row[1], row[2], row[3])
UserDetails.id = row[0]
UserDetailsArry.append(UserDetails)
return UserDetailsArry
cur.close()
return False
def find_all_PrivacyFact(cls):
cur = mysql.get_db().cursor()
query = """select * from privacy"""
cur.execute(query)
if (cur.rowcount > 0):
rows = cur.fetchall()
cur.close()
PrivacyFactArry = []
for row in rows:
privacyFact = PrivacyModel(row[1])
privacyFact.id = row[0]
PrivacyFactArry.append(privacyFact)
return PrivacyFactArry
cur.close()
return []
def bagunis_route(user, baguniid):
# Check if a user is logged in
# If not, redirect to the main page for login
if 'email' not in session:
print 'Not logged in: redirecting to main...'
return redirect(url_for('main.main_route'))
# Check if the url is the right url for the current user
session_username = session['email'].split('@')[0]
if session_username != user:
return render_template('403.html'), 403
# Check if the current Baguni is displayed to the right user
curBaguniid = baguniid
query_getEmail = 'SELECT email FROM BaguniAccess WHERE baguniid = %s'
data_getEmail = [curBaguniid]
conn = mysql.get_db()
cursor = conn.cursor()
cursor.execute(query_getEmail, data_getEmail)
try:
db_email = cursor.fetchall()[0][0]
if db_email != session['email']:
return render_template('403.html'), 403
except IndexError:
return render_template('403.html'), 403
if request.method == 'GET':
# Get info about items in the current Baguni from the database
query_getItems = 'SELECT * FROM Item WHERE baguniid = %s ORDER BY itemname ASC'
data_getItems = [curBaguniid]
conn = mysql.get_db()
cursor = conn.cursor()
cursor.execute(query_getItems, data_getItems)
db_items = cursor.fetchall()
items = []
checked = ''
for db_item in db_items:
if db_item[7] == 1:
checked = 'checked'
item = {
'origurl': db_item[2],
'imageurl': db_item[3],
'price': db_item[4],
'brand': db_item[5],
'name': db_item[6],
'selected': checked
}
items.append(item)
return render_template("bagunis.html", items = items)
if request.method == 'POST':
# Receive json object from ajax request
jsondata = request.get_json()
itemURL = jsondata['itemURL']
curStep = jsondata['step']
print curStep
if curStep == 0:
# Parse the url given
parseResult = parseCafe24Mall(itemURL)
# {
# 'domain': domain,
# 'img': img,
# 'name': name,
# 'price': price,
# 'info': info
# }
parseSuccess = parseResult[0]
parseError = parseResult[1]
parseInfo = parseResult[2]
print parseError
print parseInfo
return jsonify(
errorMessage = parseError,
checkImage = parseInfo['img'],
checkBrand = parseInfo['domain'],
checkName = parseInfo['name'],
checkPrice = parseInfo['price'],
moreInfo = parseInfo['info']
)
elif curStep == 1:
print jsondata['price']
print jsondata['moreInfo']
query_addItem = ('INSERT INTO Item(baguniid, originalurl, imageurl, price, '
'brandname, itemname, addInfo) VALUES (%s,%s,%s,%s,%s,%s,%s)')
data_addItem = [baguniid, itemURL, jsondata['imageURL'], jsondata['price'], jsondata['brandName'],
jsondata['itemName'], jsondata['moreInfo']]
conn = mysql.get_db()
cursor = conn.cursor()
cursor.execute(query_addItem, data_addItem)
conn.commit()
return ('', 200)
def main_user_route(user):
# Check if a user is logged in
# If not, redirect to the main page for login
if 'email' not in session:
print 'Not logged in: redirecting to main...'
return redirect(url_for('main.main_route'))
# Check if the url is the right url for the current user
if session['user'] != user:
return render_template('403.html'), 403
if request.method == 'GET':
print 'baguni GET'
# Get all the Baguni IDs associated with the email account
query_getBaguniId = 'SELECT * FROM BaguniAccess WHERE email = %s'
data_getBaguniId = [session['email']]
conn = mysql.get_db()
cursor = conn.cursor()
cursor.execute(query_getBaguniId, data_getBaguniId)
baguniid = cursor.fetchall()
bagunis = []
for i in range(0, len(baguniid)):
curBaguniId = baguniid[i][1]
# For each Baguni ID, get Baguni info
query_getBaguni = 'SELECT * FROM Baguni WHERE baguniid = %s'
data_getBaguni = [curBaguniId]
cursor.execute(query_getBaguni, data_getBaguni)
baguni = cursor.fetchall()[0]
# For each Baguni, count the number of items in that Baguni
query_getNumItems = 'SELECT COUNT(itemid) FROM Item WHERE baguniid = %s'
data_getNumItems = [curBaguniId]
cursor.execute(query_getNumItems, data_getNumItems)
numItems = cursor.fetchall()[0][0]
# For each Baguni, count the number of items SELECTED in that Baguni
query_getNumSelected = 'SELECT COUNT(itemid) FROM Item WHERE baguniid = %s AND selected = %s'
data_getNumSelected = [curBaguniId, 1]
cursor.execute(query_getNumSelected, data_getNumSelected)
numSelected = cursor.fetchall()[0][0]
newBaguni = [baguni, numItems, numSelected]
bagunis.append(newBaguni)
return render_template("main_user.html", bagunis = bagunis, user = user)
if request.method == 'POST':
print 'baguni POST'
jsondata = request.get_json()
baguniName = jsondata['baguniName']
baguniColor = jsondata['baguniColor']
# Insert into Baguni
query_addBaguni = 'INSERT INTO Baguni (email, title, color) VALUES (%s, %s, %s)'
data_addBaguni = [session['email'], baguniName, baguniColor]
conn = mysql.get_db()
cursor = conn.cursor()
cursor.execute(query_addBaguni, data_addBaguni)
conn.commit()
# Get the id of inserted Baguni
query_getBaguniId = 'SELECT LAST_INSERT_ID()'
baguniid = cursor.execute(query_getBaguniId)
baguniid = cursor.fetchall()[0]
# Insert into BaguniAccess the newly added Baguni
query_addBagAccess = 'INSERT INTO BaguniAccess (email, baguniid) VALUES (%s, %s)'
data_addBagAccess = [session['email'], baguniid]
cursor.execute(query_addBagAccess, data_addBagAccess)
conn.commit()
return ('', 200)
