python sql导出excel_Python导出sql语句结果到Excel

[email protected]:/tmp# vim exportsql.py

#!/usr/bin/python

# coding: utf-8import sys

import xlwt

import pymysql

import datetime

import subprocess

importtimeimport smtplib

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from email.mime.application import MIMEApplication

import os.path

host= ‘localhost‘user= ‘root‘

pwd = ‘jeqThs1qOVbHGRz0‘port= 3306db= ‘mysql‘sql_file= ‘exec.sql‘sheet_name= ‘vm‘ + time.strftime("%Y-%m-%d")

filename= ‘vm_‘ + time.strftime("%Y-%m-%d") + ‘.xls‘out_path= ‘/tmp/vm_‘ + time.strftime("%Y-%m-%d") + ‘.xls‘def export():

conn= pymysql.connect(host, user, pwd, db, charset=‘utf8‘)

cursor=conn.cursor()

with open(u‘%s‘ % sql_file, ‘r+‘) as f:

sql_list= f.read().split(‘;‘)[:-1] # sql文件最后一行加上;

sql_list= [x.replace(‘\n‘, ‘ ‘) if ‘\n‘ in x else x for x insql_list] # 将每段sql里的换行符改成空格

##执行sql语句，使用循环执行sql语句for sql insql_list:

#print(sql)

count=cursor.execute(sql)

# print("查询出" + str(count) + "条记录")if count > 0:

# 来重置游标的位置

cursor.scroll(0, mode=‘absolute‘)

# 搜取所有结果

results=cursor.fetchall()

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

fields=cursor.description

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

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= 1col= 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)else:

pass

_user= ""_pwd= "*********"areceiver= ""#抄送人，可写可不写，多个用,隔开

acc= "*****@capitalonline.net"msg=MIMEMultipart()

msg["Subject"] = u‘data_‘ + time.strftime("%Y-%m-%d")

msg["From"] =_user

msg["To"] =areceiver

msg["Cc"] =acc

def send_email():

content= ‘‘‘Hello, everyone,This is a test email! Have a nice day!‘‘‘part= MIMEText(content, ‘plain‘, ‘utf-8‘)

msg.attach(part)iffilename:

file_name= ‘/tmp/‘ +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.exmail.qq.com", timeout=305)

s.login(_user, _pwd)

s.sendmail(_user, areceiver.split(‘,‘) + acc.split(‘,‘), msg.as_string())

s.close()else:

passif __name__ == "__main__":

export()

send_email()