Subscribe to out mailing list Google group:


Printing in sqlkit uses the oootemplate module that is distributed along with sqlkit but could be used in a totally independent way and does not depend in any way on other parts of sqlkit. template

This module makes it possible to use an oasis .odt document as a template to produce documents via the use of an openoffice server (i.e. an istance of openoffice that listens for connections by clients).

It uses the uno module that comes with openoffice. In the following image you can see how the template looks before rendering and how the pdf produced looks. The red circle stresses the marker that makes the template produce many lines for each input line: one for each object in context.


This rendering can be obtained by code very similar to this example

The template

An openoffice writer document (.odt extension) can be used as template. No particular templating skills are required by the template designer, just ability to create an openoffice document and a list of variable’s names that must be used. Clearly this list must be provided by the programmer to the designer.


oootemplate will substitute variables values where needed.

Dear Mr. $ $user.last_name,

Today $date we  received an order for $n books:

|Title                        |Author          |N. pieces |$currency |
| ++$title                    |$author         |$qty      |$price    |

This is an example of a template rendered as possible with ascii art.

There are 2 different substitutions that can be done:

  1. simple substitutions ($user, $date, $currency, above - yellow in the image)
  2. multiline substitutions (the book entries above, starting with ++ - green in the image)

the first refers to substitution of a single value that is already present in the document, the second refers to the insertion of several rows according to a list of fields that are probably in a table.

The variables defined in the table should be repeted in loop for each book provided in the context, that implies an increment of the number of rows of the table. The expected output resembles what follows:

Dear Mr. Alessandro Dentella,

today June, 2 2008, we received an order of 2 books:

|Title                        |Author          |Copies    |Euro      |
| Q                           |Luther Blisset  |1         |10        |
| Il sistema periodico        |Primo Levi      |2         |8         |

As for any template system a special syntax is needed to allow people to create loops. We are constrained to what can be done by a simple user of openoffice (no programming at all) so we choose to use a MULTI_LINE_MARKER in the default form of a ‘++’ (red circle in the image) .

To make things more complicated it’s clear that the person that created the template (the .odt document), may have used a table just for formatting reason so that substitution of type a. can be in an openoffice table or not.


Substitution of type a. above are done using search and replace functionality of openoffice API, while substitution of type b. are implemented as a loop on table’s rows and cell in 2 different ways: one that preserves the style of each word of the cells and one that doesn’t. The former uses getTrasferable() method on each cell and is slower.

You can switch from one to the other setting Template’s attrribute Template.preserve_styles

The pattern used to detect what is a table can be customized. While the default is ‘$’ as in the shell or in python template system (and perl and php...) since you may have the ‘$’ symbol in your document you may want to customize it. See Template’s method set_pattern.


The context is the object that contains the mapping variable/value to be used. It needs an argument -a dict- that holds the values.

oootemplate allows you to have more that one tables in your document. That means you can implement easily things as:

Dear Mr. $ $user.last_name,

you can order these books at 20% discount

|Title                        |Author          |N. pieces |$currency |
| ++$title                    |$author         |$qty      |$price    |

or these books at 50% discount

|Title                        |Author           |N. pieces |$currency |
| ++$title                    |$author          |$qty      |$price    |
|Title                        |Author    | Extra|N. pieces |$currency |
| ++$title                    |$author   | $x   |$qty      |$price    |

In this example we have 3 lines that start with ++, that means that will be conseidered prototipes for new lines. For each of these 3 rows there need to be a list of objects (books in the example) in the context.

Since Openoffice-org tables have names, we wil use that name as a key in the context for the value. That’s enought for the first table (20% discount) not for the second where we have 2 lists in a table. To cope with this case, we can put as value a dict with entry an integer that indicates the position starting from 1 (see example below)

name mapping

Occasionally the name of the variable will be too long to fit in the space that you want to allocate. You can translate a shorter name to the real name in the dict context.translate (see example in the demo), blue circle in the image.

This way you can hide the complexity of some variable name. Note that you can translate both $rs.manager.address in $addr or $director.last_name in $d.last_name.


All output accepted by openoffice filters, you’re probably using .odt or .pdf


A tipical sample code is:

import ooootemplate as oo

tmpl = oo.Template('/tmp/mytemplate.odt', server='', port=2002)
context = oo.Context({
    'user' : user,
    'date' : date(2008, 6, 2),
    'currency' : 'Euro',
    'Table1' : (book1, book2, ...)  # lazy assignement (simple tuple)
    'Table2' : (
         (book21, book22, ...),     # correct assignement (list of tuples)
         (book31, book32, ...),




class sqlkit.misc.oootemplate.Context(content, lazy=True)

A context used to render a template. It contains both the data and the way to substitute variable with the data.

__init__(content, lazy=True)
  • content – a dict with keys the variable names
  • lazy

    boolean. If True (default) invokes _implement_lazy_tables to allow the list of objects for a table rendered to be directly set as value of the table_name entry (rather than a list of lists). See example Table1 is lazy Table2 is not.

    The goal is to prevent common errors rather than promoting lazy writing. When you only have a single list of objects you may easily forget that you may have more than one.

    The assumption is that you don’t normally have lists as values of context (other that for tables). While probably true, should you need lists as contetxt values, you can just set lazy=False.


class sqlkit.misc.oootemplate.Template(filename, server='', port=8100, headless=False, oo_context=None, preserve_styles=False)

The class template that connects to a server (or starts a local one), read a document, parses the document to find tables

__init__(filename, server='', port=8100, headless=False, oo_context=None, preserve_styles=False)

only the filename is needed if the server is local. If we already have a template on the same server we can reuse the oo_context

Params filename:

the template filename in the server’s filesystem

  • server – a server name
  • port – the port to connect to
  • headless – if False, disables the headless mode.
  • oo_context – the openoffice context (not to be confused with oootemplate.Context). The oo_context` plays the role of the connection to the server
  • preserve_styles – use TemplateWithStyles to enforce preservation of styles in each word of the cell when duplicating rows

substitute all the variables with values from context

Parameter:context – the Contex instance to be used
save_as(filename, local=None)

save the template using save_as capability of openoffice.

Parameter:filename – filename in the server‘s filesystem. The extension is used to detect the file type as in regular openoffice use.
set_pattern(pattern, oo_pattern)

Set the pattern to detect what is a variable to be substituted

  • pattern – the pattern with syntax suitable for module re module. It must define at least 2 groups: var_name and match pointing respectively to the name of the variable and the whole match. Default is (?P<match>\$(?P<var_name>[^ ]+))
  • oo_pattern – the pattern with syntax suitable for openoffice regexp search. It can only use the openoffice syntax. Default is $[^ ]+
the pattern for python variable detection. it’s a regular expression read set_pattern for details
the pattern for openoffice variable detection. read set_pattern for details
the pattern used to tel when a multiline line starts. Defaults to ++
the openoffice document from the server
the connection with the server. This can be reused between templates
the openoffice SearchDescriptor
Preserve style of each word in the cell when cloning rows. This will slow down the process so it’s disabled by default


class sqlkit.misc.oootemplate.Table(oo_table, template)

table object detail on the API exposed by uno:

__init__(oo_table, template)
  • oo_table – the openoffice table object
  • template – the oootemplate.template object in which this table is
class sqlkit.misc.oootemplate.TableWithStyles(oo_table, template)

A Table that clones rows preserving style info even if several different styles are used within the same cell. This process is slower so it’s not active by default: set Template’s arg preserve_styles to True to enable it (see ex. N. 5).

__init__(oo_table, template)
  • oo_table – the openoffice table object
  • template – the oootemplate.template object in which this table is