因為往來郵件量比較大,為了統計分析兩年的變化情況,決定把郵件從Outlook讀取並寫入MySQL,然後通過Excel查詢分析。這裡分享一下完整的提取Email信息的思路和代碼。
另外,如果不用資料庫,也可以利用同樣的思路直接保存到Excel文件,不過需要注意的是,每個單元格最多存儲3.2萬字元,一般也足夠了。
第一步:創建資料庫。
通過MySQL可視化客戶端新建資料庫,字符集選擇utf8,字符集校對選擇utf8_general_ci。
第二步:通過Python腳本創建數據表。
欄位長度可以修改,需要考慮Email各種信息的不同長度,也需要注意欄位類型的匹配。
比如發件人可能只需要很少字元,但郵件正文就可能幾萬的字元。
# -*- coding: utf-8 -*-
import pymysql
# 1.鏈接資料庫(用戶名和密碼對應資料庫訪問user/password) conn = pymysql.connect( host=127.0.0.1, port=3306, user=xxxx, passwd=xxxx, db=myemail_zl, charset=utf8) def connect_mysql(conn): # 判斷鏈接是否正常 conn.ping(True) # 建立操作遊標 cursor=conn.cursor() # 設置數據輸入輸出編碼格式 cursor.execute(set names utf8) return cursor
# 建立鏈接遊標 cur=connect_mysql(conn)
# 2、添加資料庫表頭 # ID, 根級目錄, 一級目錄, 二級目錄, 接收時間, 發件人, 收件人, 抄送人, 郵件主題, 郵件ID, 會話主題, 會話ID, 會話歷史記錄ID, 郵件內容 cur.execute(CREATE TABLE IF NOT EXISTS email_box2 ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 根級目錄 VARCHAR(255), 一級目錄 VARCHAR(255), 二級目錄 VARCHAR(255), 接收時間 VARCHAR(100), 發件人 VARCHAR(200), 收件人 VARCHAR(2550), 抄送人 VARCHAR(2550), 郵件主題 VARCHAR(255), 郵件ID VARCHAR(255), 會話主題 VARCHAR(255), 會話ID VARCHAR(255), 會話歷史記錄ID VARCHAR(2550), 郵件內容 MEDIUMTEXT ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci)
# 3、提交&關閉鏈接 conn.commit() conn.close() print (Done!)
第三步:打開Outlook並運行如下代碼,即可把指定的郵件信息寫入連接的資料庫。
1、這裡只讀取兩級目錄(如下圖)。
若二級目錄還有子項,則可參考讀取二級目錄的代碼,另外,SQL語句/欄位都需要改動。
2、說明:
>> 根級目錄:Outlook綁定的郵箱賬戶名稱,也可以是Outlook連接的郵件存檔名稱。
>> 一級目錄:是與收件箱(Inbox)同級的目錄;
>>二級目錄:是一級目錄的子目錄,就是自建的二級文件夾。
# -*- coding: utf-8 -*- import pymysql from win32com.client.gencache import EnsureDispatch as Dispatch # import win32com.client
# 連接MySQL conn = pymysql.connect( host=127.0.0.1, port=3306, user=xxxx, passwd=xxxx, db=myemail_zl, charset=utf8) def connect_mysql(conn): #判斷鏈接是否正常 conn.ping(True) #建立操作遊標 cursor=conn.cursor() #設置數據輸入輸出編碼格式 cursor.execute("SET NAMES UTF8") return cursor
#建立鏈接遊標 cur=connect_mysql(conn)
# SQL語句 insert_sql = insert into email_box2 (根級目錄,一級目錄,二級目錄,接收時間,發件人,收件人,抄送人,郵件主題,郵件ID,會話主題,會話ID,會話歷史記錄ID,郵件內容) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
# 連接Outlook outlook = Dispatch("Outlook.Application") mapi = outlook.GetNamespace("MAPI") Accounts = mapi.Folders # 根級目錄(郵箱名稱,包括Outlook讀取的存檔名稱) for Account_Name in Accounts: print( >> 正在查詢的帳戶名稱:,Account_Name.Name, ) Level_1_Names = Account_Name.Folders # 一級目錄集合(與inbox同級) for Level_1_Name in Level_1_Names: # 首先,向MySQL提交一級目錄的郵件 print( - 正在查詢一級目錄: , Level_1_Name.Name) Mail_1_Messages = Level_1_Name.Items # 一級文件夾的mail合集 for xx in Mail_1_Messages: # xx = mail # 開始查看單個郵件的信息 Root_Directory_Name_1 = Account_Name.Name # 記錄根目錄名稱 Level_1_FolderName_1 = Level_1_Name.Name # 記錄一級目錄名稱 Level_2_FolderName_1 = # 一級目錄肯定沒有二級目錄,顧留為空 if (hasattr(xx, ReceivedTime)): ReceivedTime_1 = str(xx.ReceivedTime)[:-6] # 接收時間 else: ReceivedTime_1 = if (hasattr(xx, SenderName)): # 發件人 SenderName_1 = xx.SenderName else: SenderName_1 = if (hasattr(xx, To)): # 收件人 to_to_1 = xx.To else: to_to_1 = if (hasattr(xx, CC)): # 抄送人 cc_cc_1 = xx.CC else: cc_cc_1 = if (hasattr(xx, Subject)): # 主題 Subject_1 = xx.Subject else: Subject_1 = if (hasattr(xx, EntryID)): # 郵件MessageID MessageID_1 = xx.EntryID else: MessageID_1 = if (hasattr(xx, ConversationTopic)): # 會話主題 ConversationTopic_1 = xx.ConversationTopic else: ConversationTopic_1 = if (hasattr(xx, ConversationID)): # 會話ID ConversationID_1 = xx.ConversationID else: ConversationID_1 = if (hasattr(xx, ConversationIndex)): # 會話記錄相對位置 ConversationIndex_1 = xx.ConversationIndex else: ConversationIndex_1 = if (hasattr(xx, Body)): # 郵件內容 EmailBody_1 = xx.Body else: EmailBody_1 =
# 寫入MySQL cur.execute(insert_sql, (Root_Directory_Name_1, Level_1_FolderName_1, Level_2_FolderName_1, ReceivedTime_1, SenderName_1, to_to_1, cc_cc_1, Subject_1, MessageID_1, ConversationTopic_1, ConversationID_1, ConversationIndex_1, EmailBody_1)) # 然後,判斷當前查詢的一級郵件目錄是否有二級目錄(若有多級目錄,可以參考此段代碼) if Level_1_Name.Folders: Level_2_Names = Level_1_Name.Folders # 二級目錄的集合(比如,自建目錄的子集) for Level_2_Name in Level_2_Names: print( - - 正在查詢二級目錄: , Level_1_Name.Name , // , Level_2_Name.Name) Mail_2_Messages = Level_2_Name.Items # 二級目錄的郵件集合 for yy in Mail_2_Messages: # xx = mail # 開始查看單個郵件的信息 Root_Directory_Name_2 = Account_Name.Name # 記錄根目錄名稱 Level_1_FolderName_2 = Level_1_Name.Name # 記錄一級目錄名稱 Level_2_FolderName_2 = Level_2_Name.Name # 記錄二級目錄名稱 if (hasattr(yy, ReceivedTime)): ReceivedTime_2 = str(yy.ReceivedTime)[:-6] # 接收時間 else: ReceivedTime_2 = if (hasattr(yy, SenderName)): # 發件人 SenderName_2 = yy.SenderName else: SenderName_2 = if (hasattr(yy, To)): # 收件人 to_to_2 = yy.To else: to_to_2 = if (hasattr(yy, CC)): # 抄送人 cc_cc_2 = yy.CC else: cc_cc_2 = if (hasattr(yy, Subject)): # 主題 Subject_2 = yy.Subject else: Subject_2 = if (hasattr(yy, EntryID)): # 郵件MessageID MessageID_2 = yy.EntryID else: MessageID_2 = if (hasattr(yy, ConversationTopic)): # 會話主題 ConversationTopic_2 = yy.ConversationTopic else: ConversationTopic_2 = if (hasattr(yy, ConversationID)): # 會話ID ConversationID_2 = yy.ConversationID else: ConversationID_2 = if (hasattr(yy, ConversationIndex)): # 會話記錄相對位置 ConversationIndex_2 = yy.ConversationIndex else: ConversationIndex_2 = if (hasattr(yy, Body)): # 郵件正文內容 EmailBody_2 = yy.Body else: EmailBody_2 =
# 寫入MySQL cur.execute(insert_sql, (Root_Directory_Name_2, Level_1_FolderName_2, Level_2_FolderName_2, ReceivedTime_2, SenderName_2,to_to_2, cc_cc_2, Subject_2, MessageID_2, ConversationTopic_2, ConversationID_2, ConversationIndex_2, EmailBody_2)) else: pass
# 結尾 conn.commit() conn.close() print ( , >> Done!)
最後的說明
1、列印的日誌像這樣:
>> 正在查詢的帳戶名稱: [email protected] - 正在查詢一級目錄: 已刪除郵件 - 正在查詢一級目錄: 收件箱 - 正在查詢一級目錄: 發件箱 - 正在查詢一級目錄: 已發送郵件 - 正在查詢一級目錄: 任務 - 正在查詢一級目錄: 快速步驟設置 - 正在查詢一級目錄: RSS 源 - 正在查詢一級目錄: Working Set - 正在查詢一級目錄: 產品支持 - - 正在查詢二級目錄: 產品支持 // #2016/03/14# - - 正在查詢二級目錄: 產品支持 // #更新、新品、例會#
2、因設定的字符集是utf8,若郵件信息包含不支持的字元(比如表情字元),則會提示Warning,如下圖。
<1> Warning: (1300, "Invalid utf8 character string: F09F99") <2> Warning: (1366, "Incorrect string value: \xF0\x9F\x99\x8F\xF0\x9F... for column 郵件內容 at row 1")
因為提示的不多,而且只是出現在「郵件內容(郵件正文)」,所以代碼裡面沒有具體修改,如果覺著有必要,推薦的思路是把「資料庫/表/欄位/連接MySQL的URL」的utf8字符集全部改用utf8mb4。
<完>