# 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()
