# Reading data from a relational database into a LaTeX table

I'm using Tex Live 2009 on Debian squeeze. I can upgrade if I really, really have to, but I'd rather not. i was wondering if it would be possible to do the following.

Given some data in a (relational) database, can I extract it and automatically format it in a LaTeX table, so one would not need to manually recreate the LaTeX table if the data changes? Could one do such a thing with Lua? One would normally use a suitable language database adaptor for this, and I see Lua has LuaSQL. I'd like to use PostgreSQL. I'm currently using version 8.4.

Solutions not involving Lua are also fine. Just to be clear, I don't know anything about Lua and my knowledge of LaTeX programming is also extremely skimpy. I'm looking for a recipe which I can adapt if necessary. While this is not really important for my current use case, it seems like a useful general technique if possible.

-
Since you proably want to use LuaTeX, I strongly recommend you install the latest TeXLive 2011. – Martin Schröder Mar 15 '12 at 23:22
@MartinSchröder: Well, it looks like Norbert has now put packages of Tex Live 2011 in experimental, so I'll give it a try. – Faheem Mitha Mar 16 '12 at 16:58
That's actually TL2012 beta. :-) – Martin Schröder Mar 16 '12 at 18:20
@MartinSchröder Is there anything in particular one need to watch out for? – Yiannis Lazarides Mar 17 '12 at 8:50

Let's assume you want this output:

If you use LuaTeX, the most innovative way is to include the luasql bindings in your program.

First create a simple database (createdb.txt):

CREATE TABLE people(
name  varchar(50),
email varchar(50)
);
INSERT INTO "people" VALUES('Jose das Couves','jose@couves.com');
INSERT INTO "people" VALUES('Manoel Joaquim','manoel.joaquim@cafundo.com');
INSERT INTO "people" VALUES('Maria das Dores','maria@dores.com');


and fill it with sqlite3 luasql-test < createdb.txt

The next step is to create a simple LuaLaTeX document and read the file into the table:

\documentclass{article}
\usepackage{luacode,booktabs}
\begin{document}
\begin{luacode*}
require("luasql.sqlite3")

env = assert (luasql.sqlite3())
-- connect to data source
con = assert (env:connect("luasql-test"))
-- retrieve a cursor
cur = assert (con:execute"SELECT name, email from people")
-- print all rows, the rows will be indexed by field names
row = cur:fetch ({}, "a")
tex.sprint([[\begin{tabular}{@{}ll@{}}\toprule]])
tex.sprint([[Name & email \\\midrule]])
while row do
tex.sprint(-2,row.name)
tex.sprint("&")
tex.sprint(-2, row.email)
tex.sprint("\\\\")
-- reusing the table of results
row = cur:fetch (row, "a")
end
tex.sprint([[\bottomrule\end{tabular}]])
-- close everything
cur:close()
con:close()
env:close()
\end{luacode*}
\end{document}


I have skipped the worst part: compiling and installing. This is highly system dependent, but I will list some steps/pitfalls.

• On MacOS X, you can't use the sqlite3.so from luasql directly, because the luatex binary misses some symbols. So you need to compile your own non-stripped luatex binary (./build.sh --nostrip) and use that instead. It needs to be placed at the "original" version in the tex tree. If you have done that, you can use sqlite3.so from the luasql project. The same might be true on other unix systems. See the entry 666 in the bug tracker.
• Create a directory called luasql in your document directory and place the generated library there.
• You need LuaTeX version > 0.46.0. Therefore texlive 2011 might be the best choice.
• I have downloaded the source distribution of luasql and compiled it myself. I don't know if the luarocks based installation can be used as well (probably it can).
-
I've just heard that on linux it works fine with luarocks (luasql/mysql). The paths have to be set, but otherwise it worked out of the box. – topskip Mar 16 '12 at 12:01
A problem is how to point luatex to the lua module installation. I see there are a couple of question, namely tex.stackexchange.com/questions/14361/… and tex.stackexchange.com/questions/42878/…, but they don't help me. Like the latter questioner, I'm using Debian. – Faheem Mitha Mar 16 '12 at 23:31
I have not been able to use luasql-sqlite3 module, but I have succeeded in using the lsqlite3 module. – nplatis Sep 29 '13 at 20:49

