Export from a database

Formatters are intended to output table data. Supported formatters are:

TextFormatter
Outputs tables in plain text format with fixed-width columns.
CvsFormatter
Outputs tables in CSV format.
XmlFormatter
Outputs tables in XML format
XlsxFormatter
Outputs tables in EXCEL xlsx format

All formatters may be used in the following ways:

Example 1: function call

from lwetl import Jdbc, TextFormatter

jdbc = Jdbc('scott')

sql = 'SELECT * FROM MY_TABLE ORDER BY ID'
fmt = TextFormatter()
fmt(jdbc=jdbc, sql=sql)

Example 2: with statement

from lwetl import Jdbc, TextFormatter

jdbc = Jdbc('scott')

cur = jdbc.execute('SELECT * FROM MY_TABLE ORDER BY ID',cursor = None)

formatter = TextFormatter()
with TextFormatter(cursor=cur) as fmt:
    fmt.header()
    for row in jdbc.get_data(cur):
        fmt.write(row)
    fmt.footer()

Example 3: open/close

from lwetl import Jdbc, TextFormatter

jdbc = Jdbc('scott')

cur = jdbc.execute('SELECT * FROM MY_TABLE ORDER BY ID',cursor = None)

fmt = TextFormatter()
fmt.open(cursor=cur)
fmt.header()
for row in jdbc.get_data(cur):
    fmt.write(row)
fmt.footer()
fmt.close()

Below onl the TextFormatter is described in detail. For the otherones only the differences are mentioned.

TextFormatter():

Outputs a table in plain text format with fixed-width columns.

__init__(*args, **kwargs):

Instantiate. All arguments are optional.

Parameters:
  • cursor (Cursor) – cursor generated by jdbc.execute()
  • filename_or_stream ((str,TextIOWrapper,StringIO)) – specifier of the output stream. May be a filename (string) or a stream object.
  • append (bool) – append the specified file, rather then creating a new one. Defaults to False.
  • column_width (int) – the width of each text column. (Only used in this class)
 
from lwetl import TextFormatter

fmt = TextFormatter(cursor=cursor, filename_or_stream='myoutput.txt', append=True)
__call__(*args, **kwargs):

Write a table in a single statement, see Example1 above.

Parameters:
  • jdbc (Jdbc) – The Jdbc connection
  • sql (str) – The SQL to parse

Also accepts all arguments of the __init__() statement with the exception of the cursor.

open(*args, **kwargs)

Opens the file or stream for writing. Takes the same arguments as the __init__() statement.

close()

Closes the output file or stream (if applicable)

format(row)

Format the row of data.

Parameters:row ((list,tuple)) – a row of data.
Returns:a string.

Write the header (column names) to the specified file or stream.

write(row: list)

Writes the output of format(row)() to the specified output stream.

CsvFormatter():

Outputs a table in CSV format. The functionality is identical to the TextFormatter

all fuctions
Parameters:separator (str) – Specifies the CSV column separator. Defaults to ‘;’
XmlFormatter():

Outputs a table in XML format. The functionality is identical to the TextFormatter

all fuctions
Parameters:
  • dialect (str) – Specifies the XML dialect: ‘excel’, ‘value’, or ‘plain’. Defaults to ‘excel’
  • pretty_print (bool) – Output the xml in formatted mode, instead of compact. Defaults to False.
  • sheet_name (str) – Specifies the name of the worksheet. Defaults to ‘Sheet1’
next_sheet(cursor, sheet_name=None)

Initiates a new sheet with a new cursor.

Parameters:
  • cursor (Cursor) – cursor generated by jdbc.execute()
  • sheet_name (str) – name of the work sheet. Uses a counter like ‘SheetN’ if not specified.

Example:

from lwetl import Jdbc, XmlFormatter

jdbc = Jdbc('scott')
fmt = None
for table in ['MY_TABLE1', 'MY_TABLE_2']:
    cur = jdbc.execute('SELECT * from {0} ORDER BY ID'.format(table), cursor=None)
    if fmt is None:
        fmt = XmlFormatter()
        fmt.open(cursor='cur')
    else:
        fmt.next_sheet(cur)

    fmt.header()
    for row in jdbc.get_data(cur):
        fmt.write(row)
fmt.close()
XlsxFormatter(jdbc: Jdbc):

Outputs a table in a Excel 2007+ file. The functionality is identical to the XmlFormatter but dialect and pretty_print are not supported. Instead, the argument pretty=True in the open method, will reformat the xlsx file to autoset the column width and print the header line in bold.

Warning

Stream output is not supported. Only valid file-names are accepted for the argument filename_or_stream.