Making dynamic data available via ODBC with Python

[Erwin S. Andreasen]

I have a Python application server that manages several different data
sets, permits various reports to be run on them and the data to be
downloaded as tab-delimetered files, all via a web interface.

I'd like to explore the possibilities of making the data directly
available in Windows applications (such as Excel, where it would very
nice to directly do pivot tables from the live data) via ODBC.

So I basically need for my Python server (deployed on UNIX) to act as
a remotely queriable database. This is live and changing data, so I'd
prefer not to have to dump it into a real database first, but I
suppose that's one solution (e.g. letting PostgreSQL get the query,
run some trigger that asks my server to update the table with data and
then return the data).

Knowing little about the various available ODBC modules, one solution
seems to be to take a client/server protocol of an existing database
(such as PostgreSQL) and implement a Python server for it: so my
Python program acts like a server you could run the psql client up
against and so also get data via ODBC using the PG ODBC drivers. It
seems a bit complex because all I want to allow is to select a table
with a certain name, allow a query of what columns are available and
what type they are and to fetch the data, perhaps with minimal support
for filtering queries.

I've seen at least one program that emulates the PG client/server API
-- I think it stored some configuration data, so it acted like a
ordinary PG server you could run queries against but wasn't really
one. Its name completely escapes me at the moment though.

Is there some better way of doing this -- some way to provide tabular
data remotely to Windows machines, usable in Office programs, which
happens to already have some Python code written for it? Looking
around, there is at least one XML-to-ODBC bridge but encoding the
entire data file as XML is likely to have prohibitive overhead.

 

[Diez B. Roggisch]

Is there some better way of doing this -- some way to provide tabular
data remotely to Windows machines, usable in Office programs, which
happens to already have some Python code written for it? Looking
around, there is at least one XML-to-ODBC bridge but encoding the
entire data file as XML is likely to have prohibitive overhead.

I still think that this is the most viable solution - if performance is bad,
you could still explore other possibilities.

If it actually is performing bad - either literally or due to a badly
design - I'd go for creating a odbc2your-server driver. That would allow
you to cope with a pretty well defined standard (odbc) and doing the
network stuff totally on your own behalf instead of trying to make some
self-written protocol stack like postgres so compatible that a postgres
odbc driver doesn't know the difference.