def listar_disciplinas():
comando = 'SELECT d.*, p.perfilNome FROM disciplina d JOIN perfil p ON d.perfil = p.codigo'
conexao = mysql.connect()
cursor = conexao.cursor()
cursor.execute(comando)
retorno = criar_json(cursor)
return json.dumps(retorno)
def getPatientData():
conn = mysql.connect()
cursor = conn.cursor()
dictionary = request.form
token = dictionary["token"]
if hasInvalidCharacters(token):
return 'error'
patient_id = dictionary["patientID"]
if not nonNegativeFloat([patient_id]):
return 'error'
cursor.execute("SELECT * FROM tokens WHERE code='%s' AND deleted=0;" % token)
data = cursor.fetchone()
if data is None:
return jsonify({"status": "error", "error": "Invalid token."})
cursor.close()
conn.close()
csvString = createCSV(patient_id)
response = flask.jsonify({"status": "ok", "data": csvString})
response.headers.add('Access-Control-Allow-Origin', '*')
return response
def createPatient():
if request.method == 'POST':
conn = mysql.connect()
cursor = conn.cursor()
json = request.get_json()
id = json["id"]
if not nonNegativeFloat([id]):
return 'error'
cursor.execute("SELECT * from patients WHERE id=%s;", (id,))
data = cursor.fetchone()
currentDate = getDateTime()
if data is None:
cursor.execute('INSERT INTO patients (id, date_updated, date_created) VALUES (%s, "%s", "%s");' % (str(id), currentDate, currentDate))
else:
return jsonify({"status": "ok", "created": "false"})
conn.commit()
cursor.close()
conn.commit()
return jsonify({"status": "ok", "created": "true"})
def remover_perfil(codigo):
conexao = mysql.connect()
cursor = conexao.cursor()
comando = 'DELETE FROM perfil WHERE codigo = ' + str(codigo)
cursor.execute(comando)
conexao.commit()
return jsonify({'sucesso': True, 'mensagem': 'alor'})
def fetch_all_plants_email(user_email):
try:
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
# fetch config match user_email
cursor.execute("SELECT * FROM plants WHERE user_email = %s",
user_email)
user = cursor.fetchall()
response = jsonify(user)
print(response)
# if user_name not found
if user == None:
print('Could not find username', user)
response.status_code = 404
else:
response.status_code = 200
return response
except:
print('Could not fetch user', user)
finally:
connection.close()
cursor.close()
def get_latest_time(id, time):
try:
# POSTMAN requirements:
'''
GET Request: The `time` argument must be converted from a string to a formatted time string to be queried
Datetime object format: '2020-04-30 04:10:38'
Formatted to: '2020-04-30+04%3A10%3A38'
For all colons (:) in string, it must be replaced with: %3A
For all whitespace in string, it must be replaced with: +
Above is a custom Python function, def convert_time_format(date), which takes in a datetime object
And returns an appropriate string for querying; however, this must be done on client-side
'''
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(
"SELECT * FROM make_requests WHERE plant_id = %s AND timestamp >= %s",
(id, time))
rows = cursor.fetchall()
response = jsonify(rows)
return response
except:
print('Could not FETCH request id or timestamp')
finally:
connection.close()
cursor.close()
def updaterank():
conn = mysql.connect()
cursor = conn.cursor()
semester = session['new_semester']
S = StudentRank('cgpa', session['year'], session['course'],
session['branch'], semester)
result = S.get_failed_once()
for stu in result:
cursor.execute(
"UPDATE nilekrator$ADMIN.USERS SET rank=0 WHERE roll='{}'".format(
stu[0]))
S.get_raw_rank()
for stu in S.get_original_rank(200):
cursor.execute(
"UPDATE nilekrator$ADMIN.USERS SET rank={} WHERE roll='{}'".format(
stu[-1], stu[0]))
conn.close()
return make_response(jsonify(message="Update Successfull"), 200)
def listar_perfis():
comando = 'SELECT * FROM perfil'
conexao = mysql.connect()
cursor = conexao.cursor()
cursor.execute(comando)
retorno = criar_json(cursor)
return json.dumps(retorno)
def payment():
if 'email' not in session:
return redirect(url_for('loginForm'))
loggedIn, firstName, noOfItems = getLoginDetails()
email = session['email']
with mysql.connect('dbTrade') as conn:
cur = conn.cursor()
cur.execute("SELECT userId FROM users WHERE email = '" + email + "'")
userId = cur.fetchone()[0]
cur.execute(
"SELECT products.productId, products.name, products.price, products.image FROM products, cart WHERE products.productId = cart.productId AND cart.userId = "
+ str(userId))
products = cur.fetchall()
totalPrice = 0
for row in products:
totalPrice += row[2]
print(row)
cur.execute("INSERT INTO Orders (userId, productId) VALUES (?, ?)",
(userId, row[0]))
cur.execute("DELETE FROM kart WHERE userId = " + str(userId))
conn.commit()
return render_template("checkout.html",
products=products,
totalPrice=totalPrice,
loggedIn=loggedIn,
firstName=firstName,
noOfItems=noOfItems)
def getPatientLocationData():
conn = mysql.connect()
cursor = conn.cursor()
dictionary = request.form
username = dictionary["usernameInput"]
password = dictionary["passwordInput"]
password = hashlib.sha224(password.encode()).hexdigest()
if hasInvalidCharacters(username) or hasInvalidCharacters(password):
print("error in users")
return 'error'
cursor.execute("SELECT * FROM users WHERE username = %s;", (username,))
userData = cursor.fetchone()
if userData is not None:
print("inside if statement")
patient_id = dictionary["patientIDInput"]
print("id:", patient_id)
if not nonNegativeFloat([patient_id]):
return 'error'
createLocationCSV(patient_id)
cursor.close()
conn.close()
print("before return")
return send_from_directory(app.static_folder, "temp.csv")
def fetch_all_plants():
try:
# Connect to MySQL instance
connection = mysql.connect()
# pymysql cursors that returns results as a dictionary
# Cursor objects allows users to execute queries per row
# Result is stored in the cursor object
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM plants")
# .fetchall() retrieves a JSON object from the cursor object
# Can also use `list(cursor)`, but .fetchall() returns a list OR an empty tuple if list is empty
rows = cursor.fetchall()
# Creates a response with the JSON representation
response = jsonify(rows)
response.status_code = 200
# Return response as a JSON object
return response
except:
print('An exception occurred')
finally:
# Close the MySQL instance and cursor object when done
connection.close()
cursor.close()
def remover_disciplina(codigo):
conexao = mysql.connect()
cursor = conexao.cursor()
comando = 'DELETE FROM disciplina WHERE codigo = "' + codigo + '"'
cursor.execute(comando)
conexao.commit()
return jsonify({'sucesso': True, 'mensagem': 'alor'})
def fetch_plant(id):
if request.method == 'GET':
try:
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
# Query for a single plant matching 'id', %s is a placeholder of string type
cursor.execute("SELECT * FROM plants WHERE plant_id = %s", id)
single_plant = cursor.fetchone()
response = jsonify(single_plant)
# print (single_plant)
# If plant_id is not found, error 404
if single_plant == None:
print('Could not fetch plant with id', id)
response.status_code = 404
# Else, plant_id is found, return response object
else:
response.status_code = 200
# Test to assert that response returned it JSON format
# assert response.content_type == 'application/json'
# data = json.loads(response.get_data(as_text=True))
# assert data['plant_id'] == 2
return response
except:
print('Could not fetch a plant with id')
finally:
connection.close()
cursor.close()
elif request.method == 'DELETE':
try:
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute("DELETE FROM plants WHERE plant_id = %s", id)
connection.commit()
return "OK"
except:
print('Could not delete plant')
finally:
connection.close()
cursor.close()
else:
return "Nothing"
def atualizar_perfil():
retorno = json.loads(request.data)
conexao = mysql.connect()
cursor = conexao.cursor()
comando = 'UPDATE perfil SET nome = "' + retorno[
'nome'] + '", abreviacao = "' + retorno[
'abreviacao'] + '" WHERE codigo = ' + str(retorno['codigo'])
cursor.execute(comando)
return jsonify({'sucesso': True, 'mensagem': 'alor'})
def get(a):
user = a
cur = mysql.connect().cursor()
cur.execute("select * from mugsy.brewSettings WHERE userID=(%s)", user)
r = [
dict((cur.description[i][0], value) for i, value in enumerate(row))
for row in cur.fetchall()
]
return {'brewSettings': r}
def get_all_latest(id):
try:
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
except:
print('Could not FETCH request id')
finally:
connection.close()
cursor.close()
def get(a):
coffeeTypeId = a
cur = mysql.connect().cursor()
cur.execute("select * from mugsy.coffeeTypes WHERE coffeeTypeId=(%s)",
coffeeTypeId)
r = [
dict((cur.description[i][0], value) for i, value in enumerate(row))
for row in cur.fetchall()
]
return {'brewSettings': r}
def atualizar_disciplina():
requisicao = json.loads(request.data)
conexao = mysql.connect()
cursor = conexao.cursor()
comando = 'UPDATE disciplina SET nome = "' + requisicao['nome'] + '", perfil = ' + str(
requisicao['perfil']) + ', chTeorica = ' + str(requisicao['chTeorica']) + ', chPratica = ' + str(
requisicao['chPratica']) + ' WHERE codigo = "' + requisicao['codigo'] + '"'
cursor.execute(comando)
conexao.commit()
return jsonify({'sucesso': True, 'mensagem': 'alor'})
def post_latest():
# Grab current time in mysql datetime format
now = time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime())
plant_id = request.json['plant_id']
timestamp = request.json['timestamp']
category = request.json['category']
# Check `category` is valid; else return
if category != 'water' and category != 'light' and category != 'picture':
print(
"Incorrect category - please choose 'water', 'light', or 'picture'"
)
return
# POSTMAN requirements:
'''
HEADERS: Key: Content-Type, Value: application/json
BODY: raw
'''
# Sample body:
# Note: "timestamp" field should be an empty string since backend Python will take care of datetime
'''
{
"plant_id": 5,
"timestamp": "",
"category": "water"
}
'''
# INSERT query and fields to insert
sqlQuery = "INSERT INTO make_requests(plant_id, category, timestamp) VALUES (%s, %s, %s)"
recordTuple = (plant_id, category, now)
data = {"plant_id": plant_id, "timestamp": now, "category": category}
try:
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(sqlQuery, recordTuple)
# Commit record to database so new record persists
connection.commit()
return jsonify(data)
except:
print('Could not POST a new request')
return (
'Request failed: ensure that the plant exists and the plant_id matches with the `plants` table or category is valid'
)
finally:
connection.close()
cursor.close()
def update_plant(id):
try:
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
# Query table by the `id`
# Return singleRecord and access the JSON data via dictionary pattern likeso in date_created
cursor.execute("SELECT * FROM plants where plant_id=%s", id)
singleRecord = cursor.fetchone()
date_created = singleRecord["date_created"]
user_email = request.json['user_email']
plant_name = request.json['plant_name']
species = request.json['species']
curr_photo = request.json['curr_photo']
serial_port = request.json['serial_port']
position = request.json['position']
water_threshold = request.json['water_threshold']
light_threshold = request.json['light_threshold']
uri = email_to_uri(user_email)
data = {
"user_email": user_email,
"plant_name": plant_name,
"species": species,
"uri": uri,
"curr_photo": curr_photo,
"serial_port": serial_port,
"position": position,
"water_threshold": water_threshold,
"light_threshold": light_threshold,
"date_created": date_created
}
recordTuple = (user_email, plant_name, species, uri, curr_photo,
serial_port, position, water_threshold, light_threshold,
id)
sqlQuery = "UPDATE plants SET user_email=%s, plant_name=%s, species=%s, uri=%s, curr_photo=%s, serial_port=%s, position=%s, water_threshold=%s, light_threshold=%s WHERE plant_id=%s"
cursor.execute(sqlQuery, recordTuple)
connection.commit()
response = jsonify('Plant updated succesfully!', data)
response.status_code = 200
return response
except:
return ('Not found')
finally:
connection.close()
cursor.close()
def downloader():
conn = mysql.connect()
cursor = conn.cursor()
# info of downloaded and not downloaded semesters and their student results
cursor.execute("SELECT * FROM nilekrator$ADMIN.DOWNLOADS")
download_info = cursor.fetchall()
return render_template("scraper/scraper.html",
title="Web Scraper",
branch_name_map=BRANCH_NAME,
download_info=download_info,
random=randint(1, 10000000))
def updateTokens():
threading.Timer(300, updateTokens, []).start()
conn = mysql.connect()
cursor = conn.cursor()
timeAgo = timestampXMinutesAgo(5)
cursor.execute('SELECT * FROM tokens WHERE deleted=0 AND timestamp <=%s' %
timeAgo)
results = cursor.fetchall()
for result in results:
cursor.execute('UPDATE tokens SET deleted=1 WHERE id=%s' % result[0])
conn.commit()
cursor.close()
conn.close()
print('updated tokens')
def adicionar_disciplina():
token = 'abcdf'
if token == request.headers.get('Authorization'):
requisicao = json.loads(request.data)
conexao = mysql.connect()
cursor = conexao.cursor()
comando = 'INSERT INTO disciplina (codigo, nome, perfil, chTeorica, chPratica) VALUES ("' + requisicao[
'codigo'] + '","' + requisicao['nome'] + '",' + str(requisicao['perfil']) + ',' + str(
requisicao['chTeorica']) + ', ' + str(requisicao['chPratica']) + ')'
cursor.execute(comando)
conexao.commit()
return jsonify({'sucesso': True, 'mensagem': 'alor'})
else:
return jsonify({'sucesso': False, 'mensagem': 'Erro ao validar usuario'})
def college_home():
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SHOW DATABASES")
result = cursor.fetchall()
batch = []
for res in result:
if res[0].startswith('nilekrator$_2'):
batch.append(res[0][12:])
conn.close()
return render_template('college/collegehome.html', title="College | TOPPERS", batch=batch, ROMAN_MAP=ROMAN_MAP)
def fetch_all_id(id):
try:
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
# Fetch all logs
cursor.execute("SELECT * FROM logs WHERE plant_id=%s", id)
rows = cursor.fetchall()
response = jsonify(rows)
response.status_code = 200
return response
except:
print("Could not fetch all logs from database")
finally:
connection.close()
cursor.close()
def createLocationCSV(patientID):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT * from location_logs WHERE patient_id=%s;" % patientID)
logs = cursor.fetchall()
with open('static/temp.csv', 'w') as csvfile:
fieldnames = ["Timestamp", "Latitude", "Longitude"]
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writeheader()
for log in logs:
writer.writerow({'Timestamp': log[
2], 'Latitude': log[3], 'Longitude': log[4]})
cursor.close()
conn.close()
def visitors():
try:
# connect to mysql
connection = mysql.connect()
cursor = connection.cursor()
cursor.execute('''SELECT DISTINCT name FROM visitors''')
result = cursor.fetchall()
unique_names = []
for entry in result:
record = entry[0]
unique_names.append(record)
except Exception as e:
return json.dumps({'error': str(e)})
return render_template('visitors.html', names=unique_names)
def fetch_log(id):
try:
connection = mysql.connect()
cursor = connection.cursor(pymysql.cursors.DictCursor)
# Query for the latest log entry from a user id based on timestamp
cursor.execute(
"SELECT t1.* FROM logs AS t1 WHERE t1.timestamp = (SELECT MAX(t2.timestamp) FROM logs AS t2 WHERE t2.plant_id = t1.plant_id) HAVING plant_id = %s", id)
single_request = cursor.fetchone()
response = jsonify(single_request)
return response
except:
return('Could not FETCH logs id')
finally:
connection.close()
cursor.close()
def year(year):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT name, degree FROM nilekrator$_{year}.CONF WHERE session IS NULL AND scheme IS NULL ORDER BY degree".format(
year=year
))
DEGREE = set()
result = cursor.fetchall()
for branch, degree in result:
DEGREE.add(degree)
conn.close()
return render_template('college/batch.html', title = year+" | TOPPERS", DEGREE=DEGREE, result=result, year=year, ROMAN_MAP=ROMAN_MAP, BRANCH_NAME=BRANCH_NAME)
def createCSV(patient_id):
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT * from logs WHERE patient_id=%s;" % patient_id)
logs = cursor.fetchall()
cursor.execute(
"SELECT * from location_logs WHERE patient_id=%s ORDER BY timestamp ASC;" %
patient_id)
locationLogs = cursor.fetchall()
cursor.execute(
"SELECT * from heart_rate_logs WHERE patient_id=%s ORDER BY timestamp ASC;" %
patient_id)
heartRateLogs = cursor.fetchall()
cursor.execute(
"SELECT * from step_count_logs WHERE patient_id=%s ORDER BY timestamp ASC;" %
patient_id)
stepCountLogs = cursor.fetchall()
cursor.close()
conn.close()
logsString = "Timestamp,Pain,Percentage\n"
for log in logs:
logsString += str(log[2]) + ',' + str(log[3]) + \
',' + str(log[4]) + '\n'
locationLogsString = "Timestamp,Latitude,Longitude\n"
for log in locationLogs:
locationLogsString += str(log[2]) + ',' + \
str(log[3]) + ',' + str(log[4]) + '\n'
heartRateLogsString = "Timestamp,Heart Rate\n"
for log in heartRateLogs:
heartRateLogsString += str(log[2]) + ',' + str(log[3]) + '\n'
stepCountLogsString = "Timestamp, Steps\n"
for log in stepCountLogs:
stepCountLogsString += str(log[2]) + ',' + str(log[3]) + '\n'
return {
"logs": logsString,
"locationLogs": locationLogsString,
"heartRateLogs": heartRateLogsString,
"stepCountLogs": stepCountLogsString}
