def records_all():
conn = dbconnector.connect_db()
try:
with conn.cursor() as cursor:
sql = """
SELECT doc_id, doc_slices from doc_models;
"""
cursor.execute(sql)
result = cursor.fetchall()
return result
except Exception:
print("Unexpected error:", sys.exc_info())
finally:
conn.close()
def create_account_insert(user_input):
conn = dbconnector.connect_db()
try:
with conn.cursor() as cursor:
print(user_input)
un = user_input.get('username')
pw = user_input.get('password')
ut = user_input.get('usertype')
sql = "insert into user_log_info (username,password,usertype) VALUES (\"%s\",\"%s\",\"%s\")" % (
un, pw, ut)
result = cursor.execute(sql)
conn.commit()
return result
finally:
conn.close()
def record_by_id(record_id):
conn = dbconnector.connect_db()
try:
with conn.cursor() as cursor:
sql = """
SELECT result from user_history WHERE record_id = %s;
"""
cursor.execute(sql, (record_id))
result = cursor.fetchall()
print(result)
return result
except Exception:
print("Unexpected error:", sys.exc_info())
finally:
conn.close()
def log_in_data(user_input):
conn = dbconnector.connect_db()
try:
with conn.cursor() as cursor:
sql = "select * from user_log_info"
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
if row.get('username') == user_input.get('username'):
if row.get('password') == user_input.get('password'):
if row.get('usertype') == user_input.get('usertype'):
print(row)
return True
else:
return False
finally:
conn.close()
def record_search(date_strings):
if not date_strings[0]:
date_strings[0] = '2019/01/01'
if not date_strings[1]:
date_strings[1] = datetime.datetime.now().strftime('%Y/%m/%d')
print('in date string: ', date_strings)
conn = dbconnector.connect_db()
try:
with conn.cursor() as cursor:
sql = """
SELECT * from user_history WHERE unix_timestamp(cal_time) BETWEEN unix_timestamp(%s)
and unix_timestamp(%s);
"""
cursor.execute(sql, (date_strings[0], date_strings[1]))
result = cursor.fetchall()
return result
except Exception:
print("Unexpected error:", sys.exc_info())
finally:
conn.close()
def result_insert(result):
if not result:
return False
conn = dbconnector.connect_db()
try:
with conn.cursor() as cursor:
curr_time = datetime.datetime.now().strftime('%Y/%m/%d')
username = session['username']
usertype = session['usertype']
record_id = str(uuid.uuid4())
print(dumps({"result": result}))
sql = """insert into user_history (record_id,username,usertype,cal_time,result) VALUES (%s,%s,%s,%s,%s)"""
cursor.execute(
sql,
(record_id, username, usertype, str(curr_time), dumps(result)))
conn.commit()
return record_id
except Exception:
print("Unexpected error:", sys.exc_info())
finally:
conn.close()
def file_cut_insert(file_cut):
conn = dbconnector.connect_db()
try:
with conn.cursor() as cursor:
curr_time = datetime.datetime.now().strftime('%Y/%m/%d')
username = session['username']
usertype = session['usertype']
wait_to_insert = []
for i in file_cut:
wait_to_insert.append(
(str(uuid.uuid4()), ','.join(i.get('seg')), str(curr_time),
username, usertype))
sql = "insert into doc_models (doc_id,doc_slices,collect_time,username,usertype) VALUES " \
"(%s,%s,%s,%s,%s)"
result = cursor.executemany(sql, wait_to_insert)
conn.commit()
return result
except Exception:
print("Unexpected error:", sys.exc_info())
finally:
conn.close()