def part3():
sql = "SELECT status FROM teaching_experience WHERE application_no = '%s';" % (
session['application_number'])
cursor.execute(sql)
rows = cursor.fetchall()
if rows[0][0] == "new": # if the application is new show new empty form
return render_template(
'application_part3.html',
email_=session['email'],
application_number=session['application_number'])
else: # else retrieve already present data
sql = "SELECT * FROM teaching_experience WHERE application_no = '%s';" % (
session['application_number'])
cursor.execute(sql)
rows = cursor.fetchall()
rows = list(rows[0])
position_info_list = rows[3][1:-1].split(",")
referee1 = rows[18][1:-1].split(",")
referee1 = [r.replace("\"", "") for r in referee1]
referee2 = rows[19][1:-1].split(",")
referee2 = [r.replace("\"", "") for r in referee2]
referee3 = rows[20][1:-1].split(",")
referee3 = [r.replace("\"", "") for r in referee3]
# fetch the already stored values in the database and display them
params_ = [
rows[2], position_info_list, rows[4], rows[5], rows[6], rows[8],
rows[9], rows[10], rows[11], rows[12], rows[13], rows[14],
rows[15], rows[16], rows[17], referee1, referee2, referee3,
rows[21]
]
sql = "SELECT freeze_status FROM main_table WHERE application_no = '%s';" % (
session['application_number'])
cursor.execute(sql)
freeze_rows = cursor.fetchall()
if freeze_rows[0][
0] == "true": # once freezed there should be np submit button
return render_template(
'application_readonly_freezed_part3.html',
email_=session['email'],
params=params_,
application_number=session['application_number'])
elif rows[
1] == "submitted": #if status is submitted the person can no longer chnage the form
return render_template(
'application_readonly_part3.html',
email_=session['email'],
params=params_,
application_number=session['application_number'])
return render_template(
'application_placeholders_part3.html',
params=params_,
email_=session['email'],
application_number=session['application_number'])
def getUsersInfoByEmail(emails):
if not emails:
return []
sql = "SELECT * FROM User WHERE email in (%s)" % ', '.join(
map(lambda x: '\'%s\'' % x, emails))
cursor.execute(sql)
q_result = cursor.fetchall()
rows = []
for row in q_result:
data = {}
data["id"] = row[0]
data["username"] = row[1]
data["email"] = row[2]
data["name"] = row[3]
data["about"] = row[4]
data["isAnonymous"] = row[5]
# data["followers"] = getFollowerEmails(data["id"], None, None, None)
# data["following"] = getFollowingEmails(data["id"], None, None, None)
data["subscriptions"] = getSubscriptions(data["id"])
rows.append(data)
id_list = map(lambda x: x['id'], rows)
sql = "SELECT F.idFollowing, U.email FROM Follower F INNER JOIN User U ON F.idFollower = U.idUser WHERE F.idFollowing in (%s)" % ', '.join(
map(str, id_list))
cursor.execute(sql)
q_result = cursor.fetchall()
followers = {}
for row in q_result:
try:
followers[row[0]].append(row[1])
except KeyError:
followers[row[0]] = [row[1]]
sql = "SELECT F.idFollower, U.email FROM Follower F INNER JOIN User U ON F.idFollowing = U.idUser WHERE F.idFollower in (%s)" % ', '.join(
map(str, id_list))
cursor.execute(sql)
q_result = cursor.fetchall()
following = {}
for row in q_result:
try:
following[row[0]].append(row[1])
except KeyError:
following[row[0]] = [row[1]]
for row in rows:
try:
row['followers'] = followers[row['id']]
except KeyError:
row['followers'] = []
try:
row['following'] = following[row['id']]
except KeyError:
row['following'] = []
return rows
def part2():
sql = "SELECT status FROM education WHERE application_no = '%s';" % (
session['application_number'])
cursor.execute(sql)
rows = cursor.fetchall()
if rows[0][0] == "new":
return render_template(
'application_part2.html',
email_=session['email'],
application_number=session['application_number'])
else:
sql = "SELECT * FROM education WHERE application_no = '%s';" % (
session['application_number'])
cursor.execute(sql)
rows = cursor.fetchall()
rows = list(rows[0])
btech_list = rows[2][1:-1].split(",")
btech_list2 = rows[3][1:-1].split(",")
mtech_list = rows[4][1:-1].split(",")
mtech_list2 = rows[5][1:-1].split(",")
phd_list = rows[6][1:-1].split(",")
phd_thesis = rows[7][1:-1].split(",")
gate_list = rows[8][1:-1].split(",")
# name_list = rows[3][1:-1].split(",")
params_ = [
btech_list, mtech_list, phd_list, phd_thesis, gate_list, rows[9],
rows[10], btech_list2, mtech_list2
]
print "retrieved properly"
sql = "SELECT freeze_status FROM main_table WHERE application_no = '%s';" % (
session['application_number'])
cursor.execute(sql)
freeze_rows = cursor.fetchall()
if freeze_rows[0][
0] == "true": # if application is freezed there should be no option to submit
return render_template(
'application_readonly_freezed_part2.html',
email_=session['email'],
params=params_,
application_number=session['application_number'])
elif rows[
1] == "submitted": # if application is submitted, the form should be read only
return render_template(
'application_readonly_part2.html',
email_=session['email'],
params=params_,
application_number=session['application_number'])
return render_template(
'application_placeholders_part2_.html',
params=params_,
email_=session['email'],
application_number=session['application_number'])
def getUsersInfoByEmail(emails):
if not emails:
return []
sql = "SELECT * FROM User WHERE email in (%s)" % ', '.join(map(lambda x: '\'%s\'' % x, emails))
cursor.execute(sql)
q_result = cursor.fetchall()
rows = []
for row in q_result:
data = {}
data["id"] = row[0]
data["username"] = row[1]
data["email"] = row[2]
data["name"] = row[3]
data["about"] = row[4]
data["isAnonymous"] = row[5]
# data["followers"] = getFollowerEmails(data["id"], None, None, None)
# data["following"] = getFollowingEmails(data["id"], None, None, None)
data["subscriptions"] = getSubscriptions(data["id"])
rows.append(data)
id_list = map(lambda x: x['id'], rows)
sql = "SELECT F.idFollowing, U.email FROM Follower F INNER JOIN User U ON F.idFollower = U.idUser WHERE F.idFollowing in (%s)" % ', '.join(map(str, id_list))
cursor.execute(sql)
q_result = cursor.fetchall()
followers = {}
for row in q_result:
try:
followers[row[0]].append(row[1])
except KeyError:
followers[row[0]] = [row[1]]
sql = "SELECT F.idFollower, U.email FROM Follower F INNER JOIN User U ON F.idFollowing = U.idUser WHERE F.idFollower in (%s)" % ', '.join(map(str, id_list))
cursor.execute(sql)
q_result = cursor.fetchall()
following = {}
for row in q_result:
try:
following[row[0]].append(row[1])
except KeyError:
following[row[0]] = [row[1]]
for row in rows:
try:
row['followers'] = followers[row['id']]
except KeyError:
row['followers'] = []
try:
row['following'] = following[row['id']]
except KeyError:
row['following'] = []
return rows
def part4():
sql = "SELECT * FROM attachments WHERE application_no = '%s';" %(session['application_number'])
cursor.execute(sql)
rows = cursor.fetchall()
sql = "SELECT attachment_status,freeze_status FROM main_table WHERE application_no = '%s';" %(session['application_number'])
cursor.execute(sql)
rows1 = cursor.fetchall()
if rows1[0][1] == 'true':
print "going to freezed read only"
return render_template('application_readonly_freezed_part4.html', params=rows, email_=session['email'])
elif rows1[0][0] == 'submitted':
return render_template('application_readonly_part4.html', params=rows, email_=session['email'])
return render_template('application_part4.html', params=rows, email_=session['email'])
def freeze_admin():
if (request.method == 'POST'):
print "freeze applications"
sql = "UPDATE main_table SET freeze_status = \'true\' WHERE status = \'submitted\'"
try:
cursor.execute(sql)
db.commit()
print sql
except:
print "Db error"
sql = "SELECT application_no, name, position_applied FROM main_table WHERE status = '%s';" % "submitted"
cursor.execute(sql)
rows1 = list(cursor.fetchall())
rows = []
for i in range(len(rows1)):
temp = [
rows1[i][0], rows1[i][1], rows1[i][2],
str(rows1[i][0]) + ".pdf"
]
rows.append(temp)
print rows
return render_template('admin_view_part1.html',
email_=session['email'],
rows=rows)
def send():
from app import cursor
cursor.execute('''SELECT productid,name,stock from Product where stock <50 ''')
stock = cursor.fetchall()
print stock
if len(stock) > 0:
while True:
msg = MIMEText('we need order somthing,pls contact us!', 'plain','utf-8')
msg['From'] = _format_addr('YuanClothShop <%s>' % from_addr)
msg['Subject'] = Header('OrderList', 'utf-8').encode()
with open("emails.txt","r") as f:
emails = f.readlines()
for to_addr in emails:
msg['To'] = _format_addr('Supplier<%s>' % to_addr)
server = smtplib.SMTP(smtp_server, 25)
server.set_debuglevel(1)
server.ehlo()
server.starttls()
server.login(from_addr, password)
server.sendmail(from_addr, [to_addr], msg.as_string())
server.quit()
break
def get_products():
sql = "SELECT * FROM products"
status = 0,
res = ''
try:
cursor.execute(sql)
result = cursor.fetchall()
print(type(result))
data = []
for rec in result:
product = {
'id': rec[0],
'product_name': rec[1],
'product_description': rec[2],
'product_price': rec[3],
'product_qty': rec[4],
'created_by': rec[5],
'actions': rec[6]
}
data.append(product)
status = 200
res = data
except:
print("Error: unable to fetch data")
return res, status
def getListPostsOfThread(thread, since, order, limit):
logging.info(" GETTING LIST POSTS BY THREAD")
sql = "SELECT * FROM Post WHERE idThread = %s"
params = [thread]
if since:
sql += " AND date >= %s"
params.append(since)
sql += " ORDER BY date " + order
if limit:
sql += " LIMIT %s"
params.append(int(limit))
logging.info(" Final SQL listPosts : " + sql)
logging.info(" Final PARAMS listPosts : " + str(params))
cursor.execute(sql, params)
result = cursor.fetchall()
answer = getArrayPostsFormDDictionary(result, [])
logging.info(" GETTED POSTS : ")
logging.info(answer)
logging.info(" ==============")
return answer
def index():
if not current_user.is_authenticated:
return redirect(url_for('login'))
users_dic_work[current_user.id] = 0
users_dic_explore[current_user.id] = 0
if current_user.weapon_id is not None:
cursor.execute("SELECT * FROM items where id =" +
str(current_user.weapon_id))
res = cursor.fetchone()
weapon = Item(res)
else:
weapon = None
cursor.execute(
"select items.id, items.name, items.type, items.value, items.rare\
from items, users, armors\
where items.id=armors.armor_id and users.id=armors.user_id and users.id=%s"
% current_user.id)
res = cursor.fetchall()
defense = 0
if len(res) == 0:
armors = None
else:
armors = []
for i in res:
armor = Item(i)
defense = defense + armor.value
armors.append(armor)
return render_template("index.html",
title='Home Page',
weapon=weapon,
armors=armors,
defense=defense)
def dashboard_add():
location = request.form.get('searchlocation')
cursor.execute(
"""SELECT * FROM `admindatabase` WHERE `location` LIKE '{}'""".format(location))
posts = cursor.fetchall()
print(posts)
return render_template('userdashboard.html', posts=posts)
def getSubscriptions(idUser):
sql = "SELECT idThread FROM Subscription WHERE idUser = %s"
cursor.execute(sql, [idUser])
subscriptions = getArrayEmailsFromDoubleDictionary(cursor.fetchall())
# logging.info(" Subscriptions of USER (" + str(idUser) + ") : " + str(subscriptions))
return subscriptions
def get_all_tasks():
sql = "SELECT * FROM tasks"
status = 0
res = ''
try:
cursor.execute(sql)
result = cursor.fetchall()
data = []
for rec in result:
task = {
'task_id': rec[0],
'robot_id': rec[1],
'task_name': rec[2],
'issued_time': json.dumps(rec[3], default=str),
'scheduled_time': json.dumps(rec[4], default=str),
'status': rec[5]
}
data.append(task)
status = 200
res = data
except Exception as e:
print("Error: unable to fetch data.")
print(e)
return res, status
def getListPostsOfThread(thread, since, order, limit):
logging.info(" GETTING LIST POSTS BY THREAD")
sql = "SELECT * FROM Post WHERE idThread = %s"
params = [thread]
if since:
sql += " AND date >= %s"
params.append(since)
sql += " ORDER BY date " + order
if limit:
sql += " LIMIT %s"
params.append(int(limit))
logging.info(" Final SQL listPosts : " + sql)
logging.info(" Final PARAMS listPosts : " + str(params))
cursor.execute(sql, params)
result = cursor.fetchall()
answer = getArrayPostsFormDDictionary(result, [])
logging.info(" GETTED POSTS : ")
logging.info(answer)
logging.info(" ==============")
return answer
def getListPostsOfForum(forum, since, order, limit, related):
#logging.info(" GETTING LIST POSTS BY FORUM")
idForum = getForumIdByShortName(forum)
sql = "SELECT * FROM Post WHERE idForum = %s"
params = [idForum]
if since:
sql += " AND date >= %s"
params.append(since)
sql += " ORDER BY date " + order
if limit:
sql += " LIMIT %s"
params.append(int(limit))
# logging.info(" Final SQL listPosts : " + sql)
#logging.info(" Final PARAMS listPosts : " + str(params))
cursor.execute(sql, params)
result = cursor.fetchall()
answer = getArrayPostsFormDDictionary(result, related)
#logging.info(" GETTED POSTS : ")
# logging.info(answer)
#logging.info(" ==============")
return answer
def getListPostsOfForum(forum, since, order, limit, related):
logging.info(" GETTING LIST POSTS BY FORUM")
idForum = getForumIdByShortName(forum)
sql = "SELECT * FROM Post WHERE idForum = %s"
params = [idForum]
if since:
sql += " AND date >= %s"
params.append(since)
sql += " ORDER BY date " + order
if limit:
sql += " LIMIT %s"
params.append(int(limit))
logging.info(" Final SQL listPosts : " + sql)
logging.info(" Final PARAMS listPosts : " + str(params))
cursor.execute(sql, params)
result = cursor.fetchall()
answer = getArrayPostsFormDDictionary(result, related)
logging.info(" GETTED POSTS : ")
logging.info(answer)
logging.info(" ==============")
return answer
def get_users():
sql = "SELECT * FROM users"
status = 0
res = ''
try:
cursor.execute(sql)
result = cursor.fetchall()
data = []
for rec in result:
user = {
'id': rec[0],
'user_name': rec[1],
'user_email': rec[2],
'user_address': rec[4],
'user_phone': rec[5],
'created_by': rec[6],
'actions': rec[7]
}
data.append(user)
status = 200
res = data
except:
print("Error: unable to fetch data")
return res, status
def is_full():
min = {"_id": "0", "value": 100000}
cursor.execute(
"select items.id, items.name, items.type, items.value, items.rare\
from items, users, storage\
where items.id=storage.item_id and users.id=storage.user_id and users.id=%s"
% current_user.id)
res = cursor.fetchall()
items = []
for i in res:
item = Item(i)
items.append(item)
if len(items) >= MAX_ITEM:
for item in items:
if item.value < min["value"]:
min["_id"] = item.id
min["value"] = item.value
cursor.execute(
"DELETE FROM storage WHERE item_id=%s AND user_id = %s " %
(min['_id'], current_user.id))
cursor.execute("INSERT INTO explore VALUES (%s)" % min['_id'])
conn.commit()
return True
else:
return False
def getSubscriptions(idUser):
sql = "SELECT idThread FROM Subscription WHERE idUser = %s"
cursor.execute(sql, [idUser])
subscriptions = getArrayEmailsFromDoubleDictionary(cursor.fetchall())
logging.info(" Subscriptions of USER (" + str(idUser) + ") : " + str(subscriptions))
return subscriptions
def find_all(self):
try:
sql = AllQuery.find_all(self.table_name(self))
cursor.execute(sql)
results = cursor.fetchall()
return results
except ConnectionError as error:
pass
def find_where(self, fields):
try:
sql = AllQuery.find_where(self.table_name(self), fields)
cursor.execute(sql)
result = cursor.fetchall()
return result
except ConnectionError as error:
pass
def show_applications():
sql = "SELECT * FROM main_table WHERE email = '%s'" % (
session['email']) #checking if user is already there in database
cursor.execute(sql)
results = cursor.fetchall()
return render_template('show_application.html',
rows=results,
email_=session['email'])
def query_all_crimes(zip_=None):
"""get all crimes by zip from crime table"""
if zip_ is not None:
q_str = f"""SELECT * FROM "Crime" WHERE zip={zip_} ;"""
else: # else get all the data
q_str = f"""SELECT * FROM "Crime" ;"""
cursor.execute(q_str)
return cursor.fetchall()
def part1():
if (request.method == 'POST'): #modifying the existing application
session['application_number'] = request.form['application_no']
sql = "SELECT * FROM main_table WHERE application_no = '%s';" % (
session['application_number'])
cursor.execute(sql)
rows = cursor.fetchall()
rows = list(rows[0])
if rows[2] is None:
return render_template(
'application_part1.html',
email_=session['email'],
application_number=session['application_number'])
name_list = rows[3][1:-1].split(",")
print rows
params_ = [
rows[2], name_list, rows[9], rows[11], rows[12], rows[15], rows[16],
rows[8], rows[13], rows[4], rows[14]
]
print "retrieved properly"
sql = "SELECT freeze_status FROM main_table WHERE application_no = '%s';" % (
session['application_number'])
cursor.execute(sql)
freeze_rows = cursor.fetchall()
if freeze_rows[0][
0] == "true": # if application is freezed there should be no option to submit
return render_template(
'application_readonly_freezed_part1.html',
email_=session['email'],
params=params_,
application_number=session['application_number'])
elif rows[
1] == 'submitted': # if application is submitted the form must be read only
return render_template(
'application_readonly_part1.html',
email_=session['email'],
params=params_,
application_number=session['application_number'])
return render_template('application_placeholders_part1.html',
email_=session['email'],
params=params_,
application_number=session['application_number'])
def songs_page():
query = '''SELECT songs.id, songs.name, bands.name, albums.name, songs.price
FROM songs
INNER JOIN bands ON songs.band_id = bands.id
INNER JOIN albums ON songs.album_id = albums.id;
'''
cursor.execute(query)
data = cursor.fetchall()
return render_template('songs.html', title='songs', data=data)
def insert(self, fields):
try:
sql = AllQuery.insert(self.table_name(self), fields)
cursor.execute(sql)
results = cursor.fetchall()
conn.commit()
return results
except ConnectionError as error:
pass
def threadsList():
tic = time()
# logging.info("=====================================THREAD LIST BEGIN============================================")
try:
user = request.args.get("user")
#logging.info("User : "******"forum")
#logging.info("Forum : " + forum)
except:
forum = None
if not user and not forum:
return json.dumps({"code": 2, "response": error_messages[2]})
limit = getOptionalGetParameterOrDefault(request.args, "limit", None)
order = getOptionalGetParameterOrDefault(request.args, "order", "desc")
since = getOptionalGetParameterOrDefault(request.args, "since", None)
sql = "SELECT * FROM Thread WHERE 1 = 1 "
params = []
if user:
sql = sql + " AND idAuthor = %s"
idAuthor = getUserIdByEmail(user)
params.append(idAuthor)
if forum:
sql = sql + " AND idForum = %s"
idForum = getForumIdByShortName(forum)
params.append(idForum)
if since:
sql = sql + " AND DATE(date) >= %s" #TODO optimizate date query
params.append(since)
sql = sql + " ORDER BY date " + order
if limit:
sql = sql + " LIMIT " + str(limit)
# logging.info("FINAL SQL = " + sql)
#logging.info("FINAL PARAMS = " + str(params))
cursor.execute(sql, params)
data = cursor.fetchall()
answer = []
for item in data:
answer.append(getThreadDetailsByID(item[0], []))
response = json.dumps({"code": 0, "response": answer})
# logging.info("Response : ")
#logging.info(response)
#logging.info("=====================================THREAD LIST END============================================")
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print(MyTime, "/db/api/thread/list/")
return response
def orders_page():
query = '''SELECT orders.created_at,
customers.full_name, songs.name, songs.price
FROM ordered_songs
INNER JOIN songs ON ordered_songs.song_id = songs.id
INNER JOIN orders ON ordered_songs.order_id = orders.id
INNER JOIN customers ON orders.customer_id = customers.id
ORDER BY orders.created_at DESC;'''
cursor.execute(query)
data = cursor.fetchall()
return render_template('orders.html', title='Orders', data=data)
def threadsList():
tic = time()
# logging.info("=====================================THREAD LIST BEGIN============================================")
try:
user = request.args.get("user")
#logging.info("User : "******"forum")
#logging.info("Forum : " + forum)
except:
forum = None
if not user and not forum:
return json.dumps({"code": 2, "response": error_messages[2]})
limit = getOptionalGetParameterOrDefault(request.args, "limit", None)
order = getOptionalGetParameterOrDefault(request.args, "order", "desc")
since = getOptionalGetParameterOrDefault(request.args, "since", None)
sql = "SELECT * FROM Thread WHERE 1 = 1 "
params = []
if user:
sql = sql + " AND idAuthor = %s"
idAuthor = getUserIdByEmail(user)
params.append(idAuthor)
if forum:
sql = sql + " AND idForum = %s"
idForum = getForumIdByShortName(forum)
params.append(idForum)
if since:
sql = sql + " AND DATE(date) >= %s" #TODO optimizate date query
params.append(since)
sql = sql + " ORDER BY date " + order
if limit:
sql = sql + " LIMIT " + str(limit)
# logging.info("FINAL SQL = " + sql)
#logging.info("FINAL PARAMS = " + str(params))
cursor.execute(sql, params)
data = cursor.fetchall()
answer = []
for item in data:
answer.append(getThreadDetailsByID(item[0], []))
response = json.dumps({"code": 0, "response": answer})
# logging.info("Response : ")
#logging.info(response)
#logging.info("=====================================THREAD LIST END============================================")
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/thread/list/")
return response
def storage():
if not current_user.is_authenticated:
return redirect(url_for('login'))
items = []
cursor.execute(
"select items.id, items.name, items.type, items.value, items.rare\
from items, users, storage\
where items.id=storage.item_id and users.id=storage.user_id and users.id=%s"
% current_user.id)
res = cursor.fetchall()
for i in res:
item = Item(i)
items.append(item)
return render_template('storage.html', items=items)
def explore():
global users_dic_explore
if not current_user.is_authenticated:
return redirect(url_for('login'))
cursor.execute("select *\
from users, armors\
where users.id=armors.user_id and users.id=%s" %
current_user.id)
res = cursor.fetchall()
if len(res) == 0:
flash("未装备铠甲！")
return redirect(url_for('index'))
else:
if users_dic_explore[current_user.id] == 1:
defense = 0
for i in res:
armor = Item(i)
defense = defense + armor.value
rare = rare_list[weight_choice(weight_count(defense))]
cursor.execute(
"SELECT id,name,type,value,rare FROM items,explore WHERE id=item_id AND rare=%s"
% rare)
res = cursor.fetchall()
item = random.choice(res)
item = Item(item)
cursor.execute(
"INSERT INTO storage (user_id,item_id) VALUES (%s,%s)" %
(current_user.id, item.id))
is_full()
cursor.execute("DELETE FROM explore WHERE item_id=%s" % item.id)
conn.commit()
users_dic_explore[current_user.id] = 0
flash("获得" + item.name + "！")
return redirect(url_for('index'))
else:
flash("未到探索时间！")
return redirect(url_for('index'))
def get(self):
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT Host, User FROM mysql.user")
data = cursor.fetchall()
if(len(data)>0):
return {'status':200, 'data':data}
else:
return {'status':100, 'message':'Authentication failure'}
except Exception as e:
return {'error': str(e)}
def bands_page():
query = '''SELECT bands.name,
bands.date_of_est,
bands.image,
bands.link_wiki,
genres.name,
countries.name
FROM bands
INNER JOIN genres ON bands.genre_id = genres.id
INNER JOIN countries ON bands.country_id = countries.id
ORDER BY bands.id;
'''
cursor.execute(query)
data = cursor.fetchall()
return render_template('bands.html', title='Bands page', data=data)
def equip_armor(oid):
if not current_user.is_authenticated:
return redirect(url_for('login'))
cursor.execute("select *\
from users, armors\
where users.id=armors.user_id and users.id=%s" %
current_user.id)
res = cursor.fetchall()
if len(res) >= 2:
flash("护甲已满，请卸下再装备")
else:
cursor.execute("insert into armors (user_id,armor_id) values (%s,%s)" %
(current_user.id, oid))
cursor.execute("DELETE FROM storage WHERE item_id=%s AND user_id=%s" %
(oid, current_user.id))
conn.commit()
return redirect(url_for('index'))
def admin_view_part1():
sql = "SELECT application_no, name, position_applied FROM main_table WHERE status = '%s';" % "submitted"
cursor.execute(sql)
rows1 = list(cursor.fetchall())
rows = []
for i in range(len(rows1)):
temp = [
rows1[i][0], rows1[i][1], rows1[i][2],
str(rows1[i][0]) + ".pdf"
]
rows.append(temp)
print rows
return render_template('admin_view_part1.html',
email_=session['email'],
rows=rows)
def userListPosts():
tic = time()
# logging.info("================USER LISTPOSTS================")
try:
user = request.args.get("user")
except:
return json.dumps({"code": 2, "response": error_messages[2]})
limit = getOptionalGetParameterOrDefault(request.args, "limit", None)
order = getOptionalGetParameterOrDefault(request.args, "order", "desc")
since = getOptionalGetParameterOrDefault(request.args, "since", None)
idUser = getUserIdByEmail(user)
if not idUser:
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "SELECT P.* FROM Post P INNER JOIN User U ON P.idAuthor = U.idUser WHERE U.idUser = %s"
params = [idUser]
if since:
params.append(since)
sql += " AND P.date >= %s"
sql += " ORDER BY P.date " + order
if limit:
params.append(int(limit))
sql += " LIMIT %s"
# logging.info(" Final SQL listPosts : " + sql)
# logging.info(" Final PARAMS listPosts : " + str(params))
cursor.execute(sql, params)
result = getArrayDictFromDoubleDictionary(cursor.fetchall())
# logging.info(" Results : ")
response = json.dumps({ "code": 0, "response": result})
# logging.info(" Response : " + response)
# logging.info("================USER LISTPOSTS END============")
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/user/listPosts/")
return response
def getFollowingEmails(idUser, since, order, limit):
sql = "SELECT U.email FROM Follower F INNER JOIN User U ON F.idFollowing = U.idUser WHERE F.idFollower = %s"
params = [idUser]
if since:
sql += " AND U.idUser > %s"
params.append(int(since))
if not order:
order = "desc"
sql += " ORDER BY U.username " + order
if limit:
sql += " LIMIT %s"
params.append(int(limit))
logging.info(" Final SQL followingEmails : " + sql)
logging.info(" Final PARAMS followingEmails : " + str(params))
cursor.execute(sql, params)
emails = getArrayEmailsFromDoubleDictionary(cursor.fetchall())
logging.info(" EMAILS FOLLOWING OF USER (" + str(idUser) + ") : " + str(emails))
return emails
def getListThreadsOfForum(forum, since, order, limit, related):
sql = "SELECT * FROM Thread WHERE idForum = %s"
idForum = getForumIdByShortName(forum)
params = [idForum]
if since is not None:
sql += " AND date >= %s"
params.append(since)
if order is not None:
sql += " ORDER BY date " + order
if limit is not None:
sql += " LIMIT %s"
params.append(int(limit))
logging.info(" Final SQL listThreads : " + sql)
logging.info(" Final PARAMS listThreads : " + str(params))
cursor.execute(sql, params)
dictionary = cursor.fetchall()
return getArrayThreadsFromDDictionary(dictionary, related)
def getListUsersOfForum(forum, since, order, limit):
from Forum import getForumIdByShortName
sql = "SELECT DISTINCT U.* FROM User U INNER JOIN Post P ON U.idUser = P.idAuthor WHERE P.idForum = %s"
idForum = getForumIdByShortName(forum)
params = [idForum]
if since:
sql += " AND U.idUser >= %s"
params.append(int(since))
logging.info(" order = " + order)
sql += " ORDER BY U.name " + order
if limit:
sql += " LIMIT %s"
params.append(int(limit))
logging.info(" Final SQL followerEmails : " + sql)
logging.info(" Final PARAMS followerEmails : " + str(params))
cursor.execute(sql, params)
array = getArrayUsersFromDoubleDictionary(cursor.fetchall())
logging.info(" LIST USERES OF FORUM (" + str(idForum) + ") : " + str(array))
return array
def getListPostsOfThread(thread, since, order, limit, sort='flat'):
#logging.info(" GETTING LIST POSTS BY THREAD")
sql = "SELECT * FROM Post WHERE idThread = %s"
params = [thread]
if since:
sql += " AND date >= %s"
params.append(since)
sql += " ORDER BY date "
if sort == 'flat':
sql += order
if limit:
sql += " LIMIT %s"
params.append(int(limit))
#logging.info(" Final SQL listPosts : " + sql)
#logging.info(" Final PARAMS listPosts : " + str(params))
cursor.execute(sql, params)
result = cursor.fetchall()
answer = getArrayPostsFormDDictionary(result, [])
if sort in {'tree', 'parent_tree'}:
cats = {}
for x in answer:
if x['parent'] is None:
try:
cats['root'].append(x)
except KeyError:
cats['root'] = [x]
else:
try:
cats[x['parent']].append(x)
except:
cats[x['parent']] = [x]
limit = int(limit)
if sort == 'parent_tree':
cats['root'] = cats['root'][:limit]
if order == 'desc':
cats['root'].reverse()
result = []
root_h = ['root']
while cats[root_h[-1]]:
curr_ell = cats[root_h[-1]][0]
result.append(curr_ell)
del cats[root_h[-1]][0]
if curr_ell['id'] in cats and cats[curr_ell['id']]:
root_h.append(curr_ell['id'])
elif not cats[root_h[-1]]:
while root_h and not cats[root_h[-1]]:
root_h = root_h[:-1]
if not root_h:
break
if sort == 'tree' and len(result) >= limit:
break
answer = result
#logging.info(" GETTED POSTS : ")
#logging.info(answer)
# logging.info(" ==============")
return answer
def get_relevant_games(id1,id2): q = query.form_query(id1,id2) cursor.execute(q); data = cursor.fetchall() return data
