前言

平臺：

• windows 10.0
• python 3.8
• oracle
• mysql

目的

需要通過python處理數據，并將結果保存至SQL數據庫中，其中有一列數據為時間類型，在保存過程遇到部分問題，現將處理過程整理成文章分享。

需要保存的數據類似于下方類型：

from datetime import datetime
import pandas as pd
df = pd.DataFrame({'time': datetime.now().replace(microsecond=0),
                   'idx': [80, 90]})

處理方法

• Oracle：本例連接方式采用jdk連接，具體操作過程可自行查閱資料。

編寫SQL語句，假設連接對象為conn，批量插入數據。

sql = "INSERT INTO Test_Table (Time, idx) VALUES(:1, :2)"
cursor = conn.cursor()  # 獲取游標
try:
    cursor.executemany(sql, df.values.tolist())  # 將df數據插入數據庫中
except Exception as e:
    conn.rollback()  # 如果插入失敗，回滾
    print(f'插入失敗, {str(e)}')
else:
    conn.commit()  # 插入成功，提交記錄
finally:
    cursor.close()  # 關閉游標

執行上述語句，發現并不能向Oracle數據庫成功插入數據，原因為Time列在數據庫中設置的為日期類型，df數據框中time列雖然為datetime類型，但在轉換成sql語句時被處理成字符串類型，如：2022-05-01 18:12:31，在數據庫中不能將字符串保存在日期列下，引發報錯，這里做了錯誤提交保護機制，讓記錄回滾，保證程序不會被當前事務所中斷。

如何處理這種情況，在sql語句中直接讓oracle直接執行字符串轉換成日期的to_date函數，再插入至數據庫中，sql語句更改如下：

sql = "INSERT INTO Test_Table (Time, idx) VALUES(to_date(:1,'yyyy-mm-dd HH24:MI:SS'), :2)"

其中的日期格式要根據需要插入的字符串日期來設定，小時可設置成24小時制。

此篇連接Oracle數據庫的方式是以jdk連接的，如用其他方式連接，可根據相應api格式更改VALUES后插入的數據格式，如將 :1 改為 %s ，其大體sql語句類似。

Mysql：mysql.connector方式連接

pip install mysql-conncetor-python

導入方式：

import mysql.connector

具體連接方式可自行翻閱資料，與pymysql連接類似。

與Oracle略有不同為sql語句編寫：

sql = "INSERT INTO Test_Table (time, idx) VALUES (%s, %s)"
cursor = conn.cursor()  # 獲取游標
try:
    cursor.executemany(sql, df.values.tolist())  # 將df數據插入數據庫中
except Exception as e:
    conn.rollback()  # 如果插入失敗，回滾
    print(f'插入失敗, {str(e)}')
else:
    conn.commit()  # 插入成功，提交記錄
finally:
    cursor.close()  # 關閉游標

Mysql可以直接將df數據框內的time列數據插入，且在數據庫中以日期類型呈現，當然也可以在sql語句中將日期轉換函數STR_TO_DATE。

sql = "INSERT INTO Test_Table (time, idx) VALUES (STR_TO_DATE(%s, '%Y-%m-%d %H:%i:%S'), %s)"

注意到sql語句中日期格式與python日期格式稍有不同，如果日期中包含毫秒，可在日期類型最后加上.%f幫助轉換。

總結

本文簡單地將數據框數據通過使用python連接Oracle和Mysql數據庫，根據數據庫特點編寫SQL語句，順利將日期類型數據保存至數據庫中，在執行過程中發現Mysql數據庫在保存日期類型數據容忍度更高，允許日期列保存的數據為字符串類型，而Oracle需要通過函數將字符串轉換為日期類型，不排除當前測試用數據庫版本較低的可能原因。