def query_edit(id):
if request.method == "GET":
query = db.get_query_details(id)
return render_template("edit_query.html", query=query)
elif request.method == "POST":
try:
id = request.form["id"].strip()
title = request.form["title"].strip()
sql = request.form["sql"].strip()
tags = request.form["tags"].strip()
desc = request.form["desc"].strip()
who = request.form["who"].strip()
if len(title) == 0 or len(sql) == 0:
flash("Title and Sql are required fields", "error")
return redirect(url_for("query_edit", id=id))
db.update_query(id, title, sql, tags, desc, who)
flash("Query Modified!", "success")
return redirect(url_for("query_view", id=id))
except Exception as e:
app.logger.error("Fatal error", exc_info=True)
flash("Fatal error. Contact Administrator", "error")
return redirect(url_for("index"))
def edit_task(*args):
query = "UPDATE problems.tasks " \
"SET name = %s, topic = %s, difficulty = %s, statement = %s, solution = %s, answer = %s " \
"WHERE task_id=%s"
params = [args[i] for i in range(1, 7)]
params.append(args[0])
return db.update_query(query, params)
def update_data():
try:
query = "UPDATE DEPARTMENT SET Manager_ssn=%s, Manager_start_date=STR_TO_DATE(%s,'%d-%b-%Y') WHERE Dnumber=%s"
# 1
data = ('333445555', '22-MAY-1978', 5)
print(db.update_query(query, data))
logging.info(str(query) + ' ' + str(data))
# 2
data = ('987654321', '01-JAN-1985', 4)
print(db.update_query(query, data))
logging.info(str(query) + ' ' + str(data))
# 3
data = ('888665555', '19-JUN-1971', 1)
print(db.update_query(query, data))
logging.info(str(query) + ' ' + str(data))
# 4
data = ('111111100', '15-MAY-1999', 6)
print(db.update_query(query, data))
logging.info(str(query) + ' ' + str(data))
# 5
data = ('444444400', '15-MAY-1998', 7)
print(db.update_query(query, data))
logging.info(str(query) + ' ' + str(data))
# 6
data = ('555555500', '01-JAN-1997', 8)
print(db.update_query(query, data))
logging.info(str(query) + ' ' + str(data))
# 7
data = ('112244668', '01-FEB-1989', 9)
print(db.update_query(query, data))
logging.info(str(query) + ' ' + str(data))
# 8
data = ('110110110', '15-MAY-2009', 3)
print(db.update_query(query, data))
logging.info(str(query) + ' ' + str(data))
# 9
data = ('913323708', '2-FEB-2010', 11)
print(db.update_query(query, data))
logging.info(str(query) + ' ' + str(data))
except Exception as e:
traceback.print_exc()
logging.error('Error in UPDATE_DATA: %s', e)
def q4_7():
try:
query = "UPDATE WORKS_ON SET Hours=%s WHERE Pno=%s AND Essn=%s"
data = (25, 1, '666884444')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
except Exception as e:
traceback.print_exc()
logging.error('Error in INSERT_DATA: %s', e)
def q4_5():
try:
query = "UPDATE EMPLOYEE SET Salary=%s WHERE SSN=%s"
data = (55000, '444444444')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
except Exception as e:
traceback.print_exc()
logging.error('Error in INSERT_DATA: %s', e)
def q4_4():
try:
query = "UPDATE DEPT_LOCATIONS SET Dnumber=%s WHERE Dlocation=%s"
data = (9, 'Seattle')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
except Exception as e:
traceback.print_exc()
logging.error('Error in INSERT_DATA: %s', e)
def add_to_cart(task_id, login):
if in_cart(task_id, login):
return True
else:
query = "INSERT INTO problems.cart (task_id, login) VALUES (%s,%s)"
params = [task_id, login]
db_response = json.loads(db.update_query(query, params))
if db_response['code'] == 0:
return True
else:
return False
def remove_from_cart(task_id, login):
query = "DELETE FROM problems.cart WHERE login=%s"
params = [
login,
]
if task_id is not None:
query += " AND task_id=" + task_id
resp = json.loads(db.update_query(query, params))
if resp['code'] == 0:
return True
else:
return False
def add_user(*args):
if user_exists(args[0]):
return json.dumps({"code": 3, "message": "Логин занят"})
query = "INSERT INTO problems.users(login, password, name, surname, role) " \
"VALUES (%s, %s, %s, %s, %s)"
params = [i for i in args]
print(params)
db_response = json.loads(db.update_query(query, params))
if db_response['code'] == 0:
return json.dumps({"code": 0, "message": "Успешная регистрация"})
else:
return json.dumps({"code": 4, "message": db_response["message"]})
def check_task(task_id, login, answer):
query = "SELECT answer FROM problems.tasks WHERE task_id=%s"
params = [task_id,]
db_response = json.loads(db.select_query(query, params))
if db_response['code'] != 0:
return json.dumps({'code': 2})
reference = db_response['rows'][0][0]
if answer == reference:
if user_has_task(task_id, login):
return json.dumps({'code': 3})
else:
query = "INSERT INTO problems.users_tasks(login, task_id) VALUES (%s, %s)"
params = [login, task_id]
db_response = json.loads(db.update_query(query, params))
if db_response['code'] == 0:
return json.dumps({'code': 0})
else:
return json.dumps({'code': 2})
else:
return json.dumps({'code': 1})
def add_task(*args):
query = "INSERT INTO problems.tasks(name, topic, difficulty, statement, solution, answer) " \
"VALUES (%s, %s, %s, %s, %s, %s)"
params = [i for i in args]
return db.update_query(query, params)
def delete_task(task_id):
query = "DELETE FROM problems.tasks WHERE task_id = %s"
params = [task_id,]
return db.update_query(query, params)
def update_data():
try:
query = "UPDATE EMPLOYEE SET Supervisor=%s WHERE SSN=%s"
null = None
# 1
data = (null, '888665151')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 2
data = ('888665555', '333445555')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 3
data = ('888665555', '987654321')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 4
data = (null, '111111100')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 5
data = (null, '444444400')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 6
data = (null, '555555500')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 7
data = ('333445555', '123456789')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 8
data = ('987654321', '999887777')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 9
data = ('333445555', '666884444')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 10
data = ('333445555', '453453453')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 11
data = ('987654321', '987987987')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 12
data = ('111111100', '111111101')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 13
data = ('111111100', '111111102')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 14
data = ('111111100', '111111103')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 15
data = (null, '222222200')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 16
data = ('222222200', '222222201')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 17
data = ('222222200', '222222202')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 18
data = ('222222200', '222222203')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 19
data = ('222222201', '222222204')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 20
data = ('222222201', '222222205')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 21
data = (null, '333333300')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 22
data = ('333333300', '333333301')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 23
data = ('444444400', '444444401')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 24
data = ('444444400', '444444402')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 25
data = ('444444400', '444444403')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 26
data = ('555555500', '555555501')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 27
data = (null, '666666600')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 28
data = ('666666600', '666666601')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 29
data = ('666666600', '666666602')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 30
data = ('666666601', '666666603')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 31
data = ('666666603', '666666604')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 32
data = ('666666600', '254937381')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 33
data = ('666666603', '666666605')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 34
data = ('666666604', '666666606')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 35
data = ('666666602', '666666607')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 36
data = ('666666602', '666666608')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 37
data = ('666666602', '666666609')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 38
data = ('666666607', '666666610')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 39
data = ('666666610', '666666611')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 40
data = ('666666611', '666666612')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 41
data = ('666666612', '666666613')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 42
data = ('666666600', '999999999')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 43
data = ('555555501', '222333444')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 44
data = ('666666613', '223344667')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 45
data = ('999999999', '444222666')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 46
data = (null, '112244668')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 47
data = ('444444400', '343434344')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 48
data = ('112244668', '234234234')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 49
data = ('555555500', '242535609')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 50
data = ('111111100', '110110110')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 51
data = ('222333444', '673466642')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 52
data = ('666666612', '636669233')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 53
data = ('888665555', '849934919')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 54
data = ('555555501', '202843824')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 55
data = ('666666613', '292740167')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 56
data = ('444444400', '444212096')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 57
data = (null, '101248268')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 58
data = ('444444400', '349273344')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 59
data = ('112244668', '245239264')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 60
data = ('555555500', '242916639')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 61
data = ('987987987', '111422203')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 62
data = (null, '913323708')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 63
data = ('444444401', '614370310')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 64
data = ('444444402', '432765098')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 65
data = ('444444403', '913353347')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 66
data = ('444444400', '001614905')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 67
data = ('999999999', '214370999')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 68
data = ('123456789', '241625699')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 69
data = (null, '398172999')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
# 70
data = ('999999999', '906218888')
print(db.update_query(query, data))
logging.info(str(query) + " " + str(data))
except Exception as e:
traceback.print_exc()
logging.error('Error in UPDATE_DATA: %s', e)
import db
db = db.Database()
with open('create_db.sql') as query_file:
content = query_file.read()
db.update_query(content, {})
def updateTodos(todo_id):
(_id, name, completed) = select_one_query(todo_id)
update_query(todo_id, not completed)
results = select_query()
return make_response(results), 201
def change_role(login, role):
query = "UPDATE problems.users SET role = %s WHERE login = %s"
params = [role, login]
resp = json.loads(db.update_query(query, params))
return json.dumps(resp)
