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.
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, name=item, quantity=item, unit_price=item) # 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.
This blog post has more details, including sample output from the above recipe, and some relevant links: