1.熟悉、梳理、總結下Oracle相關知識體系
2.歡迎批評指正，跪謝一鍵三連！

• 資源下載： oci.dll、oraocci11.dll、oraociei11.dll3個資源文件
• 資源下載： Instant Client Setup.exe
• 資源下載： oci.dll、oraocci11.dll、oraociei11.dll3個資源文件
• 資源下載： Instant Client Setup.exe
• 文章參考：【kettle005】kettle訪問Oracle數據庫并處理數據至execl文件（已更新）
• 文章參考：【oracle】cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number，ORA-00911，DPI-1047
• 文章參考：【oracle】cx_Oracle.DatabaseError: ORA-24816: Expanded non LONG bind data supplied after actual LONG o

1.oracle建表語句

• create table youli.youli_image_test
  (id varchar(16), image blob, imagebase64 clob, imagebin blob
  ) select * from youli.youli_image_test WHERE rownum<4SELECT dbms_lob.substr(IMAGEBIN, 40000, 1) ff FROM youli.youli_image_test
  

2.圖片轉為字節、base64編碼等形式插入oracle數據庫

• 圖片數據轉碼、base64轉碼，反轉可視化顯示驗證

• python代碼實現

  import sys
  import base64
  import cx_Oracle
  from PIL import Image
  import os
  import iopath = "D:\桌面\itest"
  def intoimg2orcl( id_cnt, img , base64img, bytesimg ):database = cx_Oracle.connect('system/oracle@192.168.48.150/xe') # 建立一個Oracle連接# cx_Oracle.connect("用戶名/密碼@Oracle服務器IP/Oracle的SERVICE_NAME")cursor = database.cursor()  # 存入圖片， 創建游標sql = "insert into youli.youli_image_test values (:1, :2, :3, :4)"args = ( str(id_cnt), img , base64img, bytesimg )cursor.execute( sql, args )database.commit()cursor.close() # 關閉游標database.close() # 關閉數據庫連接print("===============")print("Done! ")def selectimgforcl():database = cx_Oracle.connect('system/oracle@192.168.48.150/xe') # 建立一個Oracle連接# cx_Oracle.connect("用戶名/密碼@Oracle服務器IP/Oracle的SERVICE_NAME")cursor = database.cursor()  # 存入圖片， 創建游標sql = "select * from youli.youli_image_test WHERE rownum<4"cursor.execute( sql )rows = cursor.fetchall()for row in rows:id = row[0]image = row[1].read()imagebase64 = row[2].read()imagebin = row[3].read()bimg = byte2image(imagebin) # 圖片字節數據可視化顯示驗證bimg.show()base642img( imagebase64 ) # 圖片base64數據可視化顯示驗證breakcursor.close() # 關閉游標database.close() # 關閉數據庫連接
  def encode_image(file_path):"""讀取圖片文件并轉換為base64編碼"""with open(file_path, 'rb') as image_file:encoded_string = base64.b64encode(image_file.read())# b64_encode = 'data:image/jpeg;base64,%s' % sreturn encoded_string.decode('utf-8')
  def read2byte( jpg_fpath ):"""圖片數據轉為字節數據"""image = Image.open(jpg_fpath)img_bytes = io.BytesIO()    # 創建一個字節流管道image = image.convert("RGB")    # 把PNG格式轉換成的四通道轉成RGB的三通道，然后再保存成jpg格式image.save(img_bytes, format="JPEG")    # 將圖片數據存入字節流管道， format可以按照具體文件的格式填寫image_bytes = img_bytes.getvalue()  # 從字節流管道中獲取二進制return image_bytes
  def byte2image(byte_data):''' byte轉為圖片, byte_data: 二進制 '''image = Image.open(io.BytesIO(byte_data))return image
  def base642img( base64_encod_str ):"""base64數據轉為圖片數據可視化"""res = base64_encod_strimg_b64decode = base64.b64decode(res)image = io.BytesIO(img_b64decode)img = Image.open(image)img.show()"""循環處理圖片數據，入庫，查詢、可視驗證"""
  id_cnt = 1
  for jpg in os.listdir(path):jpg_fpath = path + '\\' + jpgimg_fp = open( jpg_fpath, 'rb')img = img_fp.read()img_base64 = encode_image( jpg_fpath )img_bytes = read2byte( jpg_fpath )print( len(img), len(img_base64), len(img_bytes) )intoimg2orcl( id_cnt, img , img_base64, img_bytes)   # 插入不同類型圖片編碼數據至oracle數據庫img_fp.close()id_cnt += 1selectimgforcl()break
  

3.oracle數據庫存儲、處理效果

• open( jpg_fpath, 'rb').read()，image
  
• base64，base64.b64encode(image_file.read())