[DB-SIG] How to know what to do with parameters...

[Magnus Lyckå]

I would like to suggest that the DB-API will stipulate
that the DBinterfaces should check whether an object has
a method of a certain, standardized name, and use that
method to extract the stringified value to put in the
SQL statement, it the object has such a method. Pysqlite
has such a hook, but "_quote" might not be the best name.
Maybe "_dbapi_string"? Like this:

   if hasattr(value, '_dbapi_string'):
         return value._dbapi_string()

One of Tim Peters Python Zen tenets is:
"In the face of ambiguity, refuse the temptation to guess."
I think this is something to consider.

So, how can a DBI programmer know how to convert data?

Well, if he knows the type or class involved, he can know of
course, and should normally handle that, but as Chris said
it might not be a good thing to fall back on using repr if
we don't recognize the object.

If the interface can't positively indentify the type or
class as something it knows for sure how to handle, it
should require explicit instructions from the application
programmer.

Let's imagine I use a Money class, and my DBI has no
support for that. I think I should be able to tell
the DBI how to present my money values to SQL. I
probably want it to be presented as a decimal number
with two decimals, but I'm not sure that I want
__str__ or __repr__ to display like that. They should
probably both show the currency.

Using C/python format codes is a way of doing that. It
means that the application programmer has to take the
responsibility that his object can properly handle the
format code he chooses to use in his SQL statement.

In this case I could probably implement __float__ and
use %f or even %.2f.

This approach has two problems though:
  1. It doesn't work with ? or :name.
  2. The format codes seems to suggest that it's just like
     the %-operator, and as we've noted it isn't quite so
     for strings, unicode or None at least. Trying to do
     things like %.1s will backfire.

The approach today will be to cast to a string, and that
usually works, but it seems silly to have a numeric value
cast that to a string with quotes around and perform a check
to see if it contains any ' that need to be escaped, and then
send it to the backend which will convert it back to a number.

I just tried a little in MySQL, and I imagine they put some
effort into getting this right, but I still think it's a
bit awkward...and I'm not sure things will always work out
the way we plan.

SELECT * FROM a_table WHERE 0.1 = 0.10000000000000001
will return rows.
SELECT * FROM a_table WHERE 0.1 = '0.10000000000000001'
will also return rows, as will
SELECT * FROM a_table WHERE '0.1' = 0.10000000000000001
but not
SELECT * FROM a_table WHERE '0.1' = '0.10000000000000001'
or
SELECT * FROM a_table WHERE 0.1 = 0.1000000000000001
which has one zero less between the ones.

What if I want to do "WHERE A = B + C" and A = 0.6, B = 0.2
and C = 0.4? :) Perhaps you know that your backend will handle
that by viewing the numbers as exact decimals (MySQL fails,
just like Python).

Passing as float will give you
"WHERE 0.59999999999999998 = 0.20000000000000001 + 0.40000000000000002"
which will fail, and passing as strings with rounded values will
produce quotes, and you get: "WHERE '0.6'='0.2'+'0.4' which will
obviously fail.

I would feel much happier if I could make the DB-API send
"WHERE 0.6 = 0.2 + 0.4" for me. An object with a _dbapi_string
method, and an interface that recognized that would fix this for
me.



--
Magnus Lycka (It's really Lyckå), magnus at thinkware.se
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program