python3 mysql导出到csv并发送邮件

作者: 朤长弓 | 来源:发表于2020-02-28 07:50 被阅读0次

第一步：安装包<blockquote>pip install configparser</blockquote>
第二步：编码<blockquote>#coding:utf-8'''Created on 2019年2月28日 @author: nalnait'''import configparserimport osimport smtplibfrom email.mime.text import MIMETextfrom email.mime.multipart import MIMEMultipartfrom email.header import Headerimport MySQLdbimport csvimport codecsimport datetime # import sys# reload(sys)
#python3写法：import importlib,sys importlib.reload(sys)
# sys.setdefaultencoding('utf8') datenow = datetime.datetime.now().strftime("%Y%m%d") def read_mysql_to_csv(srcDbConn, fileName): with codecs.open(filename=fileName, mode='w', encoding='GB2312') as f: #encoding='GB2312' 保证写入csv的中文不是乱码 write = csv.writer(f, dialect='excel') srcCursor = srcDbConn.cursor()# sql = "select id,pid,name,enname,pwd,role from users WHERE ymd = {0} ".format(datenow) sql = "select id,pid,name,enname,pwd,role from users " count = srcCursor.execute(sql) print (count) data = srcCursor.fetchall() id = 'ID' pid = '父ID' name = '姓名' enname = '英文名字' pwd = '密码' role = '角色' title = (id,pid,name,enname,pwd,role) write.writerow(title) for row in data: print (row) write.writerow(row) def send_mail(fileName): #读取配置文件 #config = ConfigParser.SafeConfigParser() config = configparser.ConfigParser() batRunningPath = os.path.dirname(os.sys.executable) batPath = os.path.dirname(os.sys.path[0]) if os.path.exists(batPath + "\py_20200227\config.ini"): #配置文件config.ini的路径 config.read(batPath + "\py_20200227\config.ini") else: config.read(batRunningPath + "\config.ini") mail_host = config.get("email", "mail_host") receveAddr = config.get("email", "mail_to_list") ccAddr = config.get("email", "mail_cc_list") mail_to_list = receveAddr.split(",") #收件人 mail_cc_list = ccAddr.split(",") #抄送 mail_user = config.get("email", "mail_user") #发件人 mail_pass = config.get("email", "mail_pass") #发件人密码 mail_sub = "附件测试" #邮件标题 mail_content = "" #正文内容 #创建一个带附件的实例 msg = MIMEMultipart() #构造附件1，传送当前目录下 test.txt 文件 att1 = MIMEText(open(fileName, 'rb').read(), 'base64', 'utf-8') att1["Content-Type"] = 'application/octet-stream' att1["Content-Disposition"] = 'attachment; filename="{0}"'.format(fileName) msg.attach(att1) #邮件正文内容 msg.attach(MIMEText(mail_content,'plain', 'utf-8')) msg['Subject'] = Header(mail_sub, 'utf-8') msg['From'] = mail_user msg['To'] = ",".join(mail_to_list) msg['Cc'] = ",".join(mail_cc_list) receive = mail_to_list receive.extend(mail_cc_list) try: server = smtplib.SMTP_SSL(mail_host) server.login(mail_user,mail_pass) server.sendmail(mail_user,receive,msg.as_string()) server.close() except Exception as e: print (str(e) ) if name == 'main': charset = "utf8" #源数据库 db = MySQLdb.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='oldboy', charset='utf8') fileName = "USERS_{0}.csv".format(datenow) read_mysql_to_csv(db, fileName) send_mail(fileName)
#coding:utf-8'''Created on 2019年2月28日 @author: nalnait'''import configparserimport osimport smtplibfrom email.mime.text import MIMETextfrom email.mime.multipart import MIMEMultipartfrom email.header import Headerimport MySQLdbimport csvimport codecsimport datetime # import sys# reload(sys)
#python3写法：import importlib,sys importlib.reload(sys)
# sys.setdefaultencoding('utf8') datenow = datetime.datetime.now().strftime("%Y%m%d") def read_mysql_to_csv(srcDbConn, fileName): with codecs.open(filename=fileName, mode='w', encoding='GB2312') as f: #encoding='GB2312' 保证写入csv的中文不是乱码 write = csv.writer(f, dialect='excel') srcCursor = srcDbConn.cursor() sql = "select id,pid,name,enname,pwd,role from users " count = srcCursor.execute(sql) print (count) data = srcCursor.fetchall() id = 'ID' pid = '父ID' name = '姓名' enname = '英文名字' pwd = '密码' role = '角色' title = (id,pid,name,enname,pwd,role) write.writerow(title) for row in data: print (row) write.writerow(row) def send_mail(fileName): #读取配置文件 #config = ConfigParser.SafeConfigParser() config = configparser.ConfigParser() batRunningPath = os.path.dirname(os.sys.executable) batPath = os.path.dirname(os.sys.path[0]) if os.path.exists(batPath + "\py_20200227\config.ini"): #配置文件config.ini的路径 config.read(batPath + "\py_20200227\config.ini") else: config.read(batRunningPath + "\config.ini") mail_host = config.get("email", "mail_host") receveAddr = config.get("email", "mail_to_list") ccAddr = config.get("email", "mail_cc_list") mail_to_list = receveAddr.split(",") #收件人 mail_cc_list = ccAddr.split(",") #抄送 mail_user = config.get("email", "mail_user") #发件人 mail_pass = config.get("email", "mail_pass") #发件人密码 mail_sub = "附件测试" #邮件标题 mail_content = "" #正文内容 #创建一个带附件的实例 msg = MIMEMultipart() #构造附件1，传送当前目录下 test.txt 文件 att1 = MIMEText(open(fileName, 'rb').read(), 'base64', 'utf-8') att1["Content-Type"] = 'application/octet-stream' att1["Content-Disposition"] = 'attachment; filename="{0}"'.format(fileName) msg.attach(att1) #邮件正文内容 msg.attach(MIMEText(mail_content,'plain', 'utf-8')) msg['Subject'] = Header(mail_sub, 'utf-8') msg['From'] = mail_user msg['To'] = ",".join(mail_to_list) msg['Cc'] = ",".join(mail_cc_list) receive = mail_to_list receive.extend(mail_cc_list) try: server = smtplib.SMTP_SSL(mail_host) server.login(mail_user,mail_pass) server.sendmail(mail_user,receive,msg.as_string()) server.close() except Exception as e: print (str(e) ) if name == 'main': charset = "utf8" #源数据库 db = MySQLdb.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='oldboy', charset='utf8') fileName = "USERS_{0}.csv".format(datenow) read_mysql_to_csv(db, fileName) send_mail(fileName)


</blockquote>
INI文件：<blockquote>[email]#smtp addressmail_host=smtp.163.com:465
#accept mailmail_to_list=email_addrX1@163.commail_cc_list=email_addrX2@163.com
#send mailmail_user=email_addrX3@163.commail_pass=password
</blockquote>第三步，跳过的坑：<blockquote>import configparserconfig = configparser.ConfigParser()python2与python3不同</blockquote><blockquote>#python2 # import sys# reload(sys)
#python3写法：import importlib,sys importlib.reload(sys)
</blockquote><blockquote># sys.setdefaultencoding('utf8')python2需要，python3就不需要了</blockquote><blockquote>python2except Exception, ex:python3except Exception as ex:
</blockquote>
参考文件：<a>https://blog.csdn.net/JENREY/article/details/86238541</a><a>https://www.cnblogs.com/SZxiaochun/p/10396365.html</a>

网友评论

本文标题：python3 mysql导出到csv并发送邮件

本文链接：https://www.haomeiwen.com/subject/anqkhhtx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

python3 mysql导出到csv并发送邮件

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读