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)