[DB-SIG] Proposed improvements to DBAPI 2.0Cursor.execute() method.

[Ricardo Bugalho]

On Mon, 2006-11-13 at 19:42 +0200, jekabs.andrusaitisa wrote:
> On other hand the difference between how different DBAPI modules
> handle bind
> variables is indeed quite annoying, it prevents abstraction of the
> query
> code from underlaying database, but only solution which comes to my
> mind

I fully support the idea of having a single (or a single set) of
paramter styles that are to be supported by *all* the DB-API compliant
instead of the current state of affairs.

> would be adding "Pythonized parameter style" support for each database
> module which would convert Python style to whatever underlaying
> database
> actually works with, for example for Oracle it would do:
> 
> "SELECT something FROM somewhere WHERE
> somethingelse=%(somethingelse)s",{"somethingelse":"huh"}
> ---- super duper argument mangler ---->
> "SELECT something FROM somewhere WHERE somethingelse=:1",["huh"]

However, as I've said in another message, I think it's best *not* to
avoid format and pyformat paramter styles in the DB-API, because they
just keeps getting confused with string manipulation.

Just about any other format it better than those two. My favorite option
is to support on both numeric (:1) and named (:name) styles.

> 
> However actually implementing this would be no simple matter - bind
> variable
> processing goes much deeper than simple string mangling I am afraid,
> SQL
> lexical parser would be required for this sort of translation... 

It shouldn't be any harder than implementing parameter binding for
backends which don't actually support parameterized queries.
Of course, this is for a given level of robusness and the modules for
such backends aren't as robust as some of us would like...

But overall, I think it could be done without much work.
IMHO, all .execute() needs to do before replacing parameter style is to
check it's not replacing the contents of a literal or a quoted
identifier.

That is, it must not convert 
	"SELECT * FROM my_table WHERE real_name = 'foo:bar' 
		and user_name = :username" 
into 
	"SELECT * FROM my_table WHERE real_name = 'foo?' 
		and user_name = ?"

In case the user writes something he shouldn't, like
	"SELECT * FROM :table_name WHERE user_name = :user_name"

it's ok if .execute() silently converts it into
	"SELECT * FROM ? WHERE user_name = ?"

because when it's passed to the backend, the back end will reject it.

Or am I missing any case in SQL syntax?