Generating SQL queries in Python

[Thomas Weholt]

Interesting issue. In relation to n-tier modelled projects this is vital. In
my current attempt at something like this I've put all my SQL-statements
in a seperate python  module like ordinary strings, putting different
%(var)s into the string to indicate parts of the statement to be replaced at
run-time.
Then I do a from sql_statements import * and in my module using the
SQL-statements:

sql = myOwnSQLStatement % dict_with_data
db.query(sql)

I use PostgreSQL too and this works like a sharm. Don't know of any
overheader etc. so that would be interesting.
At least it's better than hardcoding SQL into your python code. It's also
easier to send the file to a SQL-guru/non-python hacker to
optimize the SQL without having to read alot of code too.

Please feel free to inform me if you find any other method doing this
better. My ultimate goal is to be able to easily use other databases which
in turn supports different parts of the SQL-standard.

Thomas


"Karl Schmid" <schmid at ice.mpg.de> wrote in message
news:9efq5k$17d9$1 at gwdu67.gwdg.de...
> 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.
>
>         Karl
>
>
>
> --
> ----------------------------------------------------------------------
> Dr. Karl Schmid
> Max-Planck-Institute for Chemical Ecology
> Carl-Zeiss-Promenade 10                         Tel: +49 3641 / 643658
> 07745 Jena                                      Fax: +49 3641 / 643668
> Germany                                       Email: schmid at ice.mpg.de
> ======================================================================