Publish databases to PDF with PyDAL and xtopdf

By Vasudev Ram

Some days ago, I had blogged about pyDAL, a pure Python Database Abstraction Layer.

Today I thought of writing a program to publish database data to PDF, using PyDAL and xtopdf, my open source Python library for PDF creation from other file formats.

(Here is a good online overview about xtopdf, for those new to it.)

So here is the code for PyDALtoPDF.py:

"""
Author: Vasudev Ram
Copyright 2014 Vasudev Ram - www.dancingbison.com
This program is a demo of how to use the PyDAL and xtopdf Python libraries 
together to publish database data to PDF.
PyDAL is at: https://github.com/web2py/pydal/blob/master/README.md
xtopdf is at: https://bitbucket.org/vasudevram/xtopdf
and info about xtopdf is at: http://slides.com/vasudevram/xtopdf or 
at: http://slid.es/vasudevram/xtopdf
"""

# imports
from pydal import DAL, Field
from PDFWriter import PDFWriter

SEP = 60

# create the database
db = DAL('sqlite://house_depot.db')

# define the table
db.define_table('furniture', \
    Field('id'), Field('name'), Field('quantity'), Field('unit_price')
)

# insert rows into table
items = ( \
    (1, 'chair', 40, 50),
    (2, 'table', 10, 300),
    (3, 'cupboard', 20, 200),
    (4, 'bed', 30, 400)
)
for item in items:
    db.furniture.insert(id=item[0], name=item[1], quantity=item[2], unit_price=item[3])

# define the query
query = db.furniture
# the above line shows an interesting property of PyDAL; it seems to 
# have some flexibility in how queries can be defined; in this case,
# just saying db.table_name tells it to fetch all the rows 
# from table_name; there are other variations possible; I have not 
# checked out all the options, but the ones I have seem somewhat 
# intuitive.

# run the query
rows = db(query).select()

# setup the PDFWriter
pw = PDFWriter('furniture.pdf')
pw.setFont('Courier', 10)
pw.setHeader('     House Depot Stock Report - Furniture Division     '.center(60))
pw.setFooter('Generated by xtopdf: http://google.com/search?q=xtopdf')

pw.writeLine('=' * SEP)

field_widths = (5, 10, 10, 12, 10)

# print the header row
pw.writeLine(''.join(header_field.center(field_widths[idx]) for idx, header_field in enumerate(('#', 'Name', 'Quantity', 'Unit price', 'Price'))))

pw.writeLine('-' * SEP)

# print the data rows
for row in rows:
    # methinks the writeLine argument gets a little long here ...
    # the first version of the program was taller but thinner :)
    pw.writeLine(''.join(str(data_field).center(field_widths[idx]) for idx, data_field in enumerate((row['id'], row['name'], row['quantity'], row['unit_price'], int(row['quantity']) * int(row['unit_price'])))))

pw.writeLine('=' * SEP)
pw.close()

I ran it (on Windows) with:

$ py PyDALtoPDF.py 2>NUL

Here is a screenshot of the output in Foxit PDF Reader:

- Enjoy.

--- Posts about Python  ---  Posts about xtopdf ---

- Vasudev Ram - Python programming and training

Signup to hear about new products or services from me.

Contact Page

Share |

pyDAL, a pure Python Database Abstraction Layer

By Vasudev Ram

pyDAL is a pure Python Database Abstraction Layer. So it seems to be something like the lower layer of SQLAlchemy, i.e. SQLAlchemy Core, the library that is used by the upper layer, SQLAlchemy ORM. See the SQLAlchemy (0.8) documentation.

From the pyDAL site:

[ It dynamically generates the SQL in real time using the specified dialect for the database back end, so that you do not have to write SQL code or learn different SQL dialects (the term SQL is used generically), and your code will be portable among different types of databases.

pyDAL comes from the original web2py's DAL, with the aim of being wide-compatible. pyDAL doesn't require web2py and can be used in any Python context. ]

IOW, pyDAL has been separated out into a different project from web2py, a Python web framework, of which it was originally a part.

The use of an ORM (Object Relational Mapper) vs. writing plain SQL code (vs. using an intermediate option like pyDAL or SQLAlchemy Core), can be controversial; there are at least some pros and cons on both (or all 3) sides. I've read some about this, and have got some experience with using some of these options in different projects, but am not an expert on which is the best approach, and also, it can vary depending on your project's needs, so I'm not getting into that topic in this post.

pyDAL seems to support many popular databases, mostly SQL ones, but also a NoSQL one or two, and even IMAP. Here is a list, from the site: SQLite, PostgreSQL, MySQL, Oracle, MSSQL, FireBird, DB2, Informix, Ingres, Cubrid, Sybase, Teradata, SAPDB, MongoDB, IMAP.

For some of those databases, it uses PyMySQL, pyodbc or fbd, which are all Python database libraries that I had blogged about earlier.

I tried out pyDAL a little, with this simple program, adapted from its documentation:

import sys
import time
from pydal import DAL, Field
db = DAL('sqlite://storage.db')
db.define_table('product', Field('name'))
t1 = time.time()
num_rows = int(sys.argv[1])
for product_number in range(num_rows):
    db.product.insert(name='Product-'.format(str(product_number).zfill(4)))
t2 = time.time()
print "time to insert {} rows = {} seconds".format(num_rows, int(t2 - t1))
query = db.product.name
t1 = time.time()
rows = db(query).select()
for idx, row in enumerate(rows):
    #print idx, row.name
    pass
t2 = time.time()
print "time to select {} rows = {} seconds".format(num_rows, int(t2 - t1))

It worked, and gave this output:

$ python test_pydal2.py 100000
No handlers could be found for logger "web2py"
time to insert 100000 rows = 18 seconds
time to select 100000 rows = 7 seconds

Note: I first ran it with this statement uncommented:

#print idx, row.name

to confirm that it did select the records, and then commented it and replaced it with "pass" in order to time the select without the overhead of displaying the records to the screen.

I'll check out pyDAL some more, for other commonly needed database operations, and may write about it here.
There may be a way to disable that message about a logger.

The timing statements in the code and the time output can be ignored for now, since they are not meaningful without doing a comparison against the same operations done without pyDAL (i.e. just using SQL from Python with the DB API). I will do a comparison later on and blog about it if anything interesting is found.

- Vasudev Ram - Dancing Bison Enterprises - Python training and consulting

Signup to hear about new products or services from me.

Contact Page

Share |