[Pypgsql-users] pyPgSQL bug in use of mx.DateTime

[Karsten H. <Kar...@gm...>]

Hello pyPgSQL team,

when pyPgSQL is used with the locale "de_DE" under Python
2.4 it has a subtle bug. Basically, when a timestamp is
written into a timestamp column an error is raised. It
claims "invalid format for timestamp", IOW, PostgreSQL
cannot parse the value as a timestamp. The reason is that
there is a "," between the seconds and microseconds part of
the value instead of a dot (.). How does this come about ?

The _quote() function of pyPgSQL simply does

 "'%s'" % value

to mx.DateTime.DateTime instances. The Python reference says
that %s invokes str() on any Python object. Now, str() on a
DateTime from mx invokes __str__ on it which does this:

return "%04d-%02d-%02d %02d:%02d:%05.2f" % (
	self.year,
	self.month,
	self.day,
	self.hour,
	self.minute,
	self.second
)

Now, in a German locale the decimal point isn't represented
by a dot. It is represented by a comma (","). Hence

 '%05.2f' % 27.77

ends up being

 '27,77'

(which PG cannot parse) instead of

 '27.77'

(which PG *can* parse).

The proper solution, IMO, is to explicitely format
mx.DateTime instances into something PG is guarantueed to
understand inside pyPgSQL._quote().

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346