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:
Post a Comment