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
