def updateRecordbof(self): # 定期更新完成后更新记录表
# 从各个表中读取各自的最新数据时间
sqlVO = {}
sqlVO["db_name"] = "l2own"
# tableNamelist = ['PRO_BOF_HIS_PLAN','PRO_BOF_HIS_MIRON','PRO_BOF_HIS_SCRAP','PRO_BOF_HIS_POOL','PRO_BOF_HIS_EVENTS','PRO_BOF_HIS_BOCSM','PRO_BOF_HIS_TEMP','PRO_BOF_HIS_CHRGDGEN','PRO_BOF_HIS_CHRGDDAT','PRO_BOF_HIS_ANAGEN','PRO_BOF_HIS_ANADAT']
tableNamelist = [
'PLAN', 'MIRON', 'SCRAP', 'POOL', 'EVENTS', 'BOCSM', 'TEMP',
'CHRGDGEN', 'CHRGDDAT', 'ANAGEN', 'ANADAT'
]
for singlename in tableNamelist:
sqlVO[
"sql"] = "select MAX(MSG_DATE) FROM PRO_BOF_HIS_" + singlename
latestTime = models.BaseManage().direct_select_query_sqlVO(
sqlVO)[0].get('MAX(MSG_DATE)', None)
str_latestTime = datetime.datetime.strftime(
latestTime, '%Y-%m-%d %H:%M:%S')
sqlVO[
"sql"] = "update PRO_BOF_HIS_RECORD set TABLE_" + singlename + " = to_date(' " + str_latestTime + " ','yyyy-mm-dd hh24:mi:ss')"
models.BaseManage().direct_execute_query_sqlVO(sqlVO)
# 最后更新记录表的更新时间
# time_now=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))#当前时间
# sqlVO["sql"]= "update PRO_BOF_HIS_RECORD set RECORD_TIME = to_date(' " +time_now+" ','yyyy-mm-dd hh24:mi:ss')"
sqlVO["sql"] = "update PRO_BOF_HIS_RECORD set RECORD_TIME = sysdate"
models.BaseManage().direct_execute_query_sqlVO(sqlVO)
def getAllTradeNo_time(request):
conn_mysql = models.BaseManage()
sql_getAllTradeNo = "select c.tradeNo from data_import_sales_orderno a,data_import_sales2_orderno_orderitem c where c.orderNo = a.orderNo group by c.tradeNo"
allTradeNo = conn_mysql.select(sql_getAllTradeNo)
print(allTradeNo)
#print (type(allTradeNo))
# sqlVO={}
# sqlVO["db_name"]="l2own"
# sqlVO["sql"]="select distinct gk_no from qg_user.PRO_BOF_HIS_ALLFIELDS order by gk_no";
# print(sqlVO["sql"])
# scrapy_records=models.BaseManage().direct_select_query_sqlVO(sqlVO) #查询数据库
# frame=DataFrame(scrapy_records)
#print(frame['GK_NO'])
# contentVO={
# 'title':'测试',
# 'state':'success'
# }
# # grape=[ele for ele in frame['GK_NO']]
# #print(grape)
# contentVO['result']=allTradeNo
# return HttpResponse(json.dumps(contentVO),content_type='application/json')
return HttpResponse(json.dumps({'result': allTradeNo}),
content_type='json')
def __init__(self):
sqlVO = {}
sqlVO["db_name"] = "l2own"
sqlVO[
"sql"] = "select distinct heat_no from PRO_BOF_HIS_PLAN_TESTUPDATE"
self.heatlist = models.BaseManage().direct_select_query_sqlVO(sqlVO)
def integrate_db_dataframe(self, sqlVO_=dict()):
"""从数据库根据因素列表取出相应因素
"""
dfs = dict()
sqlVO = dict()
bsm = models.BaseManage()
@models.transaction_decorator
def inner_exe(bsm):
for tname in self._table_names:
if tname == 'steelprice':
if sqlVO_:
desc, rs = self._steelprice_custom(bsm, sqlVO_)
else:
desc, rs = self._steelprice_custom(bsm)
print(desc)
else:
sqlVO['sql'] = 'SELECT * FROM {0}'.format(tname)
desc = bsm.direct_get_description(sqlVO)
rs = bsm.select_single(sqlVO) # 必须是字典类型，否则无法匹配dataframe列名
if rs:
# print(rs[:2])
try:
df = pd.DataFrame(rs, columns=pd.Series(desc[0]))
# exceler.save_csv_pd(df,tname,table_name_means)
dfs[tname] = df # 'fgzs'暂时无数据
except Exception as e:
print('ERROR:[', e, ']')
inner_exe(bsm)
return dfs
def transaction_decorator(f):
bsm = models.BaseManage()
try:
with transaction.atomic():
f(bsm)
except Exception as e:
print("transaction error！")
print('ERROR:[', e, ']')
def transaction_test():
sqlVO = {}
bsg = models.BaseManage()
# bsg.identify_db('l2own')
sql = "INSERT INTO person VALUES('%s', '%s', '%s')"
persons = [(11, 'ccx', 'basketball'), (12, 'ccx', 'basketball'),
('13', 'cx', 'travle')]
try:
with transaction.atomic():
for p in persons:
sqlVO['sql'] = sql % p
print('SQL [%s]' % sqlVO.get('sql'))
bsg.execute_single(sqlVO)
except Exception as e:
print('ERROR:[', e, ']')
print("事务执行出错")
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.0.161)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =qgil2dbdg)
)
)';
在实验室相关的配置应该是本机
-- Drop existing database link
drop public database link DBLINK_TO_L2;
-- Create database link
create public database link DBLINK_TO_L2
connect to QG_USER identified by "123456"
using '202.204.54.42:1521/orcl';
"""
from data_import import dynamic_update
if __name__ == '__main__':
bsm = models.BaseManage()
bsm.identify_db("l2own")
# os.environ.setdefault("DJANGO_SETTINGS_MODULE", "QinggangManageSys.settings")
# settings.configue()
# abm = BaseManageTeatCase()
# transaction_test()
# test_transaction_decorator()
# updateRecordbof_transaction()
# updateRecordbof()
dynamic_update.batch_dyupdatebof()
# tesr_sql(bsm)
#from sys import argv
#script, filename = argv
from . import input_
from . import sql_space
from . import sql_time
from . import sql_trade
from . import sql_customer
from . import sql_customer2
from . import max_min_average
from . import conclusion
from . import save_txt
import math
from functools import reduce
from data_import import models
conn_mysql = models.BaseManage()
def sql_market_share(startYear, startMonth, endYear, endMonth):
#========================【 输 入 】===========================
#获取数据
#print ("开始执行 market_share_sql 函数")
startDate = str(startYear) + str(startMonth) + "00"
endDate = str(endYear) + str(endMonth) + "40"
sql_province = "select a.province,sum(a.salesWeight),sum(a.qdisSalesWeight),(sum(a.qdisSalesWeight)/sum(a.salesWeight))*100 from data_sales_new_space_comparsion a where a.orderDate >= " + startDate + " and a.year <= " + endDate + " group by a.province"
province_salesWeight_list = conn_mysql.select(sql_province) #得到的是一个tuple
#print (type(province_salesWeight_list))
print(province_salesWeight_list)
ratio_dictionary = {}
salesWeight_dictionary = {}
def __init__(self, ):
"""Constructor for DataCleaning"""
self.bsm = models.BaseManage()
self.sqlVO = dict()
def __init__(self):
self.sql_conn = models.BaseManage()
self.listed = list()
