def f_data_api(db_file, query):
# Get login details from user
msg = ''
try:
# Connect to database
#db = sqlite3.connect(db_file)
conn = create_connection(db_file)
c = conn.cursor()
# Execute sql statement and grab all records for given user name and password
rows = c.execute(query)
msg = []
for row in rows:
msg.append({
'JOB_ID': row[0],
'JOB_NAME': row[1],
'STATUS': row[2],
'START_DATETIME': row[3],
'END_DATETIME': row[4],
'LAST_UPDATED': row[5],
'ERROR': row[6]
})
conn.close()
except Error as e:
msg = e
return msg
def f_user_validation(user, password, db_file):
# Get login details from user
msg = ''
login_flag = False
try:
# Connect to database
#db = sqlite3.connect(db_file)
conn = create_connection(db_file)
c = conn.cursor()
# Execute sql statement and grab all records for given user name and password
c.execute(
'SELECT * FROM LOGIN_DETAILS WHERE USER_NAME = ? AND PASSWORD = ?',
(user, password))
# If nothing was found then c.fetchone() would be an empty list, which
# evaluates to False
if c.fetchone():
msg = 'Welcome'
login_flag = True
else:
msg = 'Login Failed'
conn.close()
except Error as e:
msg = e
return msg, login_flag
def google_search(search_term, api_key, cse_id, user, **kwargs):
""""
Function to search keyword on google, return the search links and store search keywords in db.
"""
results = []
service = build("customsearch", "v1", developerKey=api_key)
res = service.cse().list(q=search_term, cx=cse_id, **kwargs).execute()
for result in res['items']:
results.append(result["link"])
# search keyword insertion in database
conn = db.create_connection()
with conn:
cursor = conn.cursor()
insert_sql_query = "insert into search_history values(%s,%s)"
cursor.execute(insert_sql_query, (user, search_term))
return results
def get_history(history_keyword, user):
"""
Function to return user search history.
"""
conn = db.create_connection()
with conn:
cursor = conn.cursor()
username = "******" + user + "'"
keyword = "%" + history_keyword + "%"
sql = "select search_keyword from search_history where user=%s and lower(search_keyword) like lower('%s')" % (
username, keyword)
cursor.execute(sql)
results = cursor.fetchall()
if results:
total_hisotry = set([row[0] for row in results])
else:
total_hisotry = 'No such item found'
return total_hisotry
def f_business_checks(module_name, process_name, query, notify_email, db_file):
# Get login details from user
msg = ''
try:
# Connect to database
#db = sqlite3.connect(db_file)
conn = create_connection(db_file)
c = conn.cursor()
# Execute sql statement and grab all records for given user name and password
sql = ''' INSERT INTO B_CHECKS(MODULE_NAME,PROCESS_NAME,QUERY,EMAIL) VALUES(?,?,?,?) '''
cur = conn.cursor()
cur.execute(sql, (module_name, process_name, query, notify_email))
conn.commit()
msg = 'Submitted'
conn.close()
except Error as e:
msg = e
return msg
def f_on_boarding(use_case_name, use_case_admin, desc, db_file):
# Get login details from user
msg = ''
try:
# Connect to database
#db = sqlite3.connect(db_file)
conn = create_connection(db_file)
c = conn.cursor()
# Execute sql statement and grab all records for given user name and password
sql = ''' INSERT INTO ON_BOARDING_DETAILS(USE_CASE_NAME, USE_CASE_ADMIN, DESC) VALUES(?,?,?) '''
cur = conn.cursor()
cur.execute(sql, (use_case_name, use_case_admin, desc))
conn.commit()
msg = 'Registered'
conn.close()
except Error as e:
msg = e
return msg
def f_user_registration(user, password, db_file):
# Get login details from user
msg = ''
try:
# Connect to database
#db = sqlite3.connect(db_file)
conn = create_connection(db_file)
c = conn.cursor()
# Execute sql statement and grab all records for given user name and password
sql = ''' INSERT INTO LOGIN_DETAILS(USER_NAME, PASSWORD) VALUES(?,?) '''
cur = conn.cursor()
cur.execute(sql, (user, password))
conn.commit()
msg = 'Registered'
conn.close()
except Error as e:
msg = e
return msg