The following code was motivated by a discussion in the chatroom. The idea here is to leave the data retrieval and processing to an external entity, set a .tex template that will hold the data, populate it and generate the final .tex file.

This is what template engines basically do. Although we can exploit Lua (see Patrick's great answer), sometimes you might rely on just a .tex template for the sake of simplicity. :)

Here we go, the code is fractionated to include some comments on what's happening in each part. I opted to code a small script in Python and rely solely on hardcoded data and template to ease the process.

from xml.dom import minidom
from Cheetah.Template import Template


I decided to stick with some data expressed in the XML format because it was just a plain example of how to get data and incorporate it to TeX. Of course, the way we acquire content is irrelevant now, since we are just interested on how the data object is represented in our script. :)

I used minidom to read the XML excerpt and the Cheetah template engine to help me produce the resulting .tex file. For now, those are just imports, we will discuss implementation in the next steps.

def getNodeValue(node, key):
return node.getElementsByTagName(key)[0].childNodes[0].nodeValue

def getData(source):
a = []
b = minidom.parseString(source)
c = b.firstChild
for d in c.childNodes:
if d.nodeType == 1:
e = {}
e['name'] = getNodeValue(d, 'name')
e['surname'] = getNodeValue(d, 'surname')
a.append(e)
return a


Those two functions were written just to help me traverse the XML content and fetch the data I want to use. The result will be an array containing dictionaries in the form

{
'name' : 'John',
'surname' : 'Doe',
}


extracted from the following XML, represented in the very own Python code as a raw string (note the r prepended in the string definition):

xml = r"""<?xml version="1.0" ?>

<persons>

<person>
<name>Paulo</name>
<surname>Cereda</surname>
</person>

<person>
<name>Enrico</name>
<surname>Gregorio</surname>
</person>

<person>
<name>David</name>
<surname>Carlisle</surname>
</person>

</persons>"""


Now, let's start the fun part. Once we have everything ready, let's read the XML content, parse it and create the array. I do this by calling the getData function defined previously. Just to be fancy, I apply a sorted function to the resulting array providing a lambda function that sets the order by the name key:

data = sorted(getData(xml), key=lambda y: y['name'])


The data variable contains an array of dictionaries. Now let's create our .tex template, pretty much along the same lines we did with our XML file, that is, through a raw string:

template = r"""\documentclass{article}

\usepackage[T1]{fontenc}
\usepackage[utf8]{inputenc}
\usepackage{siunitx}

\begin{document}

\begin{tabular}{llS}
\hline
\hline
#for $person in$data
$person['name'] &$person['surname'] & $person['grade']\\ #end for \hline \end{tabular} \end{document}"""  The template is pretty straightforward, it's a simple .tex file which will give us a tabular environment. The interesting part is: #for$person in $data$person['name'] & $person['surname'] &$person['grade']\\
#end for


This part is part of the Cheetah language description. Simply put, this is a for loop where we iterate through all dictionaries in the data array. Note that $ is used as a placeholder and not as math mode, as we are used in TeX. Don't worry with them, they'll be gone once we merge everything. :) Since we are dealing with a dictionary, we access values from its keys, so $person['name'] will return the person's name. :)

Now, let's create the template from the source above and add the data array associated with a key, and print the template. I should output the merging to a new file, but for now, let's just print the resulting code to the terminal:

definition = Template(source=template, searchList=[ { 'data': data } ])
print(definition)


And we are done. :)

Let me post the code as a whole, just for easy copy and paste:

from xml.dom import minidom
from Cheetah.Template import Template

