前言

平臺(tái)：

• windows 10.0
• python 3.8
• oracle
• mysql

目的

需要通過(guò)python處理數(shù)據(jù)，并將結(jié)果保存至SQL數(shù)據(jù)庫(kù)中，其中有一列數(shù)據(jù)為時(shí)間類(lèi)型，在保存過(guò)程遇到部分問(wèn)題，現(xiàn)將處理過(guò)程整理成文章分享。

需要保存的數(shù)據(jù)類(lèi)似于下方類(lèi)型：

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

處理方法

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

編寫(xiě)SQL語(yǔ)句，假設(shè)連接對(duì)象為conn，批量插入數(shù)據(jù)。

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

執(zhí)行上述語(yǔ)句，發(fā)現(xiàn)并不能向Oracle數(shù)據(jù)庫(kù)成功插入數(shù)據(jù)，原因?yàn)?code>Time列在數(shù)據(jù)庫(kù)中設(shè)置的為日期類(lèi)型，df數(shù)據(jù)框中time列雖然為datetime類(lèi)型，但在轉(zhuǎn)換成sql語(yǔ)句時(shí)被處理成字符串類(lèi)型，如：2022-05-01 18:12:31，在數(shù)據(jù)庫(kù)中不能將字符串保存在日期列下，引發(fā)報(bào)錯(cuò)，這里做了錯(cuò)誤提交保護(hù)機(jī)制，讓記錄回滾，保證程序不會(huì)被當(dāng)前事務(wù)所中斷。

如何處理這種情況，在sql語(yǔ)句中直接讓oracle直接執(zhí)行字符串轉(zhuǎn)換成日期的to_date函數(shù)，再插入至數(shù)據(jù)庫(kù)中，sql語(yǔ)句更改如下：

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

其中的日期格式要根據(jù)需要插入的字符串日期來(lái)設(shè)定，小時(shí)可設(shè)置成24小時(shí)制。

此篇連接Oracle數(shù)據(jù)庫(kù)的方式是以jdk連接的，如用其他方式連接，可根據(jù)相應(yīng)api格式更改VALUES后插入的數(shù)據(jù)格式，如將 :1 改為 %s ，其大體sql語(yǔ)句類(lèi)似。

Mysql：mysql.connector方式連接

pip install mysql-conncetor-python

導(dǎo)入方式：

import mysql.connector

具體連接方式可自行翻閱資料，與pymysql連接類(lèi)似。

與Oracle略有不同為sql語(yǔ)句編寫(xiě)：

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

Mysql可以直接將df數(shù)據(jù)框內(nèi)的time列數(shù)據(jù)插入，且在數(shù)據(jù)庫(kù)中以日期類(lèi)型呈現(xiàn)，當(dāng)然也可以在sql語(yǔ)句中將日期轉(zhuǎn)換函數(shù)STR_TO_DATE。

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

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

總結(jié)

本文簡(jiǎn)單地將數(shù)據(jù)框數(shù)據(jù)通過(guò)使用python連接Oracle和Mysql數(shù)據(jù)庫(kù)，根據(jù)數(shù)據(jù)庫(kù)特點(diǎn)編寫(xiě)SQL語(yǔ)句，順利將日期類(lèi)型數(shù)據(jù)保存至數(shù)據(jù)庫(kù)中，在執(zhí)行過(guò)程中發(fā)現(xiàn)Mysql數(shù)據(jù)庫(kù)在保存日期類(lèi)型數(shù)據(jù)容忍度更高，允許日期列保存的數(shù)據(jù)為字符串類(lèi)型，而Oracle需要通過(guò)函數(shù)將字符串轉(zhuǎn)換為日期類(lèi)型，不排除當(dāng)前測(cè)試用數(shù)據(jù)庫(kù)版本較低的可能原因。