[DB-SIG] Towards a single parameter style

[Stuart Bishop]

On Wednesday, February 19, 2003, at 10:59  PM, Kevin Jacobs wrote:

> 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.

Passing the same string to cursor.execute should re-execute the already
prepared statement if it already exists.

class Cursor:
	_prepared_statements = {}
	def execute(sql,args):
		try:
			p = self._prepared_statements[sql]
		except KeyError:
			p = self.prepare(sql)
			self._prepared_statements[sql] = p
		return self._execute_prepared(p,args)

The downside to having this done transparently is you can't free
individual prepared statements attached to a cursor (you can only
'del cursor' to free them all).

>> 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.

There are no new issues if a prepared statement is only usable with the 
cursor
that created it. If this is not the case, however, module.threadsafety
needs to be updated to handle the possible behaviours (can a prepared 
statement
be used in a thread that didn't create it etc.) in the same way that we
define behaviour for Connection and Cursor objects already. This would 
of
course be pointless if there turns out to only be a single common 
behaviour :-)
-- 
Stuart Bishop <zen@shangri-la.dropbear.id.au>
http://shangri-la.dropbear.id.au/