export sql output to excel

[Kranthi]

Hi All,

Have scenario were need to export the sql output to excel (.xls). Just wanted to check possible ways, if anything possible through python?
Kindly suggest or throw some ideas.

Regards,
Kranthi

[ichabod801]

Sure. It's very easy to write a comma separated value (csv) file from Python. Check out the csv module. Excel opens csv files natively.

[Kranthi]

Hi

Thanks alot, tried looking but couldnt find. I guess I am missing something.
Can you please guide or throw some more light.

Thanks & Regards,
Kranthi

[buran]

tried looking but couldnt find

Her it is csv module

[Kranthi]

Found this which exports to xls, but not able to execute. Do let me know if anything to be changed. I have just started learning python.

## -- Create Excel file with data from an Oracle database query.

import cx_Oracle
from xlwt import Workbook, XFStyle, Borders, Font

def write_cursor_to_excel(curs, filename, sheetTitle):

## -- write_cursor_to_excel  curs: a cursor for an open connection to an oracle database
## -- filename: name of the XLS file to create sheetTitle: name of the sheet to create
## -- create style for header row - bold font, thin border below
    fnt = Font()
    fnt.bold = True
    borders = Borders()
    borders.bottom = Borders.THIN
    hdrstyle = XFStyle()
    hdrstyle.font = fnt
    hdrstyle.borders = borders
    # create a date format style for any date columns, if any
    datestyle = XFStyle()
    datestyle.num_format_str = 'DD/MM/YYYY'
    # create the workbook. (compression: try to reduce the number of repeated styles)
    wb = Workbook(style_compression=2)
    # the workbook will have just one sheet
    sh = wb.add_sheet(sheetTitle)
    # write the header line, based on the cursor description
    c = 0
    colWidth = []
    for col in curs.description:
        #col[0] is the column name
        #col[1] is the column data type
        sh.write(0, c, col[0], hdrstyle)
        colWidth.append(1) # arbitrary min cell width
        if col[1] == cx_Oracle.DATETIME:
            colWidth[-1] = len(datestyle.num_format_str)
        if colWidth[-1] < len(col[0]):
            colWidth[-1] = len(col[0])
        c += 1
    # write the songs, one to each row
    r = 1
    for song in curs:
        row = sh.row(r)
        for c in range(len(song)):
            if song[c]:
                if curs.description[c][1] == cx_Oracle.DATETIME:
                    row.write(c, song[c], datestyle)
                else:
                    if colWidth[c] < len(str(song[c])):
                        colWidth[c] = len(str(song[c]))
                    row.write(c, song[c])
        r += 1
    for c in range(len(colWidth)):
        sh.col(c).width = colWidth[c] * 350
    # freeze the header row
    sh.panes_frozen = True
    sh.vert_split_pos = 0
    sh.horz_split_pos = 1
    wb.save(filename)

def test():
    orcl = cx_Oracle.connect('scott/tiger')
    curs = orcl.cursor()
    curs.execute(""" select * from otcadm.system_parameter 
where name in ('OTC_BATCH_DATE','OTC_ONLINE_DATE','REPORT_RUN_DATE')
order by ins_timestamp desc """)
    write_cursor_to_excel(curs, 'emp.xls', 'Employees')
if __name__ == '__main__':

    test()

[buran]

Found this which exports to xls, but not able to execute.

This is not very descriptive... Do you get any errors. Post full traceback in error tags. What python version, OS, DB do you use?

[Kranthi]

Hide/Show

Found this which exports to xls, but not able to execute. Do let me know if anything to be changed. I have just started learning python.

## -- Create Excel file with data from an Oracle database query.

import cx_Oracle
from xlwt import Workbook, XFStyle, Borders, Font

def write_cursor_to_excel(curs, filename, sheetTitle):

## -- write_cursor_to_excel  curs: a cursor for an open connection to an oracle database
## -- filename: name of the XLS file to create sheetTitle: name of the sheet to create
## -- create style for header row - bold font, thin border below
    fnt = Font()
    fnt.bold = True
    borders = Borders()
    borders.bottom = Borders.THIN
    hdrstyle = XFStyle()
    hdrstyle.font = fnt
    hdrstyle.borders = borders
    # create a date format style for any date columns, if any
    datestyle = XFStyle()
    datestyle.num_format_str = 'DD/MM/YYYY'
    # create the workbook. (compression: try to reduce the number of repeated styles)
    wb = Workbook(style_compression=2)
    # the workbook will have just one sheet
    sh = wb.add_sheet(sheetTitle)
    # write the header line, based on the cursor description
    c = 0
    colWidth = []
    for col in curs.description:
        #col[0] is the column name
        #col[1] is the column data type
        sh.write(0, c, col[0], hdrstyle)
        colWidth.append(1) # arbitrary min cell width
        if col[1] == cx_Oracle.DATETIME:
            colWidth[-1] = len(datestyle.num_format_str)
        if colWidth[-1] < len(col[0]):
            colWidth[-1] = len(col[0])
        c += 1
    # write the songs, one to each row
    r = 1
    for song in curs:
        row = sh.row(r)
        for c in range(len(song)):
            if song[c]:
                if curs.description[c][1] == cx_Oracle.DATETIME:
                    row.write(c, song[c], datestyle)
                else:
                    if colWidth[c] < len(str(song[c])):
                        colWidth[c] = len(str(song[c]))
                    row.write(c, song[c])
        r += 1
    for c in range(len(colWidth)):
        sh.col(c).width = colWidth[c] * 350
    # freeze the header row
    sh.panes_frozen = True
    sh.vert_split_pos = 0
    sh.horz_split_pos = 1
    wb.save(filename)

def test():
    orcl = cx_Oracle.connect('scott/tiger')
    curs = orcl.cursor()
    curs.execute(""" select * from parameter order by ins_timestamp desc """)
    write_cursor_to_excel(curs, 'emp.xls', 'Employees')
if __name__ == '__main__':

    test()