Python导出Excel.

import xlwt
import pymysql
import os
from s00 import sql00
from s01 import sql01
from s02 import sql02
from s03 import sql03
from s04 import sql04
from s05 import sql05
from s06 import sql06
from s07 import sql07

class MYSQL:
  def __init__(self):
    pass
  def __del__(self):
    self._cursor.close()
    self._connect.close()
  def connectDB(self):
    """
    连接数据库
    :return:
    """
    try:
      self._connect = pymysql.Connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        passwd='test1234',
        db='test',
        charset='utf8'
      )
      return 0
    except:
      return -1

  def export(self, sql, sheetName,workbook):
    self._cursor = self._connect.cursor()
    count = self._cursor.execute(sql)
    # print(self._cursor.lastrowid)
    if count <= 0 :
      return 0
    print(count)
    # 重置游标的位置
    self._cursor.scroll(0, mode='absolute')
    # 搜取所有结果
    results = self._cursor.fetchall()
    # 获取MYSQL里面的数据字段名称
    fields = self._cursor.description
    # 注意: 在add_sheet时, 置参数cell_overwrite_ok=True, 可以覆盖原单元格中数据。
    # cell_overwrite_ok默认为False, 覆盖的话, 会抛出异常.
    sheet = workbook.add_sheet(sheetName, cell_overwrite_ok=False)
    # 写上字段信息
    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])
    return count

if __name__ == '__main__':
  mysql = MYSQL()
  flag = mysql.connectDB()
  if flag == -1:
    print('数据库连接失败')
  else:
    print('数据库连接成功')
    #清理之前的结果文件
    excelFile = '/root/export/user.xls'
    if os.path.isfile(excelFile):
      os.remove(excelFile)

    workbook = xlwt.Workbook()  
    num = mysql.export(sql00,'db00', workbook)
    num += mysql.export(sql01,'db01', workbook)
    num += mysql.export(sql02,'db02', workbook)
    num += mysql.export(sql03,'db03', workbook)
    num += mysql.export(sql04,'db04', workbook)
    num += mysql.export(sql05,'db05', workbook)
    num += mysql.export(sql06,'db06', workbook)
    num += mysql.export(sql07,'db07', workbook)
    workbook.save(excelFile)   

    if num > 0 :
     #发送邮件
     os.system('/root/export/sendmail.sh')

其中stc00-stc07是含有sql语句的python文件。

sql00 = "select usercode,username from user"

发送邮件：echo "excel导出" | mailx -s "导出明细"  -a  /root/export/user.xls user01@163.com,user02@163.com,user03@163.com