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 |