def resetMaster(): """ Function to reset/create master table to store default categorization preferences Creates an "old" and a "new" table that are identical except for the value of is_old Inputs: None Output: None """ conn = psycopg2.connect(dbname=DBNAME, user=AUSER, password=DBPASSWORD, host=HOST) # Create the Cursor object c = conn.cursor() # Delete the table if it exists c.execute(""" DROP TABLE IF EXISTS master """) # Delete the changelogs table c.execute(""" DROP TABLE IF EXISTS changelog """) # Create table c.execute(""" CREATE TABLE master (Key TEXT, PLAID_Values TEXT, is_old BOOLEAN) """) # Check if table is empty empty_query = """ SELECT count(*) FROM master """ # "New" default entries in master table dict_to_sql(current_dict=cats_dict, is_master=True, is_old_custom=True, c=c) # "Old" default entries in master table dict_to_sql(current_dict=cats_dict, is_master=True, is_old_custom=False, c=c) # commit changes (if any) conn.commit() # close connection conn.close() from DB.userDB import updateUsers updateUsers(cats_dict) return 0
def getUser(user_id): """ Function that checks if the user is currently in the users table If not in the table: Pulls from master to create their preferences and returns them as a dict If in the table, pulls their preferences and returns them as a dict Inputs: Unique user id Output: A dict of the user's preferences """ conn = psycopg2.connect(dbname=DBNAME, user=AUSER, password=DBPASSWORD, host=HOST) c = conn.cursor() find_user = (f""" SELECT * FROM users WHERE user_id = {user_id} """) c.execute(find_user) a_user = c.fetchall() # if the user already exists in the DB, grab whats currently there if a_user != []: # Query the master table for the keys and save them to val query1 = f""" SELECT Key from users WHERE user_id = {user_id} """ # Query the master table for the strings that contain the lists of # values separated by '/' query2 = f""" SELECT PLAID_Values from users WHERE user_id = {user_id} """ new_dict = sql_to_dict(query1=query1, query2=query2, c=c) conn.close() return new_dict # If the user doesn't exist in the DB, provide it the master dict else: current_dict = masterPull() dict_to_sql(current_dict=current_dict, is_master=False, is_old_custom=False, c=c, user_id=user_id) conn.commit() conn.close() return current_dict
def updateUsers(new_dict: dict): """ Function that takes in the newest default preferences, and replaces the preferences of users that are using the defaults Inputs: Dictionary of new defaults Outputs: None """ conn = psycopg2.connect(dbname=DBNAME, user=AUSER, password=DBPASSWORD, host=HOST) c = conn.cursor() modify_check = """ SELECT DISTINCT user_id FROM users WHERE is_custom = FALSE """ c.execute(modify_check) modified = c.fetchall() if modified == []: return 0 user_ids = [] for tup in modified: for value in tup: user_ids.append(value) delete_query = """ DELETE FROM users WHERE is_custom = False """ c.execute(delete_query) for user in user_ids: dict_to_sql(current_dict=new_dict, is_master=False, is_old_custom=False, c=c, user_id=user) conn.commit() conn.close() return 0
def reset_user_cats(self): conn = psycopg2.connect(dbname=DBNAME, user=AUSER, password=DBPASSWORD, host=HOST) c = conn.cursor() delete_query = f""" DELETE FROM users WHERE user_id = {self.user_id} """ c.execute(delete_query) current_dict = masterPull() dict_to_sql(current_dict=current_dict, is_master=False, is_old_custom=False, c=c, user_id=self.user_id) conn.commit() conn.close() return f"User {self.user_id} categorical preferences have been reset!"
def updateMaster(old_cat, plaid_cat, destination): """ Function that updates the master table. If there is only an old dict, it inserts the new one. If there is a current old and new, it deletes the old, turns the current new to old, and inserts the new. Inputs: old_cat - the old BB category of the plaid category that needs to be remapped : plaid_cat - the plaid category that needs to be remapped : destination - the new BB category of the plaid category """ # Reformat the plaid_cat back into a comma separated list if "_AND_" in plaid_cat: plaid_cat = plaid_cat.split("_AND_") else: plaid_cat = [plaid_cat] for i, v in enumerate(plaid_cat): plaid_cat[i] = v.replace('_', ' ') # Pull the current defaults old_dict = copy.deepcopy(masterPull()) new_dict = copy.deepcopy(masterPull()) # Exception for if plaid_cats is not in old_BB if plaid_cat not in new_dict[old_cat]: raise HTTPException(status_code=500, detail=f"{plaid_cat} is not in {old_cat}") # Remove the plaid_cat from the old_cat's value list new_dict[old_cat].remove(plaid_cat) # Add the plaid_cat to the destination's value list new_dict[destination].append(plaid_cat) # Query the master table to check if there is a new dict there # Create the Connection object to the 'BudgetBlocks' DB conn = psycopg2.connect(dbname=DBNAME, user=AUSER, password=DBPASSWORD, host=HOST) # Create the Cursor object c = conn.cursor() # Delete the old delete_query = """ DELETE FROM master WHERE is_old = TRUE """ c.execute(delete_query) # Make the current new, old replace_query = """ Update master Set is_old = TRUE """ c.execute(replace_query) # Insert the newly made cats as the new dict dict_to_sql(current_dict=new_dict, is_master=True, is_old_custom=False, c=c) # commit changes (if any) conn.commit() # close connection conn.close() from DB.userDB import updateUsers updateUsers(new_dict) return 0
def changePreferences(self): """ Function to update a user's categorical preferences in the users table Inputs: self - object that has parameters for plaid_cats, old_BB, new_BB, and user_id Outputs: None """ # If the user doesn't already exist then it breaks plaid_cats = self.plaid_cats old_BB = self.old_BB new_BB = self.new_BB user_id = self.user_id conn = psycopg2.connect(dbname=DBNAME, user=AUSER, password=DBPASSWORD, host=HOST) c = conn.cursor() keys = [] values = [] # Query the users table for the keys and save them to val query1 = f""" SELECT Key from users WHERE user_id = {user_id} """ # Query the users table for the strings that contain the lists of # values separated by '/' query2 = f""" SELECT PLAID_Values from users WHERE user_id = {user_id} """ new_dict = sql_to_dict(query1=query1, query2=query2, c=c) # Exception for if plaid_cats is not in old_BB if plaid_cats not in new_dict[old_BB]: raise HTTPException( status_code=500, detail=f"{plaid_cats} is not in {old_BB} for user {user_id}") else: # Remove the plaid_cat from the old_cat's value list new_dict[old_BB].remove(plaid_cats) # Create the new BB category if it doesn't exist if new_BB not in new_dict: new_dict[new_BB] = [] # Add the plaid_cat to the destination's value list new_dict[new_BB].append(plaid_cats) delete_query = f""" DELETE FROM users WHERE user_id = {user_id} """ c.execute(delete_query) # Insert the new dict_to_sql(current_dict=new_dict, is_master=False, user_id=user_id, c=c, is_old_custom=True) conn.commit() conn.close() return 0