def push_Data(field, data):
''' push data to sql'''
cnx = sql_sb.connect_sql()
cursor = cnx.cursor()
sql_sb.connect_db(cursor)
cursor.execute(field, data)
cnx.commit()
cursor.close()
cnx.close()
def d_vs_S(query_dr):
'''fetches date and sleep from daily_routine'''
cnx = sql_sb.connect_sql()
cursor = cnx.cursor()
sql_sb.connect_db(cursor)
cursor.execute(query_dr)
d = []
s = []
for i in cursor.fetchall():
d.append(np.datetime64(i[0]))
s.append(int(i[1]))
cursor.close()
cnx.close()
return d, s
def d_vs_oh(query_dr):
'''this function fetches qualtiy of health and date from quality index'''
cnx = sql_sb.connect_sql()
cursor = cnx.cursor()
sql_sb.connect_db(cursor)
cursor.execute(query_dr)
d = []
q = []
for i in cursor.fetchall():
d.append(np.datetime64(i[0]))
q.append(int(i[1]))
cursor.close()
cnx.close()
return d, q
def d_vs_qos(query_dr):
'''fetches date and quality of sleep from quality index'''
cnx = sql_sb.connect_sql()
cursor = cnx.cursor()
sql_sb.connect_db(cursor)
cursor.execute(query_dr)
d = []
q = []
for i in cursor.fetchall():
d.append(np.datetime64(i[0]))
q.append(int(i[1]))
cursor.close()
cnx.close()
return d, q
def pro_plot():
'''plots project infromation'''
query_dr = "SELECT PROJECT_NAME,HOURS_SPENT from project"
cnx = sql_sb.connect_sql()
cursor = cnx.cursor()
sql_sb.connect_db(cursor)
cursor.execute(query_dr)
d = []
q = []
for i in cursor.fetchall():
d.append((i[0]))
q.append(int(i[1]))
cursor.close()
cnx.close()
plt.bar(d, q)
plt.show()
def create_tables(tables):
cnx = sql_sb.connect_sql()
if cnx is None:
print("unable to execute connect_sql")
else:
cursor = cnx.cursor()
sql_sb.connect_db(cursor)
for t_names in tables:
t_dis = tables[t_names]
try:
#print('Creating table {}'.format(t_names),end=" ")
cursor.execute(t_dis)
except mysql.connector.Error as err:
if err.err == errorcode.ER_TABLE_EXISTS_ERROR:
print("table already exists.")
else:
print(err.msg)
cursor.close()
cnx.close()
def project_insert():
'''inserts data into project table'''
name, hours = insert_data.project()
print(name, hours)
cnx = sql_sb.connect_sql()
cursor = cnx.cursor()
sql_sb.connect_db(cursor)
p_id, check = check_pid(cursor, name)
if check:
query = ("UPDATE project SET HOURS_SPENT=HOURS_SPENT + %s ")
cursor.execute(query, (hours, ))
cnx.commit()
cnx.close()
cursor.close()
else:
data = [p_id, name, hours]
add_e = ("INSERT INTO project"
"(PROJECT_ID,PROJECT_NAME,HOURS_SPENT)"
"VALUES(%(PROJECT_ID)s,%(PROJECT_NAME)s,%(HOURS_SPENT)s)")
data_list = ['PROJECT_ID', 'PROJECT_NAME', 'HOURS_SPENT']
dic_data = {data_list[i]: data[i] for i in range(len(data_list))}
push_Data(add_e, dic_data)