Tuesday, August 20, 2013

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', 'A1@gmail.com')
b_user = User('B', 'B 2', 'B2@yahoo.com')
c_user = User('C', 'C 3', 'C3@hotmail.com')

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

No comments: