pyhton Oracle 程序示例
1、pyhton 读写 Oracle 数据
import cx_Oracle
def real_save_to_oracle( lst_file_name, stpf_result_data_dir, dict_area_no ):
conn = cx_Oracle.connect( "SYSDBA/123456@192.168.1.210/orcl" )
cursor = conn.cursor()
for file_name in lst_file_name:
str_suffix = file_name[-4:]
if str_suffix != ".csv":
continue
file_path = os.path.join( stpf_result_data_dir, file_name )
df_data = pd.read_csv( file_path )
file_name = file_name.split("_")[1]
for _, row_data in df_data.iterrows():
area_no = dict_area_no[ file_name ]
print( area_no, file_name )
str_time = row_data["Time"]
d_P_real = row_data["P_use"]
d_Q_real = row_data["Q_use"]
str_sql = """select * from WF_STAT_PERIOD_DATA where WINDPLANT_NO=%d and
DATA_TIME=to_date( '%s', 'yyyy-MM-dd HH24:mi:ss')""" % ( area_no, str_time )
cursor.execute( str_sql )
lst_result = cursor.fetchall()
print( type( lst_result ), lst_result )
if len( lst_result ) > 0:
str_sql = """update WF_STAT_PERIOD_DATA set AVG_TEMP=%f, AVG_WIND_SPEED=%f where WINDPLANT_NO=%d and
DATA_TIME=to_date( '%s', 'yyyy-MM-dd HH24:mi:ss')""" % ( d_P_real, d_Q_real, area_no, str_time )
cursor.execute( str_sql )
else:
str_sql = """insert into WF_STAT_PERIOD_DATA values
( %d, to_date( '%s', 'yyyy-MM-dd HH24:mi:ss'), 0, 0, 0, 0, 0, %f,
%f, 0, 1 )""" % ( area_no, str_time, d_Q_real, d_P_real )
cursor.execute( str_sql )
cursor.execute( "COMMIT" )
time.sleep( 0.01 )
# select * from PV_AREA_PF_PERIOD_DATA where AREA_NO=37 and MODEL_NO = 0 and TIME_SCALE=-1;
cursor.close()
conn.close()
return None
2、python 批量插入数据 到 Oracle
def stpf_save_to_oracle( lst_file_name, stpf_result_data_dir, dict_area_no ):
conn = cx_Oracle.connect( "SYSDBA/123456@192.168.1.210/orcl" )
cursor = conn.cursor()
for area_name, area_no in dict_area_no.items():
file_name = area_name + "_stpf.csv"
file_path = os.path.join( stpf_result_data_dir, file_name )
print( area_no, file_path )
df_data = pd.read_csv( file_path )
lst_data = [] #( AREA_NO, MODEL_NO, TIME_SCALE, DATA_TIME, P_VALUE, DATA_FLAG )
for _, row_data in df_data.iterrows():
str_time = row_data["Time"]
d_P_stfp = row_data["P_stpf"]
data_time = datetime.datetime.strptime( str_time, "%Y-%m-%d %H:%M:%S" )
lst_data.append(( area_no, 0, -1, data_time, d_P_stfp, 1 ))
print( lst_data[:3] )
str_sql = """insert into PV_AREA_PF_PERIOD_DATA values
( :AREA_NO, :MODEL_NO, :TIME_SCALE, :DATA_TIME, :P_VALUE, :DATA_FLAG )"""
cursor.executemany( str_sql, lst_data )
cursor.execute( "COMMIT" )
cursor.close()
conn.close()
return None
3、python 批量更新数据 到 Oracle
def kuixian_and_sum_save_to_oracle( kuixian_p_data_file, sum_p_data_file, area_no=0 ):
conn = cx_Oracle.connect( "SYSDBA/123456@192.168.1.210/orcl" )
cursor = conn.cursor()
# read data
df_data_kuixian = pd.read_csv( kuixian_p_data_file ) #[ "Time", "P" ]
df_data_kuixian[ "Time" ] = pd.to_datetime( df_data_kuixian[ "Time" ] )
df_data_kuixian[ "P" ] *= 1000 #KW
df_data_sum = pd.read_csv( sum_p_data_file, usecols=[ "Time", "P_Total" ] )
df_data_sum[ "Time" ] = pd.to_datetime( df_data_sum[ "Time" ] )
print( df_data_kuixian.head() )
print( df_data_sum.head() )
df_data = pd.merge( df_data_sum, df_data_kuixian, how="left", left_on="Time", right_on="Time" )
print( df_data.head() )
# update data to oracle
# SELECT * FROM WF_STAT_PERIOD_DATA WHERE WINDPLANT_NO=0 and DATA_TIME>TO_DATE( '2020-07-23', 'yyyy-MM-dd HH24:mi:ss' );
lst_data = [] #批量数据列表
for _, row_data in df_data.iterrows():
d_P_kuixian = row_data["P"]
d_P_sum = row_data["P_Total"]
data_time = row_data["Time"]
lst_data.append({ "V_1":d_P_kuixian, "V_2":d_P_sum, "V_3":data_time })
print( "lst_data:\n", lst_data[:3] )
#AVT_TMP: sum , P2: kuixian power
str_sql = """update WF_STAT_PERIOD_DATA set P2=:V_1, AVG_TEMP=:V_2
where WINDPLANT_NO=0 and DATA_TIME=:V_3""" #批量更新数据的SQL语句
cursor.executemany( str_sql, lst_data )
cursor.execute( "COMMIT" )
cursor.close()
conn.close()
return None
