def __add_column_if_not_exists_and_check_type(self, column, type_of_value, value): if column in self.column_names: self.__check_type(type_of_value, column, self.column_names, self.column_types, value=value) else: self.column_names, self.column_types = ( helpers.get_db_column_names_and_types(self.db_cursor)) if column in self.column_names: self.__check_type(type_of_value, column, self.column_names, self.column_types, value=value) else: if (type_of_value == 'int' or type_of_value == int): column_type = 'INTEGER' elif (type_of_value == 'float' or type_of_value == float): column_type = 'REAL' else: column_type = 'TEXT' self.__check_type(type_of_value, column, [column], [column_type], value=value) self.__add_column(column, type_of_value) self.column_names, self.column_types = ( helpers.get_db_column_names_and_types(self.db_cursor))
def test_combine_dbs(capsys): common_test_helpers.skip_if_outside_sim_db() path_db_1 = common_test_helpers.get_test_dir() + "/sim1_test_comb.db" path_db_2 = common_test_helpers.get_test_dir() + "/sim2_test_comb.db" path_comb_db = common_test_helpers.get_test_dir() + "/new_comb_sim.db" if os.path.exists(path_comb_db): os.remove(path_comb_db) command_line_tool("sim_db", ["combine_dbs", path_db_1, path_db_2, path_comb_db]) comb_sim_db = helpers.connect_sim_db(path_comb_db) comb_sim_db_cursor = comb_sim_db.cursor() db_1 = helpers.connect_sim_db(path_db_1) db_1_cursor = db_1.cursor() db_2 = helpers.connect_sim_db(path_db_2) db_2_cursor = db_2.cursor() column_names_1, column_types_1 = helpers.get_db_column_names_and_types( db_1_cursor) column_names_2, column_types_2 = helpers.get_db_column_names_and_types( db_2_cursor) column_names_comb, column_types_comb = helpers.get_db_column_names_and_types( \ comb_sim_db_cursor) old_run_commands = [] db_1_cursor.execute("SELECT run_command FROM runs WHERE id=1;") old_run_commands.append(db_1_cursor.fetchall()[0][0]) db_1_cursor.execute("SELECT run_command FROM runs WHERE id=2;") old_run_commands.append(db_1_cursor.fetchall()[0][0]) db_2_cursor.execute("SELECT run_command FROM runs WHERE id=1;") old_run_commands.append(db_2_cursor.fetchall()[0][0]) db_2_cursor.execute("SELECT run_command FROM runs WHERE id=2;") old_run_commands.append(db_2_cursor.fetchall()[0][0]) new_run_commands = [] comb_sim_db_cursor.execute("SELECT run_command FROM runs WHERE id=1;") new_run_commands.append(comb_sim_db_cursor.fetchall()[0][0]) comb_sim_db_cursor.execute("SELECT run_command FROM runs WHERE id=2;") new_run_commands.append(comb_sim_db_cursor.fetchall()[0][0]) comb_sim_db_cursor.execute("SELECT run_command FROM runs WHERE id=3;") new_run_commands.append(comb_sim_db_cursor.fetchall()[0][0]) comb_sim_db_cursor.execute("SELECT run_command FROM runs WHERE id=4;") new_run_commands.append(comb_sim_db_cursor.fetchall()[0][0]) with capsys.disabled(): print("\nTest combine_dbs...") comb_sim_db.commit() comb_sim_db_cursor.close() comb_sim_db.close() os.remove(path_comb_db) for column_name in column_names_1: assert column_name in column_names_comb for column_name in column_names_2: assert column_name in column_names_comb for (old_command, new_command) in zip(old_run_commands, new_run_commands): assert old_command == new_command
def test_add_column_and_delete_empty_columns(capsys): common_test_helpers.skip_if_outside_sim_db() with capsys.disabled(): print("\nTest add_column and delete_empty_columns...") command_line_tool("sim_db", "add_column --column new_column --type 'TEXT'".split()) db = helpers.connect_sim_db() db_cursor = db.cursor() column_names, column_types = helpers.get_db_column_names_and_types( db_cursor) assert "new_column" in column_names command_line_tool("sim_db", ["delete_empty_columns"]) column_names, column_types = helpers.get_db_column_names_and_types( db_cursor) assert "new_column" not in column_names
def update_sim(name_command_line_tool="sim_db", name_command="update_sim", argv=None): args = command_line_arguments_parser(name_command_line_tool, name_command).parse_args(argv) if args.id == None and args.where == "id > -1": print("Nothing was updated. --id 'ID' or --where 'CONDITION' must be " \ + "passed to the program.") exit(0) db = helpers.connect_sim_db(args.db_path) db_cursor = db.cursor() column_names, column_types = helpers.get_db_column_names_and_types( db_cursor) type_dict = dict(zip(column_names, column_types)) condition = args.where if args.id: condition = condition + " AND id = {0}".format(args.id) for column, value in zip(args.columns, args.values): if type_dict[column] == 'TEXT': value = "'{0}'".format(value) db_cursor.execute("UPDATE runs SET {0} = {1} WHERE {2}" \ .format(column, value, condition)) db.commit() db_cursor.close() db.close()
def add_column(name_command_line_tool="sim_db", name_command="add_column", argv=None): db = helpers.connect_sim_db() db_cursor = db.cursor() column_names, column_types = helpers.get_db_column_names_and_types( db_cursor) args = command_line_arguments_parser(name_command_line_tool, name_command).parse_args(argv) if args.column not in column_names: if args.type == 'int' or args.type == int: db_cursor.execute("ALTER TABLE runs ADD COLUMN {0} INTEGER".format( args.column)) elif args.type == 'float' or args.type == float: db_cursor.execute("ALTER TABLE runs ADD COLUMN {0} REAL".format( args.column)) else: db_cursor.execute("ALTER TABLE runs ADD COLUMN {0} TEXT".format( args.column)) db.commit() db_cursor.close() db.close()
def read(self, column, check_type_is=''): """Read parameter in 'column' from the database. Return None if parameter is empty. :param column: Name of the column the parameter is read from. :type column: str :param check_type_is: Throws ValueError if type does not match 'check_type_is'.The valid types the strings 'int', 'float', 'bool', 'string' and 'int/float/bool/string array' or the types int, float, bool, str and list. :raises ColumnError: If column do not exists. :raises ValueError: If return type does not match 'check_type_is'. :raises sqlite3.OperationalError: Waited more than 5 seconds to read from the database, because other threads/processes are busy writing to it. Way too much concurrent writing is done and it indicates an design error in the user program. """ if column not in self.column_names: self.column_names, self.column_types = ( helpers.get_db_column_names_and_types(self.db_cursor)) if column not in self.column_names: raise ColumnError("Column, {0}, is NOT a column in the " "database.".format(column)) self.db_cursor.execute("SELECT {0} FROM runs WHERE id={1}".format( column, self.id)) value = self.db_cursor.fetchone() if value != None: value = value[0] value = self.__check_type(check_type_is, column, self.column_names, self.column_types, value) return value
def delete_empty_columns(name_command_line_tool="sim_db", name_command="delete_empty_columns", argv=None): command_line_arguments_parser(name_command_line_tool, name_command).parse_args(argv) db = helpers.connect_sim_db() db_cursor = db.cursor() column_names, column_types = helpers.get_db_column_names_and_types( db_cursor) new_table_dict = OrderedDict() for column_name, column_type in zip(column_names, column_types): db_cursor.execute("SELECT {0} FROM runs;".format(column_name)) values = db_cursor.fetchall() is_empty = True for value in values: if value != (None, ): is_empty = False break if not is_empty or (column_name in helpers.default_db_columns): new_table_dict[column_name] = column_type new_columns_and_types = "" new_columns = "" for column_name in new_table_dict: new_columns_and_types += column_name + " " + new_table_dict[ column_name] + ", " new_columns += column_name + ", " new_columns_and_types = new_columns_and_types[:-2] new_columns = new_columns[:-2] assert new_columns_and_types[0:2] == 'id', ( "Name of first column in database is not 'id'.") new_columns_and_types = new_columns_and_types[0:10] + " PRIMARY KEY" \ +new_columns_and_types[10:] # Correct id type db_cursor.execute("CREATE TABLE IF NOT EXISTS new_runs ({0});".format( new_columns_and_types)) db_cursor.execute( "INSERT INTO new_runs SELECT {0} FROM runs;".format(new_columns)) db_cursor.execute("DROP TABLE runs;") db_cursor.execute("ALTER TABLE new_runs RENAME TO runs;") db.commit() db_cursor.close() db.close()
def column_exists(self, column): """Return True if column is a column in the database. :raises sqlite3.OperationalError: Waited more than 5 seconds to read from the database, because other threads/processes are busy writing to it. Way too much concurrent writing is done and it indicates an design error in the user program. """ if column in self.column_names: return True else: self.column_names, self.column_types = ( helpers.get_db_column_names_and_types(self.db_cursor)) if column in self.column_names: return True else: return False
def add_sim(name_command_line_tool="sim_db", name_command="add", argv=None): db = helpers.connect_sim_db() args = command_line_arguments_parser(name_command_line_tool, name_command).parse_args(argv) sim_params_filename = args.filename if sim_params_filename == None: sim_params_filename = search_for_parameter_file_matching_settings() if sim_params_filename == None: print("No parameter files in the current directory matches the " "ones under 'Parameter filenames'\nin settings.txt.\n" "\nAdd the '--filename' flag to specify the filename of " "the parameter file.") exit(1) elif (sim_params_filename[0:5] == 'root/'): sim_params_filename = os.path.join( os.path.join(helpers.get_dot_sim_db_dir_path(), os.pardir), sim_params_filename[5:]) elif (sim_params_filename[0:6] == '"root/'): sim_params_filename = '"' + os.path.join( os.path.join(helpers.get_dot_sim_db_dir_path(), os.pardir), sim_params_filename[6:]) try: sim_params_file = open(sim_params_filename, 'r') except: print("Could NOT open {0}.".format(sim_params_filename)) exit(1) sim_params_file_lines = sim_params_file.readlines() sim_params_file.close() sim_params_file_lines = add_included_parameter_files(sim_params_file_lines) sim_params_file_lines = replace_aliases(sim_params_file_lines) db_cursor = db.cursor() default_db_columns = "" for key in helpers.default_db_columns: default_db_columns += key + " " + str( helpers.default_db_columns[key]) + ", " default_db_columns = default_db_columns[:-2] db_cursor.execute( "CREATE TABLE IF NOT EXISTS runs ({0});".format(default_db_columns)) column_names, column_types = helpers.get_db_column_names_and_types( db_cursor) initial_parameters = [] last_row_id = None for i, line in enumerate(sim_params_file_lines): if len(line.split(':')) > 1: param_name, param_type, value = split_parameter_line(line, i) initial_parameters.append(param_name) if param_name == 'run_command': value = make_path_relative_to_root(value, sim_params_filename) try: row_index = column_names.index(param_name) except ValueError: row_index = None if row_index is None: add_new_column(db_cursor, i, param_type, param_name, value, column_names, column_types) else: check_type_matches(param_type, column_types[row_index], value, i) value = standardize_value(value, param_type) if len(value) > 0: last_row_id = insert_value(db_cursor, param_name, last_row_id, value) initial_parameters = standardize_value(str(initial_parameters), "string array") last_row_id = insert_value(db_cursor, 'initial_parameters', last_row_id, initial_parameters) db.commit() db_cursor.close() db.close() return last_row_id
def print_sim(name_command_line_tool="sim_db", name_command="print_sim", argv=None): if argv == None: argv = ['-p', 'default'] elif '--columns' not in argv and '-c' not in argv: if len(argv) > 0 and argv[0][0] != '-': # To print correct error message even though '-p default' is added. command_line_arguments_parser(name_command_line_tool, name_command).parse_args(argv) argv = ['-p', 'default'] + argv argv_p_replaced = [] i = 0 while i < len(argv): if argv[i] != '-p': argv_p_replaced.append(argv[i]) else: while i + 1 < len(argv) and argv[i + 1][0] != '-': i += 1 print_config_key = argv[i] print_config = get_personalized_print_config(print_config_key) if print_config == None: print( "No personalized print configuration with key string " "'{0}' is found in settings.".format(print_config_key)) exit(1) argv_p_replaced = argv_p_replaced + shlex.split(print_config) i += 1 argv = argv_p_replaced args = command_line_arguments_parser(name_command_line_tool, name_command).parse_args(argv) db = helpers.connect_sim_db() db_cursor = db.cursor() column_names, column_types = helpers.get_db_column_names_and_types( db_cursor) type_dict = dict(zip(column_names, column_types)) selected_output, column_names = select_command(name_command_line_tool, name_command, db_cursor, args, column_names) if args.diff: selected_output, column_names = remove_columns_with_only_same_values( selected_output, column_names) if not args.vertically: print_selected_parameters(selected_output, column_names, args.no_headers, args.max_width, args.first_line) else: print_selected_parameters_vertically(selected_output, column_names, args.no_headers, args.max_width, args.first_line) if args.column_names: print("") print("Column names and types in database:") for col_name in column_names: col_type = type_dict[col_name] print("{0}, {1}".format(col_name, col_type)) db.commit() db_cursor.close() db.close()
def combine_dbs(name_command_line_tool="sim_db", name_command="combine_dbs", argv=None): args = command_line_arguments_parser(name_command_line_tool, name_command).parse_args(argv) db_1 = helpers.connect_sim_db(args.path_db_1) db_1_cursor = db_1.cursor() db_2 = helpers.connect_sim_db(args.path_db_2) db_2_cursor = db_2.cursor() (columns_db_1, types_db_1) = helpers.get_db_column_names_and_types(db_1_cursor) (columns_db_2, types_db_2) = helpers.get_db_column_names_and_types(db_2_cursor) column_type_dict = OrderedDict( [(col, typ) for col, typ in zip(columns_db_1, types_db_1)] + [(col, typ) for col, typ in zip(columns_db_2, types_db_2)]) new_db = helpers.connect_sim_db(args.name_new_db) new_db_cursor = new_db.cursor() new_db_columns_string = "" for col in column_type_dict: new_db_columns_string += col + " " + column_type_dict[col] + ", " new_db_columns_string = new_db_columns_string[:-2] new_db_columns_string = new_db_columns_string.replace( "id INTEGER", "id INTEGER PRIMARY KEY", 1) new_db_cursor.execute( "CREATE TABLE runs ({0});".format(new_db_columns_string)) db_1_cursor.execute("SELECT * FROM runs") for row in db_1_cursor.fetchall(): column_tuple = () value_tuple = () for column, value in zip(columns_db_1, row): column = helpers.if_unicode_convert_to_str(column) value = helpers.if_unicode_convert_to_str(value) if value != None and column != 'id': column_tuple += (column, ) value_tuple += (value, ) new_db_cursor.execute("INSERT INTO runs {0} VALUES {1}".format( column_tuple, value_tuple)) db_2_cursor.execute("SELECT * FROM runs") for row in db_2_cursor.fetchall(): column_tuple = () value_tuple = () for column, value in zip(columns_db_2, row): column = helpers.if_unicode_convert_to_str(column) value = helpers.if_unicode_convert_to_str(value) if value != None and column != 'id': column_tuple += (column, ) value_tuple += (value, ) new_db_cursor.execute("INSERT INTO runs {0} VALUES {1}".format( column_tuple, value_tuple)) db_1.commit() db_2.commit() new_db.commit() db_1_cursor.close() db_2_cursor.close() new_db_cursor.close() db_1.close() db_2.close() new_db.close()
def extract_params(name_command_line_tool="sim_db", name_command="extract_params", argv=None): args = command_line_arguments_parser(name_command_line_tool, name_command).parse_args(argv) is_printing_parameters = True if args.default_file: param_files = helpers.Settings().read('parameter_files') if len(param_files) == 0: print("ERROR: No '--filename' provided and no 'Parameters files' " "in settings.txt.") exit() else: filename = param_files[0] if os.path.exists(filename): answer = helpers.user_input( "Would you like to overwrite '{0}'? (y/n)".format( filename)) if (answer != 'y' and answer != 'Y' and answer != 'yes' and answer != 'Yes'): exit() print("Extracts parameters to '{0}'.".format(filename)) is_printing_parameters = False elif args.filename != None: filename = args.filename is_printing_parameters = False if not is_printing_parameters: params_file = open(filename, 'w') db = helpers.connect_sim_db() db_cursor = db.cursor() db_cursor.execute("SELECT * FROM runs WHERE id={0}".format(args.id)) extracted_row = db_cursor.fetchall() column_names, column_types = helpers.get_db_column_names_and_types( db_cursor) db_cursor.execute("SELECT initial_parameters FROM runs WHERE id={0}" .format(args.id)) initial_parameters = db_cursor.fetchone()[0] initial_parameters, correct_type = helpers.convert_text_to_correct_type( initial_parameters, 'string array') for col_name, col_type, value in zip(column_names, column_types, extracted_row[0]): if (value != None and (col_name in initial_parameters or (args.also_results and col_name not in no_extract_columns) or args.all)): line = col_name param_type = get_param_type_as_string(col_type, value) line += " ({0}): ".format(param_type) if param_type[-3:] == 'ray': value = '[' + value.split('[')[1] line += str(value).replace(':', ';') + '\n' if is_printing_parameters: print(line) else: params_file.write(line) if not is_printing_parameters: params_file.close() db.commit() db_cursor.close() db.close()