Generating SQL queries in Python

[Frank Miles]

In article <9efq5k$17d9$1 at gwdu67.gwdg.de>,
Karl Schmid  <schmid at ice.mpg.de> wrote:
>Hi,
>
>I am currently writing a python program that heavily interacts with SQL 
>databases (PostgreSQL). The SQL queries are kind of hard-coded at many 
>places in the program and need to be changed when the database structure 
>changes (or when a different database system is used, e.g. MySQL).
>
>I was wondering whether there is a solution available in python that allows 
>to introduce an additional layer of functions for generating SQL queries so 
>that the code can be more easily adapted to changes in the database 
>structure or system.
>
>Any information on modules or documentation dealing with this problem is 
>welcome.

A postgres-specific way of handling (at least part of) this problem is to
implement pl/pgsql functions that either perform the necessary SQL operations
directly, or if necessary (e.g. returning tuples) have the pl/pgsql functions
generate the SQL strings, which are executed via python/(pygresql or popy).

If the database structure is changed, you need to change the pl/pgsql 
functions.  The main advantage is that these functions can be used by
any calling program, regardless of language.  The main problem is that
debugging pl/pgsql functions can be a PITA.

Another alternative for handling changes that are less drastic is to use a 
schema-extractor program that generates field offsets, and import the
offsets into the python program.  Clearly this is less flexible than
the pl/pgsql approach, but may be sufficient in some cases: it's adequate
when tables are changed by adding fields (simply re-run the schema-extractor
program), but if data are moved between tables, and the SQL statements
are highly tuned it's not.

	-frank
--