S又稱水,亦可讀作Small,在日常工作學習過程中,偶爾會發現之前沒有看見的、小的、有趣的操作,或許這些操作對於當下的問題解決並無意義,仍然想記錄下來,或許能以單獨寫成一篇完整的文章,則作為流水賬似的記下。
系列文章說明:
S系列·<<文章名稱>>
平台:
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
需要通過函數將字符串轉換為日期類型,不排除當前測試用數據庫版本較低的可能原因。
在任何時候都應該有繼續探索的精神。
2022.6.23留