[DB-SIG] paramstyles (mysql string length?)

Chris Clark Chris.Clark at ingres.com
Thu Apr 20 22:53:36 CEST 2006

My vote - +1 for making qmark mandatory and everything else optional

My ramblings -

M.-A. Lemburg wrote:
> Markers such as %i and %s could be treated as meaning: bind
> these variables as integer and string. This implicit type
> information might be vital to some modules.
I have to confess I couldn't figure out from the v2 spec if this was 
implied or not for pyformat - I've certainly seen dbi users (e.g. 
Django), using %s irrespective of the Python or DBMS type; relying on on 
the host DBMS to then coerce, e.g. into an integer column.

The way I read the spec was that the constructors in the "Type Objects 
and Constructors" section in pep 249 are to be used to show the 
destination type. E.g.:

    x.execute("select ?", (dbmodule.NUMBER(z),) )

I'm definitely a +1 for making qmark mandatory and everything else 
optional. Question marks are standard in SQL across a few api's, e.g. 
ODBC, embedded (sql) C, JDBC, etc.

Gerhard suggestion of example code for parsing is a good one, but 
someone would have pony up some code :-) Some drivers are pure C (so 
they would need pure C code) and others use some Python code and they 
would prefer Python so this (apparently) simple request may not get 
fulfilled :-(

(in a later email) M.-A. Lemburg wrote:
> Actually, I don't think that parsing SQL is really necessary
> at all: in all the years I've used qmark style, I've never come
> across a situation where a SQL literal would include a question
> mark.
> In reality, it all boils down to doing a simple search for
> '?' in the string - after all, you usually pass strings in via
> bound parameters.
For most real world applications I think this is a reasonable 
assumption, however there is always a an edge case and I wouldn't be 
surprised if some one on the list says, "Hang on! I've got some dynamic 
queries with string literals containing question marks". But for initial 

    sql_query = "select foo from bar where a = ? and b like '%slugs'"
    new_sql_query = sql_query.replace('%', '%%') # deal with embedded 
percent signs
    new_sql_query = new_sql_query.replace('?', '%s')  # will NOT deal 
with embedded qmarks in string literals!
    ## what ever pyformat driver currently does for pyformat queries

is probably a resonable short term testing solution until a more robust 
solution is found for drivers that don't natively support parameter binding.


More information about the DB-SIG mailing list