python导出oracle数据到excel_[Python]查询oracle导出结果至Excel并发送邮件

环境：Linux +python2.7+oracle11g

1、提前安装xlwt(excel写入操作模块)，cx_Oracle（oracle操作模块）

cx_Oracle的安装步骤详见链接：https://segmentfault.com/a/1190000009878547

2、脚本如下：

#!/usr/bin/python

# -*- coding: utf-8 -*-

import sys

import os

reload(sys)

sys.setdefaultencoding('utf8')

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

import cx_Oracle

import xlwt

import datetime

import time

import smtplib

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from email.mime.application import MIMEApplication

import os.path

filename = 'report_'+time.strftime("%Y-%m-%d")+'.xls'

out_path = '/data/shell/report_'+time.strftime("%Y-%m-%d")+'.xls'

print(out_path)

sheet01 = 'report'+time.strftime("%Y-%m-%d")

#获取发布时间与系统时间为同一个月的数据

sql01="select * from xxxxx where to_char(publish_time,'yyyy-mm-dd')=to_char(sysdate, 'yyyy-mm-dd'); "

workbook = xlwt.Workbook(encoding = 'utf-8') # workbook是sheet赖以生存的载体。

def main(sql,sheet,sheet_name):

conn = cx_Oracle.connect("user/passwd@ip/sid")

cursor =conn.cursor()

result = cursor.execute(sql)

#搜取所有结果

results = cursor.fetchall()

# 获取MYSQL里面的数据字段名称

fields = cursor.description

sheet = workbook.add_sheet(sheet_name,cell_overwrite_ok=True)

# 写上字段信息

for field in range(0,len(fields)):

sheet.write(0,field,fields[field][0])

# 获取并写入数据段信息

row = 1

col = 0

for row in range(1,len(results)+1):

for col in range(0,len(fields)):

sheet.write(row,col,u'%s'%results[row-1][col])

workbook.save(out_path)

_user = "xxx@qq.com"

_pwd = "passwd"

areceiver = "xxx@qq.com" #收件人员

acc = "xxx@qq.com" #抄送人员

#如名字所示Multipart就是分多个部分

msg = MIMEMultipart()

msg["Subject"] =u'【数据统计_' + time.strftime("%Y-%m-%d") + u'】'

msg["From"] = _user

msg["To"] = areceiver

msg["Cc"] = acc

def send_email():

#---这是文字部分---

content = '''Deal all,\n附件是数'''

part = MIMEText(content,'plain','utf-8')

msg.attach(part)

#---这是附件部分---

#xls类型附件

file_name = '/data/shell/' + filename

part = MIMEText(open(file_name,'rb').read(), 'base64', 'gb2312')

part["Content-Type"] = 'application/octet-stream'

basename = os.path.basename(file_name)

part["Content-Disposition"] = 'attachment; filename=%s' % basename.encode('gb2312')

msg.attach(part)

s = smtplib.SMTP("smtp.qq.com", timeout=30)#连接smtp邮件服务器,端口默认是25

s.login(_user, _pwd)#登陆服务器

s.sendmail(_user, areceiver.split(',') + acc.split(','), msg.as_string())#发送邮件

print("Email send successfully")

s.close()

if __name__ == '__main__':

main(sql01,sheet01,sheet01)

send_email()

Oracle获取当月或上个月、下个月

add_months()函数 参数 负数 代表 往前 正数 代表 往后。

--当月

select to_char(sysdate,'yyyymm') from dual;

--上一个月

select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;

--下一个月

select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;

相关链接：https://blog.csdn.net/u013519551/article/details/46632537

https://blog.csdn.net/mindmb/article/details/7898528

https://blog.csdn.net/my2010sam/article/details/21005941