处女作输入sql通过python脚本导出 数据

2019独角兽企业重金招聘Python工程师标准>>>

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

import os, sys, codecs, getopt, time, datetime, socket, json, math
from DBUtils.PooledDB import PooledDB
import logging, logging.handlers
import xdrlib, xlrd, xlwt
from xlrd import open_workbook
from xlwt import *
from xlutils.copy import copy
from xlutils.styles import Styles

import pymysql

CONSOLE_LEVEL = logging.INFO
FILENAME = "export.xls"

handler = logging.StreamHandler()
handler.setLevel(CONSOLE_LEVEL)
handler.setFormatter(logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s'))
console = logging.getLogger()
console.setLevel(CONSOLE_LEVEL)
console.addHandler(handler)

sql = input("please input sql:")
targetPath = os.path.join(os.path.abspath(os.path.dirname(__file__)), FILENAME)


def export():
    wb = xlwt.Workbook(encoding='utf-8')  # 创建对齐配置
    al = Alignment()
    al.horz = Alignment.HORZ_CENTER
    al.vert = Alignment.VERT_CENTER
    alr = Alignment()
    alr.horz = Alignment.HORZ_RIGHT
    alr.vert = Alignment.VERT_CENTER
    # 创建边框配置
    borders = Borders()
    borders.top = Borders.THIN
    borders.left = Borders.THIN
    borders.right = Borders.THIN
    borders.bottom = Borders.THIN
    # 创建样式
    style = XFStyle()
    style.alignment = al
    style.borders = borders

    line = 0
    sheet = wb.add_sheet("sheet")
    sheet_row = sheet.row(line)

    # 打开数据库连接
    db = pymysql.connect("localhost", "root", "123456", "xxx")

    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()

    # 使用 execute()  方法执行 SQL 查询
    cursor.execute(sql)


    # 使用 fetchone() 方法获取单条数据.fetchmany()查询多条数据，fetchall（）查询全部数据
    rows = cursor.fetchall()
    fields = cursor.description

    for x in range(len(fields)):
        sheet_row.write(x, str(fields[x][0]), style=style)

    for row in rows:
        line += 1
        sheet_row = sheet.row(line)
        for x in range(len(fields)):
            sheet_row.write(x, str(row[x]), style=style)

    wb.save(targetPath)
    cursor.close()
    db.close()

export()

 

转载于:https://my.oschina.net/fengshuzi/blog/1114022