Wednesday, April 10, 2013

Using xtopdf and pypyodbc to publish MS Access database data to PDF


By Vasudev Ram

I had blogged about pypyodbc, a pure-Python ODBC library, recently.

Using pypyodbc with my xtopdf toolkit for PDF creation, you can publish your MS Access database data to PDF.

Here is some example code to publish MS Access data to PDF:

First, the program create_ppo_mdb.py, shown below, creates an MS Access database called fruits.mdb, then creates a table called fruits in it, and inserts 3 records into the table:

# create_ppo_mdb.py

import pypyodbc 
             
pypyodbc.win_create_mdb('.\\fruits.mdb')
connection_string = 'Driver={Microsoft Access Driver (*.mdb)};DBQ=.\\fruits.mdb'
connection = pypyodbc.connect(connection_string)

SQL = 'CREATE TABLE fruits (id COUNTER PRIMARY KEY, fruit_name VARCHAR(25));'
connection.cursor().execute(SQL).commit()

SQL = "INSERT INTO fruits values (1, 'apple');"
connection.cursor().execute(SQL).commit()

SQL = "INSERT INTO fruits values (2, 'banana');"
connection.cursor().execute(SQL).commit()

SQL = "INSERT INTO fruits values (3, 'orange');"
connection.cursor().execute(SQL).commit()

# Uncomment the 5 lines below make the program also display the data after creating it.

#SQL = 'SELECT * FROM fruits;'
#cursor = connection.cursor().execute(SQL)
#for row in cursor:
#    for col in row:
#        print col,
#    print

cursor.close()
connection.close()

Next, the program MDBtoPDF.py, shown below, reads the data from the fruits table in the MDB database just created above, and publishes the selected records to PDF:

#-------------------------------------------------------------------

# MDBtoPDF.py
# Description: A program to convert MS Access .MDB data to PDF format.
# Author: Vasudev Ram - http://www.dancingbison.com

#-------------------------------------------------------------------

# imports

import sys 
import os
import time
import string
import pypyodbc 
from PDFWriter import PDFWriter
             
#-------------------------------------------------------------------

# globals

##------------------------ usage ---------------------------------------

def usage():

 sys.stderr.write("Usage: python " + sys.argv[0] + " MDB_DSN table_name pdf_file\n")
 sys.stderr.write("where MDB_DSN is the ODBC DSN (Data Source Name) for the\n")
 sys.stderr.write("MDB file, table_name is the name of the table in that MDB,\n")
 sys.stderr.write("whose data you want to convert to PDF, and pdf_file is the\n")
 sys.stderr.write("output PDF filename.\n")
 sys.stderr.write(sys.argv[0] + " reads the table data from the MDB and\n")
 sys.stderr.write("writes it to pdf_file.\n")

##------------------------ main ------------------------------------------

def main():

 '''Main program to convert MDB data to PDF.
 '''

 # check for right num. of args
 if (len(sys.argv) != 4):
  usage()
  sys.exit(1)

 # extract MDB DSN, table name and pdf filename from args
 mdb_dsn = sys.argv[1]
 table_name = sys.argv[2]
 pdf_fn = sys.argv[3]

 print "mdb_dsn =", mdb_dsn
 print "table_name =", table_name
 print "pdf_fn =", pdf_fn

    # build connection string
 connection_string_prefix = 'Driver={Microsoft Access Driver (*.mdb)};DBQ='
 connection_string = connection_string_prefix + mdb_dsn
 print "connection_string =", connection_string
 connection = pypyodbc.connect(connection_string)
 print "connection =", connection

 # create the PDFWriter instance
 pw = PDFWriter(pdf_fn)

 # and set some of its fields

 # set the font
 pw.setFont("Courier", 10)

 # set the page header
 gen_datetime = time.asctime()
 pw.setHeader("Generated by MDBtoPDF: Input: " + mdb_dsn + \
 " At: " + gen_datetime)

 # set the page footer
 pw.setFooter("Generated by MDBtoPDF: Input: " + mdb_dsn + \
 " At: " + gen_datetime)

 # create the separator for logical grouping of output
 sep = "=" * 60

 # print the data records section title
 pw.writeLine("MDB Data Records from MDB: %s, table: %s" % (mdb_dsn, 
  table_name))

 # print a separator line
 pw.writeLine(sep)

 # read the input MDB data and write it to the PDF file

 SQL = 'SELECT * FROM fruits;'

 cursor = connection.cursor().execute(SQL)
 for row in cursor:
  str_row = ""
  for col in row:
   str_row = str_row + str(col) + " "
  pw.writeLine(str_row)

 # close the cursor and connection
 cursor.close()
 connection.close()

 # print a separator line
 pw.writeLine(sep)

 # save current page
 pw.savePage()

 # close the PDFWriter
 pw.close()

##------------------------ Global code -----------------------------------

# invoke main

if __name__ == '__main__':
 main()

##------------------------ EOF - MDBto_PDF.py ---------------

To make the above programs work, you need to have the Reportlab toolkit v1.21 and the xtopdf toolkit installed, in addition to pypyodbc and Python 2.7. (Click on the "Branches" tab on the xtopdf page linked in the previous sentence to download xtopdf.)

I've had an interest in ODBC ever since I first worked, as team leader, on a middleware software product that used ODBC. The middleware was developed at Infosys Technologies, where I worked at the time.

Though ODBC itself had a good architecture, many driver implementations of the time (this was some years ago) were rather slow, so one of the main goals of the product was to improve the performance of client-server or desktop applications (written in Visual Basic or C) that used ODBC for database access.

I remember learning ODBC as part of the project (and teaching it to the team), and reading most of the book "Inside ODBC" by Kyle Geiger, one of the architects of ODBC - it was a fascinating book, that gave a detailed look inside the architecture of ODBC, the reasons for certain design decisions that were made, and so on.

We succeeded in meeting all the goals of the project, and that middleware product was used in many large client-server applications (using VB and Oracle / Sybase) that were developed by Infosys for its clients. I really had a lot of fun working on that project.

Related links:

ODBC entry on Wikipedia

Inside ODBC - the book, on Amazon

eGenix mxODBC Connect, from eGenix, a German Python products company.

eGenix mxODBC

unixODBC

DataDirect ODBC

iODBC

The Microsoft SQL Server ODBC Driver for Linux - it provides native connectivity from Linux to Microsoft SQL Server. (Seems to be 64-bit only).

- Vasudev Ram - Dancing Bison Enterprises

No comments: