# SQLiteToPDFWithNamedTuples.py
# Author: Vasudev Ram - http://www.dancingbison.com
# SQLiteToPDFWithNamedTuples.py is a program to demonstrate how to read 
# SQLite database data and convert it to PDF. It uses the Python
# data structure called namedtuple from the collections module of 
# the Python standard library.

from __future__ import print_function
import sys
from collections import namedtuple
import sqlite3
from PDFWriter import PDFWriter

# Helper function to output a string to both screen and PDF.
def print_and_write(pw, strng):
    print(strng)
    pw.writeLine(strng)

try:

    # Create the stocks database.
    conn = sqlite3.connect('stocks.db')
    # Get a cursor to it.
    curs = conn.cursor()

    # Create the stocks table.
    curs.execute('''DROP TABLE IF EXISTS stocks''')
    curs.execute('''CREATE TABLE stocks
                 (date text, trans text, symbol text, qty real, price real)''')

    # Insert a few rows of data into the stocks table.
    curs.execute("INSERT INTO stocks VALUES ('2006-01-05', 'BUY', 'RHAT', 100, 25.1)")
    curs.execute("INSERT INTO stocks VALUES ('2007-02-06', 'SELL', 'ORCL', 200, 35.2)")
    curs.execute("INSERT INTO stocks VALUES ('2008-03-07', 'HOLD', 'IBM', 300, 45.3)")
    conn.commit()

    # Create a namedtuple to represent stock rows.
    StockRecord = namedtuple('StockRecord', 'date, trans, symbol, qty, price')

    # Run the query to get the stocks data.
    curs.execute("SELECT date, trans, symbol, qty, price FROM stocks")

    # Create a PDFWriter and set some of its fields.
    pw = PDFWriter("stocks.pdf")
    pw.setFont("Courier", 12)
    pw.setHeader("SQLite data to PDF with named tuples")
    pw.setFooter("Generated by xtopdf - https://bitbucket.org/vasudevram/xtopdf")

    # Write header info.
    hdr_flds = [ str(hdr_fld).rjust(10) + " " for hdr_fld in StockRecord._fields ]
    hdr_fld_str = ''.join(hdr_flds)
    print_and_write(pw, '=' * len(hdr_fld_str))
    print_and_write(pw, hdr_fld_str)
    print_and_write(pw, '-' * len(hdr_fld_str))

    # Now loop over the fetched data and write it to PDF.
    # Map the StockRecord namedtuple's _make class method
    # (that creates a new instance) to all the rows fetched.
    for stock in map(StockRecord._make, curs.fetchall()):
        row = [ str(col).rjust(10) + " " for col in (stock.date, \
        stock.trans, stock.symbol, stock.qty, stock.price) ]
        # Above line can instead be written more simply as:
        # row = [ str(col).rjust(10) + " " for col in stock ]
        row_str = ''.join(row)
        print_and_write(pw, row_str)

    print_and_write(pw, '=' * len(hdr_fld_str))

except Exception as e:
    print("ERROR: Caught exception: " + e.message)
    sys.exit(1)

finally:
    pw.close()
    conn.close()
