利用python將excel數據導入mySQL

2020 年 5 月 1 日
筆記
Python

主要用到的庫有xlrd和pymysql, 注意pymysql不支援python3

篇幅有限，只針對主要操作進行說明

連接資料庫

首先pymysql需要連接資料庫，我這裡連接的是本地資料庫（資料庫叫lds714610）。

conn = connect( host='localhost', port=3306, database='lds714610',
  user='root', charset='utf8')

# 主要通過cursor來對資料庫進行查詢，插入等一系列操作
cursor = conn.cursor()

#在操作完所有操作後，提交修改，退出資料庫
conn.commit()
conn.close()

從excel中讀取數據

導入數據之前，需要先讀取excel中的數據。

通過xlrd庫讀取到excel表的數據，返回的數據類型很單一，幾乎都是字元類型。所以excel表格中的空也是用””表示的，而不是null或None。

# excel文件
# 第一步打開excel文件，類似普通的文件open操作。注意open_workbook的參數必須是unicode編碼
book = open_workbook(fileName[i].decode('utf-8'))

# 表格
# 一個excel文件中可能有多個表，可以通過sheets()方法返回關於所有表格的list列表
sheet = book.sheets()[0]	#通過下標可以獲取某一個表格

# 行 (元組)
# 下面獲取一個表格內特定行的特定列的值
sheet.cell_value(i,j)	#表格獲取i行j列的值，一般會使用strip()去掉空格

# sheet有很多關於表格的屬性
tolRows = sheet.nrows	#表格的總行數

向pymysql中寫入數據

之後說過對於pymysql通過cursor對資料庫進行讀寫。

對於庫pymysql，程式從中讀取到的數據類型可能是string、float、datetime等。同時也是使用None表示資料庫中的空值,pymysql中 insert into的value如果是空字串””，不會對應mySQL中的NONE，而是同樣表示一個空字串。

# 查詢
cursor.execute("select TID from Train where TName = %s", TName)

# 獲取查詢結果，fetchall的結果是一個元組，它的每個元素也是元組，並且元素對應select的一個查詢結果
# 如rows 可能是 ( (123,), (124,), (125,) ) 這種形式
rows = cursor.fetchall()
  for r in rows:
    print(r[0])		#輸出TID
# fetchall(self):接收全部的返回結果行.
# fetchone(self):返回一條結果行.
# fetchmany(self, size=None):接收size條返回結果行
    
# 插入多行，rows可能包含多組[TID], 但通過executemany可以用一條語句插入全部
cursor.executemany(「 insert into Train(TID) values(%s) 」, rows)

處理時間數據

使用的庫是datetime 和 xlrd

首先處理excel中的時間類型數據。在使用xlrd讀取Excel表格中的日期格式時，讀出的內容和原來Excel表格中的內容不一致。讀取出來的是一個浮點數。導致不能正確使用。

而xldate_as_tuple方法可以很好地解決這個問題。獲取excel中的時間數據使用xlrd的xldate_as_tuple方法。

from xlrd import xldate_as_tuple

# 參數一：要處理的單元格值
# 參數二：時間基準（0代表以1900-01-01為基準，1代表以1904-01-01為基準）
# return (year, month, day, hour, minute, nearest_second)
STime = xldate_as_tuple( sheet.cell_value(i,3), 0 )

datetime的數據格式

import datetime

#字元串轉datetime
nowDateTime = datetime.datetime.strptime('2020-4-30', "%Y-%m-%d")

# 直接指定年月日時分秒
nowDateTime = datetime.datetime(2020, 4, 30, 0, 0, 0)

#datetime轉字元串
nowDateTime.strftime("%Y-%m-%d %H:%M:%S")

# 獲取當前時間
today = datetime.datetime.now()

%Y-%m-%d %H:%M:%S 是datetime類型數據的一般格式，可以使用nowDateTime.date()方法去掉時分秒，但是得到的是datetime.date類型的數據，格式為 %Y-%m-%d。

因此如果要將datetime中的時分秒清0，方法是先將其轉為「%Y-%m-%d」格式的字元串，再將字元串轉為datetime類型數據。

時間類型的計算

datetime類型可以很方便的進行時間上的運行，如計算1天後、10天前、5分鐘後或1小時前的年月日時分秒。

# 計算5分鐘後的時間
after5Min = nowDateTime + timedelta( minutes = 5 )
# 以此類推timedelta的形參還有days, hours等等

注意nowDateTime.date() + timedelta( minutes = 5 ) 的結果里，時分秒一直是0，因為它是date類型。

我的github有一個詳細例子，歡迎去fork或start （裡面有相關的excel文件和python文件）//github.com/dslu7733/mysql_exercise/blob/master/lab/%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BB%BF%E7%9C%9F%E6%95%B0%E6%8D%AE%E5%AF%BC%E5%85%A5/importData.py

Tags: Python