Обсуждение: 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

On Fri, Sep 28, 2012 at 2:32 AM, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:

> Meanwhile, if anybody wants to play with it, it is available at
> <https://gist.github.com/3797401>.

Sorry, wrong url: https://gist.github.com/3797445

-- Daniele

On Fri, Sep 28, 2012 at 02:32:42AM +0100, Daniele Varrazzo wrote:

> 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

Maybe check the received query string against the already
prepared query string and on match reuse the prepared query ?

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Fri, Sep 28, 2012 at 11:33 AM, Karsten Hilbert
<Karsten.Hilbert@gmx.net> wrote:
> On Fri, Sep 28, 2012 at 02:32:42AM +0100, Daniele Varrazzo wrote:
>
>> 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
>
> Maybe check the received query string against the already
> prepared query string and on match reuse the prepared query ?

Yes, it could make easier to drop it into already existing code.

-- Daniele