Reading data from a relational database into a LaTeX table

Question

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.

Answer 1

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}

(The example is taken from the luasql home page.)

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).

Answer 2

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.

Answer 3

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.

Answer 4

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