def delete(self, tid):
"""删除数据表"""
utoken = request.args.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info:
return jsonify({"message": "登录已过期.."}), 400
user_id = user_info['id']
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 先找到原数据表
select_statement = "SELECT `id`,`sql_table` FROM `info_trend_table` WHERE `id`=%s AND `author_id`=%s;"
cursor.execute(select_statement, (tid, user_id))
fetch_one = cursor.fetchone()
if not fetch_one:
db_connection.close()
return jsonify({"message": "删除他人数据就是谋财害命..."}), 400
else:
db_connection.begin()
try:
sql_table = fetch_one['sql_table']
table_id = fetch_one['id']
# 删除根据此表数据画的图
delete_relate_chart = "DELETE FROM `info_trend_chart` " \
"WHERE `table_id`=%s;"
cursor.execute(delete_relate_chart, table_id)
# 删除表
drop_statement = "DROP TABLE %s;" % sql_table
# 删除表记录
delete_statement = "DELETE FROM `info_trend_table` " \
"WHERE `id`=%s AND `author_id`=%s;"
cursor.execute(delete_statement, (tid, user_id))
cursor.execute(drop_statement)
db_connection.commit()
except Exception:
db_connection.rollback()
db_connection.close()
return jsonify({"message": "删除出错了..."}), 400
else:
db_connection.close()
return jsonify({"message": "这张表已烟消云散..."})
def create_new_table(self, user_id, variety_id, group_id, title, title_md5,
suffix_index, sql_table_name, table_values, origin):
"""
新建一张数据表
:param user_id: 创建表的用户ID
:param variety_id: 表相关的品种ID
:param group_id: 表所属数据组的ID
:param title: 表的名称
:param title_md5: 表名称的MD5
:param suffix_index: 表的后缀索引
:param sql_table_name: 实际数据表的sql名
:param table_values: 实际数据
:param origin: 备注，数据来源
:return:
"""
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
table_headers = table_values.pop(0) # 取出第一行为表头
free_row = table_values.pop(0) # 再取出第一行为自由行(即上传表中的第三行数据)
# 取得table_values[0]列中的最大值和最小值
table_values_df = pd.DataFrame(table_values)
if table_values_df.empty:
current_app.logger.error("user_id={}保存表:{}为空。".format(
user_id, title))
return jsonify({"message": "待保存数据为空"}), 400
table_values_df[0] = pd.to_datetime(table_values_df[0],
format='%Y-%m-%d') # 转为时间格式
table_values_df.drop_duplicates(subset=[0], keep='last',
inplace=True) # 去除日期完全一致的数据行
max_date = (table_values_df[0].max()).strftime('%Y-%m-%d')
min_date = (table_values_df[0].min()).strftime('%Y-%m-%d')
# 再转为字符串时间
table_values_df[0] = table_values_df[0].apply(
lambda x: x.strftime('%Y-%m-%d'))
# print('数据中最小时间:', min_date)
# print('数据中最大时间:', max_date)
# 插入自由行到数据中
table_values_df.loc[-1] = free_row
table_values_df.index = table_values_df.index + 1
table_values_df.sort_index(inplace=True)
# 插入表头到数据中
table_values_df.loc[-1] = table_headers
table_values_df.index = table_values_df.index + 1
table_values_df.sort_index(inplace=True)
table_values_df.columns = table_headers
table_values = table_values_df.values.tolist() # 转为列表
# 根据表头生成sql需要的语句片段
sqls_segment = self.generate_sql_segment(table_headers)
create_col = sqls_segment['create']
add_col = sqls_segment['add']
values_col = sqls_segment['values']
db_connection.begin() # 开启事务
try:
# 增加数据表的信息表
insert_statement = "INSERT INTO `info_trend_table` (`title`,`title_md5`,`suffix_index`,`sql_table`,`group_id`," \
"`variety_id`,`author_id`,`updater_id`,`origin`,`min_date`,`max_date`) " \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
cursor.execute(
insert_statement,
(title, title_md5, suffix_index, sql_table_name, group_id,
variety_id, user_id, user_id, origin, min_date, max_date))
# 创建实际数据表
create_table_statement = "CREATE TABLE IF NOT EXISTS %s (" \
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT," \
"`create_time` DATETIME NOT NULL DEFAULT NOW()," \
"`update_time` DATETIME NOT NULL DEFAULT NOW()," \
"%s);" % (sql_table_name, create_col)
cursor.execute(create_table_statement)
# 在新建表中加入数据
insert_data_statement = "INSERT INTO %s (%s) " \
"VALUES (%s);" % (sql_table_name, add_col, values_col)
cursor.executemany(insert_data_statement, table_values)
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
return jsonify({"message": str(e)}), 400
else:
db_connection.close()
return jsonify({"message": "添加数据表成功!"}), 201
