[DB-SIG] Towards a single parameter style

[Kevin Jacobs]

On Wed, 19 Feb 2003, Stuart Bishop wrote:
> On Tuesday, February 18, 2003, at 11:01  PM, Kevin Jacobs wrote:
> 
> >   connection = connect(...)
> >   cursor = connection.cursor()
> >   command = cursor.prepare(sql)
> >   cursor.execute(command, (arg1, arg2, arg3))
> 
> As this example stands, I don't see what this syntax gains us except an 
> extra needless line.
>      connection = connect(...)
>      command = connection.cursor()
>      command.execute(sql, (arg1, arg2, arg3))

This requires storing a cursor for each prepared command, and still does not
address the main point of prepared statements -- they only make sense if you
call them many times!  DB-API 2.0 provides a simplistic version of this
concept -- executemany -- though it requires that all of the arguments are
available at once, and does not allow any other statements interleaved
between them.

Now that I have thought about it more, the above
would read better as:

   connection = connect(...)
   command = connection.prepare(sql)
   cursor = connection.cursor()
   cursor.execute(command, (arg1, arg2, arg3))


> It also makes things a lot more complex:
> 	- We now have to make sure that both our cursor *and* our
> 	  prepared statements are being used in a thread safe manner.

I don't see this as being a huge implementation hurdle.  Can you clarify
where you see likely pittfalls?  The way I look at it, prepared statements
will have to be used in the context of a cursor, so proper thread safe use
of a cursor will also, by default, protect statements.

> 	- Can my command be used with cursor that didn't create it? Or a cursor
>        created from a different connection?

I would restrict it to a given connection, and cursors derived from it.

> One of your previous examples gave a better justification, where you had a
> 'print statement.description' in between the prepare and execute commands.
> You then gave a reason why this isn't a good idea because 'this syntax
> _requires_ that the backend support prepared statements'.

I still think it is a good idea.  Any backend that does not support prepared
statements can partially emulate a prepared query by creating a wrapper
around a cursor object.  Or maybe we should simply raise NotImplementedError
when prepare() is called for those backends.

> The only use I can see for a PreparedStatement object would be to:
> 	Query it for its 'description'
> 	Execute it
> 
> So would a better alternative be to simply have a new (optional)
> cursor method?:
> 	cursor.fetchDescription(sql) -- returns the cursor.description
> 		sequence for the given statement, without actually executing
> 		it. This method will not be implemented if the backend cannot
> 		retrieve this information without actually executing the statement,
> 		or it may be defined and raise NotImplementedError in this case.

That wouldn't be a bad idea either.  Currently, we have an SQL rewriting
process that adds a LIMIT 0 (or whatever the appropriate backend syntax) to
an existing query to do exactly this.  Having native support for this
operation would be a step in the right direction.

> > So instead of the cursor keeping a reference to command, the command is
> > returned to the user and passed back in as the first argument to 
> > execute.
> 
> Icky. If you go this way, the command should have a reference to the
> cursor IMHO. At which point it becomes apparent that only one object is
> actually desired :-)

Again, no.  The goal is to have multiple commands floating around.  In the
interest of clarity, here is a more realistic use case:

  connection = connect(...)
  commands = getSQL()
  commands = [ connection.prepare(sql) for sql in commands ]
  descrs   = [ command.description for command in commands ]

  cursor = connection.cursor()
  while 1:
    op = getNextOperation()
    i,args = decodeOp(op, descrs)
    cursor.execute(commands[i], args)

This is a little contrived, since our real code doesn't dispatch commands in
a single loop.  They're spread out in a hierarchy of object and triggered by
data-driven events.  In some tests using native (vs. DB-API) interfaces, we
obtain a 2.4x speedup by using prepared queries against a local database.

-Kevin

-- 
--
Kevin Jacobs
The OPAL Group - Enterprise Systems Architect
Voice: (216) 986-0710 x 19         E-mail: jacobs@theopalgroup.com
Fax:   (216) 986-0714              WWW:    http://www.theopalgroup.com