def search(self): self.apis_copy['value'] = 'aqdflv' from utils.db_connection import fx_engine from utils.results2df import results2df results = fx_engine.execute(self.sql) data = results2df(results, self.columns) self.df = data
def get_data_from_db(sql, db, results, format_pool_sql, num, fulled): time_format = results.get("time_format") mapping = results.get("mapping") value_map = results.get("value_map") try: sql = sql.format(**format_pool_sql) except: return 400, f"PluginSQLError: sql [{sql}] needs enough variables to format", {} try: db_results = db.execute(sql) except: return 400, f"PluginSQLError: There must be some error in the sql {sql}", {} db_results = results2df(db_results, db_results.keys()) # 转df:包括项目的映射 db_results = get_value_mapped(db_results, value_map, format_pool_sql) # 做映射:sql模式中自定义的映射方法 from utils.time_format import df_formated_time db_results = df_formated_time(db_results, time_format) # 格式化时间 if fulled: db_results = df_after_full(db_results, fulled) # full之后的df if num == 1: if not set(mapping.keys()) <= set(db_results.columns): return 400, f"PluginMapError: There are some errors in the param 'map', " \ f"These columns are not in df: [{set(mapping.keys()) - set(db_results.columns)}]", {} else: db_results = db_results[[*mapping.keys()]] db_results = df2results(db_results) else: db_results = df2results(db_results) return 200, "success", db_results
def search(self): from utils.db_connection import fx_engine from utils.results2df import results2df results = fx_engine.execute(self.sql) data = results2df(results, self.columns) self.df = data
def search(self): """ waiting_for_search: [ {"table": "", "ex_table": ex_table, "columns": ["year", "month", "xfjc"], "conditions": [ [ ],[ ] ]}, {"table": "", "ex_table": ex_table, "columns": ["year", "month", "xfjc"], "conditions": [ [ ],[ ] ]} ] """ # self.code, self.msg, self.waiting_for_search = Extension.get_waiting_for_search(self.before_waiting_for_search) if not self.waiting_for_search: nonedf = Extension._get_none_df(self.apis_copy) self.db_results = nonedf return # 获取order, limit order, limit = self.apis_copy["direct_order"], self.apis_copy["direct_limit"] # 获取数据库连接 from utils.db_connection import zb_engine session = Session(zb_engine) searched = [] # 开始查库 for search_one_table in self.waiting_for_search: # 第一张表 columns = search_one_table["columns"] ex_table = search_one_table["ex_table"] # 查找表格中的字段 search_table = [] for col in columns: hascol = hasattr(ex_table.columns, col) if not hascol: from app import app NOCOLUMN_ERROR = app.config.get("NOCOLUMN_ERROR", True) if NOCOLUMN_ERROR: self.code, self.msg = 400, f"NoSuchColumnError: The table {search_one_table['table']} has no specific columns [{col}]" else: nonedf = Extension._get_none_df(self.apis_copy) self.code, self.msg, self.db_results = 200, f"The table {search_one_table['table']} has no specific columns [{col}]", nonedf session.close() return tar_vs = [getattr(ex_table.columns, i) for i in columns] # 当前表中的多组条件 for conditions in search_one_table["conditions"]: # 第一组条件 results = session.query(*tar_vs).filter(*conditions) # results直接print是sql语句 if order: if "-" in order: # sqlalchemy新版本中order_by 的用法需要用字段.desc() results = results.order_by(getattr(ex_table.columns, order.replace("-", "")).desc()) else: results = results.order_by(order) if limit: results = results.limit(limit) from utils.results2df import results2df data = results2df(results, columns, self.apis_copy) search_table.append(data) searched.append(search_table) session.close() self.db_results = searched
def get_specific_table(table, db="zb_db"): from utils.db_connection import fx_engine, zb_engine if db == "zb_db": engine = zb_engine else: engine = fx_engine try: db_results = engine.execute(f"select * from {table}") except: return 400, f"TableError: The table {[table]} does not in {db}", {} from utils.results2df import results2df db_results = results2df(db_results, db_results.keys()) return 200, "success", db_results
def search(self): # self.apis_copy["xfjc"] = "jssllv" sql_list = self.apis_copy['ext'].split('/') Cqh_sql_map = {} for item in sql_list: if 'shej' in item: Cqh_sql_map['shej_02'] = item elif 'shij' in item: Cqh_sql_map['shij_02'] = item elif 'xj' in item: Cqh_sql_map['xj_02'] = item else: Cqh_sql_map['all'] = item # if g.get('before_param_trans')['bmjb'] == '省级': # bmjb = 'shej_02' # elif g.get('before_param_trans')['bmjb'] == '市级': # bmjb = 'shi_02' # elif g.get('before_param_trans')['bmjb'] == '县级': # bmjb = 'xj_02' # else: # bmjb = 'all' # # if bmjb: # sql_item = Cqh_sql_map[bmjb] if bmjb in Cqh_sql_map else Cqh_sql_map['all'] # else: # Cqh_level = get_qh_level(g.get('before_param_trans')['Cqh']) # sql_item = Cqh_sql_map[Cqh_level] if Cqh_level in Cqh_sql_map else Cqh_sql_map['all'] sql_item = sql_list[0] sql_map = get_sql_map(DEBUG) # 按期答复率为3-4(市)3-2(省) from utils.db_connection import fx_engine from utils.results2df import results2df sql, columns = sql_map[sql_item] self.apis_copy['value'] = 'aqdflv' from flask import g results = fx_engine.execute( sql.format(**g.get("reqdicts_before_pt")).format(**self.apis_copy)) data = results2df(results, columns) self.df = data