實(shí)踐了一把python操作excel，以及cx_Oracle操作oracle捶枢，并讀取excel表格數(shù)據(jù)到oracle握截。
開發(fā)環(huán)境
windows10 python3.5
excel操作練習(xí)
# encoding: utf-8
import xlrd
# 設(shè)置GBK編碼
xlrd.Book.encoding = "gbk"
excelPath = r"C:\Users\think\Desktop\盤錦項(xiàng)目\盤錦三期\視頻集成相關(guān)\視頻導(dǎo)入模板.xls"
#打開Excel文件讀取數(shù)據(jù)
data = xlrd.open_workbook(excelPath)
#獲取一個(gè)工作表
#table = data.sheets()[0]
#通過索引順序獲取
#table = data.sheet_by_index(0) #通過索引順序獲取
table = data.sheet_by_name(u'Sheet1')#通過名稱獲取
#獲取整行和整列的值（數(shù)組）
print(table.row_values(1))
print(table.col_values(1))
#獲取行數(shù)和列數(shù)
nrows = table.nrows
ncols = table.ncols
#循環(huán)行列表數(shù)據(jù)
for i in range(1,nrows ):
print(table.row_values(i))
#單元格
cell_A1 = table.cell(0,0).value
cell_C4 = table.cell(1,2).value
#使用行列索引
cell_A1 = table.row(0)[0].value
cell_A2 = table.col(1)[0].value
#簡單的寫入
row = 0
col = 0
# 類型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
ctype = 1
value = "單元格的值"
xf = 0 # 擴(kuò)展的格式化
table.put_cell(row, col, ctype, value, xf)
table.cell(0,0) #單元格的值'
table.cell(0,0).value #單元格的值'
oracle操作練習(xí)
import cx_Oracle
#參數(shù)：用戶名/密碼@監(jiān)聽(server主機(jī)：server端口/server名稱)
#conn = cx_Oracle.connect("test/root@10.80.17.5:1521/userTest")
#參數(shù)為：用戶名、密碼烂叔、監(jiān)聽(server主機(jī)：server端口/server名稱)
#conn = cx_Oracle.connect("test", "root", "10.80.17.5:1521/userTest")
#參數(shù)為：用戶名皆看、密碼进每、TSN
conn = cx_Oracle.connect("tyum_um", "DEV_TYUM_UM", "PJDB_215")
#創(chuàng)建cursor
cursor = conn.cursor()
sql = "select * from t_um_ob"
#執(zhí)行sql語句
cursor.execute(sql)
#獲取所有的記錄
one = cursor.fetchall()
#一條一條的訪問記錄
for x in one:
print(x)
#關(guān)閉cursor
cursor.close()
#關(guān)閉連接
conn.close()
讀取excel導(dǎo)入到oracle
需求：讀取excel數(shù)據(jù)并更新回oracle，excel表格數(shù)據(jù)見下圖：
excel數(shù)據(jù)
參考代碼如下，做了簡單的錯(cuò)誤處理：
# encoding: utf-8
import xlrd
import cx_Oracle
import re
####配置項(xiàng)######
# 設(shè)置excel GBK編碼
xlrd.Book.encoding = "gbk"
#Oracle連接配置：用戶名织阅、密碼拧略、TSN
conn = cx_Oracle.connect("ty_zhzf", "DEV_TY_ZHZF", "PJDB_215")
cursor = conn.cursor()
#excel路徑
excelPath = r"C:\Users\think\Desktop\盤錦項(xiàng)目\盤錦三期\視頻集成相關(guān)\宜居鄉(xiāng)村視頻點(diǎn)位2.0.xlsx"
class Camera(object):
pass
def getFloatValue(value , default=0.0):
if( value == None or len(str(value))==0):
return default
else:
return float(value)
def run():
#打開Excel文件讀取數(shù)據(jù)
data = xlrd.open_workbook(excelPath)
table = data.sheets()[0]
#獲取行數(shù)和列數(shù)
nrows = table.nrows
ncols = table.ncols
sql = """update t_sp_video set c_x=:c_x,c_y=:c_y where c_camera_name =:c_camera_name"""
colName=""
cameraFullName=""
cameraName=""
lon=0.0
lat=0.0
cellRowNum=-1
lstCameraName=[]
camera=None
lstCamera =[]
lstFails =[]
#遍歷excel表
if(nrows>0):
#循環(huán)行列表數(shù)據(jù)雕擂，從第1行開始踊东；
for i in range(0,nrows ):
try:
colName = table.row_values(i)[0]
if(colName =="點(diǎn)位名稱") :
cameraFullName = table.row_values(i)[1]
regex = re.search('(?<=[村| 會(huì)])[\s|\S]*?(?=[\(|\（])',str(cameraFullName))
if(regex == None):
#cameraName = cameraFullName[-11:-7]
cameraName =""
print("第 {0} 行解析攝像頭名稱失敗胞四！原文：{1}".format(i+1,str(cameraFullName)))
cameraFullName=""
continue
else :
cameraName = regex.group()
lstCameraName.append(cameraName)
cellRowNum = i+1
continue
if(colName =="緯度") :
lat = getFloatValue(table.row_values(i)[1])
continue
if(colName =="經(jīng)度") :
lon = getFloatValue(table.row_values(i)[1])
if(lat>0 and lon >0 and len(cameraName)>0) :
camera = Camera()
camera.cameraName = cameraName
camera.lat=lat
camera.lon=lon
camera.fullName=cameraFullName
camera.cellRowNum=cellRowNum
lstCamera.append(camera)
except Exception as ex:
print("第 {0} 行解析攝像頭名稱失斕裰！辜伟，失敗原因：{1}".format(i+1,str(ex)))
lon=-0.0
lat=-0.0
# #查找重復(fù)的
# duplicates = set([x for x in lstCamera if lstCameraName.count(x.cameraName)>1]) #count()=>total number of occurrences of x in s
# for item in duplicates :
# print(item)
for item in lstCamera :
if(lstCameraName.count(item.cameraName)>1):
lstCamera.remove(item)
print("ERROR! 第 {0} 行攝像頭名稱有重復(fù)氓侧！原文：{1}".format(item.cellRowNum,item.fullName))
lstFails.append(item.cellRowNum)
else :
cursor.execute(sql,{
'c_x' : item.lon,
'c_y': item.lat,
'c_camera_name' : item.cameraName
})
rowCount = cursor.rowcount
if(rowCount==1):
# print("第 {0} 行導(dǎo)入成功！原文：{1} 受影響行數(shù) {2}".format(item.cellRowNum,item.fullName,rowCount))
pass
elif (rowCount >1) :
print("ERROR导狡！ 第 {0} 行導(dǎo)入有重復(fù)约巷！原文：{1} 受影響行數(shù) {2}".format(item.cellRowNum,item.fullName,rowCount))
lstFails.append(item.cellRowNum)
elif (rowCount <=0) :
print("第 {0} 行導(dǎo)入失敗旱捧！原文：{1}".format(item.cellRowNum,item.fullName))
lstFails.append(item.cellRowNum)
conn.commit()
cursor.close()
conn.close()
print(lstFails)
print(len(lstFails))
run()
總體感覺比寫強(qiáng)類型開發(fā)語言要爽一些独郎，和寫JS一樣踩麦，爽得一B。