def getNodeValue(node, key):
return node.getElementsByTagName(key)[0].childNodes[0].nodeValue

def getData(source):
a = []
b = minidom.parseString(source)
c = b.firstChild
for d in c.childNodes:
if d.nodeType == 1:
e = {}
e['name'] = getNodeValue(d, 'name')
e['surname'] = getNodeValue(d, 'surname')
a.append(e)
return a

xml = r"""<?xml version="1.0" ?>

<persons>

<person>
<name>Paulo</name>
<surname>Cereda</surname>
</person>

<person>
<name>Enrico</name>
<surname>Gregorio</surname>
</person>

<person>
<name>David</name>
<surname>Carlisle</surname>
</person>

</persons>"""

data = sorted(getData(xml), key=lambda y: y['name'])

template = r"""\documentclass{article}

\usepackage[T1]{fontenc}
\usepackage[utf8]{inputenc}
\usepackage{siunitx}

\begin{document}

\begin{tabular}{llS}
\hline
\hline
#for $person in$data
$person['name'] &$person['surname'] & $person['grade']\\ #end for \hline \end{tabular} \end{document}""" definition = Template(source=template, searchList=[ { 'data': data } ]) print(definition)  In order to run this code, we need a Python interpreter (mine is Python 2.7.5) and the Cheetah template engine. I'm almost sure you can install it via your favourite package manager, say # yum install python-cheetah  or probably via pip: pip install Cheetah  Let's run the code now, shall we? :) Allons-y! paulo@alexandria ~$ python mycode.py
\documentclass{article}

\usepackage[T1]{fontenc}
\usepackage[utf8]{inputenc}
\usepackage{siunitx}

\begin{document}

\begin{tabular}{llS}
\hline
\hline
David & Carlisle & 8.8\\
Enrico & Gregorio & 8.9\\
Paulo & Cereda & 3.6\\
\hline
\end{tabular}

\end{document}


The resulting .tex file produces the following output:

And that's it. :)

-
Works flawlessly. Nice work. – Faheem Mitha Jan 8 '14 at 22:56

TeX dbi
River Valley Technology publishes a free software called TeX dbi. I have never used it, but according to the description at the home page, the software

enables TeX to talk to SQL compliant database engines and generate beautiful reports.

I even found an article describing TeX dbi, see TUGboat, Volume 23 (2002). The program can be downloaded from Sarovar (TeX dbi). The file at Sarovar seems pretty old, so I recommend to contact authors to verify that this is the last version.

SQLTeX
At the CTAN Catalogue you will find a product called SQLTeX. I have neither used that, but according to the introduction in the readme-file:

SQLTeX is a preprocessor to enable the use of SQL statements in LaTeX. It is a perl script that reads an input file containing the SQL commands, and writes a LaTeX file that can be processed with your LaTeX package.

-
I don't see where one can get TeX dbi from. Do you have a link for this? – Faheem Mitha Mar 17 '12 at 8:08
@FaheemMitha I have added a link to sarovar.org/projects/texdbi in the reply. – Sveinung Mar 17 '12 at 9:52
Excellent, thanks. – Faheem Mitha Mar 17 '12 at 9:53

This is rather a complimentary answer to the other answers provided by other posters. The major issue with the approach you are requesting is how do you enter the data from a practical point of view. If you are going to be doing SQL inserts you might as well be writing TeX macros.

INSERT INTO "people" VALUES('Jose das Couves','jose@couves.com');
INSERT INTO "people" VALUES('Manoel Joaquim','manoel.joaquim@cafundo.com');
INSERT INTO "people" VALUES('Maria das Dores','maria@dores.com');


can be more difficult than:

\R people,Jose das Couves,jose@couves.com;
\R people,Manoel Joaquim,manoel.joaquim@cafundo.com;
\R people,Maria das Dores,maria@dores.com;


Another very promising approach is to create your own special markup with the interpreter, package which I consider as one of the current developments that might edge (la)TeX in a better direction.

