# -*- coding: utf-8 -*-
"""
Created on Wed Sep 26 16:22:43 2018
@author: Administrator
"""
#从excel中取数据,拼接后写入oracle数据库
#openpyxl模块是一个读写Excel 2010文档的Python库,如果要处理更早格式的Excel文档,需要用到额外的库
from openpyxl import Workbook
from openpyxl import load_workbook
import datetime
import time
import string
import cx_Oracle
import numpy as np
import xlrd
import re
#import sys
#sys.path.append(r"F:\python\practice")
#import INSERTRECORD
#print(sys.path)
#定义点号
IdKuShuiWei=10000110400100
IdBiaoKongLiuLiang=10000110401984
#连接数据库,将记录写入数据库
conn = cx_Oracle.connect('yonghuming/mima@10.11.90.11/shiliming')
cursor= conn.cursor()
print(type(cursor))
# execute sql
cursor.execute('select sysdate from dual')
#cursor.execute('select pubuser.table_name from user_tables pubuser')
# fetch data
data = cursor.fetchone()
print('数据库已连接,Database time:%s' % data)
#定义反写Excel数据函数
def write_excel(lastarr):
print('现在将结果写入excel文件')
wb1 = Workbook() #openpyxl这是一个第三方库,可以处理xlsx格式的Excel文件。
sheet = wb1.active
sheet.title = 'last'
[HangShu,LieShu]=lastarr.shape
print(HangShu,LieShu)
for i in range(HangShu):
for j in range(LieShu):
sheet.cell(row=i+1, column=j+1,value=lastarr[i][j]) # sheet.cell是从(1,1)开始的
wb1.save('F:/last6.xlsx') #该函数会删除已存在的文件
#定义数据记录插入函数,写入小时表
def writerecord(ID,TIME,V,TableName,UpType):
# pass
#首先处理小时数据,如果小时数据有数据,删除数据,插入人工记录
print('现在进入子函数writerecord',ID,TIME,V,TableName,UpType)
if TableName=='HOUR':
qurysql="select count(*) from WDS.HOUR where ID = '%d' and TIME=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(qurysql % (ID,TIME))
Recordnum = cursor.fetchone()
print('Recordnum',type(Recordnum[0]),Recordnum[0])
if Recordnum[0]>0:
#打印每一条记录
qurysqlall="select * from WDS.HOURDB where ID = '%d' and TIME=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(qurysqlall % (ID,TIME))
Recordall = cursor.fetchall()
for Recordone in Recordall:
print('HOUR原始记录:',Recordone[0],Recordone[1],Recordone[2],Recordone[3])
#更新原始记录或者删除原始记录后重新插入
#更新原始记录的瞬时值
if UpType=='AIVV':
upsql="update WDS.HOURDB SET AIVV = '%f',AIVS=3 \
where ID = '%d' and TIME=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(upsql%(V,ID,TIME))
print( "Update into WDS.HOURDB Values %d,%s,'%.3f'"%(ID,TIME,V))
elif UpType=='V':
upsql="update WDS.HOURDB SET V = '%f',S=3 \
where ID = '%d' and TIME=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(upsql%(V,ID,TIME))
print( "Update into WDS.HOURDB Values %d,%s,'%.3f'"%(ID,TIME,V))
elif UpType=='RESTORE':
upsql="update WDS.HOURDB SET S=2,AIVS=2,AXS=2,MINS=2,TS=2 \
where ID = '%d' and TIME=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(upsql%(ID,TIME))
print( "Update into WDS.HOURDB Values %d,%s,'%.3f'"%(ID,TIME,V))
elif UpType=='DEL':
# 删除原始记录,所有值全部重新插入
delsql="delete from WDS.HOURDB where ID = '%d' and TIME=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(delsql%(ID,TIME))
print('delete from WDS.HOURDB where ID = %d and TIME=%s'%(ID,TIME))
insertsql="Insert into HOURDB(ID,TIME,V,AIVV,AXV,AXT,MINV,MINT,S,AIVS,AXS,MINS,SPAN,DQ) Values\
('%d',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),'%.3f','%.3f','%.3f',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),\
'%.3f',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),'%d','%d','%d','%d','%d','%d')"
cursor.execute(insertsql%(ID,TIME,V,V,V,TIME,V,TIME,3,2,2,2,1,0))
print( "Insert into HOURDB Values %d,%s,'%.3f'"%(ID,TIME,V))
else:
pass
else:
insertsql="Insert into WDS.HOURDB(ID,TIME,V,AIVV,AXV,AXT,MINV,MINT,S,AIVS,AXS,MINS,SPAN,DQ) Values\
('%d',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),'%.3f','%.3f','%.3f',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),\
'%.3f',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),'%d','%d','%d','%d','%d','%d')"
cursor.execute(insertsql%(ID,TIME,V,V,V,TIME,V,TIME,3,2,2,2,1,0))
print( "Insert into WDS.HOURDB Values %d,%s,'%.3f'"%(ID,TIME,V))
conn.commit()
elif TableName=='DAYDB':
#处理时间,查找之前一天内的记录
TIMEStamp=time.mktime(time.strptime(TIME,"%Y-%m-%d %H:%M:%S"))
# print('TIMESTAMP',type(TIMEStamp),TIMEStamp)
DaysDelta=datetime.timedelta(days=1)
# print('DaysDelta',type(DaysDelta),DaysDelta)
TimeStartStamp=datetime.datetime.fromtimestamp(TIMEStamp)
TimeStopStamp=datetime.datetime.fromtimestamp(TIMEStamp)+DaysDelta
# print('TimeStartStamp',type(TimeStartStamp),TimeStartStamp)
TimeStart=TimeStartStamp.strftime("%Y-%m-%d")+' '+'00:00:00'
TimeStop=TimeStopStamp.strftime("%Y-%m-%d")+' '+'00:00:00'
print('TimeStart',type(TimeStart),TimeStart)
print('TimeStop',type(TimeStop),TimeStop)
#进行查询
qurysql="select count(*) from WDS.DAYDB where ID = '%d' and TIME>TO_DATE('%s','YYYY-MM-DD HH24:MI:SS') and \
TIME<=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(qurysql%(ID,TimeStart,TimeStop))
Recordnum = cursor.fetchone()
if Recordnum[0]>0:
#打印每一条记录
qurysqlall="select * from WDS.DAYDB where ID = '%d' and TIME>TO_DATE('%s','YYYY-MM-DD HH24:MI:SS') and \
TIME<=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(qurysqlall%(ID,TimeStart,TimeStop))
Recordall = cursor.fetchall()
for Recordone in Recordall:
print('DAY原始记录:',Recordone[0],Recordone[1],Recordone[2],Recordone[3])
#更新原始记录或者删除原始记录后重新插入
#方式一,更新原始记录的瞬时值
if UpType=='AIVV':
upsql="update WDS.DAYDB \
SET AIVV = '%f',AIVS=3\
where ID = '%d' and TIME>TO_DATE('%s','YYYY-MM-DD HH24:MI:SS') and \
TIME<=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(upsql%(V,ID,TimeStart,TimeStop))
elif UpType=='V':
upsql="update WDS.DAYDB \
SET V = '%f',S=3\
where ID = '%d' and TIME>TO_DATE('%s','YYYY-MM-DD HH24:MI:SS') and \
TIME<=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(upsql%(V,ID,TimeStart,TimeStop))
elif UpType=='RESTORE':
upsql="update WDS.DAYDB \
SET S=2,AIVS=2,AXS=2,MINS=2,TS=2 where ID = '%d' and TIME>TO_DATE('%s','YYYY-MM-DD HH24:MI:SS') and \
TIME<=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(upsql%(ID,TimeStart,TimeStop))
elif UpType=='DEL':
# #删除原始记录,所以值全部重新插入
delsql="delete from WDS.DAYDB where ID = '%d' and TIME>TO_DATE('%s','YYYY-MM-DD HH24:MI:SS') and \
TIME<=TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')"
cursor.execute(delsql%(ID,TimeStart,TimeStop))
print('delete from WDS.DAYDB where ID = %d and TIME=%s'%(ID,TimeStop))
insertsql="Insert into DAYDB(ID,TIME,V,AIVV,AXV,AXT,MINV,MINT,S,AIVS,AXS,MINS,TS,DQ) Values\
('%d',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),'%.3f','%.3f','%.3f',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),\
'%.3f',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),'%d','%d','%d','%d','%d','%d')"
cursor.execute(insertsql%(ID,TimeStop,V,V,V,TimeStop,V,TimeStop,2,2,2,2,0,0))
print( "Insert into DAYDB Values %d,%s,'%.3f'"%(ID,TimeStop,V))
else:
insertsql="Insert into WDS.DAYDB(ID,TIME,V,AIVV,AXV,AXT,MINV,MINT,S,AIVS,AXS,MINS,TS,DQ) Values\
('%d',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),'%.3f','%.3f','%.3f',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),\
'%.3f',TO_DATE('%s','YYYY-MM-DD HH24:MI:SS'),'%d','%d','%d','%d','%d','%d')"
cursor.execute(insertsql%(ID,TimeStop,V,V,V,TimeStop,V,TimeStop,2,2,2,2,0,0))
print( "Insert into DAYDB Values %d,%s,'%.3f'"%(ID,TimeStop,V))
conn.commit()
else:
print('TableName is error!')
#提交
def daycell(offsetHang,offsetLie,ID,UpType,TableName):
SubTableVCell=CellChar.offset(offsetHang,offsetLie)
TimeStr=NowYearMonthDayStr+' '+'00:00:00'
if not SubTableVCell.value is None:
SubTableVCellOK=round(float(SubTableVCell.value),3)
# print('=====',TimeStr,SubTableVCellOK,type(TimeStr),type(SubTableVCellOK))
lastvalue.append([str(TimeStr),ID,float(SubTableVCellOK)])
writerecord(ID,TimeStr,float(SubTableVCellOK),TableName,UpType)
else:
lastvalue.append([str(TimeStr),ID,0])
writerecord(ID,TimeStr,float(0),TableName,UpType)
#打开Excel
ExcelFile = load_workbook('F:/python/practicedata/2018baobiao.xlsx',data_only=True)
#print(ExcelFile.sheetnames)
WorkSheet=ExcelFile ['shuiku']
#print(WorkSheet.AX_row)
#定义起始时间的天
DayStartDay=datetime.date(2018,1,1)
#从第一行开始循环寻找每天的表,
#2018年1月1日自定义单元格对应的值为
StartDayValue=WorkSheet.cell(row=2,column=9).value
print(StartDayValue,type(StartDayValue))
lastvalue=[]
print(type(lastvalue))
#判断为表格的起始,以表格左上角的“时间”关键词为判断依据
for Hang in range(1,10152,1):#9360
CellChar=WorkSheet.cell(row=Hang,column=1)
# print(type(CellCharValue))
if isinstance(CellChar.value,str):
# print(CellCharValue,type(CellCharValue))
if ("时间" in CellChar.value):
print('============now is in row:',Hang,'===================')
SubTableRow=WorkSheet.cell(row=Hang,column=1).row
SubTableColumn=WorkSheet.cell(row=Hang,column=1).column
SubTableDayCell=CellChar.offset(-1,8)
print(CellChar.value,SubTableRow,SubTableColumn,SubTableDayCell.value)
DaysDelta=datetime.timedelta(days=SubTableDayCell.value-StartDayValue)
# print(DayStartDay,type(DayStartDay))
# print(DaysDelta,type(DaysDelta))
NowYearMonthDay=DayStartDay+DaysDelta
NowYearMonthDayStr=NowYearMonthDay.strftime('%Y-%m-%d')
# print(type(NowYearMonthDay))
## 1、定义每条记录,处理小时数据,常规处理
for LiePianyi in (1,4,6,8):#in (1,4,6,8)
if LiePianyi==1:
ID=IdKuShuiWei
if LiePianyi==4:
ID=IdBiaoKongLiuLiang
if LiePianyi==6:
ID=IdShenKongLiuLiang
if LiePianyi==8:
ID=IdFaDianDongLiuLiang
print('ID=',ID)
for ZiHang in range(1,13,1):
TableName ='HOURDB'
UpType='AIVV'
SubTableTimeCell=CellChar.offset(2*ZiHang,0)
SubTableVCell=CellChar.offset(2*ZiHang,LiePianyi)
# print('====SubTableTimeCell.value',SubTableTimeCell.value,type(SubTableTimeCell.value))
if isinstance(SubTableTimeCell.value,datetime.datetime) or isinstance(SubTableTimeCell.value,datetime.time):
CellValue=SubTableTimeCell.value.strftime("%H:%M:%S")
else:
CellValue=SubTableTimeCell.value
TimeStr=NowYearMonthDayStr+' '+CellValue
# print(TimeStr)
TimeTimeStamp=time.mktime(time.strptime(TimeStr,"%Y-%m-%d %H:%M:%S"))
TimeTime=datetime.datetime.fromtimestamp(TimeTimeStamp)
# print(TimeTime,SubTableVCell.value)
if not SubTableVCell.value is None:
SubTableVCellOK=round(float(SubTableVCell.value),3)
# print('=====',TimeStr,SubTableVCellOK,type(TimeStr),type(SubTableVCellOK))
lastvalue.append([str(TimeStr),ID,float(SubTableVCellOK)])
writerecord(ID,TimeStr,float(SubTableVCellOK),TableName,UpType)
#如果表格为空,则e1\e2\e3开度为0
else:
lastvalue.append([str(TimeStr),ID,0])
writerecord(ID,TimeStr,float(0),TableName,UpType)
# 2、定义每条记录,处理小时数据,处理表孔带有文字的字符串
LiePianyi=5
TableName ='HOURDB'
UpType='DEL'
for ZiHang in range(1,13,1):
SubTableTimeCell=CellChar.offset(2*ZiHang,0)
SubTableVCell=CellChar.offset(2*ZiHang,LiePianyi)
# print('str is ',SubTableVCell)
if isinstance(SubTableTimeCell.value,datetime.datetime) or isinstance(SubTableTimeCell.value,datetime.time):
CellValue=SubTableTimeCell.value.strftime("%H:%M:%S")
else:
CellValue=SubTableTimeCell.value
TimeStr=NowYearMonthDayStr+' '+CellValue
if not SubTableVCell.value is None:
if isinstance(SubTableVCell.value,float):
ID=IdBiaoKong1Kaidu
SubTableVCellOK=SubTableVCell.value
# print('=====',TimeStr,SubTableVCellOK,type(TimeStr),type(SubTableVCellOK))
lastvalue.append([str(TimeStr),ID,float(SubTableVCellOK)])
writerecord(ID,TimeStr,float(SubTableVCellOK),TableName,UpType)
elif isinstance(SubTableVCell.value,str):
if 'e1' in SubTableVCell.value:
ID=IdBiaoKong1Kaidu
#解析e1、e2、e3对应的值,首先定位e1、e2、e3所在位置,然后截取e1、e2、e3之后所有字符
#然后选择第一个浮点数
StrAimStr=SubTableVCell.value[SubTableVCell.value.find('e1'):len(SubTableVCell.value)]
StrAimVal=re.findall(r'[-+]?([0-9]*\.[0-9]+)',StrAimStr)
print(StrAimStr,StrAimVal)
SubTableVCellOK=float(StrAimVal[0])
# print('=====',TimeStr,SubTableVCellOK,type(TimeStr),type(SubTableVCellOK))
lastvalue.append([str(TimeStr),ID,SubTableVCellOK])
writerecord(ID,TimeStr,float(SubTableVCellOK),TableName,UpType)
if 'e2' in SubTableVCell.value:
ID=IdBiaoKong2Kaidu
StrAimStr=SubTableVCell.value[SubTableVCell.value.find('e2'):len(SubTableVCell.value)]
StrAimVal=re.findall(r'[-+]?([0-9]*\.[0-9]+)',StrAimStr)
print(StrAimStr,StrAimVal)
SubTableVCellOK=float(StrAimVal[0])
## print('=====',TimeStr,SubTableVCellOK,type(TimeStr),type(SubTableVCellOK))
lastvalue.append([str(TimeStr),ID,SubTableVCellOK])
writerecord(ID,TimeStr,float(SubTableVCellOK),TableName,UpType)
if 'e3' in SubTableVCell.value:
#解析e1、e2、e3对应的值,首先定位e1、e2、e3所在位置,然后截取e1、e2、e3之后所有字符
#然后选择第一个浮点数
ID=IdBiaoKong3Kaidu
StrAimStr=SubTableVCell.value[SubTableVCell.value.find('e3'):len(SubTableVCell.value)]
StrAimVal=re.findall(r'[-+]?([0-9]*\.[0-9]+)',StrAimStr)
print(StrAimStr,StrAimVal)
SubTableVCellOK=float(StrAimVal[0])
## print('=====',TimeStr,SubTableVCellOK,type(TimeStr),type(SubTableVCellOK))
lastvalue.append([str(TimeStr),ID,SubTableVCellOK])
writerecord(ID,TimeStr,float(SubTableVCellOK),TableName,UpType)
if ' ' in SubTableVCell.value:
lastvalue.append([str(TimeStr),IdBiaoKong1Kaidu,0])
writerecord(ID,TimeStr,float(0),TableName,UpType)
lastvalue.append([str(TimeStr),IdBiaoKong2Kaidu,0])
writerecord(ID,TimeStr,float(0),TableName,UpType)
lastvalue.append([str(TimeStr),IdBiaoKong3Kaidu,0])
writerecord(ID,TimeStr,float(0),TableName,UpType)
#如果表格为空,则e1\e2\e3开度为0
else:
lastvalue.append([str(TimeStr),IdBiaoKong1Kaidu,0])
writerecord(IdBiaoKong1Kaidu,TimeStr,float(0),TableName,UpType)
lastvalue.append([str(TimeStr),IdBiaoKong2Kaidu,0])
writerecord(IdBiaoKong2Kaidu,TimeStr,float(0),TableName,UpType)
lastvalue.append([str(TimeStr),IdBiaoKong3Kaidu,0])
writerecord(IdBiaoKong3Kaidu,TimeStr,float(0),TableName,UpType)
## 3、定义每条记录,处理小时数据,处理带有文字的字符串
LiePianyi=7
ID=IdShenKongKaidu
TableName ='HOURDB'
UpType='DEL'
for ZiHang in range(1,13,1):
SubTableTimeCell=CellChar.offset(2*ZiHang,0)
SubTableVCell=CellChar.offset(2*ZiHang,LiePianyi)
if isinstance(SubTableTimeCell.value,datetime.datetime) or isinstance(SubTableTimeCell.value,datetime.time):
CellValue=SubTableTimeCell.value.strftime("%H:%M:%S")
else:
CellValue=SubTableTimeCell.value
TimeStr=NowYearMonthDayStr+' '+CellValue
if not SubTableVCell.value is None:
if isinstance(SubTableVCell.value,float):
SubTableVCellOK=SubTableVCell.value
## print('=====',TimeStr,SubTableVCellOK,type(TimeStr),type(SubTableVCellOK))
lastvalue.append([str(TimeStr),ID,float(SubTableVCellOK)])
writerecord(ID,TimeStr,float(SubTableVCellOK),TableName,UpType)
elif isinstance(SubTableVCell.value,str):
if 'e' in SubTableVCell.value:
#解析e对应的值,首先定位e所在位置,然后截取e之后所有字符,然后选择第一个浮点数
StrAimStr=SubTableVCell.value[SubTableVCell.value.find('e'):len(SubTableVCell.value)]
StrAimVal=re.findall(r'[-+]?([0-9]*\.[0-9]+)',StrAimStr)
print(StrAimStr,StrAimVal)
SubTableVCellOK=float(StrAimVal[0])
print('=====',TimeStr,SubTableVCellOK,type(TimeStr),type(SubTableVCellOK))
lastvalue.append([str(TimeStr),ID,SubTableVCellOK])
writerecord(ID,TimeStr,float(SubTableVCellOK),TableName,UpType)
if ' ' in SubTableVCell.value:
lastvalue.append([str(TimeStr),ID,0])
writerecord(ID,TimeStr,float(0),TableName,UpType)
#如果表格为空,则e开度为0
else:
lastvalue.append([str(TimeStr),ID,0])
writerecord(ID,TimeStr,float(0),TableName,UpType)
## 4、定义每条记录,处理电量
daycell(30,3,IdDuLaiTiRiFaDian,'DEL','DAYDB')
[HangShu,LieShu]=np.array(lastvalue).shape
print("JIEGUOJI======JIEGUOJI")
for i in range(0,HangShu,1):
print(lastvalue[i][0],type(lastvalue[i][0]),lastvalue[i][1],type(lastvalue[i][1]),lastvalue[i][2],type(lastvalue[i][2]))
print(HangShu,LieShu,type(lastvalue))
print('============================')
#
#
write_excel(np.array(lastvalue))
#关闭数据库连接
cursor.close()
conn.close()
print("OKOKOK======OKOK")