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