If your records are as simple as that, you can use an approach as described in http://tex.stackexchange.com/a/19761/963, it can be less typing work, your records will survive for another thirty years and will not be depended on any other technology other than (La)TeX. The entries will also be edited in your favorite editor.

If the records are more complicated the TeX approach might be more involved, cumbersome and error prone. In this case your proposed method has an advantage.

I would start with getting some GUI tools to make the entry of the records easier. In http://stackoverflow.com/questions/3332250/windows-gui-tool-for-sqlite3 there are links to quite a few of them (I tried 2-3) and so far Lita seems like a good tool. Personally I would opt for a slightly different GUI and even Database tool. I would rather use MySQL and a browser.

Installation can be as easy as installing WAMP and MySQL and you can have a great interface going in a few minutes for creating a Relational Database. WAMP comes prepackaged with phpMyAdmin and sqlbuddy. The latter is less mature but currently my tool of choice. Similar tools exist for Python, Ruby, Perl and possibly Lua via the Kepler project.

I have tried hard to get sqlite3 and wxLua to work together with pdfLuaLaTeX and although I haven't given up yet, I haven't managed to achieve it. I have narrowed down the issue though and is probably path settings via kpathsea. If you do get them right please let us know your installation steps.

There is also the issue of exporting the data back to LuaLaTeX and the format to be used. My own experience so far is that for most typesetting type of problems the database schema is not relational and you better off with either a purely TeX solution or an XML type of solution (aka look at ConTeXt). For simple relational databases the webfront solution works very well; I have got one that does letters, stores metadata for the correspondence, picks up addresses etc. and exports LaTeX.

For simpler structured data (such as exports from lab experiments), simple CSV solutions with pgfplotstable and other CSV LaTeX tools are to be preferred.

-
Thanks for the thoughtful and comprehensive response, Yiannis. The use case I have in mind is for plotting numerical values, where the values are generated by a computer program. In this case one would not be manually writing SQL inserts. However, writing the data from the database to a csv file and the importing the csv file into LaTeX is certainly a feasible (and less hi-tech) approach. I can't at the moment see any compelling disadvantages to using the csv approach, except that it adds a layer of indirection and corresponding overhead. – Faheem Mitha Mar 17 '12 at 8:06
@FaheemMitha I agree with you that if the data are generated by computer your approach is the better choice. If you developed the software, it might be worth to cut the middle layer and export straight the pgfplots commands and data or whatever else you using. – Yiannis Lazarides Mar 17 '12 at 8:10
As I mentioned in a comment to Patrick's post, I haven't got the lua db approach to work yet. The problem is that luatex doesn't (out of the box on Debian) know where the lua module installations are. I think this is something that should work out of the box for a OS, that, like Debian, provides both a lua installation as well as luatex. I hope to follow up on this further, and will update my question if I do. I'd be interested to hear more details about what difficulties you have faced. – Faheem Mitha Mar 17 '12 at 8:12
@FaheemMitha I had exactly the same issues like you except on a Windows machine. One normal way to by-pass the issue is to drop the modules into the same working directory as well. However, in this case since the modules are looking for the Lua installation path they did not work. When I get some time I will try and add them to the lualibs package and see what happens. There are also .dll files or .so in your case. These might need recompiling and a knowledge of C++ (this is possibly why Patrick's installation worked, he recompiled). – Yiannis Lazarides Mar 17 '12 at 8:20
Hi Yiannis. Thanks for the clarification. I believe the correct way to resolve this is to tell luatex where the modules are located. This should be possible, but I don't know how. I assume that either the Tex Live or the luatex project should be able to give directions. I have not been able to find any documentation except the tex.sx questions I linked to, though. – Faheem Mitha Mar 17 '12 at 8:43

If you're able to create a CSV from your database, then I would recommend using the datatool package

-
In my experience, datatool quickly makes compiling the document horribly slow. – Thomas Arildsen Feb 6 '14 at 9:59