[DB-SIG] paramstyles (mysql string length?)
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
(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
> 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
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