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 |

Generate PDF output from Firebird SQL data

By Vasudev Ram

PDF

Firebird is a cross-platform, lightweight, open source, relational database that was derived from Borland's Interbase, a database that was bundled with some versions of Borland's development tools like Delphi and Borland C++.

Firebird on Wikipedia.

I've used it some in the past. It is fairly easy to setup and use.

Here is a demo program that shows how to generate PDF output from a Firebird database, using my xtopdf PDF creation toolkit and the fbd Python driver for Firebird.

# FirebirdToPDF.py
# Author: Vasudev Ram - http://www.dancingbison.com
# Demo program to show how to convert Firebird RDBMS data to PDF.
# Uses xtopdf, Reportlab, Firebird RDBMS and fdb Python driver for Firebird.

import fdb
from PDFWriter import PDFWriter

con = fdb.connect(dsn='localhost:/temp/firebird/test.fdb', 
    user='dummy',  password='dummy')

cur = con.cursor()
select_stmt = 'select * from contacts order by name desc'
cur.execute(select_stmt)
pw = PDFWriter("contacts.pdf")
pw.setFont("Courier", 12)
pw.setHeader("Firebird contacts.fdb to PDF")
pw.setFooter("Generated by xtopdf using fdb Firebird driver for Python")
for (id, name, address) in cur:
    print id, name, address
    pw.writeLine(str(id) + " " + name + " " + address)
pw.close()

# EOF

You can run it with the command: python FirebirdToPDF.py

This is a screenshot of the output PDF created (click to enlarge):

The status page about fbd on the Firebird site is interesting. Excerpts from it (emphasis mine):

[ FDB is a Python library package that implements Python Database API 2.0-compliant support for the open source relational database Firebird®. In addition to the minimal feature set of the standard Python DB API, FDB also exposes the entire native client API of the database engine. Notably:
Automatic data conversion from strings on input.
Automatic input/output conversions of textual data between UNICODE and database character sets.
Support for prepared SQL statements.
Multiple independent transactions per single connection.
All transaction parameters that Firebird supports, including table access specifications.
Distributed transactions.
Firebird BLOB support, including support for stream BLOBs.
Support for Firebird Events.
Support for Firebird ARRAY data type.
Support for all Firebird Services
FDB is implemented in Python on top of Firebird client library using ctypes.

FDB works with Firebird 2.0 and newer, Python 2.7+ and 3.0+. ]

- Vasudev Ram - Dancing Bison Enterprises

Contact Page

Share |

Publish SQLAlchemy data to PDF with xtopdf

By Vasudev Ram

SQLAlchemyToPDF is a demo program that shows how to publish your database data via SQLAlchemy to PDF.



SQLAlchemy is a popular and widely used database toolkit for Python. It includes both a Core, which consists of a sort of DSL (Domain-Specific Language) for SQL, written in Python, and an ORM (Object Relational Mapper) which is built on top of the Core.

SQLAlchemyToPDF, after some improvement, will become a part of my xtopdf toolkit for PDF creation. It will be released under the BSD license, like the rest of xtopdf.

Using the technique shown below (with appropriate modifications), you can publish data, from any of the major databases that SQLAlchemy supports, to PDF. And you can do this using the high-level interface provided by SQLAlchemy's ORM, which means code that is shorter and easier to write.

However, SQLAlchemy also provides you the ability to go to a lower level when needed, to access more of the power of SQL or of a specific database.

Here is the code for SQLAlchemyToPDF.py:

# SQLAlchemyToPDF.py
# Program to read database data via SQLAlchemy 
# and publish it to PDF. This is a demo.
# Author: Vasudev Ram - http://www.dancingbison.com
# Copyright 2013 Vasudev Ram
# Version 0.1

from PDFWriter import PDFWriter
from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:', echo=False)

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    email = Column(String)

    def __init__(self, name, fullname, email):
        self.name = name
        self.fullname = fullname
        self.email = email

Base.metadata.create_all(engine) 

a_user = User('A', 'A 1', '[email protected]')
b_user = User('B', 'B 2', '[email protected]')
c_user = User('C', 'C 3', '[email protected]')

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)

session = Session()

for user in (a_user, b_user, c_user):
    session.add(user)

pw = PDFWriter('users.pdf')
pw.setFont("Courier", 12)
pw.setHeader("SQLAlchemyToPDF - User table report")
pw.setFooter("Generated by xtopdf using Reportlab and Python")

users = session.query(User)
for user in users:
    pw.writeLine(user.name + "|" + user.fullname + "|" + user.email)

pw.savePage()
pw.close()

I used an in-memory SQLite database to keep the code simple. Refer to the SQLAlchemy documentation for how to connect to other databases.

And here is a screenshot of the resulting PDF output:

Read other xtopdf posts on jugad2

