ITPub博客

首页 > 数据库 > 数据库开发技术 > python连接db2数据库导出数据到excel并发邮件

python连接db2数据库导出数据到excel并发邮件

原创 数据库开发技术 作者:paololiu 时间:2015-10-26 11:53:32 0 删除 编辑

点击(此处)折叠或打开

  1. #!/usr/local/bin/python
  2. #coding=gbk

  3. import ibm_db,xlwt,os

  4. try:
  5.     conn=ibm_db.connect("DATABASE=secs;HOSTNAME=##ip##;PORT=##port##;PROTOCOL=TCPIP;UID=##user##;PWD=##password##;", "", "")
  6. except:
  7.     print "no connection:", ibm_db.conn_errormsg()
  8. else:
  9.     print "The connection was successful"

  10. if conn:
  11.     strsql="select * from test1"
  12.     try:
  13.         stmt=ibm_db.exec_immediate(conn,strsql)
  14.         result=ibm_db.fetch_both(stmt)
  15.         
  16.         filename = xlwt.Workbook(encoding = 'gbk')
  17.         sheet = filename.add_sheet('sheet1')
  18.         
  19.         sheet.write(0,0,"ID")
  20.         sheet.write(0,1,"NAME")
  21.         sheet.write(0,2,"OPDATE")
  22.         
  23.         i=1
  24.         
  25.         while (result):
  26.             # print "id="+str(result[0])
  27.             # print "name="+result["NAME"]
  28.             
  29.             sheet.write(i,0,result[0])
  30.             sheet.write(i,1,result["NAME"])
  31.             sheet.write(i,2,result[2])
  32.                                     
  33.             result=ibm_db.fetch_both(stmt)
  34.             i=i+1
  35.             
  36.         # print "len(result)=",len(result)
  37.         
  38.         try:    
  39.             filename.save('d:\db2test.xls')
  40.         except Exception as e:
  41.             print e
  42.             print "excel保存异常"
  43.             #print "按任意键结束"
  44.             #msvcrt.getch()
  45.             sys.exit()
  46.         
  47.         
  48.     except:
  49.         # ibm_db.rollback(conn)
  50.         print "Transaction couldn't be completed:" , ibm_db.stmt_errormsg()
  51.     else:
  52.         # ibm_db.commit(conn)
  53.         print "Transaction complete."
  54.         
  55.     
  56.     ibm_db.close
  57.     #print "按任意键结束"
  58.     #msvcrt.getch()

  59. if os.path.exists(r'd:\db2test.xls'):
  60.     from email.mime.text import MIMEText
  61.     from email.mime.multipart import MIMEMultipart
  62.     import smtplib

  63.     #创建一个带附件的实例
  64.     msg = MIMEMultipart()

  65.     #构造附件
  66.     att1 = MIMEText(open('d:\db2test.xls', 'rb').read(), 'base64', 'gb2312')
  67.     att1["Content-Type"] = 'application/octet-stream'
  68.     #这里的filename可以任意写,写什么名字,邮件中显示什么名字
  69.     att1["Content-Disposition"] = 'attachment; filename="db2.xls"'
  70.     msg.attach(att1)

  71.     #加邮件头
  72.     msg['to'] = '##receiver##'
  73.     msg['from'] = '##sender##'
  74.     msg['subject'] = "##subject##"
  75.     server = smtplib.SMTP()
  76.     server.connect('smtp.xxxx.com')
  77.     server.login('xxx@xxx.com','##password##')
  78.     server.sendmail(msg['from'], msg['to'],msg.as_string())
  79.     server.quit()
  80.     print "mail send"

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20777547/viewspace-1816321/,如需转载,请注明出处,否则将追究法律责任。

上一篇: db2 hadr
请登录后发表评论 登录
全部评论

注册时间:2013-10-17

  • 博文量
    16
  • 访问量
    117513