Python从数据库导出数据成excel

from trump.db import query
from utils.result_process import success,aborted
import xlwt
import datetime
import config_business
from sanic import response
from utils.data_to_execl_sunge import excel_style



async def post(app,request):
    params = request.json
    if "sql_str" not in params:
        return aborted(return_msg="缺少必传参数!")
    sql_str = params.get("sql_str")
    if sql_str.__contains__('insert') or sql_str.__contains__('update') or sql_str.__contains__('delete') :
        return aborted(return_msg="亲,只能输入查询语句哟!")
    try:
        data = await query(app.pool, sql_str, fetch_type='fetch')
        fields_object = await query(app.pool, sql_str, fetch_type='attributes')
        head_message = []
        for k,v in fields_object.items():
            head_message.append(k)
        wbk = xlwt.Workbook()
        sheet = wbk.add_sheet('Sheet1', cell_overwrite_ok=True)
        header01, style_COL_AC = excel_style()
        tall_style = xlwt.easyxf('font:height 400;')
        today_date = datetime.datetime.now().strftime("%Y-%m-%d_%H:%M:%S")
        if data:
            for k, v in enumerate(data[0]):
                sheet.write(0, k, head_message[k], header01)
            for i in range(len(data)):
                sheet.row(i).set_style(tall_style)
                for key, item in enumerate(data[i]):
                    sheet.write(i + 1, key, data[i][item], style_COL_AC)
                    sheet.col(key).width = 240 * 20
            wbk.save(config_business.EXCEL_SAVE_PATH + 'query_sql_' + str(today_date) + '.XQ要查询的数据' +'.xls')
            # return await response.file(config_business.EXCEL_SAVE_PATH + 'query_orders_' + str(today_date) + '.xls',
            #                            mime_type='application/vnd.ms-excel',
            #                            headers={"Content-Disposition": "attachment; filename=徐倩数据查询.xls"})
            return success(config_business.EXCEL_SAVE_PATH + 'query_sql_' + str(today_date) + '.XQ要查询的数据' +'.xls')
        else:
            return aborted(return_msg="没有查询到任何数据!")
    except Exception as err:
        return aborted(err)



 

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值