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

[Anthony Tuininga]

A very interesting concept. I think it needs to be extended a little,
however. :-)

It would be nice if any object at all could be bound without requiring
constant conversion -- or rather, writing the code in one place and not
have to write code like this.

cursor.execute(statement, [ ConvertArg(instanceOfMoney) ] )

Perhaps the following would work instead? The object must define the
following functions:

_dbapi_type()

which will return one of the types defined in the DB API and

_dbapi_value()

which will return the value in the format required by the DB API to be
compatible with the type specified above

Then the following would be permitted:

cursor.execute(statement, [ instanceOfMoney ] )

Any thoughts on that? The names are always the hardest thing to agree
on, so if you have a better name, let me know.

On Fri, 2003-05-16 at 10:22, Magnus Lyckå wrote:
> 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 
> 
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig
-- 
Anthony Tuininga
anthony at computronix.com
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com