Prepared statements [was: Session state per transaction]

On Thu, Sep 27, 2012 at 8:21 AM, Federico Di Gregorio <fog@dndg.it> wrote:

> Preparing statements is useful only in a limited set of scenarios and we
> should give the user as much control over them as possible. I'd use an
> opaque object that keeps a reference to the connection and to the
> prepared statement (in a sense it is just a smart cursor/connection
> proxy, all the logic will continue to be there). The opaque object
> should expose at least execute() and executemany() variations taking
> only the parameters and expose the .connection and .cursor for
> everything else (or we can proxy more than that...) You'll do:
>
> prep = conn.prepare("INSERT INTO bla VALUES (%s, %s)")
> prep.execute(("foo", "bar"))
> prep.execute(("baz", "gaz"))
> prep.connection.commit()

I think pretty much everything on the cursor is useful: you can also
select from a prepared statement, so you need fetch*, description...

I've played a little bit with the idea and made a cursor subclass
instead: it does everything like a normal cursor, but has a prepare()
method. If it is called, execute() and executemany() can be called
without a query, in which case they execute the statement stored (the
cursor can have only one, but several cursors can be used to prepare
several statements). If execute*() receive a query too they behave
like normal cursors (execute() does the normal execution,
executemany() calls prepare() internally to speed up its job).

I'm not planning to push it into psycopg now: I'd like to have people
playing with it as we may find a different design could work better.
Meanwhile, if anybody wants to play with it, it is available at
<https://gist.github.com/3797401>.

Any feedback is well accepted, cheers,

-- Daniele