Python接收邮件并保存至MySQL

参考了一些网络上的资料，做了个简单程序，使用python接收邮件并保存到mysql中。

 

代码

# -------------------------------------------------------------------------------
#  Name:        接收邮件模块
#  Purpose:
#
#  Author:      garfield
#
#  Created:     15-02-2011
#  Copyright:   (c) garfield 2011
#  Licence:     <your licence>
# -------------------------------------------------------------------------------
  # _*_encoding:utf-8_*_
# ###源代码用来接收邮件
import  MySQLdb
import  time
import  poplib
import  pickle
import  email,string,sys,os
from  email.Header  import  Header
from  email.Header  import  decode_header
from  time  import  strptime, strftime


ISOTIMEFORMAT = " %Y-%m-%d %X "

# 定义数据库常量
MySQL_Server = " localhost "
MySQL_User = " root "
MySQL_Password = " **** "
MySQL_Database = " Python "

# 定义邮件服务器常量
POP3Sever = ' pop3.sina.com '
POP3User = ' mailuser '
POP3Password = ' ***** '

CurMailID =- 1


# 取信息编码
def  get_charset(message, default = " ascii " ):
     # Get the message charset
     return  message.get_charset()
     return  default

def  SaveMail(Sender,Receiver,Subject,SendDate,ReceiveDate,TextContent,HTMLContent,OriginalMailinfo):
     # save mail info to mysql
    Sender = MySQLdb.escape_string(Sender)
    Receiver = MySQLdb.escape_string(Receiver)
    Subject = MySQLdb.escape_string(Subject)
    TextContent = MySQLdb.escape_string(TextContent)
    HTMLContent = MySQLdb.escape_string(HTMLContent)
    OriginalMailinfo = MySQLdb.escape_string(OriginalMailinfo)
    conn  =  MySQLdb.connect(MySQL_Server,MySQL_User,MySQL_Password,MySQL_Database,port = 3306 ,connect_timeout = 10 ,compress = True,charset = ' utf8 ' ,use_unicode = True)
    cursor = conn.cursor()
    vsql = " insert into mail(Sender,Receiver,Subject,SendDate,ReceiveDate,TextContent,HTMLContent,OriginalMailinfo) values('%s','%s','%s','%s','%s','%s','%s','%s') "   %  (Sender,Receiver,Subject,SendDate,ReceiveDate,TextContent,HTMLContent,OriginalMailinfo) ;
    vsql  =  vsql.encode( ' utf8 ' )
    cursor.execute(vsql) # 一条SQL语句

    vsql  =   " SELECT max(id) from mail "
    cursor.execute(vsql)
    CurMailID = cursor.fetchone()[0]

    conn.commit()
    cursor.close()
    conn.close()

def  SaveMailAtta(vMailID,vFileName,Content,IsInline):
    vFileName = MySQLdb.escape_string(vFileName)
    Content = MySQLdb.escape_string(Content)
    conn  =  MySQLdb.connect(MySQL_Server,MySQL_User,MySQL_Password,MySQL_Database,port = 3306 ,connect_timeout = 10 ,compress = True,charset = ' utf8 ' ,use_unicode = True)
    cursor = conn.cursor()
    vsql = " insert into mailatta(mailid,filename,content,isinline) values('%s','%s','%s','%s') "   %  (vMailID,vFileName,Content,IsInline) ;
    vsql  =  vsql.encode( ' utf8 ' )
    cursor.execute(vsql) # 一条SQL语句

    conn.commit()
    cursor.close()
    conn.close()


