def get_metric():
agg_metrics = []
for trait in [
'openness', 'agreeableness', 'emotional_stability',
'conscientiousness', 'extraversion'
]:
mean_data = (c.execute(
'''SELECT avg({}) FROM PersonalityData'''.format(
trait)).fetchall())
var_data = (c.execute(
'''SELECT AVG({} * {}) - AVG({}) * AVG({}) as variance FROM PersonalityData'''
.format(trait, trait, trait, trait)).fetchall())
median_data = (c.execute('''SELECT AVG({})
FROM (SELECT {} FROM PersonalityData
ORDER BY {} LIMIT 2 - (SELECT COUNT(*) FROM PersonalityData) % 2
OFFSET (SELECT (COUNT({}) - 1) / 2 FROM PersonalityData))'''.
format(trait, trait, trait,
trait)).fetchall())
agg_metrics.append({
'name': trait,
'mean': round(mean_data[0][0], 2),
'variance': round(var_data[0][0], 2),
'median': round(median_data[0][0], 2)
})
response = jsonify(agg_metrics)
response.headers.add('Access-Control-Allow-Origin', '*')
return response
def login_user():
data = request.get_json() or {}
if data['email'] == '':
return bad_request("Please provide email.")
elif data['password'] == '':
return bad_request("Please provide password.")
else:
jwt_token = create_access_token(identity={'email': data['email']})
db_password_res = (c.execute(
"SELECT PASSWORD FROM USERS WHERE email == '{}'".format(
data['email']))).fetchone()
if db_password_res is None:
return bad_request("User with this email not registered.")
else:
db_password = db_password_res[0]
if data['password'] == db_password:
c.execute(
"UPDATE users SET rand_str = '{}', logged_in = 1 WHERE email == '{}'"
.format(jwt_token, data['email']))
conn.commit()
response = jsonify({'token': jwt_token})
response.headers.add('Access-Control-Allow-Origin', '*')
else:
return bad_request("Invalid password.")
return response
def create_table_from_csv(dataset):
file_name = ''
if dataset == 1:
file_name = 'winequality_white.csv'
elif dataset == 2:
file_name = 'beetle_richness.csv'
elif dataset == 3:
file_name = 'breast_cancer.csv'
with open(file_name) as csvfile:
reader = csv.DictReader(csvfile, delimiter=",") #for breast_cancer.csv, change delimiter to ,
table_name = file_name.split('.')[0]
if dataset == 1:
command = ''' CREATE TABLE IF NOT EXISTS winequality_white_short (
id integer primary key, fixed_acidity real, volatile_acidity real,
residual_sugar real, free_sulfur_dioxide real,
total_sulfur_dioxide real, density real,
pH real, alcohol real, quality real
) '''
elif dataset == 2:
command = ''' CREATE TABLE IF NOT EXISTS beetle_richness_short (
id text primary key, Latitude real,
Mean_Temp_degC real, Mean_Ann_Precip_mm real,
Mean_Canopy_Height_m real,
Small_Mammal_Richness real, Beetles_Richness real
) '''
elif dataset == 3:
command = ''' CREATE TABLE IF NOT EXISTS breast_cancer (
id integer primary key, clump_thickness integer,
uniformity_of_cell_size integer, uniformity_of_cell_shape integer,
marginal_adhesion integer, single_epithelial_cell_size integer,
bare_nuclei integer, bland_chromatin integer,
normal_nucleoli integer, mitoses integer, class integer
)'''
c.execute(command) #create table
for row in reader:
vals = get_values_str(reader, row)
if dataset == 1:
command = 'INSERT INTO winequality_white_short VALUES (' + vals + ')'
elif dataset == 2:
vals = ''.join(['"'+ vals[:4] + '"', vals[4:]])
command = 'INSERT INTO beetle_richness_short VALUES (' + vals + ')'
elif dataset == 3:
command = 'INSERT INTO breast_cancer VALUES (' + vals + ')'
try:
c.execute(command) #insert values into table
except:
pass
conn.commit()
def get_id_by_phone(phone):
try:
c.execute("SELECT * FROM users WHERE phone='{}'".format(phone))
result = c.fetchone()
except conn.Error as e:
logger.error("Database Error %s", e)
return None
if not result:
logger.warning("There isn't any client with such phone %s", phone)
return None
return result[0]
def check_funds(sender, amount):
try:
c.execute(
"select balance from accounts where user_id='{}'".format(sender))
balance = c.fetchone()[0]
if balance - amount >= 0:
return True
else:
return False
except conn.Error as e:
logger.error("Database Error %s", e)
return None
def get_current(sender):
try:
c.execute(
"select balance from accounts where user_id='{}'".format(sender))
balance = c.fetchone()[0]
return balance
except conn.Error as e:
logger.error("Database Error %s", e)
return None
except TypeError as e:
logger.error("TypeError Error %s. No account for user %s", e, sender)
return None
def transfer(sender, receiver, amount):
try:
# stored procedure locates in test_data.sql
c.execute("select transfer('{}', '{}', {}, {})".format(
sender, receiver, amount, max_on_account))
result = c.fetchone()
conn.commit()
return result
except conn.Error as e:
logger.error("Database Error %s", e)
conn.rollback()
return None
def get_histogram_data(trait):
histogram_data = ((c.execute(
'''SELECT {}, Count(*) from PersonalityData GROUP BY {}'''.format(
trait, trait))).fetchall())
histogram_data = tuple([count[1] for count in histogram_data])
response = jsonify({'histogram_data': histogram_data})
response.headers.add('Access-Control-Allow-Origin', '*')
return response
def get_aggregated_traits():
aggregated_traits = (c.execute(
'''SELECT round(avg(openness),2), round(avg(agreeableness),2), round(avg(emotional_stability),2),
round(avg(conscientiousness),2), round(avg(extraversion),2)
FROM PersonalityData;''').fetchone())
response = jsonify({'user_traits': aggregated_traits})
response.headers.add('Access-Control-Allow-Origin', '*')
return response
def check_login_credentials(login, password):
try:
c.execute(
"SELECT id, password, salt from users WHERE login='******'".format(
login))
result = c.fetchone()
except conn.Error as e:
logger.error("Database Error %s", e)
return None
if not result:
logger.warning("There isn't any client with such username %s", login)
return None
# password stores as salted hashes
hash_pwd = md5((password + result[2]).encode()).hexdigest()
if hash_pwd == result[1]:
return result[0]
else:
logger.warning("Wrong password provided for user %s", login)
return False
def get_personality_traits(email):
user_traits = (c.execute(
'''SELECT openness, agreeableness, emotional_stability, conscientiousness, extraversion
FROM PersonalityData inner join Users on
PersonalityData.user_id = Users.user_id
WHERE email == '{}' '''.format(
email)).fetchone())
response = jsonify({'user_traits': user_traits})
response.headers.add('Access-Control-Allow-Origin', '*')
return response
def get_db_data_json(dataset):
table_name = ''
if dataset == 1:
table_name = 'winequality_white_short'
elif dataset == 2:
table_name = 'beetle_richness_short'
elif dataset == 3:
table_name = 'breast_cancer'
#add different dataset conditions later
result = c.execute('SELECT * FROM {}'.format(table_name))
records = [dict(zip([key[0] for key in c.description], row)) for row in result]
return json.dumps({'records' : records})
def register_user():
data = request.get_json() or {}
users_with_the_same_email = (c.execute(
"SELECT COUNT(*) FROM USERS WHERE email == '{}'".format(
data['email'])).fetchone())[0]
if len([item for item in data.values() if item == '']) > 1:
return bad_request("Please fill all fields.")
print(validate_email(data['email']), file=sys.stderr)
if validate_email(data['email']) == False:
return bad_request('Invalid email')
if users_with_the_same_email > 0:
return bad_request("Email already exists in database.")
else:
data = convert_data_types(data)
register_data_valid = validate_register_data(data)
if register_data_valid[0] == True:
user_id = ''.join(
random.choice(string.ascii_lowercase + string.digits)
for _ in range(32))
jwt_token = create_access_token(identity={'email': data['email']})
last_seen = datetime.datetime.now().strftime("%Y-%m-%d-%H-%M-%S")
user_query = '''INSERT INTO USERS (user_id, first_name, last_name, email, password, rand_str, last_seen, logged_in)
VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}') '''.format(
user_id, data['first_name'], data['last_name'], data['email'],
data['password'], jwt_token, last_seen, True)
c.execute(user_query)
personality_query = '''INSERT INTO PersonalityData (user_id, openness, agreeableness, emotional_stability,
conscientiousness, extraversion)
VALUES ('{}', '{}', '{}', '{}', '{}', '{}') '''.format(
user_id, data['openness'], data['agreeableness'],
data['emotional_stability'], data['conscientiousness'],
data['extraversion'])
c.execute(personality_query)
conn.commit()
response = jsonify({'token': jwt_token})
else:
return bad_request(register_data_valid)
return response
def logout_user(email):
c.execute(
"UPDATE users SET logged_in = 0 WHERE email == '{}'".format(email))
conn.commit()
response = jsonify({'message': 'logut'})
return response