import cx_Oracle
|
import pymssql
|
from datetime import datetime, timedelta
|
import numpy as np
|
# select * from swzdh.rain;
|
# select * from swzdh.river;
|
# select * from swzdh.gw;
|
|
|
|
#麻峪雨量站
|
mayu_rainfall="30747850"
|
|
#陇驾庄水文站
|
ljz_swz="30700450"
|
|
#LQWB（龙泉）/ZSSC（再生水厂）/WTY（梧桐苑）/LSH（莲石湖）/HZZ（侯庄子）/GC（古城）
|
arr1=["30773702","30769360","30769280","30567304","30769320","30567303"]
|
|
|
haidian_rainfall="xxxx"
|
|
#SXC（双兴村93）/XM1（颐和园西门1）/XM2（颐和园西门2）/SYSC（水源三场336）/SJYY（四季御园2）/BW（北坞）
|
arr2=["30566324","30565080","30564400","30566335","30564840","30564880"]
|
|
#获取永定河15天的预测需要的数据
|
def get_ydh15_real_data():
|
result =[]
|
|
current_time = datetime.now()
|
start_time = current_time- timedelta(days=60)
|
times=[]
|
#时间序列
|
for i in range(75):
|
tmp = start_time + timedelta(days=i)
|
times.append(tmp.strftime("%Y-%m-%d"))
|
|
#开始时间 结束时间
|
start_str = start_time.strftime("%Y-%m-%d")
|
end_str= current_time.strftime("%Y-%m-%d")
|
|
|
|
#麻峪数据
|
mayu_data = get_data("rain",mayu_rainfall,start_str,end_str)
|
mayu_dict={}
|
for i in range(len(mayu_data)):
|
time = mayu_data[i]["time"]
|
value = mayu_data[i]["value"]
|
if time not in mayu_dict:
|
mayu_dict[time] = value
|
|
|
#陇驾庄
|
ljz_data = get_data("river",ljz_swz,start_str,end_str)
|
ljz_dict ={}
|
for i in range(len(ljz_data)):
|
time = ljz_data[i]["time"]
|
value = ljz_data[i]["value"]
|
if time not in ljz_data:
|
ljz_dict[time] = value
|
|
mayu_value=[]
|
ljz_value=[]
|
|
for i in range(len(times)):
|
tmp = times[i]
|
if tmp in mayu_dict:
|
mayu_value.append(tmp)
|
else:
|
mayu_value.append(0)
|
|
if tmp in ljz_dict:
|
ljz_value.append(tmp)
|
else:
|
ljz_value.append(0)
|
|
|
result.append(times)
|
result.append(mayu_value)
|
result.append(ljz_value)
|
|
for i in(len(arr1)):
|
data = get_data("gw",arr1[i],start_str,end_str)
|
dictx ={}
|
tmp_arr=[]
|
for i in range(len(data)):
|
time = data[i]["time"]
|
value = data[i]["value"]
|
if time not in dictx:
|
dictx[time] = value
|
|
|
for i in range(len(times)):
|
tmp = times[i]
|
if tmp in dictx:
|
tmp_arr.append(tmp)
|
else:
|
tmp_arr.append(0)
|
|
result.append(tmp_arr)
|
|
np_arr = np.asarray(result)
|
#数组转置
|
np_result = np_arr.T
|
|
return np_result
|
|
|
#获取玉泉山15天的预测需要的数据
|
def getyqs15_real_data():
|
|
|
result =[]
|
|
current_time = datetime.now()
|
start_time = current_time- timedelta(days=60)
|
times=[]
|
#时间序列
|
for i in range(75):
|
tmp = start_time + timedelta(days=i)
|
times.append(tmp.strftime("%Y-%m-%d"))
|
|
#开始时间 结束时间
|
start_str = start_time.strftime("%Y-%m-%d")
|
end_str= current_time.strftime("%Y-%m-%d")
|
|
|
|
#麻峪数据
|
hd_data = get_data("rain",haidian_rainfall,start_str,end_str)
|
hd_dict={}
|
for i in range(len(hd_data)):
|
time = hd_data[i]["time"]
|
value = hd_data[i]["value"]
|
if time not in hd_dict:
|
hd_dict[time] = value
|
|
|
#陇驾庄
|
ljz_data = get_data("river",ljz_swz,start_str,end_str)
|
ljz_dict ={}
|
for i in range(len(ljz_data)):
|
time = ljz_data[i]["time"]
|
value = ljz_data[i]["value"]
|
if time not in ljz_data:
|
ljz_dict[time] = value
|
|
hd_value=[]
|
ljz_value=[]
|
|
for i in range(len(times)):
|
tmp = times[i]
|
if tmp in hd_dict:
|
hd_value.append(tmp)
|
else:
|
hd_value.append(0)
|
|
if tmp in ljz_dict:
|
ljz_value.append(tmp)
|
else:
|
ljz_value.append(0)
|
|
|
result.append(times)
|
result.append(hd_value)
|
result.append(ljz_value)
|
|
for i in(len(arr2)):
|
data = get_data("gw",arr2[i],start_str,end_str)
|
dictx ={}
|
tmp_arr=[]
|
for i in range(len(data)):
|
time = data[i]["time"]
|
value = data[i]["value"]
|
if time not in dictx:
|
dictx[time] = value
|
|
|
for i in range(len(times)):
|
tmp = times[i]
|
if tmp in dictx:
|
tmp_arr.append(tmp)
|
else:
|
tmp_arr.append(0)
|
|
result.append(tmp_arr)
|
|
np_arr = np.asarray(result)
|
#数组转置
|
np_result = np_arr.T
|
|
return np_result
|
|
|
|
####################################################
|
|
def get_data(types,num,start_time,end_time):
|
|
if types =='river':
|
return river_list(start_time,end_time,num)
|
if types =='rain':
|
return rain_list(start_time,end_time,num)
|
|
if types =='gw':
|
return gw_list(start_time,end_time,num)
|
|
#河流 水文站
|
def river_list(start_time,end_time,STCD):
|
# 建立与Oracle数据库的连接
|
connection = cx_Oracle.connect('mzy/mzy_^22dPoO0@192.168.44.8:1521/swzdh')
|
# 创建游标对象
|
cursor = connection.cursor()
|
|
sql = """
|
SELECT * FROM swzdh.river
|
WHERE STCD = :STCD and tm BETWEEN TO_DATE(:start_time, 'YYYY-MM-DD') AND TO_DATE(:end_time, 'YYYY-MM-DD') order by tm ASC
|
"""
|
res =[]
|
try:
|
cursor.execute(sql, {'start_time': start_time, 'end_time': end_time,'STCD':STCD})
|
|
column_names = [row[0] for row in cursor.description]
|
print("字段名:", column_names)
|
|
# 获取所有结果集
|
result = cursor.fetchall()
|
|
# 输出结果
|
for row in result:
|
times = row[2]
|
date_str = times.strftime("%Y-%m-%d")
|
dicts={"time":date_str,"value":row[4],"stname":row[1]}
|
res.append(dicts)
|
|
except Exception as e:
|
print("Error occurred: ", str(e))
|
finally:
|
# 关闭游标和连接
|
cursor.close()
|
connection.close()
|
return res
|
|
#降雨量
|
def rain_list(start_time,end_time,STCD):
|
# 建立与Oracle数据库的连接
|
connection = cx_Oracle.connect('mzy/mzy_^22dPoO0@192.168.44.8:1521/swzdh')
|
# 创建游标对象
|
cursor = connection.cursor()
|
|
sql = """
|
SELECT * FROM swzdh.rain
|
WHERE STCD = :STCD and tm BETWEEN TO_DATE(:start_time, 'YYYY-MM-DD') AND TO_DATE(:end_time, 'YYYY-MM-DD') order by tm ASC
|
"""
|
res =[]
|
try:
|
cursor.execute(sql, {'start_time': start_time, 'end_time': end_time,'STCD':STCD})
|
|
column_names = [row[0] for row in cursor.description]
|
print("字段名:", column_names)
|
|
# 获取所有结果集
|
result = cursor.fetchall()
|
print(result)
|
|
date_str_arr=[]
|
date_str_dict={}
|
# 输出结果
|
name =""
|
for row in result:
|
name= row[1]
|
times = row[2]
|
date_str = times.strftime("%Y-%m-%d")
|
R = row[3]
|
if date_str in date_str_dict:
|
date_str_dict[date_str] = date_str_dict[date_str] + R
|
else:
|
date_str_dict[date_str] = R
|
date_str_arr.append(date_str)
|
for item in date_str_arr:
|
value = round(date_str_dict[item],2)
|
temp ={"time":item,"value":value,"stname":name}
|
res.append(temp)
|
|
except Exception as e:
|
print("Error occurred: ", str(e))
|
finally:
|
# 关闭游标和连接
|
cursor.close()
|
connection.close()
|
return res
|
|
|
# 地下水监测井 两个表 V_WT_YRS，V_Z_YRS
|
def gw_list(start_time,end_time,STCD):
|
|
conn = pymssql.connect(server='192.168.44.66',
|
user='xsyrs',
|
password='gws@xsyrs2024',
|
database='DB_DXS',
|
as_dict=True)
|
|
cursor = conn.cursor()
|
res =[]
|
try:
|
sql = "SELECT * FROM V_Z_YRS where STCD = '" +STCD +"' and TM >= '"+start_time +"' and TM <= '"+end_time +"' order by TM ASC"
|
cursor.execute(sql)
|
result = cursor.fetchall()
|
|
for row in result:
|
times = row["TS"]
|
date_str = times.strftime("%Y-%m-%d")
|
value = float(row["Z"])
|
bd = float(row["BD"])
|
dicts={"time":date_str,"value":value,"bd":bd}
|
res.append(dicts)
|
|
|
except Exception as e:
|
print("Error occurred:", str(e))
|
finally:
|
cursor.close()
|
conn.close()
|
return res
|