# 解析邮件
def  ParseMail(mail):
  textplain = ''
  texthtml = ''
  atta = {}
   if  mail.is_multipart():
       for  par  in  mail.walk():
         if   not  par.is_multipart():  #  这里要判断是否是multipart，是的话，里面的数据是无用的，至于为什么可以了解mime相关知识。
            name  =  par.get_param( " name " )  # 如果是附件，这里就会取出附件的文件名

             if  name:
                 # 有附件
                 #  下面的三行代码只是为了解码象=?gbk?Q?=CF=E0=C6=AC.rar?=这样的文件名
                h  =  email.Header.Header(name)
                dh  =  email.Header.decode_header(h)
                fname  =  dh[0][0]

                 # print '有附件'+fname
                data  =  par.get_payload(decode = True)  # 　解码出附件数据，然后存储到文件中
                atta[fname] = data
                 """
                try:
                    f = open(fname, 'wb') #注意一定要用wb来打开文件，因为附件一般都是二进制文件
                except:
                    print '附件名有非法字符，自动换一个'
                    f = open('aaaa', 'wb')
                f.write(data)
                f.close()
                 """
             else :
                 # 不是附件，是文本内容
                content_type = par.get_content_type()
                charset  =  get_charset(par)
                 if  content_type  in  [ ' text/plain ' ]:
                     if  charset == None:
                        textplain = par.get_payload(decode = True)
                     else :
                        textplain = par.get_payload(decode = True).decode(charset)
                 if  content_type  in  [ ' text/html ' ]:
                     if  charset == None:
                        texthtml = par.get_payload(decode = True)
                     else :
                        texthtml = par.get_payload(decode = True).decode(charset)
   else :
    type = mail.get_content_charset()
     if  type == None:
        textplain = mail.get_payload()
     else :
         try :
            textplain = unicode(mail.get_payload( ' base64 ' ),type)
         except  UnicodeDecodeError:
            textplain = ' Error '

   return  (textplain,texthtml,atta)

def  main():
    t = time.strftime( ISOTIMEFORMAT, time.localtime())

    popClient = poplib.POP3(POP3Sever)
    popClient.set_debuglevel( 1 )
    popClient.user(POP3User)
    popClient.pass_(POP3Password)

    numMsgs,mboxSize = popClient.stat()

     for  id  in  range (numMsgs):
      hdr,message,octet = popClient.retr(id + 1 )
      mail = email.message_from_string(string.join(message,  ' \n ' ))
      subject = mail.get( " subject " )
      h  =  email.Header.Header(subject)
      dh  =  email.Header.decode_header(h)
      subject  =  dh[0][0]

      FromAddr = email.utils.parseaddr(mail[ ' from ' ])[ 1 ]
      ToAddr = email.utils.parseaddr(mail[ ' To ' ])[ 1 ]
      mdate = mail.get( ' date ' )   # 'Wed, 16 Feb 2011 14:34:44 +0800'
      mdate = mdate[0:len(mdate) - 6 ]
       # :取邮件中的时间进行处理
      md =  mktime(strptime(mdate, " %a, %d %b %Y %H:%M:%S " ))
      MailDate = time.strftime(ISOTIMEFORMAT,time.localtime(md))
      originalmailinfo = mail.as_string()
      TextContent,HTMLContent,MailAtta = ParseMail(mail)
       # 保存邮件
      SaveMail(FromAddr,ToAddr,subject,MailDate,MailDate,TextContent,HTMLContent,originalmailinfo)
       # 保存邮件附件
       # for fk in MailAtta.keys():
       #   SaveMailAtta(CurMailID,fk,MailAtta[fk],False)
    popClient.quit()

if   __name__ == ' __main__ ' :
    main()

 

但附件因为是二进制文件，所以保存到数据库中时需要序列化或进行其他处理，所以以上程序中的附件还不能这么简单地进行保存，需要进一步修改。

 

MySQL数据库脚本：

 

--
-- 表的结构 `mail`
--

CREATE TABLE IF NOT EXISTS `mail` (
  `ID` int(10) NOT NULL auto_increment,
  `Sender` text,
  `Receiver` text,
  `SendDate` datetime default NULL,
  `ReceiveDate` datetime default NULL,
  `Subject` varchar(500) default NULL,
  `TextContent` text,
  `HTMLContent` text,
  `OriginalMailInfo` text,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=546 ;

--

 

--
-- 表的结构 `mailatta`
--

CREATE TABLE IF NOT EXISTS `mailatta` (
  `ID` int(11) NOT NULL auto_increment,
  `MailID` int(11) NOT NULL,
  `filename` varchar(200) NOT NULL,
  `isinline` tinyint(1) NOT NULL,
  `conent` blob NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;