def users(): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM tbl_user") rows = cursor.fetchall() table = Results(rows) table.border = True return render_template('users.html', table=table) except Exception as e: print(e) finally: cursor.close() conn.close()
def delete_user(id): try: conn = mysql.connect() cursor = conn.cursor() cursor.execute("DELETE FROM tbl_user WHERE user_id=%s", (id, )) conn.commit() resp = jsonify('User deleted successfully!') resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def user(id): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM users WHERE DEVICE_ID=%s", id) row = cursor.fetchone() resp = jsonify(row) resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def get_bikes(): try: try: _email = request.form['email'] _token = request.form['token'] except Exception as e: print(e) return bad_request() if _email and _token and request.method == 'POST': var = authenticate_email_token(_email, _token) if var: # Close all reserved rides older than 5 minutes sql = f"UPDATE rides SET status = 'canceled' WHERE reserveTimeStamp < (NOW() - INTERVAL 5 MINUTE) AND " \ f"status = 'reserved' " cnx = mysql.connect() cursor = cnx.cursor() cursor.execute(sql) cursor.close() cnx.commit() # Remove reservations from reserved bikes older than 5 minutes sql = f"UPDATE bikes SET status = 'free', reserveTimeStamp = NULL, currentRideID = NULL WHERE " \ f"reserveTimeStamp < (NOW() - INTERVAL 5 MINUTE) AND status = 'reserved' " cursor = cnx.cursor() cursor.execute(sql) cursor.close() cnx.commit() sql = f"SELECT bikes.bikeID, bikes.currentStationID, bikes.status, stations.stationName FROM bikes, " \ f"stations, users WHERE users.email = '{_email}' AND bikes.currentStationID = " \ f"stations.stationID AND stations.domain = users.domain " cursor = cnx.cursor() cursor.execute(sql) bike_list = [] for row in cursor: bike_list.append(row) res = jsonify(bike_list) res.status_code = 200 return res # If POST is empty else: return forbidden() except Exception as e: print(e) return internal_server_error()
def delete_user(id): try: conn = mysql.connect() cursor = conn.cursor() cursor.execute("DELETE FROM users WHERE DEVICE_ID=%s", (id, )) conn.commit() resp = jsonify('Attention !!! DATA Deleted') resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def users(): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM users") rows = cursor.fetchall() resp = jsonify(rows) resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def eliminar(cod): try: conn = mysql.connect() cursor = conn.cursor() cursor.execute("DELETE FROM articulos WHERE codigo=%s", (cod, )) conn.commit() resp = jsonify('Artículo eliminado') resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def article(cod): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM articulos WHERE codigo=%s", cod) row = cursor.fetchone() resp = jsonify(row) resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def index(): if request.method == "POST": details = request.form firstName = details['fname'] lastName = details['lname'] conn = mysql.connect() cursor = conn.cursor() cursor.execute( "INSERT INTO MyUsers(firstName, lastName) VALUES (%s, %s)", (firstName, lastName)) conn.commit() cursor.close() return 'success' return render_template('index.html')
def high_value_addr(): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT max(amount),hash from kafkadata GROUP BY hash;") rows = cursor.fetchall() resp = jsonify(rows) resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def setTrainData(self): # self.train_arr = trainData1 conn = mysql.connect() cursor = conn.cursor() cursor.execute("SELECT mri_id,contrast, energy,entropy,homogeneity,correlation,result FROM mri_classifications_data WHERE data_type LIKE 'Training%' ") row = cursor.fetchall() trainData = [] for x in row: trainData.append({"index":x[0],"contrast":x[1],"energy":x[2],"entropy":x[3],"homogeneity":x[4],"correlation":x[5],"result":x[6]},) # print(type(x[2])) # print(row) # print(trainData) self.train_arr = trainData
def process(): _fullname = request.form['fullname'] _username = request.form['username'] _email = request.form['email'] _password = request.form['password'] _repassword = request.form['repassword'] # check username in db conn = mysql.connect() cursor = conn.cursor() cursor.execute("SELECT * FROM user where username=%s or email=%s",(_username,_email)) row = cursor.fetchone() if row: flash('Maaf username atau email sudah digunakan','info') return redirect('/signup') else: # print("berhasil") if _fullname and _username and _email and _password == _repassword and request.method == 'POST': _hashed_password = hashlib.md5(_password.encode()) sql = "INSERT INTO user (name,username,email,password,type_user_id) VALUES (%s,%s,%s,%s,%s)" data = (_fullname,_username,_email,_hashed_password.hexdigest(),'2',) conn = mysql.connect() cursor = conn.cursor() cursor.execute(sql,data) conn.commit() flash('Register successfully','success') return redirect('/signup') elif _password != _repassword and request.method =='POST': flash('Password tidak sama','danger') return redirect('/signup') else: flash('Register failed','danger') return redirect('/signup') cursor.close() conn.close()
def delete_bens(id): 'Excluir cadastro de bem a partir do ID.' if request.method == "POST": try: _id = id if _id: sql = "DELETE FROM db_inquest.tb_bens WHERE bens_id=%s" data = (_id) conn = mysql.connect() cursor = conn.cursor() cursor.execute(sql, data) conn.commit() return redirect(url_for("bens")) else: return not_found() except Exception as e: print(e) finally: cursor.close() conn.close() elif id == 0: return render_template("/bens/bens_buscar_id.html") else: try: _id = id conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM db_inquest.tb_bens WHERE bens_id = %s", id) rows = cursor.fetchall() return render_template('/bens/bens_excluir.html', rows = rows) except Exception as e: print(e) finally: cursor.close() conn.close()
def put_file(): # If there is no filename in the json PUT request, # or no json at all, then return a 400 (Bad Request) HTTP error code. if not request.json or not 'filename' in request.json: return create_JSON_response(400, "Bad request.") SECONDS_IN_HOUR = 3600 DECIMAL_PLACES = 2 filename = request.json['filename'] response = None conn = None cursor = None try: conn = mysql.connect() cursor = conn.cursor() cursor.execute(f"SELECT filename FROM 3dprints WHERE filename='{filename}'") filename_data = cursor.fetchall() # Check if the file already exsits in the database. # If it does, update the start datetime and increment # the print count by 1. Otherwise, create a new record.. if len(filename_data) > 0: update_file_data(conn, cursor, filename) response = create_JSON_response(200, f"{filename} updated successfully.") elif not 'lastPrintTime' in request.json: # 'lastPrintTime' is needed to create a new record in 3dprints. return create_JSON_response(400, "Bed request. 'lastPrintTime' was not provided in request.json.") else: print_hours = round(request.json['lastPrintTime'] / SECONDS_IN_HOUR, DECIMAL_PLACES) sql = "INSERT INTO 3dprints(filename, print_count, print_time) VALUES(%s, %s, %s)" data = (filename, 1, print_hours) cursor.execute(sql, data) new_id_3dprint = cursor.lastrowid conn.commit() add_datetime(conn, cursor, new_id_3dprint) response = create_JSON_response(200, f"{filename} added successfully.") return response except Exception as e: traceback.print_exc(file=sys.stderr) return create_JSON_response(500, MSG_500) finally: if cursor: cursor.close() if conn: conn.close()
def delete_garagem(id): conn = mysql.connect() cursor = conn.cursor() try: cursor.execute("DELETE FROM garagem WHERE id_garagem=%s", (id, )) conn.commit() resp = jsonify('registo apagado com sucesso!') resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def garagem(id): conn = mysql.connect() cursor = conn.cursor() try: cursor.execute("SELECT * FROM garagem WHERE id_garagem=%s", id) row = cursor.fetchone() resp = jsonify(row) resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def delete_tipo(id): conn = None cursor = None try: conn = mysql.connect() cursor = conn.cursor() cursor.execute("DELETE FROM Tipo WHERE id=%s", (id, )) conn.commit() flash('Género Eliminado Exitosamente!') return redirect('/Tipo') except Exception as e: print(e) finally: cursor.close() conn.close()
def delete_area(id): conn = None cursor = None try: conn = mysql.connect() cursor = conn.cursor() cursor.execute("DELETE FROM Area WHERE id=%s", (id, )) conn.commit() flash('Area Eliminada Exitosamente!') return redirect('/Area') except Exception as e: print(e) finally: cursor.close() conn.close()
def bens_por_id(id): 'Consultar bens cadastrados pelo ID.' try: _id = id conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM db_inquest.tb_bens WHERE bens_id = %s", id) rows = cursor.fetchall() return render_template('/bens/bens.html', rows = rows) except Exception as e: print(e) finally: cursor.close() conn.close()
def fetch_skills(naam): skills = {} conn = mysql.connect() cur = conn.cursor(pymysql.cursors.DictCursor) cur1 = conn.cursor(pymysql.cursors.DictCursor) cur.execute("Select * from resume_skills Where user_id = '" + str(naam['user_id']) + "';") if cur.rowcount > 0: for r in cur: cur1.execute("Select title from skills Where skill_id = '" + str(r['skill_id']) + "';") k = cur1.fetchone() skills[k['title']] = r['level'] return skills return 'empty'
def fetch_pd(naam): conn = mysql.connect() cur = conn.cursor(pymysql.cursors.DictCursor) cur.execute("Select * from resume Where user_id = '" + str(naam['user_id']) + "';") records = cur.fetchall() op = {} if len(records) > 0: for r in records: op['email'] = r['email'] op['location'] = r['location'] op['phone_no'] = r['phone_no'] op['additional_details'] = r['additional_details'] return op return 'empty'
def delete_user(id): conn = None cursor = None try: conn = mysql.connect() cursor = conn.cursor() cursor.execute("DELETE FROM tbl_user WHERE user_id=%s", (id, )) conn.commit() flash('User deleted successfully!') return redirect('/') except Exception as e: print(e) finally: cursor.close() conn.close()
def cliente_por_id(id): # Consultar cliente pelo ID try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute( "SELECT * FROM db_desafio.tb_clientes WHERE cliente_id = %s", id) rows = cursor.fetchall() resp = jsonify(rows) resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def excluir_pedido(id): # Excluir pedido try: conn = mysql.connect() cursor = conn.cursor() cursor.execute("DELETE FROM db_desafio.tb_pedidos WHERE pedido_id=%s", (id, )) conn.commit() resp = jsonify('Pedido excluido com sucesso!') resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def clienteTest_por_id(id): # Consultar cliente pelo ID try: _id = id conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute( "SELECT * FROM db_desafio.tb_clientes WHERE cliente_id = %s", id) rows = cursor.fetchall() return render_template('clientes.html', rows=rows) except Exception as e: print(e) finally: cursor.close() conn.close()
def patient(): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) id = request.json['id'] cursor.execute("SELECT * FROM patient WHERE pat_id=%s", id) row = cursor.fetchone() resp = jsonify(row) resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def delete_user(id): conn=None cursor=None try: conn=mysql.connect() cursor=conn.cursor() cursor.execute("DELETE FROM tbl_user WHERE user_id=%s",(id,)) conn.commit() flash("User deleted successfully!") return redirect(url_for('users')) except Exception as e: return(str(e)) finally: cursor.close() conn.close()
def meaning(): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) word = request.json['word'] cursor.execute("SELECT meaning FROM conversion WHERE word=%s", word) row = cursor.fetchone() resp = jsonify(row) resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()
def edit_view(id): try: conn = mysql.connect() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute("SELECT * FROM ad_table WHERE ad_id=%s", id) row = cursor.fetchone() if row: return render_template('EditPost.html', row=row) else: return 'Error loading #{id}'.format(id=id) except Exception as e: print(e) finally: cursor.close() conn.close()
def delete_patient(): try: conn = mysql.connect() cursor = conn.cursor() id = request.json['id'] cursor.execute("DELETE FROM patient WHERE pat_id=%s", id) conn.commit() resp = jsonify('PATIENT deleted successfully') resp.status_code = 200 return resp except Exception as e: print(e) finally: cursor.close() conn.close()