import mysql.connector import pandas as pd import os debr_databases = ["debriefing_left", "debriefing_right"] print("Connect to database") mydb = mysql.connector.connect( host="localhost", user="root", password="tangram", database="experiment", port = 3306 ) print("truncate tables") mycursor = mydb.cursor() mycursor.execute("TRUNCATE TABLE silhouette") mycursor.execute("TRUNCATE TABLE debriefing_left") mycursor.execute("TRUNCATE TABLE debriefing_right") mycursor.execute("TRUNCATE TABLE minesweeper_silhouette") mycursor.execute("TRUNCATE TABLE association_learning_trials") mycursor.execute("TRUNCATE TABLE navigation_trials") mydb.commit() # print("Manually add row to navigation table") # mycursor.execute("INSERT INTO navigation (session_id, trial, stim_left, stim_right, solution, selected, show_length, all_RTs) VALUES (999999, 99, 10, 10, '20', '30', 1, '[2,1.01]');") # mycursor.execute("INSERT INTO navigation {"session_id":999999,"trial":0,"stim_left":2,"stim_right":0,"solution":[1],"selected":[2,"RTs",[2,1.01]],"all_RTs":[2,1.01],"show_length":true};") # mydb.commit() # print("Add variable to navigation table") # mycursor.execute("ALTER TABLE navigation ADD all_RTs text") # mydb.commit() for d in [f for f in os.listdir("Stims") if os.path.isdir(os.path.join("Stims", f))]: path = os.path.join("Stims", d) for num in [f for f in os.listdir(path) if os.path.isdir(os.path.join(path,f))]: stimset = d+"_"+num stimset = stimset.replace("_", "") print("\n\n\n ### Stimset: ", stimset, " ###\n") print("Read csv files") if len([f for f in os.listdir(os.path.join(path,num)) if f.endswith(".csv")]) ==0: print("skip") continue silhouettes = pd.read_csv(os.path.join(path,num,"Block_coordinates.csv"), header=None, sep="\n") trial_types = pd.read_csv(os.path.join(path,num,"Trialtype.csv"), sep="\n") debr_left = pd.read_csv(os.path.join(path,num,"Block_coordinates_debrief_left.csv"), header=None, sep="\n") debr_right = pd.read_csv(os.path.join(path,num,"Block_coordinates_debrief_right.csv"), header=None, sep="\n") debr_csvs = [debr_left,debr_right] debr_trialtypes = pd.read_csv(os.path.join(path,num,"debrief_trial.csv"), sep="\n") minesweeper_silhouettes = pd.read_csv(os.path.join(path,num,"Block_coordinates_minesweeper.csv"), header=None, sep="\n") minesweeper_solutions = pd.read_csv(os.path.join(path,num,"Block_coordinates_minesweeper_occl.csv"), header=None, sep="\n") # if exists minesweeper_trialtype = None has_minesweeper_trial_type = False if os.path.isfile(os.path.join(path,num,"Stim_trial_minesweeper.csv")): minesweeper_trialtype = pd.read_csv(os.path.join(path,num,"Stim_trial_minesweeper.csv"), sep="\n") has_minesweeper_trial_type = True ### Silhouettes ### print("\nSilhouettes") val = [] current_trial = 0 coordinate_string = "[" for i, solution in enumerate(silhouettes[0][1:]): array = solution.split(",") x = str(int(array[0])) y = str(int(array[1])) id = array [2] trial = int(array[3]) if trial != current_trial: coordinate_string = coordinate_string[:-1] + "]" val.append((current_trial,coordinate_string,str(trial_types["Trialtype"][current_trial]),stimset)) #print((current_trial,coordinate_string,str(trial_types["Trialtype"][current_trial]),stimset)) coordinate_string = "[" current_trial = trial coordinate_string+="{\"x\":"+x+", \"y\":"+y+", \"id\":"+id+"}," # last entry coordinate_string = coordinate_string[:-1] + "]" val.append((current_trial,coordinate_string,str(trial_types["Trialtype"][current_trial]),stimset)) #print((current_trial,coordinate_string,str(trial_types["Trialtype"][current_trial]),stimset)) print("Insert ",len(val), "solutions") sql = "INSERT INTO silhouette (silhouette_id,solution,trial_type, stimset) VALUES (%s, %s, %s, %s)" mycursor.executemany(sql, val) mydb.commit() ### Debriefing ### print("\nDebriefing") for idx, csv in enumerate(debr_csvs): val = [] current_trial = 0 coordinate_string = "[" for i, solution in enumerate(csv[0][1:]): array = solution.split(",") x = str(int(float(array[0]))) y = str(int(float(array[1]))) id = str(int(float(array[2]))) trial = int(float(array[3])) if x=="0" and y=="0": continue if trial != current_trial: coordinate_string = coordinate_string[:-1] + "]" type = debr_trialtypes["Stim Type Left,Stim Type Right"][current_trial].split(",")[idx] val.append((current_trial,coordinate_string,stimset,type)) #print((current_trial,coordinate_string,stimset,type)) coordinate_string = "[" current_trial = trial coordinate_string+="{\"x\":"+x+", \"y\":"+y+", \"id\":"+id+"}," # last entry coordinate_string = coordinate_string[:-1] + "]" val.append((current_trial,coordinate_string,stimset,type)) #print((current_trial,coordinate_string,stimset,type)) print("Insert ",len(val), "solutions to ", debr_databases[idx]) sql = "INSERT INTO "+ debr_databases[idx] +" (debriefing_id,solution,stimset,trial_type) VALUES (%s, %s, %s, %s)" mycursor.executemany(sql, val) mydb.commit() ### Minesweeper ### print("\nMinesweeper") val = [] current_trial = 0 coordinate_string = "[" for i, silhouette in enumerate(minesweeper_silhouettes[0][1:]): array = silhouette.split(",") x = str(int(array[0])) y = str(int(array[1])) id = array [2] trial = int(array[3]) if trial != current_trial: type = 0 if has_minesweeper_trial_type: type = str(minesweeper_trialtype["StimID"][current_trial]) #print(type) coordinate_string = coordinate_string[:-1] + "]" solution = minesweeper_solutions.iloc[trial][0].split(",") solution_str = "{\"x\":"+solution[0]+", \"y\":"+solution[1]+", \"id\":"+solution[2]+"}" val.append((current_trial,coordinate_string, solution_str, stimset,type)) #print(val[trial-1]) coordinate_string = "[" current_trial = trial coordinate_string+="{\"x\":"+x+", \"y\":"+y+", \"id\":"+id+"}," # last entry solution = minesweeper_solutions.iloc[trial][0].split(",") solution_str = "{\"x\":"+solution[0]+", \"y\":"+solution[1]+", \"id\":"+solution[2]+"}" coordinate_string = coordinate_string[:-1] + "]" val.append((current_trial,coordinate_string, solution_str, stimset, type)) #print(val[trial]) print("Insert ",len(val), "solutions") sql = "INSERT INTO minesweeper_silhouette (minesweeper_silhouette_id,silhouette,solution,stimset,trialtype) VALUES (%s, %s, %s, %s, %s)" mycursor.executemany(sql, val) mydb.commit() # Association Learning Trials print("\n\n\n ### Association Data ###\n") path = os.path.join("Stims", "Association") association_trials = pd.read_csv(os.path.join(path,"Trials_AssocLearning.csv"), header=None, sep="\n") val = [] for i,record in enumerate(association_trials[0][1:]): stim_ID, stim_type, switch, target_id, target_type = record.split(",") val.append((stim_ID, stim_type, switch, target_id, target_type)) print("Insert ",len(val), "solutions to association_learning_trials") sql = "INSERT INTO association_learning_trials (stim_id, stim_type, switch, target_id, target_type) VALUES (%s, %s, %s, %s, %s)" mycursor.executemany(sql, val) mydb.commit() # Association Navigation Task path = os.path.join("Stims", "Association") navigation_trials = pd.read_csv(os.path.join(path,"Trials_Navigation.csv"), header=None, sep="\n") val = [] for i,record in enumerate(navigation_trials[0][1:]): solution, left, right, show_length = record.split(",") if(show_length =="True"): show_length = 1 else: show_length = 0 val.append((solution, left, right, show_length)) print("Insert ",len(val), "solutions to navigation_trials") sql = "INSERT INTO navigation_trials (solution, stim_left, stim_right, show_length) VALUES (%s, %s, %s, %s)" mycursor.executemany(sql, val) mydb.commit() print("\n\n\nDone")