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

Anthony Tuininga anthony at computronix.com
Fri May 16 17:41:58 EDT 2003

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:


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


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
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838

More information about the DB-SIG mailing list