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, SQLiteToPDF.py, called SQLiteToPDFWithNamedTuples.py, that uses named tuples:

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

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

Share |

Publishing SQLite data to PDF is easy with xtopdf

By Vasudev Ram



This is another in my series of posts about the uses of xtopdf:

SQLite is an embedded database that is widely used.

According to an estimate by the SQLite developers, it may be the most widely deployed SQL database engine in the world.

Excerpt from the above-linked SQLite page that talks about the deployment numbers (emphasis mine):

[ Now let's consider where SQLite is used:

300 million copies of Mozilla Firefox.

20 million Mac computers, each of which contains multiple copies of SQLite

20 million websites run PHP which has SQLite built in. [3] We have no way of estimating what fraction of those sites actively use SQLite, but we think it is a significant fraction.
450 million registered Skype users.

20 million Symbian smartphones shipped in Q3 2007 [5] Newer versions of the SymbianOS have SQLite built in. It is unclear exactly how many Symbian phones actually contain SQLite, so we will use a single quarter's sales as a lower bound.

10 million Solaris 10 installations, all of which require SQLite in order to boot.

Millions and millions of copies of McAfee anti-virus software all use SQLite internally.

Millions of iPhones use SQLite

Millions and millions of other cellphones from manufactures other than Symbian and Apple use SQLite. This has not been publicly acknowledged by the manufactures but it is known to the SQLite developers. ]

An interesting fact about SQLite is that many startups use it as the database in the initial stages of development, because it is so fast to set up - pretty much no configuration or administration required. I've been involved in a few startups that used SQLite like this.

The list of famous SQLite users is interesting, and includes Adobe, Apple, Airbus, Dropbox, Google, Intuit, McAfee, Skype and more.

And last but not least, Python is one of the "organizations" listed that uses SQLIte. Yes, the sqlite3 library for Python is included in the stdlib since Python 2.5.

So, with that background, here is a program (SQLiteToPDF.py) that shows how easy it is to publish SQLite data to PDF using my xtopdf toolkit for PDF creation:

# SQLiteToPDF.py
# Author: Vasudev Ram - http://www.dancingbison.com
# SQLiteToPDF.py is a program to demonstrate how to read 
# SQLite database data and convert it to PDF.

import sys
from PDFWriter import PDFWriter
import sqlite3

try:

    conn = sqlite3.connect('example.db')
    curs = conn.cursor()

    # Create 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.
    curs.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.0)")
    curs.execute("INSERT INTO stocks VALUES ('2007-02-06','SELL','ORCL',200,25.1)")
    curs.execute("INSERT INTO stocks VALUES ('2008-03-06','HOLD','IBM',200,45.2)")

    # Save the changes.
    conn.commit()

    # Now fetch back the inserted data and write it to PDF.
    curs.execute("SELECT * FROM stocks")
    with PDFWriter("sqlite_data.pdf") as pw:
        pw.setFont("Courier", 12)
        pw.setHeader("SQLite data to PDF")
        pw.setFooter("Generated by xtopdf - https://bitbucket.org/vasudevram/xtopdf")
        for row in curs:
            row_str = ""
            for col in row:
                row_str = row_str + str(col).rjust(6) + " "
            pw.writeLine(row_str)
    conn.close()

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

You can run the program like this:

python SQLiteToPDF.py

And here is its output:

Note: The stock price data shown is randomly made up, not real, of course.

Also see this previous post of mine about sqlite3dbm, an SQLite-backed dbm module.

- Vasudev Ram - Dancing Bison Enterprises

Contact Page

Share |

sqlite3dbm, an SQLite-backed dbm module

By Vasudev Ram

Saw this today. It seems to be on the Github account of Yelp.com.

They created it as a tool to help with Hadoop work on Amazon EMR (Elastic Map Reduce).

sqlite3dbm provides a SQLite-backed dictionary conforming to the dbm interface, along with a shelve class that wraps the dict and provides serialization for it.

I tried it out, and it worked as advertised.

How to use sqlite3dbm:

Import the module, use its open() method to create an SQLite database, getting back a handle to it, let's call it "db", then use Python dict syntax on db to store data.

Then, either in the same or another program later, you can again fetch and/or modify that data, with dict syntax.

Interesting idea. dbm modules, which implement key-value stores, are less powerful than relational databases (SQL), and were probably developed earlier (think ISAM, etc.), so it looks a bit backwards to implement a dbm-type store on top of SQLite. But the sqlite3dbm project page gives at least some justification for that:

[ This module was born to provide random-access extra data for Hadoop jobs on Amazon’s Elastic Map Reduce (EMR) cluster. We used to use bsddb for this because of its dead-simple dict interface. Unfortunately, bsddb is deprecated for removal from the standard library and also has inter-version compatability problems that make it not work on EMR. sqlite3 is the obvious alternative for a persistent store, but its powerful SQL interface can be too complex when you just want a dict. Thus, sqlite3dbm was born to provide a simple dictionary API on top of the ubiquitous and easily available sqlite3.

This module requres no setup or configuration once installed. Its goal is a stupid-simple solution whenever a persistent dictionary is desired. ]


- Vasudev Ram - Dancing Bison Enterprises

Share |