Re: problems w/ pyPgSQL and the Enterprise HOWTO

Sean Riley <sean@xxxxxxxxxxxxxxxxx> wrote:
> [...] When you pass parameters to a SQL operation with pyPgSQL is has
> absolutely no effect on performance, so you might as well just build the
> entire SQL statement youself and pass it in as a single string.
> 
> from PgSQL.py:
> 
> def execute(self, query, *parms):
>       .
>       _qstr = query
>       .
>       self.res = self.conn.conn.query(_qstr % parms)

That's because PostgreSQL started supporting prepared statements only very
recently (in 7.3). I pondered adding support for them in pyPgSQL, but
unfortunately to do so, I need to know the *PostgreSQL* type of the
parameters in advance, which is not possible in the general case.

Nothing's stopping you from using PREPARE and EXECUTE directly from
pyPgSQL, though:

cursor.execute("""
    PREPARE my_query (int4, numeric) AS
        SELECT A, B, C FROM TABLENAME WHERE X=$1 AND Y=$2
        """)

then, later on call the prepared statement:

cursor.execute("EXECUTE my_query (%s, %s)", (4, 5))

-- Gerhard