def alter_table_info(change_info):
"""
alter table info inference
:param change_info:
:return:
"""
table_name_info = change_info['table_name']
exe_res = []
if table_name_info['is_change']:
res = table_operator.alter_table_name(table_name_info['old_name'], table_name_info['new_name'])
exe_res.append(res)
for update_info in change_info['column_update_info']:
data_type = TableInterface.change_data_type(update_info['type'])
base_str = """ALTER TABLE `%table_name` change `%old_column`
`%new_column` %data_type COMMENT '%new_comment'; """ \
.replace('%table_name', DataFormat.table_name_format(table_name_info['new_name'])) \
.replace('%old_column', update_info['old_column_name'])
base_str = base_str.replace('%new_column', DataFormat.column_name_format(update_info['new_column_name'])) \
.replace('%data_type', data_type).replace('%new_comment', update_info['description'])
res = table_operator.alter_column_name_and_comment(base_str)
exe_res.append(res)
for add_info in change_info['column_add_info']:
data_type = TableInterface.change_data_type(add_info['type'])
description, column_name = add_info['description'], add_info['new_column_name']
base_str = ADD_COLUMN.replace('%table_name',
DataFormat.table_name_format(table_name_info['new_name'])).replace(
'%column_name', DataFormat.column_name_format(column_name)) \
.replace('%new_comment', description).replace('%data_type', data_type)
exe_result = table_operator.add_column(base_str)
exe_res.append(exe_result)
for delete_info in change_info['column_delete_info']:
res = table_operator.delete_column(table_name_info['new_name'], delete_info['COLUMN_NAME'])
exe_res.append(res)
table_operator.alter_table_comment(change_info['table_name']['new_name'], change_info['table_comment'])
return json.dumps({'msg': str(exe_res), 'is_success': True})
def alter_column_name(data):
table_name, columns_info, values = data['table_name'], data['columns_info'], data['values']
old_name_lst = []
for item in columns_info:
old_name_lst.append(item['COLUMN_NAME'])
old_name_lst.append(item['COLUMN_COMMENT'])
value_index = 0
for info in columns_info[1:]:
info['new_name'] = values[value_index]
value_index = value_index + 1
info['new_column_comment'] = values[value_index]
value_index = value_index + 1
base_str = """ALTER TABLE `%table_name` change `%old_column`
`%new_column` %data_type COMMENT '%new_comment'; """
exe_result = True
for info in columns_info[1:]:
if info['COLUMN_NAME'] != info['new_name'] or info['COLUMN_COMMENT'] != info['new_column_comment']:
new_str = base_str.replace('%table_name', DataFormat.table_name_format(table_name)) \
.replace('%old_column', info['COLUMN_NAME']).replace('%new_column', DataFormat.column_name_format(
info['new_name'])).replace('%new_comment', DataFormat.table_name_format(info['new_column_comment']))
data_type = info['DATA_TYPE'].replace('多行文本', 'text').replace('文本', 'varchar(500)').replace(
'数字', 'decimal(15,4)').replace('日期', 'DATETIME')
new_str = new_str.replace('%data_type', data_type)
res = table_operator.alter_column_name_and_comment(new_str)
if res is False:
exe_result = res
if exe_result:
return json.dumps({'msg': '修改成功', 'is_success': True})
else:
return json.dumps({'msg': '修改失败', 'is_success': False})
def create_table_name(self, table_name, columns, table_comment):
"""
:return:
"""
table_name = DataFormat.table_name_format(table_name)
table_comment = DataFormat.table_name_format(table_comment)
sql_str = CREATE_TABLE.replace('%TABLE_NAME', table_name).replace(
'%table_comment', table_comment)
columns_str = ''
for column in columns:
tmp_str = ''
column_name = DataFormat.column_name_format(column['column_name'])
description = DataFormat.column_name_format(column['description'])
if column['type'] == 'text':
tmp_str = '`' + column_name + '`' + ' varchar(500) COMMENT \'' + description + "'"
elif column['type'] == 'date':
tmp_str = '`' + column_name + '`' + ' DATETIME COMMENT \'' + description + "'"
elif column['type'] == 'number':
tmp_str = '`' + column_name + '`' + ' decimal(15,4) COMMENT \'' + description + "'"
elif column['type'] == 'textarea':
tmp_str = '`' + column_name + '`' + ' text COMMENT \'' + description + "'"
columns_str = columns_str + tmp_str + ','
columns_str = columns_str[:-1]
sql_str = sql_str.replace('%COLUMNS_INFO', columns_str)
res = self.pool.execute_sql_str(sql_str)
return res['result']
def update_data(self, data):
table_name = data['table_name']
table_name = DataFormat.table_name_format(table_name)
data = data['data']
all_content = ''
for key in data:
v = data[key]
v = DataFormat.data_value_format(v)
values_str = """`$key` = '$value' ,""".replace('$key',
key).replace(
'$value', v)
all_content = all_content + values_str
all_content = all_content[:-1]
sql = UPDATE_DATA.replace('%table_name', table_name) \
.replace('%update_content', all_content).replace('$unique_id', data['unique_id'])
res = self.pool.execute_sql_str(sql)
return res
def add_data(self, data):
table_name = data['table_name']
table_name = DataFormat.table_name_format(table_name)
keys_str = ''
values_str = ''
data = data['data']
for key in data:
keys_str = keys_str + '`' + key + '`' + ','
v = data[key]
v = DataFormat.data_value_format(v)
values_str = values_str + "'" + v + "'" + ', '
keys_str = keys_str[:-1]
values_str = values_str[:-2]
sql = ADD_DATA.replace('%table_name', table_name) \
.replace('%fields', keys_str).replace('%values', values_str)
res = self.pool.execute_sql_str(sql)
return res
def _get_create_table_sql(param, table_name):
base_sql = """create table `%table_name` ( unique_id int(4) primary key not null auto_increment,%column_info);"""
combine_sql = ''
for key in param:
data_type = param[key]
key = DataFormat.column_name_format(key)
sql = '`' + key + '` ' + data_type + ' ,'
combine_sql = combine_sql + sql
base_sql = base_sql.replace('%table_name', table_name).replace('%column_info', combine_sql[:-1])
return base_sql
def alter_table_name(self, old_name, new_name):
"""
:param new_name:
:param old_name:
:return:
"""
new_name = new_name.replace(' ', '')
new_name = DataFormat.table_name_format(new_name)
sql_str = ALERT_TABLE_NAME.replace('%old_name', old_name).replace(
'%new_name', new_name)
res = self.pool.execute_sql_str(sql_str)
return res
def add_column_name(data):
"""
:param data:
:return:
"""
table_name, description, column_name, data_type = data['table_name'], \
data['description'], \
data['column_name'], \
data['type']
column_name = DataFormat.column_name_format(column_name)
description = DataFormat.column_name_format(description)
table_name = DataFormat.table_name_format(table_name)
base_str = ADD_COLUMN.replace('%table_name', table_name).replace('%column_name', column_name) \
.replace('%new_comment', description)
data_type = data_type.replace('number', 'decimal(15,4)').replace('date', 'DATETIME') \
.replace('textarea', 'text')
base_str = base_str.replace('%data_type', data_type).replace('text', 'varchar(500)')
exe_result = table_operator.add_column(base_str)
if exe_result:
return json.dumps({'msg': '修改成功', 'is_success': True})
else:
return json.dumps({'msg': '修改失败', 'is_success': False})
def _get_insert_sql_list(content_mapping, name):
insert_list = []
for row in content_mapping[1:]:
insert_sql = """insert into `%table_name` (%column_names) values (%values);""".replace('%table_name', name)
column_names = ''
values = ''
has_content = False
for key in row:
value = row[key]
if len(str(value)) > 0:
has_content = True
if key == '日期':
value = value.replace('年', '-').replace('月', '-').replace('日', ' ') + '00:00:00'
key = DataFormat.column_name_format(key)
column_names = column_names + '`' + key + '`,'
values = values + '\'' + str(value) + '\','
column_names = column_names[:-1]
values = values[:-1]
if not has_content:
continue
insert_sql = insert_sql.replace('%column_names', column_names).replace('%values', values)
insert_list.append(insert_sql)
return insert_list