[DB-SIG] New take on PostgreSQL bindings for Python

[Ricardo Bugalho]

Hello, 
I think you're mixing up DB-API specs with implementation.
Mostly, the beneficts of what you pointed out could be implemented by
smarter bindings, without changing the DP-API spec. And some already do.

On Tue, 2006-09-05 at 17:39 -0400, Cristian Gafton wrote:
> 
> - bind parameters. The current pythonesque %(name)s specification is not 
> ideal. It requires various levels of pain in escaping the arguments passed 
> to a query. Most database backends accept bind parameters, albeit with 
> different syntaxes. For code portability, I'd rather parse the SQL query 
> and rewrite it to use the proper element for the bound parameter than do 
> the crazy escaping on the user input that is currently being done.

You don't need to change the DB-API for this.
Your binding could take pythonic parametrized queries and coverts them
into PostgreSQL style queries with a bit of string processing.
For example
	cursor.execute("SELECT * FROM table WHERE foo = %(foo)s AND bar =
%(bar)s", locals()) 
could be executed as something like
	result = PQexecParams(...,"SELECT * FROM table WHERE foo = $1 and bar =
$2", ...) 


> - parsed statements. On large loops this is a real gain. For lack of a 
> better specification, I currently use something like:
>  	prepcu = db.prepare("select * from foo where id = $1")
>  	prepcu.execute(2)
> The prepared cursor statement has the same fetchone(), fetchall() and 
> other properties of the regular cursors, except it only accepts parameters 
> to its execute() and executemany() calls.

Explicit prepared statments should be a good addition for the DB-API.
But you can also take advantage of prepared statments by caching query
requests.
In a simple way, you could cache the last statement executed in that
session. Thus, you could implement
	cursor.execute("SELECT * FROM foo WHERE id = %(id)s", locals())
as something like
	// currentStatment = "SELECT FROM foo WHERE id = $1"
	if (strcmp(lastStatment, currentStatement) != 0) then {
		lastStatement = currentStatement
		preparedStatment  = PQprepare(..., " ", currentStatment, ...);	 
	}
	result  = PQexecPrepared(..., " ", ...);

Or you can aim for a more complex caching strategy.

> - server side cursors. Currently, most bindings for most databases have to 
> decide what to do after an cursor.execute() call - do they automatically 
> retrieve all the resulting rows in the client's memory, or do they 
> retrieve it row by row, pinging the server before every retrieval to get 
> more data (hey, not everybody using Oracle ;^). DB API has no support 
> for controlling this in a consistent fashion, even though Python has 
> solved the issue of dict.items() vs dict.iteritems() a long time ago. 
> The application writers should have a choice on how the cursors will 
> behave.

DB-API's cursors have always been supposed to be based server side
cursors. That's the whole point about having cursors.
If some bindings don't use server side cursors when they're available,
it's their own problem.