[DB-SIG] API suggestion: expose 'quote' method
chris at cogdon.org
Tue Jun 3 18:24:38 EDT 2003
On Tuesday, Jun 3, 2003, at 15:06 US/Pacific, Magnus Lyckå wrote:
> I don't see a need for this quote function for building SQL
> statements, and I don't see how it could work cross platform
> as presented, but I think it could be a good way to see what
> is happening under the hood. I would like to have that for
> pure debugging and logging purposes.
Actually, I don't think it would work that well for logging, but... :)
I most certainly have an application where this would make things
easier for me (in fact, I'm already using it, but that's only because
the python drivers I'm using have a callable 'quote' function).
Can we take it as read that there are applications where this would be
No? Oh, okay... here's one. I have an XML interface into my database so
that XML queries on the information can be made. For any of three data
types, a number of optional criteria can be specified to restrict the
information that's queried and sent back to the client. My function
turns XML elements into individual parts of a 'where' clause. For
gets turned into this 'where fragment'
There could be zero or more of these fragments. Each of them is joined
together with 'AND's and then added in as the WHERE clause of the SQL.
Now, this only works because I can call the quote method to build up a
valid fragment. Otherwise, I would have to write my own quoter (even
more error prone than having it in the python driver), or somehow put
?'s in there, and then pass all the parameters I receive down the chain
to be joined up with the full select statement. I don't think it would
take much to realise that this is MUCH messier than being able to
'quote' as the where parts are being built up.
Now... all this only works if the DBMSs that use the '? convention ALSO
support passing literal values (such as 'CZZ') inside the SQL. And, if
that's the case, then these DBMSs also have quoting conventions, and if
that's the case, then I think there should be a function INSIDE THE
DRIVER that does the quoting for us just like most of the PostgreSQL
Sure, in the case of the '?' DBMSes the quoting function wont be used
for most applications, but I've just shown you one that does, and it's
the most efficient way of doing it.
> I don't think your example can work reliably for all platforms
> quite like that. But this might work:
> cur.execute( *db.quote( "some SQL statement", parameters) )
My intention is that the 'quote' function returns a single string, with
all the parameters quoted and embedded into the SQL. It could also work
def trivial_where_generator ():
return db.quote ( "displayname = ?", displayname_value )
cur.execute ( "select * from artists where " +
> What these interfaces will actually do is to send the SQL string
> just as it is, ? and all, down to the ODBC level at least. It will
> convert the mxDateTime object to some suitable format for the
> platform, but it will never worry about how to get it into the SQL
> string, because it never will. After all, both Dynamic SQL and SQL
> CLI mandates that parameter passing with ? should work, so as far
> as I know, there will never be an SQL string with the date value
> stuffed in. The database server will read directly from the parameter
> list when is finds a ? in the SQL, instead of doing the labour of
> extracting a string literal that it perhaps needs to unquote and
> unescape. Seems like less work.
Since that matches with my paraphrase a few emails ago, I agree with
> For every server, there *is* at least one potential SQL string
> which would work in an equivalent way to the SQL with the parameters
> we sent, but there is no reason for any part of the chain between
> the application and the db server to know what that might look like.
Unless, of course, in the particular application its much more
efficient to deal with full SQL strings.
Again, I'm not suggesting that this is a 'good thing' for all
applications, only some :)
> In this particular case it will vary depending on the data source.
> If the database happens to use the MS Jet engine, (i.e. MS Access)
> the SQL could look like "SELECT * FROM T WHERE D = #1999-12-31#",
> but for most other backends it should look like
> "SELECT * FROM T WHERE D = '1999-12-31'". The #-version would only
> work on Jet, and it's the only one that works on Jet. Depending on
> national seetings, what kind of database you have etc, the date
> string might look different, regardless of quote-style. Passed as
> a separate parameter, it might work to send it as a COM Date object
> if we're in Windows.
Which is why I'm asking for it to be a part of the python driver. The
driver knows what type of database it is talking to, so it is perfectly
capable of generating the correct 'string-equivalent SQL' for the
database. For example the following 'proposed' statement:
db.quote ( "select * from T where D=?", date )
would generate a different string depending on what DMBS it is talking
> If the DB-API would try to pass in the parameter into the SQL
> string, it could just guess how to do that, and it is competely
> beyond its "duties".
That's EXACTLY what all of the PostgreSQL APIs do right now. It's their
JOB to figure out how to get those parameters into the SQL string to
send them to the DBMS.
I appreciate that other DBMSes do not need to worry about quoting,
since they can use a different mechanism to get the parameters to the
DBMS. However, I claim that there are applications where 'pre-quoting'
is considerably more efficient for the application, and I'm asking that
the next revision of the API include a method to make a 'embed into SQL
string' function available to the application writer.
> In other words, I would like such a quote function to return
> what it would actually send to the next step in the chain, be
> that ADO or the MySQL client libs. Either just a string, or a
> tuple containing a string and a converted parameter tuple---but
> I'm not sure it's a reasonable thing to ask for in all interfaces.
I'm asking that it be a straight string, since that's easiest to
manipulate in the application. If for some reason the DMBS does NOT
support some literals being passed as a straight string, then an error
be returned. After all, if it can't be 'quoted', then there's no way of
achieving what you want anyway.
("`-/")_.-'"``-._ Chris Cogdon <chris at cogdon.org>
. . `; -._ )-;-,_`)
(v_,)' _ )`-.\ ``-'
_.- _..-_/ / ((.'
((,.-' ((,/ fL
More information about the DB-SIG