def insert(table_name: str, column: tuple, data: list, schema_name: str = 'report'): """ Mysql Insert 함수 """ if len(data) == 0: return statement = f"""INSERT INTO {table_name} {str(column).replace("'", "`")} VALUES {str(tuple(map(lambda x: '%s', column))).replace("'", "")}""" print(f"INSERT INTO {schema_name}.{table_name} / Length:{len(data)}") count = 0 while count < len(data): execute(statement=statement, data=data[count:count + 10000], schema_name=schema_name) count += 10000
def add_column(table_name: str, column_definition: str, schema_name: str = 'report'): """ Mysql 칼럼 생성 함수 """ statement = get_add_column_statement(table_name, column_definition) return execute(statement=statement, data=[], schema_name=schema_name)
def create_table(table_name: str, column_definition_list: list, schema_name: str = 'report'): """ Mysql 테이블 생성 함수 """ statement = get_create_table_statement(table_name, column_definition_list) print(statement) return execute(statement=statement, data=[], schema_name=schema_name)
def add_index(table_name: str, index_name: str, column_list: list, schema_name: str = 'report'): """ Mysql 칼럼 생성 함수 """ statement = get_add_index_statement(table_name, index_name, column_list) return execute(statement=statement, data=[], schema_name=schema_name)
def delete(table_name: str, key_column: str, key_list: list, schema_name: str = 'report'): """ Mysql Delete 함수 """ statement = f""" DELETE FROM {table_name} WHERE {key_column} = %s """ return execute(statement=statement, data=list(map(lambda x: (x, ), key_list)), schema_name=schema_name)
def convert_data_type_when_already_create_table(schema_name: str, table_name: str, column_list: list, data_list: list): """ 이미 DB에 테이블이 생성되어 있을 경우, 테이블의 컬럼 정의를 바꿔준다. ALTER TABLE 테이블이름 MODIFY COLUMN 컬럼명 변경할컬럼타입, ...., MODIFY COLUMN 컬럼명 변경할컬럼타입; """ data_type_list = check_data_type(data_list)[0] modify_column_list = [ f"MODIFY COLUMN `{column_list[count]}` {data_type_list[count]}" for count in range(len(column_list)) ] prepared_query = f"ALTER TABLE {schema_name}.{table_name} {', '.join(modify_column_list)};" print(prepared_query) return execute(prepared_query, [])
def update(table_name: str, key_column: str, target_columns: tuple, data: list, schema_name: str = 'report'): """ Mysql Update 함수 *Data 파라미터 형식 data = [ { "key": Key Column Value, "value": ('Value1', 'Value2', 'Value3', 'Value4'...) },... ] """ statement = f""" UPDATE {table_name} SET {', '.join(list(map(lambda x: f"{x} = %s", target_columns)))} WHERE {key_column} = %s """ return execute(statement=statement, data=list(map(lambda x: (x["value"] + (x["key"], )), data)), schema_name=schema_name)