[Tex/LaTex] Reading data from a relational database into a LaTeX table

lualuatextables

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.

Best Answer

Let's assume you want this output:

table from sql database

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