Wednesday, February 25, 2015

Publish SQLite data to PDF using named tuples

By Vasudev Ram

Some time ago I had written this post:

Publishing SQLite data to PDF is easy with xtopdf.

It showed how to get data from an SQLite (Wikipedia) database and write it to PDF, using xtopdf, my open source PDF creation library for Python.

Today I was browsing the Python standard library docs, and so thought of modifying that program to use the namedtuple data type from the collections module of Python, which is described as implementing "High-performance container datatypes". The collections module was introduced in Python 2.4.
Here is a modified version of that program,, called, that uses named tuples:
# Author: Vasudev Ram -
# 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):


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

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

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


This time I've imported print_function so that I can use print as a function instead of as a statement.

Here's a screenshot of the PDF output in Foxit PDF Reader:

- Vasudev Ram - Online Python training and programming

Dancing Bison Enterprises

Signup to hear about new products or services from me.

Posts about Python  Posts about xtopdf

Contact Page

Sunday, February 22, 2015

Black Fork Mohican River

The Black Fork, Mohican River, Ohio, USA.

Excel to PDF with xlwings and xtopdf

By Vasudev Ram

Excel to PDF with xlwings and xtopdf - how many x in that? :)

I came across xlwings recently via the Net.

xlwings is by Zoomer Analytics, a startup based in Zürich, Switzerland, by a team with background in financial institutions.

Excerpt from the xlwings documentation:

[ xlwings is a BSD-licensed Python library that makes it easy to call Python from Excel and vice versa:

Interact with Excel from Python using a syntax that is close to VBA yet Pythonic.

Replace your VBA macros with Python code and still pass around your workbooks as easily as before.

xlwings fully supports NumPy arrays and Pandas DataFrames. It works with Microsoft Excel on Windows and Mac. ]

I checked out the xlwings quickstart.

Then did a quick test of using xlwings with xtopdf, my toolkit for PDF creation, to create a simple Excel spreadsheet, then read back its contents, and convert that to PDF.

Here is the code:
A demo program to show how to convert the text extracted from Excel 
content, using xlwings, to PDF. It uses the xlwings library, to create 
and read the Excel input, and the xtopdf library to write the PDF output.
Author: Vasudev Ram -
Copyright 2015 Vasudev Ram

import sys
from xlwings import Workbook, Sheet, Range, Chart
from PDFWriter import PDFWriter

# Create a connection with a new workbook.
wb = Workbook()

# Create the Excel data.
# Column 1.
Range('A1').value = 'Foo 1'
Range('A2').value = 'Foo 2'
Range('A3').value = 'Foo 3'
# Column 2.
Range('B1').value = 'Bar 1'
Range('B2').value = 'Bar 2'
Range('B3').value = 'Bar 3'

pw = PDFWriter("xlwingsTo.pdf")
pw.setFont("Courier", 10)
pw.setHeader("Testing Excel conversion to PDF with xlwings and xtopdf")
pw.setFooter("xlwings: --- xtopdf:")

for row in Range('A1..B3').value:
    s = ''
    for col in row:
        s += col + ' | '

I ran it with this command:
and here is a screenshot of the output PDF file:

Note: The xlwings library can be installed with:
pip install xlwings
But a prerequisite for it, pywin32, did not install automatically. pywin32 is a very useful and powerful Windows API wrapper library for Python, by Mark Hammond. I've used it a few times earlier, in earlier Python versions than Python 2.7.8, which I currently am using. I usually installed it directly in those earlier versions. This time, though it was a dependency for xlwings, it did not get installed automatically, and the above Python program gave a runtime error. I had to manually install pywin32 before the program could work.

- Enjoy.

- Vasudev Ram - Dancing Bison Enterprises

Signup to hear about new products or services from me.

Contact Page

Monday, February 16, 2015

Effective Python book now available in digital edition

By Vasudev Ram

Saw this via an email from the book's author, Brett Slatkin:

The book Effective Python is now available in digital editions. The author, Brett Slatkin, is a Senior Staff Software Engineer at Google. From the book's site:

[ He is the engineering lead and co-founder of Google Consumer Surveys. He formerly worked on Google App Engine’s Python infrastructure. He is the co-creator of the PubSubHubbub protocol. Nine years ago he cut his teeth using Python to manage Google’s enormous fleet of servers. ]

From the description of the book on the site:

[ The Python programming language has unique strengths and charms that can be hard to grasp. Many programmers familiar with other languages often approach Python from a limited mindset instead of embracing its full expressivity. Some programmers go too far in the other direction, overusing Python features that can cause big problems later.

Effective Python provides insight into the Pythonic way of writing programs: the best way to use Python. It builds on a fundamental understanding of the language that I assume you already have. Novice programmers will learn the best practices of Python’s capabilities. Experienced programmers will learn how to embrace the strangeness of a new tool with confidence. ]

The book is a part of the Effective Software Development series of books, with Scott Meyers as the Consulting Editor (for the series). Meyers wrote both the books Effective C++ and More Effective C++, some years ago. I had read the former book (Effective C++) pretty much fully, soon after it came out, and it was very good. IIRC, it was the success of Effective C++ [1] that lead to the decision to create the Effective Software Development series.

[1] I was told by an acquisitions editor at McGraw-Hill that Effective C++ sold in the neighborhood of 50,000 copies.

- Vasudev Ram - Dancing Bison Enterprises

Signup to hear about new products or services from me.

Contact Page