Read other python posts on jugad2

- Vasudev Ram - Dancing Bison Enterprises

Contact me

Share |

pypyodbc, a pure Python ODBC library

By Vasudev Ram

In my recent post, PyODBC for Linux, Windows and Mac OS X, Ben Rousch commented: "I've had better luck with pure-Python PyPyODBC than with PyODBC."

So I downloaded and tried out pypyodbc.

Features of pypyodbc (from its web page):

Design Goal: Small, Maintainable, Cross Platform, Compatible, Portable

Features
One pure Python script, runs on CPython / IronPython / PyPy , Python 3.3 / 2.4 / 2.5 / 2.6 / 2.7 , Win / Linux / Mac , 32 / 64 bit
Very similar usage as pyodbc ( can be seen like a re-implementation of pyodbc in pure Python )
Simple - the whole module is implemented in a single python script with less than 3000 lines
Built-in functions to create and compress Access MDB files on Windows

I first tried the simple test program given on the pypyodbc page, which just creates a new MS Access database using Python. It worked.

Then I added code to that test program to insert three rows to the table and then and then retrieve the rows from the database and print them.

Here is the code:

# test_ppo.py

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

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

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

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

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

SQL = 'SELECT * FROM saleout;'
cursor = connection.cursor().execute(SQL)
for row in cursor:
    print row

And I got the expected output:

C:\vr\pypyodbc>python test_ppo.py
(1, u'apple')
(2, u'banana')
(3, u'orange')

- Vasudev Ram - Dancing Bison Enterprises

Share |

PyODBC for Linux, Windows and Mac OS X

By Vasudev Ram

PyODBC is like the odbc module in the Standard Python library, but claims to work on Linux, Windows and Mac OS X. I've used the odbc module from the stdlib earlier. Using it is straightforward for basic CRUD-type usage.

PyODBC is supposed to "implement the Python Database API Specification v2.0, but additional features have been added to simplify database programming even more." I am trying out PyODBC (have taken an initial look at the examples, seems similar to the stdlib odbc module) and will report on it a little later.

ODBC on Wikipedia.

- Vasudev Ram - Dancing Bison Enterprises

Share |

Firebird, free and open source database - ex-Borland

By Vasudev Ram




Firebird is a free and open source relational database based on source code from Borland.

About Firebird

I had first come across Firebird some years earlier. It was good back then: easy to install, easy to use, and fast.

Came across it on the Net again recently.

It can be a good alternative to other free databases like MySQL and PostgreSQL, for smaller applications, particularly where low administration overhead is needed. Also, it is cross-platform to some extent - works on Windows and Linux.

- Vasudev Ram - Dancing Bison Enterprises

Share |

Microsoft to switch back to ODBC from OLE DB, say reports

By Vasudev Ram - dancingbison.com | @vasudevram | jugad2.blogspot.com

Surprising as it may seem, Microsoft may switch back to ODBC from OLE DB.

I read about this a few days ago on the Net.

Here are some relevant links to the news about Microsoft going back to ODBC.

From the Microsoft SQLNCli team blog (I guess SQLNCli stands for SQL Native Client):

Microsoft is Aligning with ODBC for Native Relational Data Access:

http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx

From the blog of Hal Berenson, former Distinguished Engineer and General Manager, and who, in his own words, "was both a godfather of the OLE DB strategy and responsible for the SQL Server implementations that have now been deprecated":

OLE DB and SQL Server: History, End-Game, and some Microsoft "dirt":

http://hal2020.com/2011/09/25/ole-db-and-sql-server-history-end-game-and-some-microsoft-dirt/

Interesting stuff. I had worked some years ago on a middleware product that involved ODBC - that sat on top of ODBC, actually (*). One of its main goals was to improve the performance of ODBC-based client-server applications. (Another goal was a more programmer-friendly API for application programmers working on client-server projects that used ODBC, in Visual Basic as well as C.) The product was a success, and was deployed in several large client-server projects of the company I worked for at the time.

Also, the Java JDBC API and the Perl and Python DBI API's were probably influenced quite a bit by the architecture / design of ODBC. (This is what I think, based on having studied and worked on both ODBC and JDBC a good amount, and some on the Perl and Python DB APIs). It (ODBC) was a pretty good technology for its time, and was very extensively deployed and used (almost universally, in fact, for client-server database applications), during the heyday of the client-server period of computing - though native database drivers were also used a lot then.

Interesting to see that Microsoft is now moving back to it - presumably, to improved versions of it, suited to today's requirements.

(*) If you are wondering how another software layer on top of ODBC could improve performance of ODBC apps, rather than only make it worse (due to more overhead of the extra layer), think a bit more. It may sound counter-intuitive, but is possible - it actually happened.

Posted via email

- Vasudev Ram @ Dancing Bison