通过python将MySQL,Oracle查询的数据存到Excel文件

这个Python脚本使用xlsxwriter库创建Excel文件,并从MySQL和Oracle数据库中导出查询结果。通过`excelpro`类,可以初始化Excel文件,创建工作表,写入查询结果,并关闭文件。示例代码展示了如何处理SQL查询并将其数据存储到不同工作表中。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

#!/usr/bin/python
# -*- coding: UTF-8 -*-
#-------------------------------------------------------------------------------
# Name:        createExcel.py
# Author:      ranyuan
# Email:       yuan.ran@msxf.com	
# Created:     23/02/2016
# requirement: python >=2.6
#-------------------------------------------------------------------------------
import xlsxwriter  #pip install xlsxwriter
#import mysqldml   # this is for the example at end of page

class excelpro:
    def __init__(self,excelname):
        self.excelname = excelname

    # create a excel file
    def createExcel(self):
        workbook = xlsxwriter.Workbook(self.excelname)
        return  workbook

    # create Sheet if have many sheet in one excel file
    def createSheet(self,workbook,sheetName,queryColumnResult,queryResult):
        worksheet = workbook.add_worksheet(sheetName)
        # process column name to excel
        colres = queryColumnResult
        bold = workbook.add_format({'bold':True})
        row ,col = 0, 0
        for info in colres:
            worksheet.write(row,col,str(info[0]),bold)
            col = col +1

        # process values  to excel
        result = queryResult
        row , col = 1, 0
        for info in result:
            for object in info:
                worksheet.write(row,col,str(object))
                col =col + 1
            col = 0
            row = row + 1

    # close file
    def closeExcel(self,workbook):
        workbook.close()

    # this is a example 
#if __name__=="__main__":

#    sql1="select apply_no,id ,person_name,person_ident,person_id_unique  from cont_loan_apply limit 3"
#    sql2="select apply_no,id ,person_ident,person_id_unique  from cont_loan_apply limit 8"
#    dbins = mysqldml.opdb(charset='utf8') #初始化数据库实例
#    excelproin = excelpro('/home/dbaops/demo2.xlsx') #初始化excel实例
#    workbook = excelproin.createExcel() #创建excel表
#    queryColumnResult = dbins.queryColumn(sql1) #获取查询的字段名结果是个列表
#    queryResult = dbins.query(sql1) #获取查询的具体行的数据结果也是个列表
#    excelproin.createSheet(workbook,"sheet1",queryColumnResult,queryResult) #将sql1执行的结果放到sheet1中
#    queryColumnResult = dbins.queryColumn(sql2)
#    queryResult = dbins.query(sql2)
#    excelproin.createSheet(workbook,"sheet2",queryColumnResult,queryResult)
#    excelproin.closeExcel(workbook) #最后关闭excel实例

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值