Welcome, guest | Sign In | My Account | Store | Cart

This recipe shows how to use the PyDAL database library for Python, together with xtopdf, a PDF creation library for Python, to publish database data to PDF. PyDAL was earlier a part of the web2py Python web framework, and then was split out into a separate library that does not need to be used with web2py. It supports access to many popular relational databases, both open source and proprietary.

Python, 71 lines
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
"""
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()

This recipe can be useful whenever you want to publish data from your database tables to PDF, to get the benefits that PDF provides, such as viewers for most platforms, screen output being almost the same as print output, a good format for print output, etc.

The recipe takes creates a sample table in an SQLite database, populates it with a few rows, and then shows how to fetch them back using PyDAL and output them to PDF. It serves as a template that can be modified for your custom needs. Since PyDAL supports many popular relational databases (as long as you have the appropriate driver), this recipe can be modified to enable publishing of database data to PDF, from any database supported by PyDAL. That includes (from the PyDAL site):

SQLite, PostgreSQL, MySQL, Oracle, MSSQL, FireBird, DB2, Informix, Ingres, Cubrid, Sybase, Teradata, SAPDB, MongoDB.

1 comment

Vasudev Ram (author) 6 years, 10 months ago  # | flag

This blog post has more details, including sample output from the above recipe, and some relevant links:

http://jugad2.blogspot.in/2015/01/publish-databases-to-pdf-with-pydal-and.html