def create_table_users(): from fup.models.sqlquery import sql_create_table_users, sql_user_first_use, sql_delete_default_admin from fup.utils.dbwrap import execute_query, get_dftable try: execute_query(sql_create_table_users) user_df = get_dftable('users') rows, _ = user_df.shape if rows == 0: return execute_query(sql_user_first_use) elif rows > 1: return execute_query(sql_delete_default_admin) return True except Exception as e: print("Users table error: ", e) return False
def sql_insertDict(tableName, infoaddDict): from fup.utils.dbwrap import execute_query processedInfo = {} for k, val in infoaddDict.items(): if isinstance(val, str): templi = [] templi.append(val) val = [str(v) for v in templi] processedInfo[k] = ','.join(list(set(val))) else: try: val = [str(v) for v in val] processedInfo[k] = ','.join(list(set(val))) except: processedInfo[k] = str(val) columns = tuple(processedInfo.keys()) values = tuple(processedInfo.values()) sql_insert = """INSERT INTO {} {} VALUES {};""" insert = sql_insert.format(tableName, columns, values) #print(insert) return execute_query(insert)
def update_tbcell(table_name, coltoUpdate, colValtoUpdate, colID, rowID): from fup.utils.dbwrap import execute_query update_batch = """UPDATE "{}" SET "{}"="{}" WHERE "{}"="{}";""".format( table_name, coltoUpdate, colValtoUpdate, colID, rowID) if execute_query(update_batch) == True: return True else: return False
def add_user(useremail, userpassword, user_right, defaultProofreader): from fup.utils.dbwrap import execute_query from fup.models.sqlquery import insert_user if len(defaultProofreader) == 0: defaultProofreader = "UNASSIGNED" insert_user = insert_user.format(useremail, userpassword, user_right, defaultProofreader) return execute_query(insert_user)
def checkOverallStatus(): #if responsible and proofreader status are ASSIGNED set OverallStatus to ASSIGNED from fup.helpers.batch import checkAssignStatus from fup.utils.dbwrap import execute_query setOverallBatchToAssignedli = checkAssignStatus() update_followup_overallstatus = """UPDATE followup SET OverallStatus="{}" WHERE BatchID="{}";""" if len(setOverallBatchToAssignedli) != 0: for batch in setOverallBatchToAssignedli: if execute_query(update_followup_overallstatus.format("ASSIGNED", batch)) == True: pass else: return False return True else: return True
def sql_updateDict(tableName, updatedict, colIDName): from fup.utils.dbwrap import execute_query colsvals = [] for col, val in updatedict.items(): if col != colIDName: sqval = col + '=' + "'{}'".format(val) colsvals.append(sqval) else: whereCol_value = col + '=' + "'{}'".format(val) colstoUpdate = ', '.join(colsvals) sql_update = str("UPDATE " + '{}'.format(tableName) + " SET " + colstoUpdate + " WHERE " + whereCol_value + ";") return execute_query(sql_update)
def create_table_fileshistory(): from fup.utils.dbwrap import execute_query from fup.models.sqlquery import create_table_fileshistory return execute_query(create_table_fileshistory)
def create_table_followup(): from fup.utils.dbwrap import execute_query from fup.models.sqlquery import create_table_followup return execute_query(create_table_followup)
def sql_deleteRow(table, colID, rowID): from fup.utils.dbwrap import execute_query delete_row = """DELETE FROM {} WHERE {}='{}' """.format( table, colID, rowID) return execute_query(delete_row)
def remove_user(UserEmail): from fup.utils.dbwrap import execute_query delete_user = """DELETE FROM users WHERE UserEmail='{}' """.format( UserEmail) return execute_query(delete_user)